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

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