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?

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

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.

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.