Refactor network interfaces and protocols inventory tables

[vikman90]

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

Loading

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

• Agree indexer definition with @wazuh/devel-indexer-leaders.
  • Create ECS compliant index templates  wazuh-indexer#270 (comment)

[LucioDonda]

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:

• network_iface
  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 , PRIMARY KEY (name,adapter,type)

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

• network_protocol
  iface , type , gateway , dhcp , metric , checksum , item_id , PRIMARY KEY (iface, pe)

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

• network_address
  iface , proto , address , netmask , broadcast , checksum , item_id , PRIMARY KEY (iface, oto, dress)

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

• network
  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 , PRIMARY KEY (iface, adapter, iface_type, addr_proto, address)

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