8/25/2010

SCCM PatchMgmt - Custom Reports

Here are some SCCM Patch Managenment Reports which i've created for my customers

Maintenance/Patch - Windows and each Computer

select
v_Collection.Name as CollectionName,
v_ServiceWindow.Description,
v_FullCollectionMembership.Name as Computername
from v_ServiceWindow
inner join v_FullCollectionMembership on (v_FullCollectionMembership.CollectionID = v_ServiceWindow.CollectionID)
inner join v_Collection on (v_Collection.CollectionID = v_FullCollectionMembership.CollectionID)
order By v_Collection.Name



Required Updates for a specific Computer

select
v_R_System.name0 as 'Computername',
v_UpdateInfo.Title as 'Updatename',
v_StateNames.Statename
from
v_StateNames,
v_Update_ComplianceStatusAll
Inner Join v_R_System On (v_R_System.ResourceID = v_Update_ComplianceStatusAll.ResourceID)
Inner Join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_Update_ComplianceStatusAll.CI_ID)
where
v_StateNames.TopicType = 500 and
v_StateNames.StateID = v_Update_ComplianceStatusAll.Status and
v_R_System.name0 = @Computername and
Statename = 'Update is required'


Count of Required Updates for all Computers

select
v_R_System.Name0 as 'Computername',
Count(v_StateNames.Statename) as 'Required Updates'
from
v_StateNames,
v_Update_ComplianceStatusAll
Inner Join v_R_System On (v_R_System.ResourceID = v_Update_ComplianceStatusAll.ResourceID)
Inner Join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_Update_ComplianceStatusAll.CI_ID)
where
v_StateNames.TopicType = 500 and
v_StateNames.StateID = v_Update_ComplianceStatusAll.Status and
v_StateNames.Statename = 'Update is required'
Group By v_R_System.Name0


Number of Computers requiring an Update

select
v_UpdateInfo.Title as 'Updatename',
Count(v_R_System.name0) as 'Count of Computers'
from
v_StateNames,
v_Update_ComplianceStatusAll
Inner Join v_R_System On (v_R_System.ResourceID = v_Update_ComplianceStatusAll.ResourceID)
Inner Join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_Update_ComplianceStatusAll.CI_ID)
where
v_StateNames.TopicType = 500 and
v_StateNames.StateID = v_Update_ComplianceStatusAll.Status and
Statename = 'Update is required'
Group By v_UpdateInfo.Title
Order By 'Count of Computers' DESC


Assinged number of Updates for each Computer

select
v_R_System.Name0 as Computername,
Count(v_UpdateInfo.Title) as Updates
from v_UpdateState_Combined
inner join v_R_System on (v_R_System.ResourceID = v_UpdateState_Combined.ResourceID)
inner join v_CIAssignmentToCI on (v_CIAssignmentToCI.CI_ID = v_UpdateState_Combined.CI_ID)
inner join v_CIAssignment on (v_CIAssignment.AssignmentID = v_CIAssignmentToCI.AssignmentID)
inner join v_UpdateInfo on (v_UpdateInfo.CI_ID = v_UpdateState_Combined.CI_ID)
inner join v_StateNames on (v_StateNames.TopicType = v_UpdateState_Combined.StateType)
where
v_StateNames.StateID = v_UpdateState_Combined.StateID and
v_StateNames.StateName not like 'Update is not required'
Group by v_R_System.Name0


Assinged Updates and current State for a specific Computer

select
v_R_System.Name0 as 'Computername',
v_UpdateInfo.Title as 'UpdateDescription',
v_StateNames.StateName as 'State'
from v_UpdateState_Combined, v_CIAssignmentToCI,v_CIAssignment, v_UpdateInfo,v_StateNames, v_R_System
where v_R_System.Name0 = @MACHINENAME and
v_UpdateState_Combined.ResourceID = v_R_System.ResourceID and
v_UpdateState_Combined.CI_ID = v_CIAssignmentToCI.CI_ID and
v_CIAssignmentToCI.AssignmentID = v_CIAssignment.AssignmentID and
v_UpdateState_Combined.CI_ID = v_UpdateInfo.CI_ID and
v_UpdateState_Combined.StateType = v_StateNames.TopicType and
v_UpdateState_Combined.StateID = v_StateNames.StateID and
v_StateNames.StateName not like 'Update is not required'
Order By UpdateDescription


Assinged Updates in an UpdateList and current State for a specific Computer

select distinct v_R_System.Name0,v_StateNames.StateName, v_UpdateInfo.Title
from v_AuthListInfo,v_CIRelation,v_UpdateState_Combined,v_StateNames,v_UpdateInfo,v_R_System
where v_AuthListInfo.CI_ID = v_CIRelation.FromCIID and
v_CIRelation.ToCIID = v_UpdateInfo.CI_ID and
v_UpdateState_Combined.CI_ID = v_CIRelation.ToCIID and
v_StateNames.TopicType = v_UpdateState_Combined.StateType and
v_StateNames.StateID = v_UpdateState_Combined.StateID and
v_R_System.ResourceID = v_UpdateState_Combined.ResourceID and
v_R_System.Name0 = @COMPUTERNAME and
v_AuthListInfo.Title = @UPDATELIST and
v_StateNames.StateName not like 'Update is not required'


