Freigeben über

Useful ConfigMgr query for Intune Enrolled Devices

  • Artikel

The following SQL query was shared by Gerjan Eghuizen an Unified Communications Consultant working at Enexis and Niels Buit (Microsoft), useful for anyone who has Mobile Devices enrolled via Microsoft Intune in ConfigMgr 2012 R2.

The end-result is a report with this information (clickable):

image

The SQL query:

CREATE TABLE
          #machines
          (
          DeviceID uniqueidentifier, -- Remove GUID: FROM SMSID to get this
          ResourceID int,
          SMS_Unique_Identifier0 nvarchar(255),
          Name0 nvarchar(255),
          Netbios_Name0 nvarchar(255),
Client_Version0 nvarchar(256),
          Client_Type0 int,
          Operating_System_Name_and0  nvarchar(256),
          DeviceOwner0 int,
                DeviceUser nvarchar(255),
                DeviceIMEI nvarchar(255)
          )

          CREATE INDEX ix_machines2 ON #machines (DeviceID)
          INSERT INTO #machines(DeviceID, ResourceID, SMS_Unique_Identifier0, Name0, Netbios_Name0,
          Client_Version0, Client_Type0, Operating_System_Name_and0, DeviceOwner0, DeviceUser, DeviceIMEI)
          SELECT CONVERT(uniqueidentifier, CASE  LEN(machines.SMS_Unique_Identifier0)
          WHEN 36 then machines.SMS_Unique_Identifier0
          ELSE SUBSTRING(machines.SMS_Unique_Identifier0,6,LEN(machines.SMS_Unique_Identifier0)-5) end),

machines.ResourceID,
          machines.SMS_Unique_Identifier0,
          machines.Name0,
          machines.Netbios_Name0,
          machines.Client_Version0,
          machines.Client_Type0,
          machines.Operating_System_Name_and0,
          machines.DeviceOwner0,

(SELECT UniqueUserName FROM fn_rbac_usermachinerelation(@UserSIDs) relations WHERE relations.MachineResourceID=machines.ResourceID),

(SELECT IMEI0 FROM fn_rbac_GS_Device_COMPUTERSYSTEM(@UserSIDs) computersys WHERE computersys.ResourceID=machines.ResourceID)          FROM fn_rbac_R_System(@UserSIDs) machines          WHERE ISNULL(machines.Obsolete0, 0) <> 1 AND ISNULL(machines.Decommissioned0, 0) <> 1 AND machines.Client0 = 1
          AND ISNULL(machines.AgentEdition0, 0) <> 5  AND machines.Client_Type0 = 3
          Select
          RS.SMS_Unique_Identifier0,
          (case when RS.Name0 is not null then RS.Name0 else RS.Netbios_Name0 end) as DeviceName,
                RS.DeviceUser as DeviceUser,
                RS.DeviceIMEI as DeviceIMEI,
                RS.Client_Version0 AS OSVersion,
          (case when comp.OEM0 is not null then comp.OEM0 else mdmci.Manufacturer end) as DeviceOEMInfo,
          (case when comp.PlatformType0 is not null then comp.PlatformType0 else compsys.SystemType0 end) as DeviceType,
          (case when comp.Product0 is not null then comp.Product0 else mdmci.DeviceOperatingSystem end) as Product,
          cs.LastPolicyRequest AS LastPolicyRequest,
          (Select top(1) v.AgentTime from fn_rbac_AgentDiscoveries(@UserSIDs)  v
          where RS.ResourceID = v.ResourceId and
          v.AgentName = 'Heartbeat Discovery' order by
v.AgentTime DESC) AS LastHeartbeatTime,
          wks.LastHWScan as LastHWScan,
          RS.DeviceOwner0
          from #machines   RS
          left outer join fn_rbac_GS_DEVICE_COMPUTERSYSTEM(@UserSIDs)  comp on comp.ResourceID=RS.ResourceID
          left outer join fn_rbac_CH_ClientSummary(@UserSIDs)  cs on cs.ResourceID=RS.ResourceID
          left outer join fn_rbac_GS_WORKSTATION_STATUS(@UserSIDs)  wks on wks.ResourceID = RS.ResourceID
          left outer JOIN fn_rbac_mdmclientidentity(@UserSIDs) mdmci ON mdmci.DeviceID = RS.DeviceID
          left outer join fn_rbac_GS_COMPUTER_SYSTEM(@UserSIDs) compsys on compsys.ResourceID = RS.ResourceID
          where (@ProductName = '*' or  comp.Product0 = @ProductName or mdmci.DeviceOperatingSystem = @ProductName)

Comments

  • Anonymous
    October 18, 2015
    Thank you.