Need help with a report on patching (SQL scripted)
I need to create a report on patch compliance and am new to SQL. I would like to limit this report to patches created from 21 days old to 60 days old which is where I am having the main problem. For this report I want to know what patches in THIS MONTHS patching run have failed. I don't want to deal with every failed patch throughout history, I have another report for that. Here is what I have so far:
M.NAME AS ComputerName,
M.USER_LOGGED AS USER_LOGGED,
P.TITLE AS DISPLAY_NAME,
JOIN KBSYS.PATCH P ON P.ID = MS.PATCH_ID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
MS.DETECT_STATUS != 'PATCHED' AND
P.IS_SUPERCEDED = '0' AND
P.SEVERITY != 'low' AND
P.Creation_date BETWEEN 20210320 and 20210520 AND
L.NAME = 'Server Label Name'
ORDER BY M.NAME
This succeeds in limiting the report to the specified date range. The problem is I don't want to manually change those dates on 5 reports every month. After teaching myself some MySQL I came up with this replacement line for highlighted:
P.Creation_date BETWEEN (CURRENT_DATE(), INTERVAL 21 DAY) AND (CURRENT_DATE(), INTERVAL 60 DAY)
But this generates a error because it does not like INTERVAL in a where statement. If I was writing this in PowerShell I would just create do it like this :
Top of the script:
$TodaysDate = (Get-Date).ToString("yyyyMMdd")
$A = ($TodaysDate - 21)
$B = ($TodaysDate - 60)
and then in the Where statement :
P.Creation_date BETWEEN $A AND $B
This should really be a canned report, or at a minimum much easier to generate.
The following where clause would return the rows with a creation_date from 3 days ago to today.
where P.creation_date > (DATE_SUB(CURDATE(), INTERVAL 3 DAY))
You should be able use this along with a AND P.creation_date < (DATE_SUB(CURDATE(), INTERVAL ? DAY))
To accomplish your desired date range.
Here’s a hint, setup a ticket report using the wizard and when it gets to the filter part, set the report to say tickets logged in the last 60 days.
Once the report is saved take the option to view the SQL of the report. In the WHERE statement you will find the exact SQL to use for any record in the last 60 days.
Cut the text and use the SQL in your patch report, job done ;o)
Watch Nick the Ninja ignore that answer!!