PENDING SCCM Patching Report for Updates Compliance

[Vishwas Bhanuvalli]

Hello All,
I need an help on SCCM reporting please. I know SCCM has some inbuild compliance reports but unfortunately, it doesn't contain the report that I am looking for.
I tried creating a custom report but I am having a difficulty in creating the same. My requirement is as below. Can someone please send me the steps on how to create a report for the same?

Machine Name

Machine IP

Number of pending patches

KB numbers for pending patch

Your help is highly appreciated!

Regards,
Vishwas Bhanuvalli
[email protected]

 

[Garth]

Hello All,
I need an help on SCCM reporting please. I know SCCM has some inbuild compliance reports but unfortunately, it doesn't contain the report that I am looking for.
I tried creating a custom report but I am having a difficulty in creating the same. My requirement is as below. Can someone please send me the steps on how to create a report for the same?

Machine Name

Machine IP

Number of pending patches

KB numbers for pending patch

Your help is highly appreciated!

Regards,
Vishwas Bhanuvalli
[email protected]

What do you have for a query now? What is not working for you? BTW, I would NEVER add IP address to a report, particularly for servers.

 

[Vishwas Bhanuvalli]

Hi Garth,
Thanks a lot for your response.
I want to create a custom compliance report. Which will say how many patches pending on my machine and what are the patches pending.
That’s ok if IP is not included. I atleast need host name.

 

[Garth]

Hi Garth,
Thanks a lot for your response.
I want to create a custom compliance report. Which will say how many patches pending on my machine and what are the patches pending.
That’s ok if IP is not included. I atleast need host name.

So what do you have now? What is not working for you?

 

[Vishwas Bhanuvalli]

So what do you have now? What is not working for you?

OKay. Let me explain you this way!

I have a below query and when i run this, It will prompt me for machine name. Once machine name is provided, It will display the info such as, Patch name, Required or not, installed or not. Now, i want to similar report, which will prompt for collection name not for a single machine and provide the same report for entire collection. can you help me with the same?


declare @lcid as int set @lcid = dbo.fn_LShortNameToLCID(@locale)
select
Vendor=vnd.CategoryInstanceName,
UpdateClassification=cls.CategoryInstanceName,
ui.Title,
ui.BulletinID,
ui.ArticleID,
Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
Installed=(case when css.Status=3 then '*' else '' end),
IsRequired=(case when css.Status=2 then '*' else '' end),
Deadline=cdl.Deadline,
ui.CI_UniqueID as UniqueUpdateID,
ui.InfoURL as InformationURL
from fn_rbac_UpdateComplianceStatus(@UserSIDs) css
join fn_rbac_UpdateInfo(@lcid, @UserSIDs) ui on ui.CI_ID=css.CI_ID
join fn_rbac_CICategoryInfo_All(@lcid, @UserSIDs) vnd on vnd.CI_ID=ui.CI_ID and vnd.CategoryTypeName='Company'
join fn_rbac_CICategoryInfo_All(@lcid, @UserSIDs) cls on cls.CI_ID=ui.CI_ID and cls.CategoryTypeName='UpdateClassification'
left join fn_rbac_CITargetedMachines(@UserSIDs) ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = css.ResourceID
outer apply (
select Deadline=min(a.EnforcementDeadline)
from fn_rbac_CIAssignment(@UserSIDs) a
join fn_rbac_CIAssignmentToCI(@UserSIDs) atc on atc.AssignmentID=a.AssignmentID and atc.CI_ID=css.CI_ID
) cdl
where css.ResourceID = @RscID
and (@Vendor = '' or vnd.CategoryInstanceName = @Vendor)
and (@UpdateClass = '' or cls.CategoryInstanceName = @UpdateClass)
order by ui.Title