There are instances (you might want to get list of required patches by server ,so can download and install in DMZ who do not have internet or for patch compliance checking for specific computer and many) , where you might be searching for list patches of required by specific computer/computers and generally ,we look at default reports if there is any report which can give me the required information.
If you look at default reports,there is no exact report give you the list of patches required by specific computer with targeted,required,when was it released and other information.
You can use below SQL Query to run from SQL SERVER MANAGEMENT STUDIO or use this in your SSRS Reports with parameter for computer name.
This query has been customized to present the Month posted,Title of the patch,Targeted to the client,Required ,KB URL,Date posted and deadline if there is any.
Note: This Query is filtered with Severity (Security and critical) from view v_UpdateInfo.
SQL Code:
select CAST(DATEPART(yyyy,ui.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, ui.DatePosted) AS VARCHAR(255)), 2) AS MonthPosted,
ui.bulletinid [BulletinID],ui.articleid [ArticleID], ui.Title,
Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
IsRequired=(case when css.Status=2 then '*' else '' end),
ui.InfoURL as InformationURL,
ui.dateposted [Date Posted] ,
Deadline=cdl.Deadline
from V_UpdateComplianceStatus css
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = css.ResourceID
INNER join v_CICategories_All catall2 on catall2.CI_ID=css.CI_ID
INNER join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID
and catinfo2.CategoryTypeName='UpdateClassification'
JOIN dbo.v_R_System AS vrs ON vrs.ResourceID = css.ResourceID
outer apply (
select Deadline=min(a.EnforcementDeadline)
from v_CIAssignment a
join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID and atc.CI_ID=css.CI_ID
) cdl
WHERE vrs.Name0='Win7X64-001' and
ui.Severity IN (8, 10) --this is for security and critical updates
AND css.Status=2 --for required
ORDER BY 1
I posted the same query on TechNet forums ,more info https://social.technet.microsoft.com/Forums/en-US/8fe96532-d9ef-48f2-a356-6e1195d9273e/report-of-what-updates-are-needed-by-server?forum=configmanagergeneral#7185a84e-afab-47d2-82bd-7418e02dd75f
14 Comments
Pingback: SCCM Configmgr SSRS Report Get list of missing updates for PC from specific Software update group | All about Microsoft Endpoint Manager
Hi Eswar, this looks very useful, is there a way of just producing the count of patches that are missing for devices in a Collection. We are just starting to use SCCM to do patching on our server estate and have a requirement to see how far out of date each server is so that we can estimate the time required to update them.
Hi Nick,
This is possible and you can make use of these software update reports: http://eskonr.com/2016/02/sccm-configmgr-ssrs-report-how-do-i-quickly-tell-if-clients-are-compliant-or-not-for-all-the-approved-patches/
http://eskonr.com/2015/12/sccm-configmgr-2012-updated-patch-compliance-reports-for-software-update-group-and-collection-with-patch-progression/
Thanks,
Eswar
here is an sql query that direcly queries the hotfixes and related hostnames from SCCM database
https://bestitsm.wordpress.com/2018/07/12/how-to-get-list-of-installed-hotfixes-from-sccm-database/
Eswar
I think i got it wrong. (Management 2 - Updates required but not deployed) report might be the one i needed after all.
I think the NOT DEPLOYED means the updates that is not deployed to the collection that i am running the report against. Is that correct? Thanks, Dave.
I think that's true, it tells about whether the patch deployed to the collection or not.
Hi Eswar
Another request for you.
Is it possible to modify this and have a report that list all required updates (whether deployed or not, or downloaded or not), for a collection? Very similar to the built in report (Management 2 - Updates required but not deployed). But, not limiting it to "Not deployed".
Thanks again, DM.
Mate, that will be great if you could create a computer report based on Software Update Group.
Keep up the good work.
sure,this is in my To-Do list,will blog it soon.
Hi Eswar,
Have you managed to create a computer report based on Software Update Group?
This would be very useful.
Thanks
not yet Dave but will do it ,lab is still down..
Hi Dave,
yes,i posted blog on this http://eskonr.com/2015/12/sccm-configmgr-ssrs-report-get-list-of-missing-updates-for-pc-from-specific-software-update-group/ .have a check.
Very good.
But is it possible to get such a report based on 'custom' software update groups?
Yes It is possible. I will look into that later but here are the list of Configmgr 2012 R2 SQL views which might help you to customize reports https://gallery.technet.microsoft.com/SCCM-Configmgr-2012-R2-SQL-5fefdd3b