Combined the idea of loading a CSV from a local file:
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
-- 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 != ''
PL.name as Application,
CASE WHEN EXISTS (
FROM Application_Whitelist WL
WHERE PL.Name = WL.name
ELSE 'Not Approved'
END AS Status
FROM programs PL
ORDER BY Status, name;