none
Create a Collection of machines missing a single patch MS07-042 RRS feed

  • Question

  •  

    Good Morning All,

       I am trying to create a collection for all machines missing a single patch.  MS07-042.  This patch has multiple KB numbers. 

     

    I noticed a couple of odd occurances.

    1) Console query builder does not expose the Updates classes.

    2) Console query builder does expose all the ITMU classes.  I am not sure if these are populated with the WSUS information gathered by the WUA.

     

    I did create the following query

     

    select * 
    from  SMS_R_System inner join SMS_UpdateComplianceStatus on SMS_UpdateComplianceStatus.MachineID = SMS_R_System.ResourceId
       inner join SMS_Softwareupdate on  SMS_Softwareupdate.CI_ID = SMS_UpdateComplianceStatus.CI_ID
    where SMS_Softwareupdate.BulletinID = "MS07-042"

     

    However, I am unable to locate any documentation on what the Status Fields indicate in SMS_UpdateComplianceStatus.

     

    My Questions

    1) Is there an easier way to do this?

    2) Is there any documentation on what the fields mean in SQL?

     

    I look forward to blogging about this as soon as I am sure of an answer.

     

    Best

     

    Shaun Cassells

    http://myitforum.com/cs2/blogs/scassells/default.aspx

    Thursday, June 5, 2008 3:23 PM

