Restructure network information from the Syscollector tables

[chemamartinez]

Due to normalize reasons, the network interfaces information is stored by Syscollector in three different tables from one single JSON event collected by Syscollector.

Here we have an interface event before decode it.

{
  "type": "network",
  "ID": 1033023093,
  "timestamp": "2018/08/06 05:24:24",
  "iface": {
    "name": "ens33",
    "type": "ethernet",
    "state": "up",
    "MAC": "00:0C:29:CA:61:E9",
    "tx_packets": 95564,
    "rx_packets": 207439,
    "tx_bytes": 18989283,
    "rx_bytes": 127573249,
    "tx_errors": 0,
    "rx_errors": 0,
    "tx_dropped": 0,
    "rx_dropped": 0,
    "MTU": 1500,
    "IPv4": {
      "address": [
        "192.168.1.65"
      ],
      "netmask": [
        "255.255.255.0"
      ],
      "broadcast": [
        "192.168.1.255"
      ],
      "gateway": "192.168.1.1",
      "DHCP": "enabled"
    },
    "IPv6": {
      "address": [
        "fe80::20c:29ff:feca:61e9"
      ],
      "netmask": [
        "ffff:ffff:ffff:ffff::"
      ],
      "DHCP": "enabled"
    }
  }
}

This information is stored in the tables sys_netiface, sys_netaddr and sys_netproto using reference IDs. So, the API retrieves the information from the separated tables as follows:

# curl -u foo:bar "localhost:55000/syscollector/000/netiface?pretty"
{
   "error": 0,
   "data": {
      "totalItems": 1,
      "items": [
         {
            "name": "ens33",
            "tx": {
               "packets": 95564,
               "errors": 0,
               "bytes": 18989283,
               "dropped": 0
            },
            "scan": {
               "id": 1033023093,
               "time": "2018/08/06 05:24:24"
            },
            "rx": {
               "packets": 207439,
               "errors": 0,
               "bytes": 127573249,
               "dropped": 0
            },
            "mac": "00:0C:29:CA:61:E9",
            "mtu": 1500,
            "state": "up",
            "type": "ethernet",
            "id": 5
         }
      ]
   }
}
# curl -u foo:bar "localhost:55000/syscollector/000/netaddr?pretty"
{
   "error": 0,
   "data": {
      "totalItems": 2,
      "items": [
         {
            "broadcast": "192.168.1.255",
            "scan_id": 1033023093,
            "proto": "ipv4",
            "address": "192.168.1.65",
            "netmask": "255.255.255.0",
            "id": 5
         },
         {
            "id": 5,
            "scan_id": 1033023093,
            "address": "fe80::20c:29ff:feca:61e9",
            "netmask": "ffff:ffff:ffff:ffff::",
            "proto": "ipv6"
         }
      ]
   }
}
# curl -u foo:bar "localhost:55000/syscollector/000/netproto?pretty"
{
   "error": 0,
   "data": {
      "totalItems": 2,
      "items": [
         {
            "scan_id": 1033023093,
            "iface": "ens33",
            "dhcp": "enabled",
            "type": "ipv4",
            "gateway": "192.168.1.1",
            "id": 5
         },
         {
            "dhcp": "enabled",
            "scan_id": 1033023093,
            "iface": "ens33",
            "type": "ipv6",
            "id": 5
         }
      ]
   }
}

This behavior doesn't allow the App to print a table with the network interfaces information due to it comes from different API queries. Is that why it would be necessary to restructure the data when reading it from the DB to get something similar to the JSON event coming from the agent.

[JuanjiJG]

We would also need to unify the outputs from packages and so API calls. The Wazuh app needs the same JSON output format regardless of the agent's Operating System.

If an agent doesn't have a specific value for some property, return an empty string.

Regards, Juanjo

[mgmacias95]

The following DB request should be used in order to retrieve all necessary information at once:

# sqlite3 /var/ossec/queue/db/000.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> select * from sys_netiface, sys_netproto, sys_netaddr  where sys_netiface.name = sys_netproto.iface and sys_netiface.scan_id = sys_netproto.scan_id and sys_netproto.type = sys_netaddr.proto and sys_netaddr.scan_id = sys_netproto.scan_id group by sys_netproto.type, sys_netiface.name;
1565191792|2018/09/27 09:16:18|enp0s3||ethernet|up|1500|08:00:27:51:40:EB|1029|1879|80930|2192819|0|0|0|0|1565191792|enp0s3|ipv4|10.0.2.2|enabled|1565191792|ipv4|192.168.185.3|255.255.255.0|192.168.185.255
1565191792|2018/09/27 09:16:18|enp0s8||ethernet|up|1500|08:00:27:00:69:88|8728|9071|1814245|3706736|0|0|0|0|1565191792|enp0s8|ipv4|unknown|enabled|1565191792|ipv4|192.168.185.3|255.255.255.0|192.168.185.255
1565191792|2018/09/27 09:16:18|enp0s3||ethernet|up|1500|08:00:27:51:40:EB|1029|1879|80930|2192819|0|0|0|0|1565191792|enp0s3|ipv6||enabled|1565191792|ipv6|fe80::a00:27ff:fe51:40eb|ffff:ffff:ffff:ffff::|
1565191792|2018/09/27 09:16:18|enp0s8||ethernet|up|1500|08:00:27:00:69:88|8728|9071|1814245|3706736|0|0|0|0|1565191792|enp0s8|ipv6||enabled|1565191792|ipv6|fe80::a00:27ff:fe51:40eb|ffff:ffff:ffff:ffff::|