The following sample queries demonstrate how to join mobile device management views to other views. 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]

See Also