How to create a Device collection from a User Collection

Recently I had a query from Senior Managers. They wanted me to remove a Particular VPN Profile from a Small user group who were testing AOVPN with DUO. I have had the User collection but not the Device Collection. I wanted to run a PowerShell script from SCCM which removes VPN Profile from a Machine. But you cannot Run Script on User collection from SCCM. It needs to be a Device collection. After spending 30 minutes on the Confusion below is the Query I created which created a Device collection from a User collection ID and Wallah….

Hope it helps someone…

select distinct sms_R_system.Name,
sms_R_User.UniqueUserName
FROM sms_R_system

JOIN SMS_UserMachineRelationship ON SMS_R_System.Name = SMS_UserMachineRelationship.MachineResourceName

JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName

JOIN SMS_FullCollectionMembership on SMS_R_User.UniqueUserName = SMS_FullCollectionMembership.SMSID

WHERE SMS_FullCollectionMembership.CollectionID = 'PUT YOUR Collection ID here'

Share on Facebook

Tweet

Follow us