Browse Live Discover and Response Queries by Category
Approved

Windows PCs inventory asset discovery info

Hi, I've been working on this for a few days.
I know there are a few of these already on the forum, but thought I'd share in case anybody found this one useful.

SELECT
/*User section*/
logged_in_users.user User_Name,
/*System Info*/
system_info.cpu_brand,
system_info.cpu_type,
system_info.physical_memory,
system_info.hardware_vendor,
system_info.hardware_model,
system_info.hardware_serial,
/*OS info*/
os_version.name os_name,
os_version.version os_version,
os_version.build os_build,
os_version.arch os_arch
FROM system_info JOIN os_version
JOIN logged_in_users

This works fine for the online machines obviously. Not for the offline ones.

Unfortunately after a few times we start getting machines that answer with no results sent back.
Does anybody know why that would be, and how to fix it?

Thanks!

Diego Tavolari
  • in reply to Diego Tavolari

    Also if you want to get a nice readable number for memory, you can change the line 8: system_info.physical_memory, for the following:

    printf("%.2f", CAST(system_info.physical_memory AS FLOAT)/1024.0/1024.0/1024.0) 'Memory(GB)',

    That should make your memory column look nicer. If anybody knows how to round it up instead of down, that would be great to know ;)

    Enjoy!

  • in reply to Fabrice B

    Thanks Fabrice! I will look into it. But I suspect that it will not have the complete OSQuery schema included into it, so my query won't work out of the box with Data Lake.

  • Here is a revised version of the same script that will give you a column with a windows 10 version instead of build:

    SELECT
/*User section*/
   logged_in_users.user User_Name,
--   source,
/*System Info*/
   system_info.cpu_brand,
   system_info.cpu_type,
   system_info.physical_memory, 
   system_info.hardware_vendor, 
   system_info.hardware_model,
   system_info.hardware_serial,
/*OS info*/
   os_version.name os_name,
   os_version.version os_version,
   os_version.build os_build,
   case WHEN os_version.build = 19043 THEN '21H1'
        WHEN os_version.build = 19042 THEN '20H2'
        WHEN os_version.build = 19041 THEN '2004'
        WHEN os_version.build = 18363 THEN '1909'
        WHEN os_version.build = 18362 THEN '1903'
        WHEN os_version.build = 17763 THEN '1809'
        WHEN os_version.build = 17134 THEN '1803'
        WHEN os_version.build = 16299 THEN '1709'
        WHEN os_version.build = 15069 THEN '1703'
        WHEN os_version.build = 14393 THEN '1607'
        WHEN os_version.build = 10586 THEN '1511'
        WHEN os_version.build = 10240 THEN '1507'
   END AS Win_version,
   os_version.arch os_arch
FROM system_info JOIN os_version
JOIN logged_in_users

    It would be great if we could do something like this on the XDR datalake. Unfortunately I haven't found the way yet. I think Datalake has it's own schema?

Unfiltered HTML