Tranquil IT Forum

Published: **June 22, 2022 - 11:37**

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

Published: **June 22, 2022 - 5:38 PM**

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);

Published: **June 23, 2022 - 1:16 PM**

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

Published: **June 29, 2022 - 10:28 PM**

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" <> '') ;

Tranquil IT Forum

[RESOLVED] MAC Address Match with Iface

[RESOLVED] MAC Address Match with Iface

Re: Match MAC Address with Iface

Re: Match MAC Address with Iface

Re: Match MAC Address with Iface