Home / server / Questions / 848184
Accepted
RayofCommand
Asked: 2017-05-04 23:59:31 +0800 CST

Catching all Server with SQL Server (any Version) installed using SSCM

  • 772

I am trying to create a server collection which collects all Server with SQL Server installed on them in a smart way. I created a Collection and added already some servers using the criterion properties and as shown in the screenshot. Now I don't want to add 100 SQL Versions listed on the right part of the screenshot manually to my criteria. So I thought I will just rework the Query Statement myself. But I didn't find a way to copy all the SQL versions listed... (Also I don't have access to the DB behind SSCM)

And if I use a query using is like Microsoft SQL Server I will get plenty of native clients, which I don't need.

Can someone help out a rookie here?

enter image description here

sql-server

1 Answers

  1. Best Answer

    I use the following query, it will only show machines with Windows Server OS with SQL Server 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.ResourceId in (select distinct SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName Like 'Microsoft SQL Server %') AND SMS_R_System.OperatingSystemNameandVersion like "%server%"

    I've found a couple of good sites that have a lot of query samples. I use the one by Anders Rodland quite a lot, have found it very helpful. Hope this all helps

    http://www.andersrodland.com/ultimate-sccm-querie-collection-list/

    • 2