Answers

  • Torsten, do you have a refrence for the SMS_UpdateComplianceStatus table?  I want to know what the Status Field values represent.

     


    I realize this is an old thread, but I just wanted to put in my $0.02 in case someone else stumbles upon it looking for info.

    So, the SMS_UpdateComplianceStatus class (cuz we're talking collections here, which is WQL and pulls from WMI classes) is really the equivalent of the v_UpdateComplianceStatus view.  So if you want to see what's in that, go to SQL and query it.

    Also, if you want to know what the Status field values are, you can find those by looking at the v_StateNames view from SQL too where TopicType = 400 I believe.

    State 0 = Unknown
    State 1 = Not Required (meaning doesn't have patch and doesn't need it)
    State 2 = Not Detected (meaning it doesn't have patch but needs it)
    State 3 = Detected (meaning it has this patch)

    So, if you want to create a collection of machines that are missing a patch, you could do something like this:

    SELECT
      SMS_R_SYSTEM.ResourceID,
      SMS_R_SYSTEM.ResourceType,
      SMS_R_SYSTEM.Name,
      SMS_R_SYSTEM.SMSUniqueIdentifier,
      SMS_R_SYSTEM.ResourceDomainORWorkgroup,
      SMS_R_SYSTEM.Client
    FROM
      SMS_R_System
      JOIN SMS_UpdateComplianceStatus
        ON SMS_R_System.ResourceID = SMS_UpdateComplianceStatus.MachineID
        AND SMS_UpdateComplianceStatus.Status = 2
      JOIN SMS_SoftwareUpdate
        ON SMS_UpdateComplianceStatus.CI_ID = SMS_SoftwareUpdate.CI_ID
        AND SMS_SoftwareUpdate.ArticleID = 832671


    Number2 - (John Nelson)
    Microsoft MVP (2009) - System Center Configuration Manager
    http://number2blog.com
    Friday, March 19, 2010 8:40 PM
  •  Xionn wrote:
    Unfortunately the Update Classes are NOT exposed via the Query Generator in the SCCM / ConfigMgr console.

     

    That doesn't matter. You can use the class anyway. Create a new query for demo purposes and use "select * from SMS_UpdateComplianceStatus" as the query statement ...

    Monday, June 9, 2008 11:20 AM
    Moderator

All replies

  • We were looking to do the same thing but we needed a quick fix and used our old method from SMS 2003 to get this info, from add/remove programs  obviously we would rather use the same method as the software update compliance reports, but we were under the gun to get this collection created... 

     

    select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.Name not in (select distinct SMS_G_System_SYSTEM.Name from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%KB944533%")

     

    Thursday, June 5, 2008 3:43 PM
  • But doesn't that only show PCs where it has been installed.  I want to know PCs where it hasn't.

     

    You would not want to use Not Like as that would not tell you if it were applicable to the PC returned.

     

    Good idea for ARP inventory, but not granular enough.

     

    Thursday, June 5, 2008 4:07 PM
  • Thanks, great references.

    Unfortunately the Update Classes are NOT exposed via the Query Generator in the SCCM / ConfigMgr console. 

     

    The second part of my question for documentation referencing the SMS_Update... table values so I know what they mean.

     

    Still searching.  Keep the great idea's coming.

     

    Best

     

    Thursday, June 5, 2008 4:31 PM
  •  Xionn wrote:
    Unfortunately the Update Classes are NOT exposed via the Query Generator in the SCCM / ConfigMgr console.

     

    That doesn't matter. You can use the class anyway. Create a new query for demo purposes and use "select * from SMS_UpdateComplianceStatus" as the query statement ...

    Monday, June 9, 2008 11:20 AM
    Moderator
  • Torsten, do you have a refrence for the SMS_UpdateComplianceStatus table?  I want to know what the Status Field values represent.

     

    Monday, June 9, 2008 11:31 AM
  • Unfortunately not.

     

    Monday, June 9, 2008 12:42 PM
    Moderator
  • Dear all,

    I have the same needs. But unfortunatly with queries I can't see PC without specific KB NOT installed.

     

    I would like to deploy XP-SP3 but I need to know computers who have KB92587 installed to prepare the uninstall...

     

    I could be great to create collection (and not a query) with to be able to uninstall this KB before deploy SP3. There is another way to deploy SP3 without issues regarding this KB92587 ?

     

     

    Thanks

     

    Tuesday, June 17, 2008 2:43 PM
  • This gives you all computers where KB92587 is NOT installed.

    select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Name not in (select SMS_R_System.Name from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%KB92587%")

     

    (Isn't KB92587 missing one digit? kb articles usually contain 6 digits)

     

     

    Tuesday, June 17, 2008 3:04 PM
    Moderator
  • Thanks

    I will try this.

     

    Sorry for kb number... Correct Name is KB925877 (Remote Desktop Connection (RDP) 6.0 MUI pack (Update 925877 for Windows XP)

     

    JP

     

    Tuesday, June 17, 2008 3:13 PM
  • Careful, that great query Torsten gave you does not tell you where the patch is applicable.  

    Good news, if you use ConfigMgr SUP and deploy it to all machines where it is not installed it will only succeed on those who need to run it.   True your collection may never empty.

     

    I haven't had time to dig into the table values further yet.

     

    Best

     

    Tuesday, June 17, 2008 3:17 PM
  • Yes I know. But my need is only to know which computers have this KB installed to uninstall it before deploying SP3.

    Collection will not be empty after deployment because this kb is included in SP3 but not appear in Add/Remove Program right?

    it's that you mean?

     

    Tuesday, June 17, 2008 3:30 PM
  • You are correct.   This query will populate with all machines that have it installed.  As you uninstall them it will update the Hardware Inventory (HINV) and be submitted on whatever schedule you have set.  Once they have submitted HINV the collection will be updated on whatever schedule you have set.  At that time they will be removed from the collection down to the eventual goal of zero.

     

    Have fun.

    Tuesday, June 17, 2008 3:35 PM
  • Well, as I've stated in other posts, we (being Microsoft) really don't help with custom queries or reports. However, there are numerous options you could use to find the data you are looking for. Here are a couple of them.

     

    * Create a configuration baseline that looks for the update in question. This will report those that are not compliant. You CAN create a collection out of the non-compliance information.

     

    * Use the Add/Remove Programs data to query which systems do/don't have the specific update listed.

     

    Sunday, June 22, 2008 10:02 PM
  • Torsten, do you have a refrence for the SMS_UpdateComplianceStatus table?  I want to know what the Status Field values represent.

     


    I realize this is an old thread, but I just wanted to put in my $0.02 in case someone else stumbles upon it looking for info.

    So, the SMS_UpdateComplianceStatus class (cuz we're talking collections here, which is WQL and pulls from WMI classes) is really the equivalent of the v_UpdateComplianceStatus view.  So if you want to see what's in that, go to SQL and query it.

    Also, if you want to know what the Status field values are, you can find those by looking at the v_StateNames view from SQL too where TopicType = 400 I believe.

    State 0 = Unknown
    State 1 = Not Required (meaning doesn't have patch and doesn't need it)
    State 2 = Not Detected (meaning it doesn't have patch but needs it)
    State 3 = Detected (meaning it has this patch)

    So, if you want to create a collection of machines that are missing a patch, you could do something like this:

    SELECT
      SMS_R_SYSTEM.ResourceID,
      SMS_R_SYSTEM.ResourceType,
      SMS_R_SYSTEM.Name,
      SMS_R_SYSTEM.SMSUniqueIdentifier,
      SMS_R_SYSTEM.ResourceDomainORWorkgroup,
      SMS_R_SYSTEM.Client
    FROM
      SMS_R_System
      JOIN SMS_UpdateComplianceStatus
        ON SMS_R_System.ResourceID = SMS_UpdateComplianceStatus.MachineID
        AND SMS_UpdateComplianceStatus.Status = 2
      JOIN SMS_SoftwareUpdate
        ON SMS_UpdateComplianceStatus.CI_ID = SMS_SoftwareUpdate.CI_ID
        AND SMS_SoftwareUpdate.ArticleID = 832671


    Number2 - (John Nelson)
    Microsoft MVP (2009) - System Center Configuration Manager
    http://number2blog.com
    Friday, March 19, 2010 8:40 PM
  • Hi Torsten,

    Sorry to bring old stories ....

    Will all KB create entry in add/remove program display entry ?

    I am confused with this query  ""SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%KB92587%")"

    becuase according to my knowldge update will show in "installed update" location

    so doubts will it pull the machine according to above query.


    shinu

    Wednesday, August 20, 2014 11:53 AM
  • Will all KB create entry in add/remove program display entry ?

    I am confused with this query  ""SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%KB92587%")"

    becuase according to my knowldge update will show in "installed update" location

    so doubts will it pull the machine according to above query.

    No, Not all SU will add and entry into ARP. Particularly not on Vista and above.

    Garth Jones | My blogs: Enhansoft and Old Blog site | Twitter: @GarthMJ

    Wednesday, August 20, 2014 12:16 PM
    Moderator
  • It really would have been good to start a new thread, even if the topic was the same. The original is six years old (can't believe was not answered by now).

    Wally Mead

    Monday, August 25, 2014 9:36 PM
  • Don't use ARP data, that can be "stale" as it's collected via Hardware Inventory, use the query from John that looks for a specific update and its state (using the tables populates by Updates Scan);

    -----

    SELECT
      SMS_R_SYSTEM.ResourceID,
      SMS_R_SYSTEM.ResourceType,
      SMS_R_SYSTEM.Name,
      SMS_R_SYSTEM.SMSUniqueIdentifier,
      SMS_R_SYSTEM.ResourceDomainORWorkgroup,
      SMS_R_SYSTEM.Client
    FROM
      SMS_R_System
      JOIN SMS_UpdateComplianceStatus
        ON SMS_R_System.ResourceID = SMS_UpdateComplianceStatus.MachineID
        AND SMS_UpdateComplianceStatus.Status = 2
      JOIN SMS_SoftwareUpdate
        ON SMS_UpdateComplianceStatus.CI_ID = SMS_SoftwareUpdate.CI_ID
        AND SMS_SoftwareUpdate.ArticleID = 832671

    -----

    Jack

    Wednesday, December 17, 2014 5:46 PM
  • I used this and when using SMS_UpdateComplianceStatus.Status = 0 I don't get any results back when the Software Update Deployment under monitoring has 400 devices reporting as 'unknown'. Any ideas?
    Monday, September 21, 2015 3:30 AM
  • The 'Unknown' Deployment Status you are seeing from the Monitoring Workspace is the status as it pertains to the deployment item not the status as it pertains to update Applicability.

    For example, you could deploy an update group, an application, a package, a configuration baseline, and they would each have a deployment status item in the Monitoring workspace (Each item contains the same structured information)

    The query in JFetter's answer is status from the SMS_UpdateComplianceStatus class. Very few to no systems should have an 'Unknown' status when it comes to Update applicability. CM knows whether an update is applicable or not, unless the inventory cycle didnt succeed for some reason.

    Thursday, November 10, 2016 10:12 PM
  • FYI, I was unable to make above WQL work with ConfigMan 1902 until I made changes below; 

    SELECT DISTINCT SMS_SoftwareUpdate.articleid
    ,SMS_UpdateComplianceStatus.status
    ,SMS_R_SYSTEM.name
    FROM SMS_R_SYSTEM
    JOIN SMS_UpdateComplianceStatus ON SMS_R_SYSTEM.resourceid = SMS_UpdateComplianceStatus.machineid
    AND SMS_UpdateComplianceStatus.status = 2
    JOIN SMS_SoftwareUpdate ON SMS_UpdateComplianceStatus.ArticleID = SMS_SoftwareUpdate.articleid
    AND SMS_SoftwareUpdate.ArticleID = '4515383'
    WHERE SMS_R_SYSTEM.obsolete = 0
    AND SMS_R_SYSTEM.client = 1
    ORDER BY SMS_R_SYSTEM.name

    Tuesday, September 17, 2019 9:39 PM