Under Review

Find Endpoints with Outdated/Updated Software Installed

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$$';