New Sophos Support Phone Numbers in Effect July 1st, 2023

Browse Live Discover and Response Queries by Category
Approved

This query is one you are recommended to read the full content of the post to use. It is not simply a copy and paste query, like others in the forum. It is valuable in the right situation.

Add username to Windows Programs query

Hello everyone,

I need help with a simple query as I'm not well versed in SQL. Basically this is the query:

SELECT
    name,
    version,
    install_location,
    install_source,
    publisher,
    install_date,
    identifying_number
FROM programs
Where name LIKE '%CAD%'

The only problem is that I also need to show the user in the results, which I can't seem to do because that column doesn't exist in "FROM programs"
Is there anyone who can help out with this?

Kind regards,
Mago

Inactive MUZ

  • Hello Mago,

    As long as the program's installation event is generated and present in Event Viewer -> Application this query would shows all the details you need.

    *If the program installed didn't use MsiInstaller or it used a method that doesn't utilize the event logs, then you won't be able to find the events.

    SELECT 
  swe.datetime,
  REPLACE(REPLACE(json_extract(data,'$.EventData.Data'),'(NULL)',''),',','') Program_Name,
  users.username,
  swe.user_id
FROM 
  sophos_windows_events swe JOIN users ON swe.user_id = users.uuid
WHERE
  swe.provider_name = 'MsiInstaller' 
  AND swe.eventid IN (1033,11707)

Unfiltered HTML