SCCM Collection for TPM

[CHiLL]

We are looking into using BitLocker for our off-site staff laptops. I have incorporated MBAM 2.5 into SCCM for reporting and monitoring, which created a collection of MBAM supported devices. I have modified that collection only count laptop/portable devices, which works fine. One of the rules within the query created was that the 'TPM.SpecVersion' is 1.2 or greater.

 

So to find out which devices don't have a TPM or they do and it is lower than 1.2, I have copied the collection and changed the rule to 'is less than' 1.2.

 

However, both collections are displaying the exact same results of 79 devices, and they are the same 79 devices.

 

The query for supported MBAM devices is:

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_OPERATING_SYSTEM on 
SMS_G_System_OPERATING_SYSTEM.ResourceID = 
SMS_R_System.ResourceId inner join 
SMS_G_System_OPERATING_SYSTEM_EXT on 
SMS_G_System_OPERATING_SYSTEM_EXT.ResourceID = 
SMS_R_System.ResourceId inner join 
SMS_G_System_COMPUTER_SYSTEM on 
SMS_G_System_COMPUTER_SYSTEM.ResourceID = 
SMS_R_System.ResourceId left join SMS_G_System_TPM on 
SMS_G_System_TPM.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_SYSTEM_ENCLOSURE on 
SMS_G_System_SYSTEM_ENCLOSURE.ResourceId = 
SMS_R_System.ResourceId where 
(SMS_G_System_OPERATING_SYSTEM.Version like "6.1.%" and 
SMS_G_System_OPERATING_SYSTEM_EXT.SKU in 
(1,4,27,28,70,71) and SMS_G_System_TPM.SpecVersion >= "1.2" or 
not SMS_G_System_OPERATING_SYSTEM.Version like "6.1.%" or 
SMS_G_System_OPERATING_SYSTEM.Version like "6.0.%" or 
SMS_G_System_OPERATING_SYSTEM.Version like "5.%") and 
SMS_G_System_COMPUTER_SYSTEM.DomainRole = 1 and SMS_G_System_COMPUTER_SYSTEM.Model not in ("Virtual 
Machine","VMware Virtual Platform","VirtualBox") and 
SMS_G_System_COMPUTER_SYSTEM.Manufacturer not in ("Xen") 
and SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("8", 
"9", "10", "11", "12", "14", "18", "21")

 

The query for non-supported MBAM devices is:

[code]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_OPERATING_SYSTEM on 
SMS_G_System_OPERATING_SYSTEM.ResourceID = 
SMS_R_System.ResourceId inner join 
SMS_G_System_OPERATING_SYSTEM_EXT on 
SMS_G_System_OPERATING_SYSTEM_EXT.ResourceID = 
SMS_R_System.ResourceId inner join 
SMS_G_System_COMPUTER_SYSTEM on 
SMS_G_System_COMPUTER_SYSTEM.ResourceID = 
SMS_R_System.ResourceId left join SMS_G_System_TPM on 
SMS_G_System_TPM.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_SYSTEM_ENCLOSURE on 
SMS_G_System_SYSTEM_ENCLOSURE.ResourceId = 
SMS_R_System.ResourceId where 
(SMS_G_System_OPERATING_SYSTEM.Version like "6.1.%" and 
SMS_G_System_OPERATING_SYSTEM_EXT.SKU in 
(1,4,27,28,70,71) and SMS_G_System_TPM.SpecVersion < "1.2" or 
not SMS_G_System_OPERATING_SYSTEM.Version like "6.1.%" or 
SMS_G_System_OPERATING_SYSTEM.Version like "6.0.%" or 
SMS_G_System_OPERATING_SYSTEM.Version like "5.%") and 
SMS_G_System_COMPUTER_SYSTEM.DomainRole = 1 and SMS_G_System_COMPUTER_SYSTEM.Model not in ("Virtual 
Machine","VMware Virtual Platform","VirtualBox") and 
SMS_G_System_COMPUTER_SYSTEM.Manufacturer not in ("Xen") 
and SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in ("8", 
"9", "10", "11", "12", "14", "18", "21")

[/code]

 

Anyone have any ideas?

Edited March 12, 2018 by CHiLL

[ReadTheNetwork]

I think it could be the missing "not"

"not" SMS_G_System_OPERATING_SYSTEM.Version like "6.0.%" or

"not" SMS_G_System_OPERATING_SYSTEM.Version like "5.%")

 

Should it be inverted depending on if it's supported or not?

Edited March 12, 2018 by ReadTheNetwork