SCCM PatchMgmt - Custom Reports

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

Maintenance/Patch - Windows and each Computer

view plainprint?

1. select   
2. v_Collection.Name as CollectionName,   
3. v_ServiceWindow.Description,   
4. v_FullCollectionMembership.Name as Computername    
5. from v_ServiceWindow   
6. inner join v_FullCollectionMembership on (v_FullCollectionMembership.CollectionID = v_ServiceWindow.CollectionID)   
7. inner join v_Collection on (v_Collection.CollectionID = v_FullCollectionMembership.CollectionID)   
8. order By v_Collection.Name   

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

view plainprint?

1. select   
2. v_R_System.name0 as 'Computername',   
3. v_UpdateInfo.Title as 'Updatename',   
4. v_StateNames.Statename   
5. from   
6. v_StateNames,   
7. v_Update_ComplianceStatusAll   
8. Inner Join v_R_System On (v_R_System.ResourceID = v_Update_ComplianceStatusAll.ResourceID)   
9. Inner Join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_Update_ComplianceStatusAll.CI_ID)   
10. where   
11. v_StateNames.TopicType = 500 and   
12. v_StateNames.StateID = v_Update_ComplianceStatusAll.Status and   
13. v_R_System.name0 = @Computername and   
14. Statename = 'Update is required'   

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

view plainprint?

1. select   
2. v_R_System.Name0 as 'Computername',   
3. Count(v_StateNames.Statename) as 'Required Updates'   
4. from   
5. v_StateNames,   
6. v_Update_ComplianceStatusAll   
7. Inner Join v_R_System On (v_R_System.ResourceID = v_Update_ComplianceStatusAll.ResourceID)   
8. Inner Join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_Update_ComplianceStatusAll.CI_ID)   
9. where   
10. v_StateNames.TopicType = 500 and   
11. v_StateNames.StateID = v_Update_ComplianceStatusAll.Status and   
12. v_StateNames.Statename = 'Update is required'   
13. Group By v_R_System.Name0   

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

view plainprint?

1. select   
2. v_UpdateInfo.Title as 'Updatename',   
3. Count(v_R_System.name0) as 'Count of Computers'   
4. from   
5. v_StateNames,   
6. v_Update_ComplianceStatusAll   
7. Inner Join v_R_System On (v_R_System.ResourceID = v_Update_ComplianceStatusAll.ResourceID)   
8. Inner Join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_Update_ComplianceStatusAll.CI_ID)   
9. where   
10. v_StateNames.TopicType = 500 and   
11. v_StateNames.StateID = v_Update_ComplianceStatusAll.Status and   
12. Statename = 'Update is required'   
13. Group By v_UpdateInfo.Title   
14. Order By 'Count of Computers' DESC   

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

view plainprint?

1. select   
2. v_R_System.Name0 as Computername,   
3. Count(v_UpdateInfo.Title) as Updates  
4. from v_UpdateState_Combined  
5. inner join v_R_System on (v_R_System.ResourceID = v_UpdateState_Combined.ResourceID)   
6. inner join v_CIAssignmentToCI on (v_CIAssignmentToCI.CI_ID = v_UpdateState_Combined.CI_ID)   
7. inner join v_CIAssignment on (v_CIAssignment.AssignmentID = v_CIAssignmentToCI.AssignmentID)   
8. inner join v_UpdateInfo on (v_UpdateInfo.CI_ID = v_UpdateState_Combined.CI_ID)   
9. inner join v_StateNames on (v_StateNames.TopicType = v_UpdateState_Combined.StateType)   
10. where  
11. v_StateNames.StateID = v_UpdateState_Combined.StateID and  
12. v_StateNames.StateName not like 'Update is not required'  
13. Group by v_R_System.Name0  

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

view plainprint?

