SQL Query to get Mobile Device and Primary Users from Microsoft Configmgr

Following query will give you list of the mobile device managed by Intune and its user from Configmgr.


SELECT        dbo.v_R_System.Name0, dbo.v_GS_DEVICE_COMPUTERSYSTEM.IMEI0, dbo.v_GS_DEVICE_COMPUTERSYSTEM.PhoneNumber0, dbo.v_GS_DEVICE_COMPUTERSYSTEM.DeviceManufacturer0, 
                         dbo.v_GS_DEVICE_COMPUTERSYSTEM.DeviceModel0, dbo.v_GS_DEVICE_COMPUTERSYSTEM.SerialNumber0, dbo.v_GS_DEVICE_COMPUTERSYSTEM.TimeStamp AS Expr1, 
                         dbo.v_GS_DEVICE_COMPUTERSYSTEM.FirmwareVersion0, dbo.v_GS_DEVICE_COMPUTERSYSTEM.SoftwareVersion0, dbo.v_R_User.Full_User_Name0, dbo.v_R_User.Name0 AS UserID, 
                         dbo.v_R_User.User_Principal_Name0
FROM            dbo.v_GS_DEVICE_COMPUTERSYSTEM RIGHT OUTER JOIN
                         dbo.v_R_System INNER JOIN
                         dbo.v_UsersPrimaryMachines INNER JOIN
                         dbo.v_R_User ON dbo.v_UsersPrimaryMachines.UserResourceID = dbo.v_R_User.ResourceID ON dbo.v_R_System.ResourceID = dbo.v_UsersPrimaryMachines.MachineID ON 
                         dbo.v_GS_DEVICE_COMPUTERSYSTEM.ResourceID = dbo.v_R_System.ResourceID
WHERE        (dbo.v_R_System.MDMStatus = N'1')


Sample Report is shown below:

Comments

Popular posts from this blog

Unable to add ipads to Apple DEP/Business Manager

How to enable iOS unmanaged apps to read managed contacts & write unmanaged contacts without compromising security using Microsoft Intune

How to deploy Application (offline installer DMG) on Apple MacOS Devices using Microsoft Intune