Sample Queries for Mobile Device Management in Configuration Manager
Updated: January 1, 2014
Applies To: System Center 2012 Configuration Manager, System Center 2012 Configuration Manager SP1, System Center 2012 R2 Configuration Manager
The following sample queries demonstrate how to join mobile device management views to other views when the device is managed by Configuration Manager. Mobile device management views will most often be joined to other views by using the ResourceID and DeviceClientID columns.
Joining Mobile Device Management Hardware Inventory and Discovery Views
The following query retrieves all mobile device Configuration Manager clients, by NetBIOS name, the operating system, the amount of storage space on the device, and the amount of free storage space on the device. The results are sorted by the NetBIOS name. The query joins the v_GS_DEVICE_COMPUTER_SYSTEM mobile device management hardware inventory view with the v_R_System discovery view by using the ResourceID column, and it joins the v_GS_DEVICE_COMPUTER_SYSTEM and v_GS_DEVICE_MEMORY mobile device management hardware inventory views by using the ResourceID column.
SELECT v_R_System.Netbios_Name0,
v_R_System.Operating_System_Name_and0,
v_GS_DEVICE_MEMORY.Storage0,
v_GS_DEVICE_MEMORY.StorageFree0
FROM v_GS_DEVICE_COMPUTER_SYSTEM INNER JOIN v_R_System ON
v_GS_DEVICE_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_DEVICE_MEMORY ON
v_GS_DEVICE_COMPUTER_SYSTEM.ResourceID = v_GS_DEVICE_MEMORY.ResourceID
ORDER BY v_R_System.Netbios_Name0
Joining Mobile Device Management and Status Views
The following query retrieves the deployment state for all mobile device Configuration Manager clients, including the state name and description, NetBIOS name for the device, IP address, assigned site code, and deployment date and time. The results are sorted by the deployment state and then the NetBIOS name. The query joins the v_DeviceClientDeploymentState mobile device management view with the v_StateNames status view by using the StateID column. The retrieved information is filtered by the topic type of 800, which includes state messages for client deployment.
SELECT v_StateNames.StateName AS [Deployment State],
v_StateNames.StateDescription AS Description,
v_DeviceClientDeploymentState.DeviceNetBiosName AS [Device Name],
v_DeviceClientDeploymentState.IPAddress AS [IP Address],
v_DeviceClientDeploymentState.AssignedSiteCode AS [Assigned Site],
v_DeviceClientDeploymentState.DeviceDeploymentTime AS [Time Deployed]
FROM v_DeviceClientDeploymentState INNER JOIN v_StateNames ON
v_DeviceClientDeploymentState.DeviceDeploymentState = v_StateNames.StateID
WHERE (v_StateNames.TopicType = 800)
ORDER BY [Deployment State], [Device Name]