1. select   
2. v_R_System.Name0 as 'Computername',   
3. v_UpdateInfo.Title as 'UpdateDescription',   
4. v_StateNames.StateName as 'State'  
5. from v_UpdateState_Combined, v_CIAssignmentToCI,v_CIAssignment,  v_UpdateInfo,v_StateNames,  v_R_System  
6. where v_R_System.Name0 =  @MACHINENAME and  
7. v_UpdateState_Combined.ResourceID = v_R_System.ResourceID and  
8. v_UpdateState_Combined.CI_ID = v_CIAssignmentToCI.CI_ID and  
9. v_CIAssignmentToCI.AssignmentID = v_CIAssignment.AssignmentID and  
10. v_UpdateState_Combined.CI_ID = v_UpdateInfo.CI_ID and  
11. v_UpdateState_Combined.StateType = v_StateNames.TopicType and   
12. v_UpdateState_Combined.StateID = v_StateNames.StateID and  
13. v_StateNames.StateName not like 'Update is not required'  
14. Order By UpdateDescription  

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

view plainprint?

1. select distinct v_R_System.Name0,v_StateNames.StateName, v_UpdateInfo.Title  
2. from v_AuthListInfo,v_CIRelation,v_UpdateState_Combined,v_StateNames,v_UpdateInfo,v_R_System  
3. where v_AuthListInfo.CI_ID = v_CIRelation.FromCIID and  
4. v_CIRelation.ToCIID = v_UpdateInfo.CI_ID and  
5. v_UpdateState_Combined.CI_ID = v_CIRelation.ToCIID and  
6. v_StateNames.TopicType = v_UpdateState_Combined.StateType and  
7. v_StateNames.StateID = v_UpdateState_Combined.StateID and  
8. v_R_System.ResourceID = v_UpdateState_Combined.ResourceID and  
9. v_R_System.Name0 = @COMPUTERNAME and  
10. v_AuthListInfo.Title = @UPDATELIST and  
11. v_StateNames.StateName not like 'Update is not required'   

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

view plainprint?

1. select  v_R_System.Name0 as Computername , v_StateNames.StateName ,count(v_UpdateInfo.Title) as UpdateCount, v_AuthListInfo.Title as Updatelist  
2. from v_AuthListInfo  
3. inner join v_CIRelation On (v_CIRelation.FromCIID = v_AuthListInfo.CI_ID)  
4. inner join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_CIRelation.ToCIID)  
5. inner join v_CIAssignmentToCI on (v_CIAssignmentToCI.CI_ID = v_CIRelation.ToCIID)  
6. inner join v_CIAssignmentTargetedMachines on (v_CIAssignmentTargetedMachines.AssignmentID = v_CIAssignmentToCI.AssignmentID)  
7. inner join v_R_System on (v_R_System.ResourceID = v_CIAssignmentTargetedMachines.ResourceID)  
8. inner join v_UpdateState_Combined on (v_UpdateState_Combined.CI_ID = v_CIRelation.ToCIID)  
9. inner join v_StateNames on (v_StateNames.StateID = v_UpdateState_Combined.StateID)  
10. where v_AuthListInfo.Title = @UPDATELIST and  
11. v_UpdateState_Combined.ResourceID = v_R_System.ResourceID and  
12. v_StateNames.TopicType = v_UpdateState_Combined.StateType  
13. and v_StateNames.StateName not like 'Update is not required'  
14. group by v_R_System.Name0, v_StateNames.StateName, v_AuthListInfo.Title  
15. order by v_R_System.Name0  

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

view plainprint?

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

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

view plainprint?

1. select distinct v_UpdateInfo.Title  
2. from v_AuthListInfo,v_CIRelation,v_UpdateInfo  
3. where v_AuthListInfo.CI_ID = v_CIRelation.FromCIID and  
4. v_CIRelation.ToCIID = v_UpdateInfo.CI_ID and  
5. v_AuthListInfo.Title = @UPDATELIST  
6. order by v_UpdateInfo.Title  

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