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

When creating a new ConfigMgr 2012 distribution point I noticed that the directories for my DP were being created on the remote site server but no content was being placed in them. Upon investigating further I see and error “Failed to configure IIS virtual directories on the distribution point” followed by a message reading “SMSWeb website could not be found” in the distmgr.log on the primary site server. I tried removing the distribution point and adding it back several times but that didn’t help. I even tried removing IIS and adding it back manually rather than allowing ConfigMgr to install and configure it but I kept seeing the same issue. Out of frustration I decided to just manually create the SMSWeb website. First I created a directory “SMSWeb” under C:\Inetpub then I created a custom website called SMSWeb on port 8080. As soon as the website was created packages started flowing to my new distribution point. Problem solved!

In complex ConfigMgr 2007 environments it was often hard to locate packages and collection that had been buried as child objects. John Nelson wrote some pretty handy SQL queries that would parse the exact path to a given object. ConfigMgr 2012 made an attempt to remedy this issue in several ways. First of all we no longer nest object for organizational purposes, instead we use folder. Secondly each node is searchable. This does tend to make life easier however from time to time I still need to know what folder an object resides in and the built-in search doesn’t give me that ability.

As an example let’s assume I have a collection to which I deploy Adobe Flash 13.x. When Adobe 14.x releases I want to create a new collection called Adobe Flash 14.x and I want it in the same folder as the aforementioned Adobe Flash 13.x. Sure, if I want to make an edit to the Flash 13.x collection I can search for it, locate it and make changes without needing to know where in the Assets and Compliance node it truly lives but that’s not my goal here. I need to know where it lives. To find this I have a created a few SQL queries that can be used in SQL Management Studio or added as SSRS reports.

Below you will find 3 queries. One will list all of your collections with their containing folders, the next does the same for applications and lastly one for traditional packages. I wasn’t able to figure out how to get the full path to the folder if the folder is a sub-folder of other folders but that way I have my folders organized just knowing the containing folder is normally good enough.

 

–Find my collection

select
col.Name as ‘Collection Name’,
F.Name as ‘Folder Name’,
COL.CollectionID as ‘Collection ID’
from
v_collection col
LEFT JOIN vFolderMembers FM on fm.InstanceKey = col.CollectionID
LEFT Join vSMS_Folders F on F.ContainerNodeID = FM.ContainerNodeID
Where FM.ObjectTypeName = ‘SMS_Collection_Device’

order by col.Name

–Find my application

Select Distinct

Apps.DisplayName as ‘Application Name’,
C.ContentSource as ‘Application Source Path’,
F.Name as ‘FolderName’

From fn_ListApplicationCIs(1033) Apps
Left Join v_CIContentPackage CP on CP.CI_ID = Apps.CI_ID
Left Join v_Content C on C.PkgID = CP.PkgID
Left Join vFolderMembers FM on FM.InstanceKey =Apps.ModelName
Left Join vSMS_Folders F on F.ContainerNodeID = FM.ContainerNodeID
Where ContentSource Is not null
Order By ContentSource

 

— Find my package

select
Pkg.Name as ‘Package Name’,
F.Name as ‘Folder Name’,
PKGSourcePAth as ‘Package Source Path’
from
v_Package pkg
LEFT JOIN vFolderMembers FM on fm.InstanceKey = pkg.PackageID
LEFT Join vSMS_Folders F on F.ContainerNodeID = FM.ContainerNodeID
Where FM.ObjectType = 2

I’ve recently migrated all of my software deployments from the legacy package and program model into the ConfigMgr 2012 application model. During a lunch and learn that I was presenting to our User Services team explaining how this works and why we moved to it one of the technicians posed a good question. He asked, “How do I check the status of an application that I’ve targeted to a user”? I know how I would do it; I’d check the monitoring node in the console, however I don’t give the console to User Services. I start digging through the built-in reports and found nothing that would answer his question.

That seemingly simple question turned out to be less than simple. In the past I’ve always trained support staff to read execmgr.log and use the built-in reports. The application model isn’t so simple though, frankly application logs on the client still confuse me. I set out to write a report that accepts the user name as input and displays the status of all deployments targeted to that user and/or targeted to that users’ primary devices.

Here’s a simple way for the Support Desk and Computer Technicians to see the status of applications. This report accepts the user name as input and can be filtered on a given application, set of applications of all applications in ConfigMgr.

