Top
    rss

    BLOG

    Aug
    26

    SCCM Patch Management Reports

    From in System Center Technologies & Infrastructure Services

    Mit diesen SQL Queries können SCCM Reports für das Patch Management erstellt werden

    Maintenance/Patch – Windows und zugeordnete 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
     
     
    Updates welche ein Computer benötigt
     
    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'
     
     
    Anzahl an benötigten Updates für einen Computer
     
    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
     
     
     
    Anzahl an Computern, welche ein Update benötigen
     
    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
     
     
     
     
    Anzahl der zugewiesen Updates für einen 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
     
     
    Status aller zugewiesenen Updates für einen bestimmten 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
     
     
    Status aller Updates in einer Updateliste für einen bestimmten 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'
     
     
     
    Anzahl der zugewiesen Updates in einer Updateliste und deren Status
     
    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
     
     
    Anzahl der Updates in einer Update Verteilung
     
    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 
     
     
    Alle Updates in einer Updateliste
     
    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
     

    Add a comment

    0
    COMMENTS
       

      ADD A COMMENT

      Name*

      E-mail*

      Comment*

      *Required information
      © Copyright 2013 PlanB. | All rights reserved. | www.plan-b-gmbh.com | Legal Notes
      PlanB. GmbH