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