Title
#sql
w

WS

02/23/2021, 7:33 PM
does anyone have a query to pull the mac address of the default network interface for windows + mac
spookerlabs

spookerlabs

02/23/2021, 7:41 PM
I guess something like this
select mac FROM interface_details WHERE interface = (select interface FROM routes WHERE destination = '0.0.0.0');
works
7:44 PM
basically get interface from routes table with default destination (
0.0.0.0
) and so query interface_details for this interface
w

WS

02/23/2021, 7:45 PM
gotcha, getting an error on that query specifically,
Query Execution Failed : Scalar sub-query has returned multiple rows
i follow the logic though
spookerlabs

spookerlabs

02/23/2021, 7:46 PM
this query returns more then one interface ? select interface from routes WHERE destination = "0.0.0.0";
w

WS

02/23/2021, 7:47 PM
yea for sure, 105k rows
7:48 PM
10.0.0.26
172.20.10.7
10.85.15.115
utun1
en0
en0
utun1
utun1
...
spookerlabs

spookerlabs

02/23/2021, 7:50 PM
but all destination to 0.0.0.0 ?
7:51 PM
select * from routes; Which return do you have ?
8:00 PM
I dont think going to work at Windows since interface name from interface_details is different from routes
8:01 PM
Just checked now. I tested at Linux before and worked. Sorry about that
f

fritz

02/23/2021, 8:08 PM
If you wanted to take a more naive/brittle approach you could try the following, it's far from perfect but might get you closer:
SELECT interface, mac, platform, 
CASE 
  WHEN platform = 'windows' THEN MAX(dhcp_lease_obtained)
  WHEN platform = 'darwin' THEN MAX(ibytes)
  END AS max_column
FROM interface_details, os_version;
w

WS

02/23/2021, 8:09 PM
hah interesting, i was wondering how one might infer based on the available fields
f

fritz

02/23/2021, 8:11 PM
this has some pretty crappy pre-baked assumptions which could lead to mixups such as using MAX(ibytes) which could be wrong if you recently switched from LAN wired connection to wifi.