image

Here’s the SQL I’m using for the report divided by the 3 datasets required.

–Main dataset:

select
all

SMS_AppDeploymentAssetDetails.AppName as ‘App Name’,

CASE WHEN SMS_AppDeploymentAssetDetails.AppStatusType = ‘1’ Then ‘Success’
when SMS_AppDeploymentAssetDetails.AppStatusType = ‘2’ Then ‘In Progress’
when SMS_AppDeploymentAssetDetails.AppStatusType = ‘3’ Then ‘Requirements Not Met’
when SMS_AppDeploymentAssetDetails.AppStatusType = ‘4’ Then ‘Unknown’
when SMS_AppDeploymentAssetDetails.AppStatusType = ‘5’ Then ‘Error’
else ‘Unknown’
End as ‘App Status Type’,

CASE WHEN SMS_AppDeploymentAssetDetails.ComplianceState = ‘1’ Then ‘Compliant’
WHEN SMS_AppDeploymentAssetDetails.ComplianceState = ‘2’ Then ‘Non-Compliant’
WHEN SMS_AppDeploymentAssetDetails.ComplianceState = ‘4’ Then ‘Error’
WHEN SMS_AppDeploymentAssetDetails.ComplianceState = ‘6’ Then ‘Partial Compliance’
else ‘Unknown’
End as ‘Compliance State’,

CASE WHEN SMS_AppDeploymentAssetDetails.EnforcementState  >= 5000 then ‘Error’
when SMS_AppDeploymentAssetDetails.EnforcementState  >= 4000 then ‘Unknown’
when SMS_AppDeploymentAssetDetails.EnforcementState  >= 3000 then ‘Requirements Not Met’
when SMS_AppDeploymentAssetDetails.EnforcementState  >= 2000 then ‘In Progress’
when SMS_AppDeploymentAssetDetails.EnforcementState  >= 1000 then ‘Success’
else ‘Unknown’
End As ‘Enforcement State’,

CASE WHEN SMS_AppDeploymentAssetDetails.DeploymentIntent  = ‘1’ Then ‘Install’
WHEN SMS_AppDeploymentAssetDetails.DeploymentIntent  = ‘2’ Then ‘Uninstall’
WHEN SMS_AppDeploymentAssetDetails.DeploymentIntent  = ‘3’ Then ‘Preflight’
else ‘Unknown’
End as ‘Deployment Intent’,

SMS_AppDeploymentAssetDetails.DTName as ‘Deplyoment Type Name’,

CASE WHEN SMS_AppDeploymentAssetDetails.InstalledState  = ‘1’ Then ‘Uninstall’
WHEN SMS_AppDeploymentAssetDetails.InstalledState  = ‘2’ Then ‘Install’
WHEN SMS_AppDeploymentAssetDetails.InstalledState  = ‘3’ Then ‘Unknown’
else ‘Unknown’
End as ‘Installed State’,

CASE WHEN SMS_AppDeploymentAssetDetails.IsMachineAssignedToUser = ‘1’ Then ‘Yes’
Else ‘No’
End as ‘Device Assigned to User?’,
SMS_AppDeploymentAssetDetails.MachineName as ‘Device Name’,

 

CASE WHEN CHARINDEX(‘\’,SMS_AppDeploymentAssetDetails.UserName)>0 THEN SUBSTRING
(SMS_AppDeploymentAssetDetails.UserName,CHARINDEX(‘\’,SMS_AppDeploymentAssetDetails.UserName)+1,255)
ELSE SMS_AppDeploymentAssetDetails.UserName END ‘User Name’

from fn_AppDeploymentAssetDetails(1033) AS SMS_AppDeploymentAssetDetails

where

((SMS_AppDeploymentAssetDetails.UserName like ‘%’ +  @user
or SMS_AppDeploymentAssetDetails.UserName =  ‘(SYSTEM)’)

and

(SMS_AppDeploymentAssetDetails.AppName in (@appname )))

ORDER BY UserName

 

–AppName parameter:

Select

distinct

SMS_AppDeploymentAssetDetails.AppName from fn_AppDeploymentAssetDetails(1033) AS SMS_AppDeploymentAssetDetails

 

–The User parameter is simply a text field.

 

Happy reporting!