Extracting Data from IDN to PowerBI

Identity Security Cloud (ISC) ISC Discussion and Questions
4

@jodi_pruitt

The trickiest part is that Power BI’s “Web” source does not allow oauth-type authentication, only basic and api key-based auth.

In order to get around this, I had to create a “Blank Query” and do everything in Power Query.

Here are some of the snippets…

First, you need to get a token using your client credentials

getToken = Json.Document(
        Web.Contents(
            param_root_uri, 
            [
                RelativePath="/oauth/token",
                Query=[
                    grant_type="client_credentials",
                    client_id=param_client_id,
                    client_secret=param_client_secret
                ],
                Content=Text.ToBinary("hi")
            ]
        )
    )

Notice that I had my root uri, client id and client secret in parameters
image

Also, if I recall correctly, the method to get a token requires a POST. The only way to make Power BI do a POST using Web.Contents is to give it some content, which is why I have that line

Content=Text.ToBinary("hi")

This particular query is hitting the /v3/search endpoint, which if you recall has a 250 document limit, so you have to paginate. I’m going to do a couple steps out of order here. First, here is the “loop” function that helps paginate through the results

results = List.Generate( 
        () => [Offset = 250, identities = getIdentities(0)],
            each not (List.IsEmpty([identities])),
            each [ identities = getIdentities( [Offset] ), 
                Offset = [Offset] + 250 ],
            each [identities]
    )

Then the actual function being called

getIdentities = (Offset) =>
    let docs = Json.Document(
            Web.Contents(
                param_root_uri,
                [
                    RelativePath="/v3/search",
                    Headers=[
                        #"Authorization"=authToken,
                        #"Content-Type"="application/json"
                    ],
                    Query=[
                        limit = "250",
                        offset = Number.ToText(Offset)
                    ],    
                Content=Text.ToBinary("{ ""indices"": [ ""identities"" ], ""query"": { ""query"": ""(attributes.cloudLifecycleState:active OR attributes.cloudLifecycleState:legalHoldActive OR attributes.cloudLifecycleState:prehire OR attributes.cloudLifecycleState:rehire) AND identityProfile.name:\""Mulesoft Profile\"""" }, ""queryResultFilter"":{ ""includes"": [ ""id"",""displayName"", ""employeeNumber"", ""manager.displayName"", ""manager.name"", ""access.id"", ""access.type"", ""access.displayName"", ""access.description"", ""access.privileged"", ""access.source.name"",""access.source.id"",""access.value"" ] },""sort"": [ ""id"" ]}")
                ]
            )
        )
    in docs

The particular search being run above is pulling back identities that are active and have a specific identity profile.

Ok, let’s put it all together

let
    getToken = Json.Document(
        Web.Contents(
            param_root_uri, 
            [
                RelativePath="/oauth/token",
                Query=[
                    grant_type="client_credentials",
                    client_id=param_client_id,
                    client_secret=param_client_secret
                ],
                Content=Text.ToBinary("hi")
            ]
        )
    ),
    token = getToken[access_token],
    authToken = "bearer " & token,
    getIdentities = (Offset) =>
    let docs = Json.Document(
            Web.Contents(
                param_root_uri,
                [
                    RelativePath="/v3/search",
                    Headers=[
                        #"Authorization"=authToken,
                        #"Content-Type"="application/json"
                    ],
                    Query=[
                        limit = "250",
                        offset = Number.ToText(Offset)
                    ],    
                Content=Text.ToBinary("{ ""indices"": [ ""identities"" ], ""query"": { ""query"": ""(attributes.cloudLifecycleState:active OR attributes.cloudLifecycleState:legalHoldActive OR attributes.cloudLifecycleState:prehire OR attributes.cloudLifecycleState:rehire) AND identityProfile.name:\""Mulesoft Profile\"""" }, ""queryResultFilter"":{ ""includes"": [ ""id"",""displayName"", ""employeeNumber"", ""manager.displayName"", ""manager.name"", ""access.id"", ""access.type"", ""access.displayName"", ""access.description"", ""access.privileged"", ""access.source.name"",""access.source.id"",""access.value"" ] },""sort"": [ ""id"" ]}")
                ]
            )
        )
    in docs,
    results = List.Generate( 
        () => [Offset = 250, identities = getIdentities(0)],
            each not (List.IsEmpty([identities])),
            each [ identities = getIdentities( [Offset] ), 
                Offset = [Offset] + 250 ],
            each [identities]
    )
in
    results

From there, you convert it to a table, expand columns, etc

6 Likes