Number of assinged Updates in an UpdateList and current State for all Computers

select v_R_System.Name0 as Computername , v_StateNames.StateName ,count(v_UpdateInfo.Title) as UpdateCount, v_AuthListInfo.Title as Updatelist
from v_AuthListInfo
inner join v_CIRelation On (v_CIRelation.FromCIID = v_AuthListInfo.CI_ID)
inner join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_CIRelation.ToCIID)
inner join v_CIAssignmentToCI on (v_CIAssignmentToCI.CI_ID = v_CIRelation.ToCIID)
inner join v_CIAssignmentTargetedMachines on (v_CIAssignmentTargetedMachines.AssignmentID = v_CIAssignmentToCI.AssignmentID)
inner join v_R_System on (v_R_System.ResourceID = v_CIAssignmentTargetedMachines.ResourceID)
inner join v_UpdateState_Combined on (v_UpdateState_Combined.CI_ID = v_CIRelation.ToCIID)
inner join v_StateNames on (v_StateNames.StateID = v_UpdateState_Combined.StateID)
where v_AuthListInfo.Title = @UPDATELIST and
v_UpdateState_Combined.ResourceID = v_R_System.ResourceID and
v_StateNames.TopicType = v_UpdateState_Combined.StateType
and v_StateNames.StateName not like 'Update is not required'
group by v_R_System.Name0, v_StateNames.StateName, v_AuthListInfo.Title
order by v_R_System.Name0


Number of Updates in an Update Deployment

select AssignmentName as Deployment,Count(CI_ID) as UpdateCount
from v_CIAssignment
Inner Join v_CIAssignmentToCI On (v_CIAssignmentToCI.AssignmentID = v_CIAssignment.AssignmentID)
where AssignmentName Like 'Update%'
Group By AssignmentName
order By AssignmentName


All Updates in an Updatelist

select distinct v_UpdateInfo.Title
from v_AuthListInfo,v_CIRelation,v_UpdateInfo
where v_AuthListInfo.CI_ID = v_CIRelation.FromCIID and
v_CIRelation.ToCIID = v_UpdateInfo.CI_ID and
v_AuthListInfo.Title = @UPDATELIST
order by v_UpdateInfo.Title

15 comments:

Anonymous said...

Hello! I have attempted to use your report "Assinged Updates and current State for a specific Computer". I created the prompts for COMPUTERNAME and UPDATELIST by copying the scripts from other existing reports.

However when run your report I get "No matching records could be found."

Would you have any insight into why this would be the case?

Thanks!
Joel

Anonymous said...

Hello! I am trying to use your report "Assinged Updates in an UpdateList and current State for a specific Computer". I added in the prompt for COMPUTERNAME and UPDATELIST by copying the prompts for existing reports. When I run the report I get "No matching records found.".

Would you have any ideas why this isn't working?
Thanks,
Joel

Unknown said...

Hello, I'm trying to use your report "Assinged Updates in an UpdateList and current State for a specific Computer". I have added in the two prompts for COMPUTERNAME and UPDATELIST by copying the scripts from existing reports.

When I run the report I get "No matching records found". Do you have any idea why this isn't working?

merowinger said...

Are the prompts are correct?
Are you sure the Client has reported the Update Scan Result to WSUS and SCCM?

HeyAdmin said...

Do I need to create prompts for these reports? Sorry...new to SCCM reports.

merowinger said...

yes you have to define a prompt for every variable (@xxx).

For the Computername you can create the prompt with this query:
select * from v_R_System

For the Updatelist you can create the prompt with this query:
select * from v_AuthListInfo

HeyAdmin said...

I'm looking for a single report that prompts for an update list and collection, and lists all the required patches for that collection. The first column is the machine name, second column is the bulletin id, third column is a description of the bulletin. If a machine is missing more than one patch, there would be multiple rows for that machine. Sort of like this:

Machine1 MS12-033
Machine1 MS12-054
Machine2 MS12-025
Machine3 MS11-044

No summary required...just the raw, missing patches. I would export this and manipulate it myself in Excel to get the summary I want.

thanks for any suggestions...

HeyAdmin said...

In my example above, the third column didnt' appear as I had typed in in my post. It would be a description of the required bulletin ID.

merowinger said...

Hi HeyAdmin,
thank you for you interest.
Please post your question in a tech forum like experts-exchange.com or myitforum.com as i don't have the time to answer it at the moment.
Thank you

Anonymous said...

Hi,
I am looking for SCCM2012 query for patch status from specific deployment and specific collection. I need the following details in my patch report
Total patch count in deployment
Total patch count installed
Total patch count missing
Total patch count not required

Anonymous said...

Hi,
I am looking for SCCM2012 query for patch status from specific deployment and specific collection. I need the following details in my patch report with against each machine name
Total patch count in deployment
Total patch count installed
Total patch count missing
Total patch count not required

Anonymous said...

Still having trouble getting the prompts to work properly in the "Assinged Updates in an UpdateList and current State for a specific Computer" report.. Can you explain how to properly create these prompts?

Anonymous said...

These were a huge help! Thanks so much for posting them. They have proven useful within some of the Orchestrator runbooks for patch automation.

MusicManiac said...

Hey,

VERY USEFUL! THANKS!

Anonymous said...

Very usefull and still working with SCCM 1802 !