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