none
Missing Office 2007 SP2 Collection Query

    Pertanyaan

  • I am trying to determine of the users with Office 2007, which are missing SP2.  All the queries I am tried do not give back accurate results.

    Here is an example of one that did not work:

    select SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName 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 "%Office%2007%" AND SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName != '2007 Microsoft Office Suite Service Pack 2 (SP2)'

    Thanks

     

    20 Mei 2010 20:53

Jawaban

  • try this instead...You need to use a subselect statement to find the machine that do have it and then remove them...see below...

     

    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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 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 "%Office%2007%" AND SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName != '2007 Microsoft Office Suite Service Pack 2 (SP2)')

     

     


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com
    • Ditandai sebagai Jawaban oleh mniccum 20 Mei 2010 21:40
    20 Mei 2010 21:12
  • 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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 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 "%Microsoft Office%Service Pack 2%" ) and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Enterprise 2007"

    ok I tested this on one of my test machines.  We have Office Enterprise 2007, hence why you see that here.

    Hope this gets you there....


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com
    • Ditandai sebagai Jawaban oleh mniccum 22 Mei 2010 13:19
    21 Mei 2010 19:08

Semua Balasan

  • try this instead...You need to use a subselect statement to find the machine that do have it and then remove them...see below...

     

    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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 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 "%Office%2007%" AND SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName != '2007 Microsoft Office Suite Service Pack 2 (SP2)')

     

     


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com
    • Ditandai sebagai Jawaban oleh mniccum 20 Mei 2010 21:40
    20 Mei 2010 21:12
  • I also want to add in these to filter out more computers but I am not sure where they are suppose to go.

    and SMS_R_System.Obsolete = 0 and SMS_R_System.Client = 1 and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%" and SMS_R_System.ResourceDomainORWorkgroup = "STLOUIS" and SMS_R_System.Name not in ("MACHINE NAMES","Machine Names")

    Thanks

    20 Mei 2010 21:43
  • I haven't tested this but you can add it 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 inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%" and SMS_R_System.ResourceDomainORWorkgroup = "STLOUIS" and SMS_R_System.Name not in ("MACHINE NAMES","Machine Names") and SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 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 "%Office%2007%" AND SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName != '2007 Microsoft Office Suite Service Pack 2 (SP2)')

     


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com
    20 Mei 2010 22:29
  • Why would the above query bring back Office 2003?

     

    Thanks

    21 Mei 2010 13:03
  • Possibly due to how you have the office 2007 detected.  Just as a side note, if you have office updates pushed from SCCM via SUP then don't bother creating a SP2 collection.  You can deploy the patch to all machines and only machines that need SP2 will get and install it.

    Since you just want machines that don't have sp2 installed why not just do this.

    SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 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 "%Microsoft Office Suite Service Pack 2%") and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "%Office 2007%"

    This should then pull up all machines that don't have Sp2 install, then it will then only look at machines that have office 2007 installed.  I have tested it but that should work fo ryou.


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com
    21 Mei 2010 14:01
  • I hate to be a pain but that is an incomplete query and I can't seem to find the correct select statement to add to the beginning of the query.

    Thanks

     

     

    21 Mei 2010 17:21
  • Possibly due to how you have the office 2007 detected.  Just as a side note, if you have office updates pushed from SCCM via SUP then don't bother creating a SP2 collection.  You can deploy the patch to all machines and only machines that need SP2 will get and install it.

    Since you just want machines that don't have sp2 installed why not just do this.

    SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 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 "%Microsoft Office Suite Service Pack 2%") and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "%Office 2007%"

    This should then pull up all machines that don't have Sp2 install, then it will then only look at machines that have office 2007 installed.  I have tested it but that should work fo ryou.


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com

    This works to bring back computers without Office 2007 SP2:

    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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 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 "%Microsoft Office Suite Service Pack 2%")

    But adding the bolded code throws an error [*The ConfigMgr Provider reported an error.*]:

    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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 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 "%Microsoft Office Suite Service Pack 2%") AND SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName LIKE "%Microsoft Office Professional Plus 2007%"

    Thanks

    21 Mei 2010 17:36
  • A quick note before I fix this....I show some machines with

    Microsoft Office 2007 Service Pack 2 and 2007 Microsoft Office Suite Service Pack 2 (SP2)

    Product version for Word, Excel, are all correct and identical even down to the same Product GUID. Give me a bit and I will recreate and test the query for you.

     


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com
    21 Mei 2010 18:41
  • 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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 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 "%Microsoft Office%Service Pack 2%" ) and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Enterprise 2007"

    ok I tested this on one of my test machines.  We have Office Enterprise 2007, hence why you see that here.

    Hope this gets you there....


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com
    • Ditandai sebagai Jawaban oleh mniccum 22 Mei 2010 13:19
    21 Mei 2010 19:08
  • 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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 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 "%Microsoft Office%Service Pack 2%" ) and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Enterprise 2007"

    ok I tested this on one of my test machines.  We have Office Enterprise 2007, hence why you see that here.

    Hope this gets you there....


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com


    This worked perfectly.  I had no idea how difficult this particular query was going to be.  I appreciate all the effort!!!!  5 star!

    Thank

     

    22 Mei 2010 13:19
  • Without the perfect query you can't perform your duties and there are so many things and tricks you can do with collections.  Glad it worked for you!
    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com
    23 Mei 2010 0:30
  • 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.ResourceId not in (select SMS_R_System.ResourceID 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 = "2007 Microsoft Office Suite Service Pack 2 (SP2)" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "2007 Microsoft Office Suite Service Pack 2 (SP2)") and SMS_R_System.Obsolete = 0 and SMS_R_System.Client = 1 and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%" and SMS_R_System.Name not in ("MACHINE NAMES","Machine Names")

    this worked for me...


    Christian Parmigiani

    23 Maret 2012 10:10
  • 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.ResourceId not in (select SMS_R_System.ResourceID 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 = "2007 Microsoft Office Suite Service Pack 2 (SP2)" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "2007 Microsoft Office Suite Service Pack 2 (SP2)") and SMS_R_System.Obsolete = 0 and SMS_R_System.Client = 1 and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT Workstation%" and SMS_R_System.Name not in ("MACHINE NAMES","Machine Names")

    FYI Chris, your query will only find x86 systems, you need to query SMS_G_System_ADD_REMOVE_PROGRAMS_64 to get the x64 systems too.



    http://www.enhansoft.com/

    23 Maret 2012 12:27