Once you’ve integrated your Configuration Manager environment with Intune and you’ve started enrolling users it’s useful to be able to find who is still connecting directly to EAS. You will want to track these users down and get them enrolled before enabling conditional access. The info contained in this blog assumes that you’ve configured the Exchange Server Connector and allowed it to discover devices from Exchange.
One might thing this task was as simple as finding all the mobile devices where System_Disc MDStatus is not 1, at least that’s what I first assumed. When I ran this:

select * FROM System_Disc

INNER JOIN MDMDeviceProperty on CAST(DeviceID as nvarchar(255)) = SMS_Unique_Identifier0

where PRopertyName = ‘MdmStatus’ and EAS_DeviceID is not NULL

I thought I was golden! 100% of my devices have MdmStatus = 1 so they must all be enrolled right?? Of course I know that is not true so I dug around some. As it turns out there’s a bug causing MdmStatus to always be 1 so we can’t use that.

Here’s a query that actually works. This shows all the devices connected to EAS in the past 7 days and where or not they are enrolled in MDM. It also tells you the user of the device so it’s easy to track them down and get them enrolled. This should help you tame BYOD!

(you will probably have to fix the smart quotes if you copy and paste this query from a browser)

SELECT

CASE WHEN IsCompliant = ‘1’ THEN ‘Device is enrolled’

ELSE ‘Device is not enrolled’

END as ‘Enrollment Status’,

deviceid as ‘Device ID’,

FriendlyName ‘Device Name’,

UserName ‘User’,

DeviceOS ‘Operating System’,

DeviceType ‘Device Type’,

DeviceModel ‘Device Model’,

ExchangeServer ‘Exchange Server Connected’,

max ((left (FirstSyncTimeUTC, 11))) as ‘First Sync Time’,

max ((left (LastSuccessSyncTimeUTC, 11))) as ‘Last Sync Time’

FROM EAS_Property

where DATEDIFF(d,LastSuccessSyncTimeUTC,GetDate()) < 7

group by

IsCompliant,

deviceid,

FriendlyName,

UserName,

DeviceOS,

DeviceType,

DeviceModel,

ExchangeServer,

FirstSyncTimeUTC,

LastSuccessSyncTimeUTC

 

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