Application Whitelist

Combined the idea of loading a CSV from a local file:

https://community.sophos.com/intercept-x-endpoint/i/query-tips/load-a-local-csv-file-or-remote-csv-file-as-a-virtual-table

to compare a list of applications against installed applications, as a sort of ad-hoc whitelist.

The query returns 'Whitelisted' if found in the CSV, 'Not Approved' otherwise. Could be expanded to check the version # as well, or to pull the CSV from a URL.

-- VARIABLE $$File Path$$  STRING
WITH
    -- LOAD CSV from LOCAL SYSTEM
    Local_CSV_file AS (
        SELECT line FROM grep WHERE pattern = ',' AND path = '$$File Path$$'
    ),
    -- Create Table for Local_CSV_file
    Application_Whitelist AS (
        SELECT SPLIT(Line,',',0) Name, SPLIT(Line,',',1) Version
        FROM Local_CSV_file WHERE Line != ''
        LIMIT 5
    )

SELECT 
PL.name as Application,
CASE WHEN EXISTS (
         select 1
         FROM Application_Whitelist WL
         WHERE PL.Name = WL.name 
      ) 
      THEN 'Whitelisted'
      ELSE 'Not Approved' 
      END AS Status
FROM programs PL
ORDER BY Status, name;