I’m experiencing an issue connecting SailPoint Identity Security Cloud with Power BI through API. While I can successfully obtain the access token, I’m encountering problems with the subsequent query and am unable to retrieve data as expected.
Has anyone faced a similar issue, or does anyone have insights on resolving this? Any guidance would be greatly appreciated.
It looks like you didn’t copy the entire power query. Note that in my post, I break it up into sections to explain what each one does and then put it all together at the end of the post.
Notable also, this section needs to be modified to suit your needs. The query portion uses both lifecycle state and Identity Profile names that are specific to my instance, so you’ll need to update them accordingly
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"" ]}")
Very much appreciate your direction and help. I’m able to successfully connect and pull some data. Apologies for following question but, how do I transform these results into table so I can try to create reports? I used to create reports with .csv tables so not much familiar with this look.
Also since some of the JSON is nested, you’ll have to expand the columns to include those attributes. It will make sense once you start clicking around
Could you please help me out how to pull all identities data, I have 60K+ identities. I can’t go by authoritative source. But when I pull it’s giving me first 250 set of records as a Error but rest of it Power Bi is not pulling. Before under the list I was getting many records.
I have two query options are not working.
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 = “500”,
offset = Number.ToText(Offset)
],
Content = Text.ToBinary(“{ ““indices””: [”“identities”“], ““query””: { ““query””: “”*”" } }")
]
)
)
// Convert the list of records into a table
itemsTable = Table.FromList(response, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
// Expand the records into columns
expandedItems = Table.ExpandRecordColumn(itemsTable, “Column1”, Record.FieldNames(response{0}))
in
expandedItem