ActiveSync Report Additions

[ChieneTait]

Hi New to this first ime poster:

Im trying to add the username or email address the the built in Active Sync report instead of the Mailbox Name but having some issues.

Can someone help?

[Esben_D]

You will have to add tblO365User (which will join automatically) and add tblADusers which you can join on UPN.

I've added both O365 user data as well as AD user data below, depending on what you scan, one might have data the other doesn't. If it is all the same, you can simply remove the columns you don't need.

Select Top 1000000 'arrow_refresh.png' As Icon,
  Case
    When tblIntuneDevice.ManagedDeviceName Is Null Then
      tblO365ActiveSyncDevice.DeviceFriendlyName
    Else tblIntuneDevice.ManagedDeviceName
  End As DeviceAssetName,
  tblIntuneDevice.AssetId As DeviceAssetId,
  tblO365ActiveSyncDevice.DeviceModel,
  tblO365ActiveSyncDevice.DeviceOs,
  tblO365ActiveSyncDevice.ClientType,
  tblO365ActiveSyncDevice.DeviceAccessState As AccessState,
  tblO365ActiveSyncDevice.FirstSyncTime,
  tblO365ActiveSyncDevice.LastSuccessSync,
  tblO365User.DisplayName,
  tblO365User.UserPrincipalName,
  tblADusers.Username,
  tblADusers.Userdomain,
  tblO365Organization.DisplayName As Organization,
  tblO365Organization.TenantId,
  tblIntuneDevice.UserId
From tblO365ActiveSyncDevice
  Inner Join tblO365Organization On tblO365Organization.OrganizationId =
    tblO365ActiveSyncDevice.OrganizationId
  Left Outer Join tblIntuneDevice On tblIntuneDevice.Imei =
    tblO365ActiveSyncDevice.DeviceImei
  Inner Join tblO365User On tblO365Organization.OrganizationId =
    tblO365User.OrganizationId
  Left Join tblADusers On tblADusers.UPN = tblO365User.UserPrincipalName
Order By DeviceAssetName