Monday 30 June 2014

MDM in SCCM 2012 R2 - Device Collection Queries

Back to ConfigMgr main menu
 
Back to MDM Menu


Collection of Windows Phone 8 devices

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_DEVICE_OSINFORMATION on SMS_G_System_DEVICE_OSINFORMATION.ResourceID = SMS_R_System.ResourceId where SMS_G_System_DEVICE_OSINFORMATION.Platform like "Windows Phone" and SMS_G_System_DEVICE_OSINFORMATION.Version like "8.0%"


Collection of Windows Phone 8.1 devices

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_DEVICE_OSINFORMATION on SMS_G_System_DEVICE_OSINFORMATION.ResourceID = SMS_R_System.ResourceId where SMS_G_System_DEVICE_OSINFORMATION.Platform like "Windows Phone" and SMS_G_System_DEVICE_OSINFORMATION.Version like "8.1%"


Collection of Windows RT devices

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.Model like "Surface%"


Collection of iPhones 

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_DEVICE_COMPUTERSYSTEM on SMS_G_System_DEVICE_COMPUTERSYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_DEVICE_COMPUTERSYSTEM.DeviceModel like "%iphone%"


Collection of iPads

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_DEVICE_COMPUTERSYSTEM on SMS_G_System_DEVICE_COMPUTERSYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_DEVICE_COMPUTERSYSTEM.DeviceModel like "%ipad%"


Collection of Android devices

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_DEVICE_OSINFORMATION on SMS_G_System_DEVICE_OSINFORMATION.ResourceID = SMS_R_System.ResourceId where SMS_G_System_DEVICE_OSINFORMATION.Platform like "Android%"



11 comments:

  1. Hi Garry,

    How to create my mobile device colletions?

    ReplyDelete
    Replies
    1. Same as any other device collection if you have a SQL query

      http://henkhoogendoorn.blogspot.ie/2011/10/create-new-windows-collections-based-on.html

      Delete
  2. Hi Gerry another great post! I do have one issue we actually have all these devices in our SCCM environment. I was able to get ALL the queries to work except the Android one. Any ideas? Thanks!

    ReplyDelete
    Replies
    1. Have a look at the properties and hardware inventory of one of the Android devices. What is listed for "Mobile Device Computer System"?

      Delete
  3. Android query doesn't work. The string "Android" isn't set in "SMS_G_System_DEVICE_COMPUTERSYSTEM.DeviceModel", but in "SMS_G_System_DEVICE_OSINFORMATION.Platform".
    Regards

    ReplyDelete
  4. Hi Gerry,

    I've have created a device collection off all the ipad device in our company. This is working just fine. Now I want to know which device belongs to which user. Could you help me.

    ReplyDelete
    Replies
    1. That is a challenge. Have a look at this tool by Peter van der Woude.

      https://www.petervanderwoude.nl/post/new-tool-remote-mobile-device-manager/

      Delete
    2. Hi,

      None of these seem to work for us? Do I have to specify the strings somewhere first?

      Delete
    3. No, you don't have to do anything. These can be used to create queries or collections. They have all been previously verified. I've just now successfully tested the iPhone one again.

      Delete
  5. Hi Gerry,

    what will be the output values of SMS_R_System.MDMStatus ? and please explain the meaning of output values like 0,1,2 and Null.

    Thanks
    Vignesh

    ReplyDelete