Query for devices with SCEP installed

[msngod]

Hi,

 

First of all I want to thank anyweb for his guides, without it I never got SCCM up and running.

 

Everything is working fine exept the the fact I want to create a device collection with all servers and workstation running System Center 2012 Endpoint protection.

I got the query that is showing under this line. After creating a device collection with this query it is showing only the servers and not the workstations.

 

 

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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "System Center 2012 Endpoint Protection") and SMS_R_System.Client = 1

 

If I remove NOT in this line SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join it is showing all the workstations but not the servers.

 

The environment is running windows server 2008 r2 on the server and Windows 7thin pc on the workstations.

 

Can you guys help me to make this query working for Windows Server 2008 R2 and Windows 7 THin pc?

[Peter van der Woude]

I'm wondering why you would need/ want this collection.. because EP is now builtin including client deployment and monitoring.

[msngod]

Because I created 2 collection:

 

1 one windows server 2008 r2 and the other one is windows 7 thin pc.

 

When you are creating a automatic deployement rule for the SCEP definitions you can only choose one collection to deploy the definitions to. Thats why I want one device collections for all servers and workstations.

 

If you got another solution, I am willing to hear that.

[Peter van der Woude]

The updates are the same for all the machines, so I don't really see why you want to split that... But if you really want that you can create a query collection and then use Endpoint Protection Deployment State - Deployment State as criteria and add an extra criteria for the OS and your done.

[msngod]

I understand that the updates for scep are the same for SCEP program. I got two device collection because we deploy different programs to servers and workstation.

 

SCEP is installed on the server and workstations and I want one device collection for all devices running scep. And with your solution it is working now!

Thank you very much for your support!