Under Review

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!

  • 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!

  • 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.

  • Hi Diego, 

    You can find the schema for data lake by clicking on following link Sophos Endpoint Data Lake Schema - Files - EDR Data Lake EAP - Sophos Community

  • 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?