Application Inventory Query

I thought I had already published this one and if I can't find it I suspect others have that same challenge.

This was from one of the videos to show how the data lake can go broad and the devices dive deep.

-- Application Inventory across all devices with data in the data lake

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