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.
Thank you in advance for your help
Without knowing what specific issues you are facing, it will be difficult to figure out how to resolve your issue.
I have an example of how I was able to use the API with pagination in the post below, so maybe it might help?
I’m able to get token, but second “loop” function that u mentioned
results = List.Generate(
() => [Offset = 250, identities = getIdentities(0)],
each not (List.IsEmpty([identities])),
each [ identities = getIdentities( [Offset] ),
Offset = [Offset] + 250 ],
each [identities]
)
Getting error: The name ‘getIdentities’ wasn’t recognized
or when I try
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
Getting error: SyntaxError: Token Eof expected.
Could you please kindly help me with this issues?
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.
Once you have the query set up, let it refresh, then at the top-left, click Convert into table
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
Again appreciate it. Will try to create nice looking table 
Good luck! If this meets your requirements, please mark the appropriate post as a solution so that others who read this will know
Good day Mark.
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
// 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
expandedItems
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
