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

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

Hi Ismail,

Thank you for your reply, I may have not been explained well.

I'm not looking for a query that reports the characteristics of every device, but how to get another column with the OS version along with the Windows programs.

Kind regards,
Luis Miguel.

Hi Luis, 

You can create a custom query based on your requirement and run using the Endpoint Queries tab. To be able to do that, you need to enable Designer Mode and run your query against the endpoints.

Take a look at the below help page. It describes steps on how to create and run custom queries.
https://docs.sophos.com/central/customer/help/en-us/ManageYourProducts/ThreatAnalysisCenter/LiveDiscover/EditOrCreateQuery/index.html

Hint: You can use JOIN in your query to add the required column from the required table. 

Hope this helps.

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

Hi,

Thank you very much for your help, much appreciated.

I think that way I wouldn't obtain the host name, anyway I have tried to run your query and this is the result:

403 : "{"error":"forbiddenAccess","message":"Access denied for table","correlationId":"1b70334b-dd7f-468a-a4f7-2dfed324d6ea","code":null,"createdAt":"2022-09-12T11:43:29.755Z","requestId":null,"docUrl":null}"

Now having a look at the schema, I can't find a table xdr_data, where could I see it?

I have tried to join xdr_data and os_version without success until now.

Thank you again and regards,

Luis Miguel.

Hi Luis,

Thank you for reaching out. 

Let me explain. 

We have two places to run the queries:
The first place is in the Data Lake and the next is running the query against a Live Endpoint as seen in the below screenshot.

Now, in the query I shared, we have two tables named "OS_version" and "programs".
These tables are available to include in our query to run only against a Live endpoint.
This can be verifed in the link below where you can get to see all the available tables.

https://osquery.io/schema/4.9.0

These tables are referred to as OSQuery schema. 

Now, to run a query in Sophos Data Lake, we have to choose tables that are available explicitly for Data Lake.
For endpoint data to be fetched from the data lake, we have a very limited number of tables available which can be verified in the below link.

https://docs.sophos.com/central/References/schemas/index.html?schema=xdr_schema_docs

Now, the query I shared in my previous post is to run against a Live Endpoint where you can choose an Endpoint by selecting the appropriate operating systes as seen in the below screenshot.







If you refer to the data lake schema link above, you could see that we do not find any table that has os_version in it which means, unfortunately, we do not have os_version schema in the data lake.

However, we could fetch the installed programs list by using the table "windows_programs" from the data lake schema.

By now, I hope I am clear with the explanation and hope this helps.


Thank You. 

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'

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'

Sure, it helps a lot, thank you very much!

And also thank you for your previous explanation. The thing is that I thought that we could query the same schema for Live Endpoint and Data Lake, I was unable to find such an explanation in all the documentation I read (and I think I have read quite a lot).

Very much appreciated. Kind regards,
Luis Miguel.

Its a pleasure assisting you Luis

Have a nice day.