Query in SCCM to list all security patch status

Forum: Config Manager
Viewing 4 posts - 1 through 4 (of 4 total)
  • #96236
    Profile photo of Ken
    Ken
    Member

    To anyone out there that does a lot of SQL queries in SCCM 2007.

    I have had a request from management to produce a report that calulates the total number of patches approved for installation(Total number of patches on all machine) vs total number of patches install. The result will indicate how many patches have failed to install/still require installing.

    If anyone has done this or something similar I would be interested in the query you created.

    Thanks
    Ken

    #96242
    Profile photo of rodtrent
    rodtrent
    Participant

    There’s a patch compliance report on the first page here:

    http://myitforum.com/myitforumwp/community/groups/configuration-manager-queries-and-reports/documents/

    And, other miscellaneous patching reports.

    However, if you can’t find what you like, Garth Jones is always available to create something for you.  Leave him a note.

    http://myitforum.com/myitforumwp/community/members/garth/

    #96328
    Profile photo of Ken
    Ken
    Member

    Thanks Rod but non of them came close.

    I kept studing canned reports and others reports and came up with this.

    I belive it show the total number of patches installed and the total number of patches still required to be installed.

    DECLARE @x as dec, @y as dec

    SET @x = (select

    SUM(NumPresent)

    from v_CIAssignmentToCI cia

    join v_UpdateInfo ui on cia.CI_ID = ui.CI_ID

    join (v_CICategories_All catall join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName=’Company’)

    on catall.CI_ID=ui.CI_ID

    left join v_CITargetedCollections col on col.CI_ID=ui.CI_ID and col.CollectionID=@CollID

    join v_UpdateSummaryPerCollection us on us.CI_ID=ui.CI_ID and us.CollectionID=@CollID

    where cia.AssignmentID=@DeploymentLocalID)

    SET @y = (select

    SUM(NumMissing)

    from v_CIAssignmentToCI cia

    join v_UpdateInfo ui on cia.CI_ID = ui.CI_ID

    join (v_CICategories_All catall join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName=’Company’)

    on catall.CI_ID=ui.CI_ID

    left join v_CITargetedCollections col on col.CI_ID=ui.CI_ID and col.CollectionID=@CollID

    join v_UpdateSummaryPerCollection us on us.CI_ID=ui.CI_ID and us.CollectionID=@CollID

    where cia.AssignmentID=@DeploymentLocalID)

    SELECT @x as ‘Total Patches Installed’, @y as ‘Total Patches Required’, convert(Decimal(19,2),((@x-@y)/@x)*100 ) as ‘Compliancy %’

    #230336
    Profile photo of Kush01
    Kush01
    Participant

    You can pull report from SCCM report.

    Here is very easy Process

    REPORTING -> REPORTS ->SOFTWARE UPDATE –A COMPLIANCE RIGHT CLICK ONCOMPLIANCE 3 –UPDATE GROUP (PER UPDATE).

    You can find step by step in this blog

    https://sccmtitbits.blogspot.in/2017/05/how-to-verify-systems-are-patched-for.html

    Attachments:
    You must be logged in to view attached files.
Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.