none
Collection Query multiple checks RRS feed

  • Question

  • I'm attempting to build a Collection query (and honestly havent' done much query building). I had a previous question here: http://social.technet.microsoft.com/Forums/en/configmgrswdist/thread/e5c7a99d-18d4-4fb1-8d60-badeb8112828

    Which worked perfectly well to identify machines with Application xyz installed. As more or less a followup I'd like to know (example if possible), how to build a query which basically does something like this.

    Show me all the machines that have Application 1 AND Application 2 AND Application 3 (basically multiple applications as identified in Add/Remove programs by Program Description)..

    THANKS as Always.

    Wednesday, July 14, 2010 12:29 PM

Answers

  • Sorry about that, here you go:

    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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_R_SYSTEM.ResourceID 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 = "App1") AND SMS_R_SYSTEM.ResourceID 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 = "App2") AND SMS_R_SYSTEM.ResourceID 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 = "App3")


    Scott Gill
    SCCM Consultant
    Thursday, July 15, 2010 5:14 PM

All replies

  • This is pretty simple.. the query below should do it for you.

    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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "App1" and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "App2" and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "App3"


    Scott Gill
    SCCM Consultant
    Wednesday, July 14, 2010 8:02 PM
  • Thanks Scott.. but NO GO.. Returns 0 results.

    I put the query in for APP1 only and return results and then add the: and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "App2" to look for multiple apps and return 0 results (when I KNOW there are machines with BOTH apps installed)...

    I'll keep trying variations, but if there if there are ideas let me know.

    (I'm fairly new to SMS/SCCM and havn't done much with these collection queries yet... trial by fire).

    Thursday, July 15, 2010 12:34 PM
  • Sorry about that, here you go:

    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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_R_SYSTEM.ResourceID 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 = "App1") AND SMS_R_SYSTEM.ResourceID 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 = "App2") AND SMS_R_SYSTEM.ResourceID 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 = "App3")


    Scott Gill
    SCCM Consultant
    Thursday, July 15, 2010 5:14 PM
  • THANKS Scott... I'll give this example a try & see what's going on..
    Monday, July 19, 2010 11:06 AM
  • Hi Scott

    The above query is very helpful and thanks again for this!

    Is it possible to further the query so that it also looks for a specific version of the multiple applications?

    Thanks, Dan

    Wednesday, February 3, 2016 12:35 PM
  • The above query is very helpful and thanks again for this!

    Is it possible to further the query so that it also looks for a specific version of the multiple applications?


    Yes you can do that add the version number to each of the subqueries.

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

    Wednesday, February 3, 2016 1:26 PM
    Moderator
  • 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_R_SYSTEM.ResourceID 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 = "App1") AND SMS_R_SYSTEM.ResourceID 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 = "App2") AND SMS_R_SYSTEM.ResourceID 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 = "App3")


    When I try this in SCCM2012 it's not working for me.
    Anyone having a working query for me?

    Wednesday, August 16, 2017 9:10 AM

  • When I try this in SCCM2012 it's not working for me.
    Anyone having a working query for me?

    And exactly what is not working for you.

    Garth Jones

    Blog: http://www.enhansoft.com/blog Old Blog: http://smsug.ca/blogs/garth_jones/default.aspx

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleased

    Wednesday, August 16, 2017 10:54 AM
    Moderator
  • Hi Garth,

    When I try to use it as query under monitoring I receive the error:
    This query either has a syntax error or is using features of the language not supported in design view

    When I use it as an query on a device collection I receive the following error:
    The query statement that you entered is not valid. Enter a valid query statement. 



    • Edited by vanderaatje Wednesday, August 16, 2017 11:57 AM
    Wednesday, August 16, 2017 11:28 AM
  • Hi Garth,

    When I try to use it as query under monitoring I receive the error:
    This query either has a syntax error or is using features of the language not supported in design view

    When I use it as an query on a device collection I receive the following error:
    The query statement that you entered is not valid. Enter a valid query statement. 




    I have personally tested Scott query and it works without any issue in CM12 and CMCB. So I would try again.

    Garth Jones

    Blog: http://www.enhansoft.com/blog Old Blog: http://smsug.ca/blogs/garth_jones/default.aspx

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleased

    Wednesday, August 16, 2017 12:21 PM
    Moderator
  • Hi Garth,

    I have found the problem when I copy it from the Edge browser it's not working when I copy it from IE it goes fine.
    Thanks for your time and hopefully this information is also handy for others

    Wednesday, August 16, 2017 1:34 PM