[RESOLVED] MAC Address Match with Iface

[gavit]

Good morning,

In an 802.1.X project, I would need to retrieve all the MAC addresses of my computer network.
More specifically, the MAC address of the Ethernet card and the WIFI card.

The problem is that PCs have up to 38 MAC addresses.
I would need to list them all and put them next to their card name (enps, eth, etc...)
But we cannot do:

Code: Select all

 host_info->'networking'->*->'mac' as mac,

How can I proceed?

Given that my code currently looks like this:

Code: Select all

select
distinct
host_info->'networking'->0->'mac' as mac,
host_info->'networking'->0->'iface' as iface,
computer_name
FROM hosts

Sincerely,
VITTAZ Gaëtan

[htouvet]

Good morning,
To make "advanced" queries on JSON, in the manner of XPath for XML, you need to add the "jsquery" module to PostgreSQL.

On Debian, to install it: (if postgresql 13 is installed)

Code: Select all

apt install postgresql-13-jsquery 
sudo -u postgres psql wapt -c "CREATE EXTENSION jsquery;"

Then the SQL query:

Something like this:

Code: Select all

SELECT
  computer_fqdn,
  t.*
FROM
  hosts,
  jsonb_to_recordset(host_info->'networking') as t
    (mac text, iface text);
                                

[gavit]

Good morning,

Thank you very much for the quick response, the SQL query works perfectly!
For MAC and Linux computers, there are no problems mounting.
But for Windows PCs, I have the following feedback:

• [ {6858AE84-3B6D-4D45-8D00-B9AFAA8D578E} (example)/list]
  
  I tried to adapt the query for the WMI table, therefore for Windows clients

  Code: Select all

  SELECT
    computer_fqdn,
    t.*
  FROM
    hosts,
    jsonb_to_recordset(wmi->'Win32_NetworkAdapter') as t
      (MACAddress text, ProductName text);
      

  
  This does not retrieve the information; the MACAddress and ProductName fields remain empty.
  
  Do you have a solution for reinstalling Windows PCs?
  Or a solution that would retrieve the correct data in one request without having to make one request for Windows and another request for Linux/MAC.
  
  Sincerely,
  VITTAZ Gaëtan

[sfonteneau]

Not far!

Code: Select all

SELECT
  computer_fqdn,
  t.*
FROM
  hosts,
  jsonb_to_recordset(wmi->'Win32_NetworkAdapter') as t
    ("MACAddress" text, "ProductName" text);          

So, for example:

Code: Select all

SELECT
  computer_fqdn,
  t.*
FROM
  hosts,
  jsonb_to_recordset(wmi->'Win32_NetworkAdapter') as t("MACAddress" text, "ProductName" text)
  WHERE t."ProductName" !~* 'vpn'
  AND t."ProductName" !~* 'bluetooth'
  AND t."ProductName" !~* 'Virtual Adapter'
  AND t."ProductName" !~* 'WAN Miniport'
  AND t."ProductName" !~* 'TAP-Windows Adapter V9'
  AND t."ProductName" !~* 'VirtualBox Host-Only Ethernet Adapter'
  AND t."ProductName" !~* 'XenServer PV Network Device'
  AND t."ProductName" !~* 'RAS Async Adapter'
  AND (t."MACAddress" <> '') ;