WSUS SQL Query - Sort by ArrivalDate

Question

I was given a SQL script to run by our Global IT group to generate the monthly WSUS reports they want. However, it returns everything in WSUS, not just the past months information. I am looking for assistance with modifying the script I was given to filter out all of the previous dates. This report is run on the 5th of every month and reports on the previous month (Decembers report will run on Jan 5th). I am fairly basic in my knowledge of SQL scripting, so I am asking for assistance from those who have more experience than I. See the original script below:

SELECT comTarget.[Name]
    ,infoBasic.State
    ,[update].SecurityBulletin
    ,[update].KnowledgebaseArticle
    ,[update].DefaultTitle
    ,[update].ArrivalDate
    ,[update].MsrcSeverity
    ,'Installed'=
    CASE
WHEN infoBasic.State = 4 THEN 'Installed'
WHEN infoBasic.State = 2 THEN 'NotInstalled'
WHEN infoBasic.State = 3 THEN 'Needed'
END
 FROM [SUSDB].[PUBLIC_VIEWS].[vComputerTarget] comTarget
 JOIN [SUSDB].PUBLIC_VIEWS.vUpdateInstallationInfoBasic infoBasic on comTarget.ComputerTargetId = infoBasic.ComputerTargetId
 JOIN [SUSDB].PUBLIC_VIEWS.vUpdate [update] on infoBasic.UpdateId = [update].updateid
Order by Name, SecurityBulletin, KnowledgebaseArticle

score 0 · Accepted Answer · answered Jan 04 '18 at 23:35

0

I found the answer to my question in another article here on StackOverflow. I am linking the article for reference link.

I had to add in the following line of code after the final JOIN:

 WHERE DATEDIFF(mm,[update].ArrivalDate, GETDATE())=1

answered Jan 04 '18 at 23:35

Stephen Parker

11
1
6

WSUS SQL Query - Sort by ArrivalDate

1 Answers1