Partager via

Where is the software updates data in the SCCM database?

  • Article

If you had a custom report or a SQL query in SMS 2003 to find what machines installed an update or what machines were missing it, and you upgraded to SCCM, you probably found out that your report or query no longer works. This is because there are new tables in the SCCM database to store software updates information. SCCM does include reports for software updates compliance, but what if I want a quick SQL query or a custom report?

We can use the v_UpdateInfo to find updates data like their Bulletin ID, their KB article, their description, etc. We can also use the v_Update_ComplianceStatusAll view to find updates scan data, or the status of a specific update on your clients. The status for the software updates is a numeric field so let me translate them for you:

0 = Detection state unknown

1 = Update is not required

2 = Update is required

3 = Update is installed

So now that we have the views we need and the meaning of the update status values, we can write our own report or query. If I wanted to query SQL to find out all the clients that installed security bulletin MS07-064, the query will look something like this:

select Name0 as ComputerName

from v_R_System a

left join v_Update_ComplianceStatusAll comp on a.ResourceID=comp.ResourceID

join v_UpdateInfo ui on comp.CI_ID=ui.CI_ID

where comp.Status = 3 and ui.BulletinID = 'MS07-064’

 

If I wanted to know the clients missing that security bulletin I would change “where comp.Status = 3” to “where comp.Status = 2”.

Comments

  • Anonymous
    May 14, 2014
    Can this query be used for creating a dynamic collection?
  • Anonymous
    July 28, 2015
    Wow great work man, You saved my day :)