Intune and PowerBI Deep Dive - Part 9 - Connectors and Tokens

post-thumb

In this post I’m going to cover (some) connectors and tokens in Microsoft Intune. In particular, how we pull in the data from them into PowerBI. These are going to be slightly different to the preceding posts as these will only take one form, a direct call. The difference here is that we’re not using a logic app, we’re just calling the API and processing the result. This is what I mean by a “direct Microsoft Graph call”, no pagination required so the Power Query M will take the form of first getting a bearer token for authorisation and then using the token to authenticate against the graph and ultimately passing the data back for us.


As of the time of writing this, I don’t have every connector set up in my lab environment, so I will put as many as I can in here as of the date of the post, however, keep an eye on this post, it may update from time to time.
Lets go…

Global Pre-requisites

Warning

I am only going to provide the direct queries below in the format of getting a bearer token first. Please be aware of that and ensure you’ve read part 4 and are clear on the requirements for both creating and calling this function. This is because we get the bearer token for authorisation and pass it to the Json.Document(Web.Contents in our query. Remember, we’re not using a logic app here as we dont need pagination (direct query), so its much easier to go grab this bearer token and use it to authenticate against our tenant directly.

All of the subsequent queries are inside collapsing menus

Power Queries available from my Github

Windows


  • The graph URL used is deviceManagement/domainJoinConnectors/.
  • Only available in beta
  • Requires the following permissions: DeviceManagementConfiguration.Read.All
let

// Application Registration Permissions needed to make this call : 	DeviceManagementConfiguration.Read.All

// Microsoft Graph URL
    Endpoint = "https://graph.microsoft.com/",
    Version = "beta/",
    Resource = "deviceManagement/domainJoinConnectors/",
    QueryParams = "",
    GraphURL = Endpoint & Version & Resource & QueryParams,    

// Get an Access Token to make Graph Calls (uses Application Registration)
    Bearer = #"Get-BearerToken" (TenantID, AppID, SecretID, Endpoint), 

// Direct Graph call (No pagination)  
    DirectGraphCall = Json.Document(Web.Contents(
        GraphURL, 
        [
            Headers=[#"Content-Type"="application/json", #"Authorization"=Bearer]
        ]
    )),
    value = DirectGraphCall[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

// Formatting
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "displayName", "lastConnectionDateTime", "state", "version"}, {"id", "displayName", "lastConnectionDateTime", "state", "version"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"lastConnectionDateTime", type datetime}})
in
    #"Changed Type"



  • The graph URL used is deviceManagement/dataProcessorServiceForWindowsFeaturesOnboarding/.
  • Only available in beta
  • Requires the following permissions: unknown
let

// Microsoft Graph URL
    Endpoint = "https://graph.microsoft.com/",
    Version = "beta/",
    Resource = "deviceManagement/dataProcessorServiceForWindowsFeaturesOnboarding",
    QueryParams = "",
    GraphURL = Endpoint & Version & Resource & QueryParams,    

// Get an Access Token to make Graph Calls (uses Application Registration)
    Bearer = #"Get-BearerToken" (TenantID, AppID, SecretID, Endpoint), 

// Direct Graph call (No pagination)  
    DirectGraphCall = Json.Document(Web.Contents(
        GraphURL, 
        [
            Headers=[#"Content-Type"="application/json", #"Authorization"=Bearer]
        ]
    )),
    #"Converted to Table" = Record.ToTable(DirectGraphCall),

// Formatting
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"@odata.context", type text}, {"hasValidWindowsLicense", type logical}, {"areDataProcessorServiceForWindowsFeaturesEnabled", type logical}})
in
    #"Changed Type"



  • The graph URL used is deviceManagement/ndesConnectors/.
  • Only available in beta
  • Requires the following permissions: DeviceManagementConfiguration.Read.All
let

// Application Registration Permissions needed to make this call : DeviceManagementConfiguration.Read.All

// Microsoft Graph URL
    Endpoint = "https://graph.microsoft.com/",
    Version = "beta/",
    Resource = "deviceManagement/ndesConnectors/",
    QueryParams = "",
    GraphURL = Endpoint & Version & Resource & QueryParams,    

// Get an Access Token to make Graph Calls (uses Application Registration)
    Bearer = #"Get-BearerToken" (TenantID, AppID, SecretID, Endpoint), 

