Manual Query finds matches for collection, but automatic collect displays 0 members

[Brian S]

The following query for duplicate serial number information identifies 77 matches when run manually. by manual i mean I select the membership query of which it is the only rule, and go into the query editor and hit the green arrow to test for query results.

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_PC_BIOS as b on b.ResourceID = SMS_R_System.ResourceId full join SMS_G_System_PC_BIOS as s1 on s1.ResourceID = b.ResourceID full join SMS_G_System_PC_BIOS as s2 on s2.SerialNumber = s1.SerialNumber where s1.SerialNumber = s2.SerialNumber and s1.ResourceID != s2.ResourceID

When this same query is done automatically by choosing update members for the collection, I get no results. Any ideas? version 2211

 

[Garth]

When this same query is done automatically by choosing update members for the collection, I get no results. Any ideas? version 2211

What exactly do you mean by this?

 

[Brian S]

I'll try to explain in more detail. When I look at the Dashboard for the collections, the member count of this particular collection is showing 0 members. If I right click on the collection and select update membership it remains at 0 after doing a re-evaluation. If I open the properties for the collection. select edit on the membership query. Open the edit query statement and run that query I get returned results matching the criteria that should be listed in the collection. Currently I get 49 returned results. Hopefully that clarifies. I'm baffled why I don't see the 49 in the collection results particularly after selecting update membership.

 

[Brian S]

The issue seems like it could be a bug. I have 68 members of the group today. perhaps its due to pruning of devices, because some of the devices listing as having duplicates don't have a corresponding host with a duplicate serial number.

If the hosts have duplicate serial numbers and you sort by serial number on the column there should be N+1 for each serial number , but on occasion there will appear a serial number that is not duplicated. I can only guess that perhaps the duped serial number host has been pruned, or deleted. I did delete those that were aged 30 days.

 

[BMontanye]

The following query for duplicate serial number information identifies 77 matches when run manually. by manual i mean I select the membership query of which it is the only rule, and go into the query editor and hit the green arrow to test for query results.

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_PC_BIOS as b on b.ResourceID = SMS_R_System.ResourceId full join SMS_G_System_PC_BIOS as s1 on s1.ResourceID = b.ResourceID full join SMS_G_System_PC_BIOS as s2 on s2.SerialNumber = s1.SerialNumber where s1.SerialNumber = s2.SerialNumber and s1.ResourceID != s2.ResourceID

When this same query is done automatically by choosing update members for the collection, I get no results. Any ideas? version 2211

I have this exact issue, but its never populating. When I preview the query on the 22H2 collection I've been trying to create, it pulls back the expected number of devices. When I then apply that and update the collection, I get 0 members.

 

[Garth]

I have this exact issue, but its never populating. When I preview the query on the 22H2 collection I've been trying to create, it pulls back the expected number of devices. When I then apply that and update the collection, I get 0 members.

The query above, you want it to have zero members. It locals dup guid devices. So exactly why do you think it should have members?

 

[BMontanye]

The query above, you want it to have zero members. It locals dup guid devices. So exactly why do you think it should have members?

No, I want it to show the 800+ devices it picks up when I run the query in preview - when actually applied to the collection membership, I get 0 members where there should be 800+. "If I open the properties for the collection. select edit on the membership query. Open the edit query statement and run that query I get returned results matching the criteria that should be listed in the collection." This exact scenario to a T.

 

[BMontanye]

Basically, when I run the query for duplicate devices in the preview - it works fine. I then click ok, and refresh the collection. I get no results, when there should be the devices that were present when I tested the query in preview. I am not sure why this happens. In order to find my duplicates, I have to open the empty collection, go to the membership rules, run the preview - which then shows me the devices that have multiple instances in the console. I am having a hard time understanding why when I run it manually in the preview, it works perfectly. When I let the collection evaluate and refresh, it finds nothing.