SCCM report to return KB status on servers

Question

I am trying to create a custom report in SCCM which will tell me if a specific KB is installed on a pool of servers, and return a binary answer (in this case, yes or no).

The problem I have is I cannot get it to return the status of just one KB. I am running this:

SELECT
 SYS.Name0 'Computer',
 SYS.operatingSystem0 'OS',
 UIN.Title 'Update',
 CASE
  WHEN UIN.Title LIKE '%KB3092627%' THEN 'Yes'
  ELSE 'No'
  END 'KB Installed'

FROM v_R_System SYS
 INNER JOIN v_UpdateComplianceStatus UCS ON SYS.ResourceID = UCS.ResourceID
 INNER JOIN v_UpdateInfo UIN ON UCS.CI_ID = UIN.CI_ID

WHERE SYS.operatingSystem0 LIKE '%Server 2008%'

ORDER BY SYS.Name0

but I get hundreds of results for each SYS.Name0, one for every detected update. All records for updates which arent the desired KB have a value of 'No' for KB Installed, but they are still listed. I tried SELECT DISTINCT to see if it got me different results, but it does not.

What I want is to determine if the KB is present, and return if it is or not. I feel like I'm not even asking the fundamental question of "Is installed or not" with this code, but I can't think of a way to ask this differently. Is this something I should be doing in Report Builder instead of the query?

Answer 1

Disclaimer: I haven't tested this on a representative data set as you didn't provide one, but I think this should do what you're trying to achieve:

SELECT DISTINCT
    s1.Name0 'Computer',
    s1.operatingSystem0 'OS',
    CASE WHEN matches.Computer IS NULL THEN 'No' ELSE 'Yes' END 'KB Installed'
FROM v_R_System s1
LEFT JOIN
(
    SELECT s2.Name0 'Computer',
    FROM v_R_System s2
    INNER JOIN v_UpdateComplianceStatus UCS ON s2.ResourceID = UCS.ResourceID
    INNER JOIN v_UpdateInfo UIN ON UCS.CI_ID = UIN.CI_ID
    WHERE UIN.Title LIKE '%KB3092627%'
) AS matches ON s1.Name0 = matches.Computer
WHERE s1.operatingSystem0 LIKE '%Server 2008%'
ORDER BY s1.Name0

So this effectively returns the distinct list of all Computers and LEFT JOINs this to result set of just records for the update in question for each given Computer. Therefore, when the field returned from the LEFT JOIN is NULL, the update hasn't been installed and vice-versa.

And to answer your question, I personally think it's fine to be doing this in the query (as opposed to in your report somehow) - it's where I would do it.

Answer 2

Please run this query below:

SELECT DISTINCT 
 SYS.Name0 'Computer',
 SYS.Operating_System_Name_and0 'OS',
 UIN.Title 'Update',
 CASE
  WHEN UIN.Title LIKE '%KB3079343%' THEN 'Yes'
  ELSE 'No'
  END 'KB Installed'

FROM v_R_System SYS
 left JOIN v_UpdateComplianceStatus UCS ON SYS.ResourceID = UCS.ResourceID
 left JOIN v_UpdateInfo UIN ON UCS.CI_ID = UIN.CI_ID

WHERE  UIN.Title LIKE '%KB3079343%'


ORDER BY SYS.Name0

In my test lab, There are multiple records corresponding one KB number. This method may not be a perfect solution.

You could try the built-in report: \Monitoring\Overview\Reporting\Reports\Software Updates - A Compliance\ Compliance 8 - Computers in a specific compliance state for an update (secondary)