Windows PCs inventory asset discovery info

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?

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?

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