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_archFROM system_info JOIN os_versionJOIN 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!
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.