/build/static/layout/Breadcrumb_cap_w.png

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:

SELECT

M.NAME AS ComputerName,

OS_NAME,

M.USER_LOGGED AS USER_LOGGED,

P.PUBLISHER,

P.TITLE AS DISPLAY_NAME,

P.SEVERITY,

P.CREATION_DATE,

MS.DETECT_STATUS


FROM

PATCH_MACHINE_STATUS MS


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)


WHERE

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.


0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: KevinG 1 month ago
10th Degree Black Belt
2

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.



Comments:
  • Well that seems to have worked... I could have sworn I tried that combination but apparently I didn't.

    Thanks for the help. - mdances 1 month ago
Posted by: Hobbsy 1 month ago
Red Belt
0

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!!

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