SOLVED SQL Server 2019 device collection in SCCM

[Harshit Pandey]

@Prajwal Desai - I am using the below query from your blog to create SQL Server 2019 device collection, but it is not working for me. The collection is created successfully, but after few minutes the collection shows 0 members. We have around 10 SQL servers running at 2019 within the database team. I checked, and they have SCCM client installed and updated to the latest version. What could be reason for this issue?

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 = 'Microsoft SQL Server 2019')

 

[Prajwal Desai]

When you enter the SQL Server WQL query while creating the collection, there is a run option that instantly checks the devices that match the criteria. If you don't see any SQL servers in the query results, it means the query is incorrect or not working.

 

[Harshit Pandey]

I found the fix, one of my colleague had a created a collection of all SQL servers with wrong query, and I was using this collection as a limiting collection for SQL 2019. When I changed limiting collection to All systems, it gave the results. That's why the collection was resulting with 0 members. What a stupid mistake.

 

[Prajwal Desai]

Nice to see you figured it out. Closing this thread.