Easily Find Mobile Device Info Using SSRS

May 20th, 2015 | Posted by John Marcum in ConfigMgr | Intune | John Marcum | Mobile Device Mangement

With the announcement of the EMS suite, the December 2014 release of Intune along with the monthly release cadence and the feature parity that R2 Service Pack 1 brought us many of us are managing mobile devices with a hybrid deployment of ConfigMgr and Intune. One shortfall I found pretty quickly is that there’s no easy way to match a user to a device in the ConfigMgr console so I begin to view the mobile device reports in SSRS. I found two decent reports but neither list the primary user of the device. When I attempt to edit one of these reports I find it to be overly complex for no apparent reason.

One of my biggest complaints about ConfigMgr is the quality of reporting out of the box. Sure, there’s a lot of reports but many were ported over from the old asp reports so they look like crap in SSRS. Others were written using stored procedures making them almost impossible to edit and lastly there’s a lot of info I need from ConfigMgr for which no report exists out of the box.

Of course the better solution to this problem would be simply adding a “primary user” column in the console which is why I filed a feedback item on connect. If you like that suggestion please vote it up. Maybe Microsoft will implement that solution however in the mean time we will need to rely on some custom reports for this task. I’ve create two reports, the first list all mobile devices being managed (devices discovered by the exchange connector aren’t displayed) along with some information that I found useful about devices as well as, of course, the primary user of the device. Here’s a preview of the headings included in this first report.

 

2015-05-20_8-04-11

The second report accepts a user name as input then list out the devices for that given user. My environment is really small so I actually just do a drop-down box with all of my users in it however you could easily convert that to a text box if that works better for you. This report looks like this.

 

2015-05-20_8-09-17

 

I’m using a custom reporting template as described by John Nelson in his 4 part blog series titled “SSRS Report – Style Based Approach to make my reports look a bit nicer and I suggest everyone else look through that blog series. Unfortunately I think attaching my actual rdl files may be more confusing that beneficial because of this though so I’m just going to give you the raw SQL for the primary dataset in each report. You will need to configure your variable input on your own but that’s pretty easy.

This is the first report:

select

vru.Full_User_Name0 as [Full Name],
SUBSTRING(vru.Unique_User_Name0, CHARINDEX(‘\’, vru.Unique_User_Name0) + 1, LEN(vru.Unique_User_Name0)) AS [User Name],
vrs.Name0 as [Device Name],
comp.PlatformType0 as [Platform],
comp.DeviceManufacturer0 as [Manufacturer],
comp.DeviceModel0 as [Model],
vrs.Operating_System_Name_and0 as [Installed OS],
comp.SerialNumber0 as [Serial Number],
comp.IMEI0 as [IMEI Number],
comp.SubscriberCarrierNetwork0 as [Carrier]
from vMDMUsersPrimaryMachines upm

INNER JOIN v_R_User vru on upm.UserResourceID = vru.ResourceID
INNER JOIN v_R_System vrs on upm.MachineID = vrs.ResourceID
INNER JOIN v_GS_DEVICE_COMPUTERSYSTEM comp on comp.ResourceID=vrs.ResourceID

WHERE ISNULL(vrs.Obsolete0, 0) <> 1 AND
ISNULL(vrs.Decommissioned0, 0) <> 1 AND
vrs.Client0 = 1  AND
ISNULL(vrs.AgentEdition0, 0) <> 5  AND
vrs.Client_Type0 = 3 and vru.Unique_User_Name0 IS NOT NULL

order by vru.Name0

 

And here’s the second one:

select

vru.Full_User_Name0 as [Full Name],
SUBSTRING(vru.Unique_User_Name0, CHARINDEX(‘\’, vru.Unique_User_Name0) + 1, LEN(vru.Unique_User_Name0)) AS [User Name],
vrs.Name0 as [Device Name],
comp.PlatformType0 as [Platform],
comp.DeviceManufacturer0 as [Manufacturer],
comp.DeviceModel0 as [Model],
vrs.Operating_System_Name_and0 as [Installed OS],
comp.SerialNumber0 as [Serial Number],
comp.IMEI0 as [IMEI Number],
comp.SubscriberCarrierNetwork0 as [Carrier]
from vMDMUsersPrimaryMachines upm

INNER JOIN v_R_User vru on upm.UserResourceID = vru.ResourceID
INNER JOIN v_R_System vrs on upm.MachineID = vrs.ResourceID
INNER JOIN v_GS_DEVICE_COMPUTERSYSTEM comp on comp.ResourceID=vrs.ResourceID

WHERE ISNULL(vrs.Obsolete0, 0) <> 1 AND
ISNULL(vrs.Decommissioned0, 0) <> 1 AND
vrs.Client0 = 1  AND
ISNULL(vrs.AgentEdition0, 0) <> 5  AND
vrs.Client_Type0 = 3 and vru.Unique_User_Name0 IS NOT NULL
and SUBSTRING(vru.Unique_User_Name0, CHARINDEX(‘\’, vru.Unique_User_Name0) + 1, LEN(vru.Unique_User_Name0)) = @user

You can follow any responses to this entry through the RSS 2.0 Responses are currently closed, but you can trackback.