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
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
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.