Hi guys,
I have a Microsoft endpoint configuration manager 2002 version and currently when I view the options/criteria in any device collection i can not tell whether it's laptop vs desktop and what model it is.
is there a way to pull a list of all computers and be able to identify laptop vs desktop and system model.
thank you for your help
I revised your query to see windows 10 only:
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_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in (8 , 9, 10, 11, 12, 14, 18, 21 ) and SMS_G_System_OPERATING_SYSTEM.Caption like "%Windows 10%"
If the response is helpful, please click "Accept Answer" and upvote it.
@Alex-4040
Thank you for posting in Microsoft Q&A forum.
You may try the sql query:
SELECT
v_GS_COMPUTER_SYSTEM.Name0,
v_GS_COMPUTER_SYSTEM.Model0,
'Chassis'=CASE
WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
END
from v_GS_SYSTEM_ENCLOSURE
inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID=v_GS_SYSTEM_ENCLOSURE.ResourceID
Here is the Chassis type values in SCCM for your reference:
https://docs.microsoft.com/en-us/archive/blogs/breben/chassis-type-values-in-sccm
If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
@Alex-4040
Thank you for posting in Microsoft Q&A forum.
SELECT
v_GS_COMPUTER_SYSTEM.Name0,
v_GS_COMPUTER_SYSTEM.Model0,
v_GS_OPERATING_SYSTEM.Caption0,
'Chassis'=CASE
WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
END
from v_GS_SYSTEM_ENCLOSURE
inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID=v_GS_SYSTEM_ENCLOSURE.ResourceID
inner join v_GS_OPERATING_SYSTEM on v_GS_OPERATING_SYSTEM.ResourceID=v_GS_COMPUTER_SYSTEM.ResourceID
If the response is helpful, please click "Accept Answer" and upvote it.
@AllenLiu-MSFT
thank you again for the help. that worked as needed.
do you know what query statement do I need to include for my device collection. I need to see only show a collection for bitlocker dployment for criteria:
laptops only
windows 10 OS
I currently have the following bbut its showing lower number
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_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in (8 , 9, 10, 11, 12, 14, 18, 21 )
Hi,
Does chassistypes0 31 (Convertible) help. I found this for several Yoga's we have.
Nik
@Alex-4040
Replace the CollectionName with your collection for bitlocker:
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_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_SYSTEM_ENCLOSURE.ChassisTypes in (8 , 9, 10, 11, 12, 14, 18, 21 )
and ResourceID in (select ResourceID from
SMS_FullCollectionMembership JOIN SMS_Collection on SMS_FullCollectionMembership.CollectionID =
SMS_Collection.CollectionID where SMS_Collection.name = "CollectionName")
i was looking at the details to insert here at the collection criteria to pull only laptops and windows 10 
.
Yes, I know, you said you need to show a collection for bitlocker deployment for criteria, so my query is for you to create the collection pull only laptops and are members of the bitlocker colloction. Did you use my query and replace the CollectionName with your collection name for bitlocker?
If I understand wrong, you may describe your needs in more detail.
the query is also returning windows 7 laptops and I just want to see windows 10 only
6 people are following this question.
