Get an Inventory of all installed applications

For query assistance, please see the following Best Practices guide

Below is a query that will list all installed applications, the publisher, application name, and version number. It performs some nice counting so you don't have to deal with a long list of duplicates.  You can quickly search for rare applications, and narrow down to just applications from a particular vendor, or specific version of an application.

-- Generate an inventory of installed applications on windows
-- VARIABLE  $$Application Name$$     STRING
-- VARIABLE  $$Application Version$$  STRING
-- VARIABLE  $$Host Name$$            STRING
-- VARIABLE  $$Publisher Name$$       STRING

WITH Counted_Apps AS (
   WITH App_List AS (
      SELECT DISTINCT meta_hostname, name, version, publisher, install_date
      FROM xdr_data
      WHERE query_name = 'windows_programs' AND name > '' AND
         LOWER(name) LIKE LOWER('%$$Application Name$$%') AND LOWER(version) LIKE LOWER('%$$Application Version$$%') AND 
         LOWER(publisher) LIKE LOWER ('%$$Publisher Name$$%') AND LOWER(meta_hostname) LIKE LOWER ('%$$Host Name$$%') 
      )
      
   -- WHEN Name, version and Publisher are wildcards group by publisher and put the name, version and devices info into a list
   SELECT 
      publisher, COUNT(meta_hostname) Instances, 
      array_join(array_agg(DISTINCT name), ','||CHR(10)) App_name_List, 
      array_join(array_agg(DISTINCT version), ','||CHR(10)) version_LIST,
      array_join(array_agg(DISTINCT meta_hostname), ','||CHR(10))  DeviceName_LIST, MIN(install_date) Earliest_Install, MAX(install_date) Last_Install
   FROM App_List WHERE 
      '$$Application Name$$' = '%' AND '$$Application Version$$' = '%' AND '$$Publisher Name$$' = '%'  
   GROUP BY publisher

   UNION ALL
   
   -- In all other instances breach out everything on their own line, only grouping the device info
   SELECT 
      publisher, COUNT(meta_hostname) Instances, 
      name, 
      version,
      array_join(array_agg(DISTINCT meta_hostname), ','||CHR(10))  DeviceName_LIST, MIN(install_date) Earliest_Install, MAX(install_date) Last_Install
   FROM App_List WHERE 
      ('$$Application Name$$' <> '%' OR '$$Application Version$$' <> '%' OR '$$Publisher Name$$' <> '%')  
   GROUP BY publisher, name, version
   )
SELECT DISTINCT * FROM Counted_Apps ORDER BY publisher ASC