search

wazuh / wazuh-agent

Wazuh agent, the Wazuh agent for endpoints.
GNU Affero General Public License v3.0
32 stars 18 forks source link

Refactor network interfaces and protocols inventory tables #284

Open vikman90 opened 2 weeks ago

vikman90 commented 2 weeks ago

Parent Issue: #292

The current network inventory separates interfaces and protocols, since each network interface may have zero or many IP addresses.

erDiagram
    INTERFACE ||--o{ PROTOCOL : has
    PROTOCOL ||--|{ ADDRESS : has

Now, since we're synchronizing this data with Indexer (which is not an SQL database), we need to merge both tables into one, repeating the data originally stored in the interfaces table.

Take into account that, since the relationship is zero-or-many to one, we must ensure that there is at less one entry for each network interface, even if it has no IP addresses.

Depends on

LucioDonda commented 2 days ago

Unification proposal:

From the three different tables the idea is to merge them into a single one using the iface name as the join column:

Details

``` CREATE TABLE network_iface ( name TEXT, adapter TEXT, type TEXT, state TEXT, mtu BIGINT, mac TEXT, tx_packets INTEGER, rx_packets INTEGER, tx_bytes BIGINT, rx_bytes BIGINT, tx_errors INTEGER, rx_errors INTEGER, tx_dropped INTEGER, rx_dropped INTEGER, checksum TEXT, item_id TEXT, PRIMARY KEY (name,adapter,type)) WITHOUT ROWID; ```

e.g: name adapter type state mtu mac tx_packets rx_packets tx_bytes rx_bytes tx_errors rx_errors tx_dropped rx_dropped checksum item_id db_status_field_dm
docker0 ethernet down 1500 02:42:ed:22:2b:f3 0 0 0 0 0 0 0 0 d753a90819890c4b483b9992fd771a97819bdd4e c3cbf3edb7c5565edb919ccb2475845270839642 1
ens18 ethernet up 1500 bc:24:11:b6:7a:77 32607 60680 2999221 181271149 0 0 0 36 6910fe0a5f8796627e32862825ba8ab94149b0fd 9b382e78f7e63538f5bd83b5a3e1f1fea96fc345 1
Details

``` CREATE TABLE network_protocol ( iface TEXT, type TEXT, gateway TEXT, dhcp TEXT NOT NULL CHECK (dhcp IN ('enabled', 'disabled', 'unknown', 'BOOTP')) DEFAULT 'unknown', metric TEXT, checksum TEXT, item_id TEXT, PRIMARY KEY (iface,type)) WITHOUT ROWID; ```

e.g: iface type gateway dhcp metric checksum item_id db_status_field_dm
docker0 ipv4 unknown 0 9bc59886dee4f7760636e99608e1abc6d6348f7e a0fc268492c048c704d5c3a831f350bcecf08c94 1
ens18 ipv4 192.168.0.1 unknown 0 6164afb881d131134df0caa3ac7c5462adc2da80 0b428995564855f49bb202a65fa0f17a760ebf93 1
ens18 ipv6 192.168.0.1 unknown 49e3d966920b462e943efbe0d7e7bb41e64a9c06 aa4b490f5363fd582dc742418f2f4f2403312625 1
Details

``` CREATE TABLE network_address ( iface TEXT, proto INTEGER, address TEXT, netmask TEXT, broadcast TEXT, checksum TEXT, item_id TEXT, PRIMARY KEY (iface,proto,address)) WITHOUT ROWID; ```

e.g: iface proto address netmask broadcast checksum item_id db_status_field_dm
docker0 0 172.17.0.1 255.255.0.0 172.17.255.255 2f9f42164aec68e8b3504cb8ba2b3a199b559bc9 eefb562b10b85a1d263d5894634dc6781b4b9eea 1
ens18 0 192.168.0.142 255.255.255.0 192.168.0.255 1cff0d20a7ff24e92846454a5bb3eab9e6b177f2 003ef7b3d91467fc62045c45844df5180b8ec28b 1
ens18 1 fe80::be24:11ff:feb6:7a77 ffff:ffff:ffff:ffff:: 13434d738c58280c51813f5b9ae08d0b344787f4 1000b104882396fef452d2aea96e760090f0e1a0 1

Result of unifying the 3 cases

Details

``` CREATE TABLE network ( iface TEXT, adapter TEXT, iface_type TEXT, state TEXT, mtu BIGINT, mac TEXT, tx_packets INTEGER, rx_packets INTEGER, tx_bytes BIGINT, rx_bytes BIGINT, tx_errors INTEGER, rx_errors INTEGER, tx_dropped INTEGER, rx_dropped INTEGER, iface_checksum TEXT, iface_item_id TEXT, proto_type TEXT, gateway TEXT, dhcp TEXT NOT NULL CHECK (dhcp IN ('enabled', 'disabled', 'unknown', 'BOOTP')) DEFAULT 'unknown', metric TEXT, proto_checksum TEXT, proto_item_id TEXT, address TEXT, netmask TEXT, broadcast TEXT, addr_proto INTEGER, addr_checksum TEXT, addr_item_id TEXT, PRIMARY KEY (iface, adapter, iface_type, addr_proto, address) ) WITHOUT ROWID; ```

e.g: iface adapter iface_type state mtu mac tx_packets rx_packets tx_bytes rx_bytes tx_errors rx_errors tx_dropped rx_dropped iface_checksum iface_item_id proto_type gateway dhcp metric proto_checksum proto_item_id address netmask broadcast addr_proto addr_checksum addr_item_id
docker0 ethernet down 1500 02:42:ed:22:2b:f3 0 0 0 0 0 0 0 0 d753a90819890c4b483b9992fd771a97819bdd4e c3cbf3edb7c5565edb919ccb2475845270839642 ipv4 unknown 0 9bc59886dee4f7760636e99608e1abc6d6348f7e a0fc268492c048c704d5c3a831f350bcecf08c94 172.17.0.1 255.255.0.0 172.17.255.255 0 2f9f42164aec68e8b3504cb8ba2b3a199b559bc9 eefb562b10b85a1d263d5894634dc6781b4b9eea
ens18 ethernet up 1500 bc:24:11:b6:7a:77 32607 60680 2999221 181271149 0 0 0 36 6910fe0a5f8796627e32862825ba8ab94149b0fd 9b382e78f7e63538f5bd83b5a3e1f1fea96fc345 ipv4 192.168.0.1 unknown 0 6164afb881d131134df0caa3ac7c5462adc2da80 0b428995564855f49bb202a65fa0f17a760ebf93 192.168.0.142 255.255.255.0 192.168.0.255 0 1cff0d20a7ff24e92846454a5bb3eab9e6b177f2 003ef7b3d91467fc62045c45844df5180b8ec28b
ens18 ethernet up 1500 bc:24:11:b6:7a:77 32607 60680 2999221 181271149 0 0 0 36 6910fe0a5f8796627e32862825ba8ab94149b0fd 9b382e78f7e63538f5bd83b5a3e1f1fea96fc345 ipv6 192.168.0.1 unknown 49e3d966920b462e943efbe0d7e7bb41e64a9c06 aa4b490f5363fd582dc742418f2f4f2403312625 fe80::be24:11ff:feb6:7a77 ffff:ffff:ffff:ffff:: 1 13434d738c58280c51813f5b9ae08d0b344787f4 1000b104882396fef452d2aea96e760090f0e1a0
LucioDonda commented 38 minutes ago

Update 22/11

From this case:


{
    "iface": [
        {
            "IPv4": [
                {
                    "address": "172.17.0.1",
                    "broadcast": "172.17.255.255",
                    "dhcp": "unknown",
                    "metric": "0",
                    "netmask": "255.255.0.0"
                }
            ],
            "adapter": "",
            "gateway": " ",
            "mac": "02:42:1c:26:13:65",
            "mtu": 1500,
            "name": "docker0",
            "rx_bytes": 0,
            "rx_dropped": 0,
            "rx_errors": 0,
            "rx_packets": 0,
            "state": "down",
            "tx_bytes": 0,
            "tx_dropped": 0,
            "tx_errors": 0,
            "tx_packets": 0,
            "type": "ethernet"
        },
        {
            "IPv4": [
                {
                    "address": "192.168.0.141",
                    "broadcast": "192.168.0.255",
                    "dhcp": "unknown",
                    "metric": "0",
                    "netmask": "255.255.255.0"
                }
            ],
            "IPv6": [
                {
                    "address": "fe80::be24:11ff:fe54:83fb",
                    "broadcast": "",
                    "dhcp": "unknown",
                    "metric": "",
                    "netmask": "ffff:ffff:ffff:ffff::"
                }
            ],
            "adapter": "",
            "gateway": "192.168.0.1",
            "mac": "bc:24:11:54:83:fb",
            "mtu": 1500,
            "name": "ens18",
            "rx_bytes": 653336088,
            "rx_dropped": 154,
            "rx_errors": 0,
            "rx_packets": 731620,
            "state": "up",
            "tx_bytes": 303676094,
            "tx_dropped": 0,
            "tx_errors": 0,
            "tx_packets": 638612,
            "type": "ethernet"
        }
    ]
}

We get this json:


{
    "networks": [
        {
            "adapter": "",
            "address": "172.17.0.1",
            "broadcast": "172.17.255.255",
            "dhcp": "unknown",
            "gateway": " ",
            "iface": "docker0",
            "iface_type": "ethernet",
            "mac": "02:42:1c:26:13:65",
            "metric": "0",
            "mtu": 1500,
            "netmask": "255.255.0.0",
            "network_checksum": "cc10013383f0c7e9dbe8adc809edd8562854ca76",
            "network_item_id": "fae3b9f3354ce8946db29fbf6c1811172ec77ea9",
            "proto_type": "ipv4",
            "rx_bytes": 0,
            "rx_dropped": 0,
            "rx_errors": 0,
            "rx_packets": 0,
            "state": "down",
            "tx_bytes": 0,
            "tx_dropped": 0,
            "tx_errors": 0,
            "tx_packets": 0
        },
        {
            "adapter": "",
            "address": "192.168.0.141",
            "broadcast": "192.168.0.255",
            "dhcp": "unknown",
            "gateway": "192.168.0.1",
            "iface": "ens18",
            "iface_type": "ethernet",
            "mac": "bc:24:11:54:83:fb",
            "metric": "0",
            "mtu": 1500,
            "netmask": "255.255.255.0",
            "network_checksum": "42953299ec1227367718861a7fe65d4a750c1a32",
            "network_item_id": "4a99254163997c30e63092675ee715c332a58b7e",
            "proto_type": "ipv4",
            "rx_bytes": 664044729,
            "rx_dropped": 154,
            "rx_errors": 0,
            "rx_packets": 785555,
            "state": "up",
            "tx_bytes": 321044806,
            "tx_dropped": 0,
            "tx_errors": 0,
            "tx_packets": 689763
        },
        {
            "adapter": "",
            "address": "fe80::be24:11ff:fe54:83fb",
            "broadcast": "",
            "dhcp": "unknown",
            "gateway": "192.168.0.1",
            "iface": "ens18",
            "iface_type": "ethernet",
            "mac": "bc:24:11:54:83:fb",
            "metric": "",
            "mtu": 1500,
            "netmask": "ffff:ffff:ffff:ffff::",
            "network_checksum": "872208a226d963159fe2b312f49b54683552c5b0",
            "network_item_id": "e86c97ff99df2ceb70f6cff90668a6552d2eb8f7",
            "proto_type": "ipv6",
            "rx_bytes": 664044729,
            "rx_dropped": 154,
            "rx_errors": 0,
            "rx_packets": 785555,
            "state": "up",
            "tx_bytes": 321044806,
            "tx_dropped": 0,
            "tx_errors": 0,
            "tx_packets": 689763
        }
    ]
}