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
Parents

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

Comment

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

Children
No Data
Unfiltered HTML