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,
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'