Below Queries will need a CSV file consisting of a List of Installed Software
Example URL - https://raw.githubusercontent.com/jainidhya/CSV/main/program_list_sample1.csv
1) Query to get Updated/Outdated Software details from Remote(Github,Website) Location
-- LOAD CSV from GIT LOCATION AND CHECK IF THE APPLICATION IS PRESENT OR NOT
-- VARIABLE $$URL$$ URL
WITH
Remote_CSV_file(Line, str) AS (
SELECT '', (SELECT result from curl where url = '$$URL$$') ||char(10)
UNION ALL
SELECT substr(str, 0, instr(str, char(10) )), substr(str, instr(str, char(10) )+1) FROM Remote_CSV_file WHERE str!=''
),
-- Create Table for Remote_CSV_file
Remote_Loaded_Table (Col1, Col2, Col3, Col4, Col5, Col6) AS (
SELECT SPLIT(Line,',',0) Col1, SPLIT(Line,',',1) Col2, SPLIT(Line,',',2) Col3, SPLIT(Line,',',3) Col4, SPLIT(Line,',',4) Col5, SPLIT(Line,',',5) Col6
FROM Remote_CSV_file WHERE Line != ''
)
-- CHECK IF THE PROGRAM IS PRESENT OR NOT
SELECT
CASE WHEN p.name > '' THEN 'FOUND' ELSE 'NOT FOUND' END App_Found_OR_Not,
RF.Col1,
RF.Col2,
RF.Col3,
RF.Col4,
RF.Col5
FROM Remote_Loaded_Table RF
LEFT JOIN programs P ON
RF.Col1 LIKE P.name
AND RF.Col2 LIKE P.version
AND RF.Col3 LIKE P.publisher
ORDER BY App_Found_OR_Not DESC
2) Query to get Updated/Outdated Software details from Local(File Share) Location
-- LOAD Allowed/NOT ALLOWED Application List
-- 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
Local_Loaded_Table AS (
SELECT SPLIT(Line,',',0) Name, SPLIT(Line,',',1) Version, SPLIT(Line,',',2) Vendor, SPLIT(Line,',',3) InstallDate, SPLIT(Line,',',4) InstallSource
FROM Local_CSV_file WHERE Line != '' AND line NOT LIKE '%Name%version%Vendor%installDate%InstallSource%'
)
-- CHECK IF THE PROGRAM IS PRESENT OR NOT
SELECT
CASE WHEN p.name > '' THEN 'FOUND' ELSE 'NOT FOUND' END Status,
LF.Name,
LF.Version,
LF.Vendor,
LF.InstallDate,
LF.InstallSource
FROM Local_Loaded_Table LF
LEFT JOIN programs P ON
LF.Name LIKE P.name
AND LF.Version LIKE P.version
AND LF.Vendor LIKE P.publisher
ORDER BY Status ASC
3) Query to get Updated/Outdated Software details using Application Name and Version details
-- CHECK WHETHER THE SOFTWARE IS UPDATED OR OUTDATED
-- VARIABLE $$Version$$ String
--VARIABLE $$Name$$ String
SELECT name, version, publisher,
CASE
WHEN version = '$$Version$$' THEN 'Software is updated'
WHEN version != '$$Version$$' THEN 'Software is outdated'
ELSE 'Application Not Installed'
END AS Status FROM programs WHERE name = '$$Name$$';