// Direct Graph call (No pagination) 
    DirectGraphCall = Json.Document(Web.Contents(
        GraphURL, 
        [
            Headers=[#"Content-Type"="application/json", #"Authorization"=Bearer]
        ]
    )),
    value = DirectGraphCall[value],

// Formatting
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Display Name" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "lastConnectionDateTime", "state", "displayName", "machineName", "enrolledDateTime", "roleScopeTagIds", "connectorVersion"}, {"id", "lastConnectionDateTime", "state", "displayName", "machineName", "enrolledDateTime", "roleScopeTagIds", "connectorVersion"}),
    #"Renamed Connector Version" = Table.RenameColumns(#"Expanded Display Name",{{"displayName", "Display Name"}, {"connectorVersion", "Connector Version"}})
    
in
    #"Renamed Connector Version"


Android


  • The graph URL used is deviceManagement/androidManagedStoreAccountEnterpriseSettings.
  • Only available in beta
  • Requires the following permissions: DeviceManagementConfiguration.Read.All
let

// Application Registration Permissions needed to make this call    : DeviceManagementConfiguration.Read.All

// Microsoft Graph URL
    Endpoint = "https://graph.microsoft.com/",
    Version = "beta/",
    Resource = "deviceManagement/androidManagedStoreAccountEnterpriseSettings",
    QueryParams = "",
    GraphURL = Endpoint & Version & Resource & QueryParams,    

// Get an Access Token to make Graph Calls (uses Application Registration)
    Bearer = #"Get-BearerToken" (TenantID, AppID, SecretID, Endpoint), 

// Direct Graph call (No pagination)  
    DirectGraphCall = Json.Document(Web.Contents(
        GraphURL, 
        [
            Headers=[#"Content-Type"="application/json", #"Authorization"=Bearer]
        ]
    )),
    #"Converted to Table" = Record.ToTable(DirectGraphCall),

// Formatting
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"@odata.context", type text}, {"id", type text}, {"bindStatus", type text}, {"lastAppSyncDateTime", type datetime}, {"lastAppSyncStatus", type text}, {"ownerUserPrincipalName", type text}, {"ownerOrganizationName", type text}, {"lastModifiedDateTime", type datetime}, {"enrollmentTarget", type text}, {"targetGroupIds", type any}, {"deviceOwnerManagementEnabled", type logical}, {"androidDeviceOwnerFullyManagedEnrollmentEnabled", type logical}, {"managedGooglePlayInitialScopeTagIds", type any}, {"companyCodes", type any}})
in
    #"Changed Type"


iOS/iPadOS


  • The graph URL used is deviceManagement/depOnboardingSettings/.
  • Only available in beta
  • Requires the following permissions: DeviceManagementServiceConfig.Read.All, DeviceManagementConfiguration.Read.All
let

// Application Registration Permissions needed to make this call : DeviceManagementServiceConfig.Read.All

// Microsoft Graph URL
    Endpoint = "https://graph.microsoft.com/",
    Version = "beta/",
    Resource = "deviceManagement/depOnboardingSettings/",
    QueryParams = "",
    GraphURL = Endpoint & Version & Resource & QueryParams,    

// Get an Access Token to make Graph Calls (uses Application Registration)
    Bearer = #"Get-BearerToken" (TenantID, AppID, SecretID, Endpoint), 

// Direct Graph call (No pagination) 
    DirectGraphCall = Json.Document(Web.Contents(
        GraphURL, 
        [
            Headers=[#"Content-Type"="application/json", #"Authorization"=Bearer]
        ]
    )),
    value = DirectGraphCall[value]{0},

// Formatting
    #"Converted to Table" = Record.ToTable(value),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"id", type text}, {"appleIdentifier", type text}, {"tokenExpirationDateTime", type datetime}, {"lastModifiedDateTime", type datetime}, {"lastSuccessfulSyncDateTime", type datetime}, {"lastSyncTriggeredDateTime", type datetime}, {"shareTokenWithSchoolDataSyncService", type logical}, {"lastSyncErrorCode", Int64.Type}, {"tokenType", type text}, {"tokenName", type text}, {"syncedDeviceCount", Int64.Type}, {"dataSharingConsentGranted", type logical}, {"roleScopeTagIds", type any}})
in
    #"Changed Type"



  • The graph URL used is deviceManagement/applePushNotificationCertificate/.
  • Available in v1.0 and beta
  • Requires the following permissions: DeviceManagementManagedDevices.Read.All, DeviceManagementServiceConfig.ReadWrite.All
let

// Application Registration Permissions needed to make this call : DeviceManagementServiceConfig.ReadWrite.All
//                                                                 DeviceManagementManagedDevices.Read.All

// Microsoft Graph URL
    Endpoint = "https://graph.microsoft.com/",
    Version = "beta/",
    Resource = "deviceManagement/applePushNotificationCertificate/",
    QueryParams = "",
    GraphURL = Endpoint & Version & Resource & QueryParams,    

// Get an Access Token to make Graph Calls (uses Application Registration)
    Bearer = #"Get-BearerToken" (TenantID, AppID, SecretID, Endpoint), 

// Direct Graph call (No pagination) 
    DirectGraphCall = Json.Document(Web.Contents(
        GraphURL, 
        [
            Headers=[#"Content-Type"="application/json", #"Authorization"=Bearer]
        ]
    )),
    #"Converted to Table" = Record.ToTable(DirectGraphCall),

// Formatting
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"@odata.context", type text}, {"id", type text}, {"appleIdentifier", type text}, {"topicIdentifier", type text}, {"lastModifiedDateTime", type datetime}, {"expirationDateTime", type datetime}, {"certificateUploadStatus", type any}, {"certificateUploadFailureReason", type any}, {"certificateSerialNumber", type text}, {"certificate", type any}})
in
    #"Changed Type"


