Tuesday 1 September 2015

SCCM Collection Query for All SQL versions

           ,      3 comments   
SQL 2012 :

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 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 "Microsoft SQL Server 2012%" and SMS_G_System_SYSTEM.SystemRole = "Server"


SQL 2008 :

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 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 "Microsoft SQL Server 2008%" and SMS_G_System_SYSTEM.SystemRole = "Server"

3 comments:

  1. Anonymous19 September 2016 at 08:05

    Hi

    thanks for this Collection's queries, however this for 2008 collecting SQL 2012 as well. I don't have clue why. Any Ideas ?

    Literally I just copied and paste the query...

    ReplyDelete
  2. Unknown14 December 2016 at 14:31

    Same here thats really strange.

    ReplyDelete
  3. Unknown18 March 2019 at 12:39

    I ran this query and it was a great start, thank you. However I received duplicates with some instances so I added a distinct to the query and also some nodes had the following installed:
    Microsoft SQL Server 2008 R2 Management Objects & Microsoft SQL Server 2008 Setup Support Files in the add/remove programs even though the primary SQL Server was > 2008. As a result I wound up with this query which worked better for me:

    select distinct 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 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 "Microsoft SQL Server 2008%" and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "Microsoft SQL Server 2008 R2 Management Objects%" and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "Microsoft SQL Server 2008 Setup Support Files%" and SMS_G_System_SYSTEM.SystemRole = "Server"

    ReplyDelete

Give me you feedback!