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

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.



Message was not very well written.
[edited by: Luis Miguel Suarez Perez at 12:27 AM (GMT -7) on 6 Sep 2022]
Parents
  • 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

    Ismail Jaweed Ahmed (Ismail) 
    Senior Professional Service Engineer

  • 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.

    Ismail Jaweed Ahmed (Ismail) 
    Senior Professional Service Engineer

  • 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 Slight smile

    Ismail Jaweed Ahmed (Ismail) 
    Senior Professional Service Engineer

  • 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. 

    Ismail Jaweed Ahmed (Ismail) 
    Senior Professional Service Engineer

  • 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 Slight smile

    Ismail Jaweed Ahmed (Ismail) 
    Senior Professional Service Engineer

Reply
  • 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 Slight smile

    Ismail Jaweed Ahmed (Ismail) 
    Senior Professional Service Engineer

Children