SCCM/WSUS report on updates needed and what type of update


  • Hello all. I have been trying to get a report that will show the updates needed on a computer broken down by update type (security, critical, rollup, etc.). I am getting duplicate entries in the results, and don't know what I need to do to filter out the results that don't apply to the computer in question. Here is what I have so far:

    SELECT distinct sys.Netbios_Name0, catinfo.CategoryInstanceName, summ.QNumbers, summ.ID, summ.Title, ps.LastStatusTime, os.LastBootUpTime0
    FROM v_UpdateComplianceStatus css
    JOIN v_R_System sys ON css.ResourceID = sys.ResourceID
    JOIN v_GS_PatchStatusEx ps ON sys.ResourceID=ps.ResourceId
    JOIN v_GS_OPERATING_SYSTEM os ON os.ResourceID=sys.ResourceId
    JOIN v_ApplicableUpdatesSummaryEx summ ON ps.UpdateID=summ.UpdateID
    JOIN v_CICategories_All catall ON catall.CI_ID = css.CI_ID AND css.Status = 2
    JOIN v_CategoryInfo catinfo ON catinfo.CategoryInstance_UniqueID = catall.CategoryInstance_UniqueID
     AND ((catinfo.CategoryInstanceID = 26) OR (catinfo.CategoryInstanceID = 30) OR (catinfo.CategoryInstanceID = 33) OR (catinfo.CategoryInstanceID = 34))
    WHERE sys.Netbios_Name0 = 'MachineName'
     AND (ps.LastStateName <> 'Install Verified' AND ps.LastStateName <> 'Preliminary Success')
     AND summ.Title NOT LIKE '%(LEGACY)%'
    ORDER BY sys.Netbios_Name0, summ.ID

    This gives me the updates in question that are needed, but produces mulitple rows for the same update. I'm probably missing something simple, but I'd appreciate any input. Thanks! Chip

    Friday, March 29, 2013 1:26 AM

All replies

  • Yes, I know this is an old post, I’m trying to clean them up.

    I have run this query and it does not produce duplicates rows. It does however show that some SU have multiple classifications, hence why you are see it a few times.

    Wednesday, December 25, 2013 4:02 PM