New Sophos Support Phone Numbers in Effect July 1st, 2023

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