Collections not showing all machines, missing about 125

[TechGuy83]

This has me so confused I have 3 collections, they are going to be used to deploy software so I need to be confident they are correct.

 

My 3 collections are as follows:

 

All Workstations

select SMS_R_SYSTEM.Name from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%Workstation%

 

All Windows 7 Workstations

select SMS_R_System.Name from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like "%Workstation%6.1%"

 

All Windows XP Workstations

select SMS_R_System.Name from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like "%Workstation%5.1%"

 

Adding Windows 7 and Windows XP collections together didn't give the number of workstations in the all workstations group, so I created this query to see what machines were missing:

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.Name in (select SMS_R_SYSTEM.Name from SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "%Workstation%") and SMS_R_System.Name not in (select SMS_R_System.Name from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like "%Workstation%5.1%") and SMS_R_System.Name not in (select SMS_R_SYSTEM.Name from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like "%Workstation%6.1%")

 

It shows I have 125 not in the collection of XP or 7 looking at the properties on the 125 most of them are tablet edition of XP but however the statement I am using is like and has a percent after the 5.1 so should capture it?? In fact it does but only when using %Workstation%. How weird anyone seen anything like this before? Am I missing something obvious?

 

Clients are installed and working ok.

[TechGuy83]

My queries had an inner join which screwed up the results. Just removed the inner join and now they work. I also added exclusions for certain machines with names that began like, the reason why I was re-writing the built-in queries.

[Peter33]

Just a hint. You don't have to join the queries to get this result. Just create 2 queries in your collection holding the original query of one collection. This will help you to avoid unexpected results, especially if you have already advertisments applied to the colection.

[TechGuy83]

Thanks for the hint. I am a little confused by it could you give me an example if possible please?

[Peter33]

Well, you have already the deafult collections for Windows 7 and for Windows XP. So instead of joining the 2 queries in your new collection (XP+7) go to the membership rules of this collection and create 2 new queries. You don't have to edit the query statements of these queries. Just limit them to the Windows 7 and Windows XP collections.

[TechGuy83]

Ok I understand now thanks for your help, much appreciated.