Add Windows OS version to Windows programs (Data Lake) query

Question

Hi all, 
 I'm trying to get the Windows OS version from the query "Windows programs (Data Lake)" query provided by Sophos. I have been looking the schema but I'm unable to get the information. 
 Please, see the query below and let me know if it is possible to modify it in order to get the OS version info. 
 SELECT meta_hostname AS ep_name, name, version, language, install_source, publisher, identifying_number, install_date FROM xdr_data WHERE query_name = 'windows_programs' 
 
 Thank you and regards, 
 Luis Miguel.

IsmailJaweed · Accepted Answer

Hi Luis, 
 Thank you for posting. The correct category to view is under " Device: Hardware and operating system details" 
 
 Here is the query SELECT
 system_info.hostname,
 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_version.name AS os_name,
 os_version.version AS os_version,
 os_version.build,
 os_version.platform,
 os_version.patch,
 uptime.days AS uptime_days,
 uptime.hours AS uptime_hours,
 uptime.minutes AS uptime_minutes,
 uptime.total_seconds AS uptime_seconds,
 time.timezone AS system_timezone,
 time.timestamp AS system_formatted_timestamp,
 time.datetime AS system_current_time
FROM system_info
CROSS JOIN os_version
CROSS JOIN uptime
CROSS JOIN time

IsmailJaweed · Answer

I have created a custom query for you that pulls all the columns from "programs" table and join all the columns from "os_version" table. SELECT
 programs.name,
 programs.version,
 programs.install_location,
 programs.install_source,
 programs.language,
 programs.publisher,
 programs.uninstall_string,
 programs.install_date,
 programs.identifying_number,
	os_version.name AS os_name,
 os_version.version AS os_version,
 os_version.build,
 os_version.platform,
 os_version.patch
	
	
FROM programs
CROSS JOIN OS_version 
 This should help

IsmailJaweed · Answer

Hi Luis, After thorough research, I was able to create a custom query for you. 
 Check this out. 
 SELECT
meta_hostname AS ep_name,
meta_endpoint_type AS type,
meta_os_platform AS OSPlatform,
meta_os_name AS OSName,
meta_os_version AS OSVersion,
name AS ProgramName,
version AS ProgramVersion,
language AS ProgramLanguage,
install_source AS InstallSource,
publisher AS ProgramPublisher,
identifying_number,
install_date As InstallDate
FROM xdr_data
where query_name= 'windows_programs' Unfortunately, we do not have an os build column in this xdr_data schema. However, we got the os version, os name and the platform. Hope this helps

Add Windows OS version to Windows programs (Data Lake) query

Top Replies