Misc


If you want a nice explanation of how to set this connector up, before getting its properties to add to your PowerBI report, then check out this earlier blog post I wrote on the Intune Defender for Endpoint Connector

  • The graph URL used is deviceManagement/mobileThreatDefenseConnectors/{mobileThreatDefenseConnectorId}.
    If you only have one connector, you can get away with not specifying an ID number (I haven’t done that in the code below), however you may need to specify the ID if you have more than one connector that falls into this bracket.
  • Available in v1.0 and beta
  • Requires the following permissions: DeviceManagementServiceConfig.Read.All, DeviceManagementConfiguration.Read.All
let

// Application Registration Permissions needed to make this call : 	DeviceManagementServiceConfig.Read.All 
//                                                                  DeviceManagementConfiguration.Read.All

// Microsoft Graph URL
    Endpoint = "https://graph.microsoft.com/",
    Version = MSGraphVersion & "/",
    Resource = "deviceManagement/mobileThreatDefenseConnectors/",
    QueryParams = "",
    GraphURL = Endpoint & Version & Resource & QueryParams,    

// Get an Access Token to make Graph Calls (uses Application Registration)
    Bearer = #"Get-BearerToken" (TenantID, AppID, SecretID, Endpoint), 

// Direct Graph call (No pagination)  
    DirectGraphCall = Json.Document(Web.Contents(
        GraphURL, 
        [
            Headers=[#"Content-Type"="application/json", #"Authorization"=Bearer]
        ]
    )),
    value = DirectGraphCall[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

// Formatting
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "lastHeartbeatDateTime", "partnerState", "androidMobileApplicationManagementEnabled", "iosMobileApplicationManagementEnabled", "windowsMobileApplicationManagementEnabled", "androidEnabled", "iosEnabled", "windowsEnabled", "macEnabled", "androidDeviceBlockedOnMissingPartnerData", "iosDeviceBlockedOnMissingPartnerData", "windowsDeviceBlockedOnMissingPartnerData", "macDeviceBlockedOnMissingPartnerData", "partnerUnsupportedOsVersionBlocked", "partnerUnresponsivenessThresholdInDays", "allowPartnerToCollectIOSApplicationMetadata", "allowPartnerToCollectIOSPersonalApplicationMetadata", "microsoftDefenderForEndpointAttachEnabled"}, 
{"id", "lastHeartbeatDateTime", "partnerState", "androidMobileApplicationManagementEnabled", "iosMobileApplicationManagementEnabled", "windowsMobileApplicationManagementEnabled", "androidEnabled", "iosEnabled", "windowsEnabled", "macEnabled", "androidDeviceBlockedOnMissingPartnerData", "iosDeviceBlockedOnMissingPartnerData", "windowsDeviceBlockedOnMissingPartnerData", "macDeviceBlockedOnMissingPartnerData", "partnerUnsupportedOsVersionBlocked", "partnerUnresponsivenessThresholdInDays", "allowPartnerToCollectIOSApplicationMetadata", "allowPartnerToCollectIOSPersonalApplicationMetadata", "microsoftDefenderForEndpointAttachEnabled"})
in
    #"Expanded Column1"


Thank you to Get Hugo Themes for their support in fixing the modules that support this page.

Do keep an eye on this post for updates, I will list the change log at the top of the post in a table.

I appreciate you taking the time to read my blog.
Your interest and support keep me motivated to create more content.
If you think others might benefit from this content, please consider sharing it
 ... Jonathan
Share this post