Introduction
Integrating SailPoint API with Power BI allows you to visualize and analyze identity data effectively. This guide will walk you through the process using a blank query in Power BI.
Why Use a Blank Query
Power BI’s built-in connectors often don’t support OAuth authentication directly. Using a blank query allows you to write custom M code to handle authentication and API requests, providing flexibility and control over the data retrieval process.
Step-by-Step Guide
Go to Power BI → Click on get Data
Select Blank Query from search
Now we have to define parameters, click on new parameter
Add Parameters Baseurl, clientid and clientsecret
Query Editor
Click on Advanced editor and type your code
Step 1: Define the Base URL and Client Credentials
First, set up the base URL and client credentials for the SailPoint API.
let
baseUrl = "https://tal-sb.api.identitynow.com",
clientId = ""
clientSecret = ""
Replace <your_client_id> and <your_client_secret> with your actual client credentials.
Step 2: Generate the Bearer Token
Next, generate the bearer token required for authentication.
tokenUrl = baseUrl & "/oauth/token",
tokenResponse = Web.Contents(tokenUrl, [
Headers = [
#"Content-Type" = "application/x-www-form-urlencoded"
],
Content = Text.ToBinary("grant_type=client_credentials&client_id=" & clientId & "&client_secret=" & clientSecret)
]),
tokenJson = Json.Document(tokenResponse),
bearerToken = tokenJson[access_token]
This code sends a POST request to the OAuth token endpoint with your client credentials and retrieves the bearer token.
Step 3: Define the API Endpoint and Parameters
Specify the API endpoint and parameters for the data you want to retrieve.
endpoint = "/v2024/campaigns",
detail = "FULL",
limit = 250,
offset = 0,
count = false,
sorters = "name"
Step 4: Function to get Identics with offset
Define a function get Identities that takes an offset parameter and retrieves identities from the API.
getIdentities = (offset as number) =>
let
url = baseUrl & "/v2024/campaigns?" & "detail=" & Uri.EscapeDataString("FULL") & "&limit=" & Number.ToText(250) & "&offset=" & Number.ToText(offset) & "&count=" & Text.From(false) & "&sorters=" & Uri.EscapeDataString("name"),
response = Web.Contents(url, [
Headers = [
Authorization = "Bearer " & bearerToken,
#"Content-Type" = "application/json"
]
]),
jsonResponse = Json.Document(response)
in
jsonResponse
Step 5: Make the API Request
Make the API request using the bearer token for authentication.
response = Web.Contents(url, [
Headers = [
Authorization = "Bearer " & bearerToken,
#"Content-Type" = "application/json"
]
])
Step 6: Parse the JSON Response
Parse the JSON response from the API.
jsonResponse = Json.Document(response)
Step 7: Convert JSON to Table and Expand Records
Convert the JSON response to a table and expand the records for easier analysis in Power BI.
dataTable = Table.FromList(jsonResponse, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
expandedTable = Table.ExpandRecordColumn(dataTable, "Column1", Record.FieldNames(dataTable{0}[Column1]))
in
expandedTable
Here is complete code
let
// Define the base URL and client credentials
baseUrl = "https://gtal-sb.api.identitynow.com",
clientId = "",
clientSecret = "",
// Generate the bearer token
tokenUrl = baseUrl & "/oauth/token",
tokenResponse = Web.Contents(tokenUrl, [
Headers = [
#"Content-Type" = "application/x-www-form-urlencoded"
],
Content = Text.ToBinary("grant_type=client_credentials&client_id=" & clientId & "&client_secret=" & clientSecret)
]),
tokenJson = Json.Document(tokenResponse),
bearerToken = tokenJson[access_token],
// Function to get identities with offset
getIdentities = (offset as number) =>
let
url = baseUrl & "/v2024/campaigns?" & "detail=" & Uri.EscapeDataString("FULL") & "&limit=" & Number.ToText(250) & "&offset=" & Number.ToText(offset) & "&count=" & Text.From(false) & "&sorters=" & Uri.EscapeDataString("name"),
response = Web.Contents(url, [
Headers = [
Authorization = "Bearer " & bearerToken,
#"Content-Type" = "application/json"
]
]),
jsonResponse = Json.Document(response)
in
jsonResponse,
// Generate list of identities
results = List.Generate(
() => [Offset = 0, identities = getIdentities(0)],
each not List.IsEmpty([identities]),
each [identities = getIdentities([Offset] + 250), Offset = [Offset] + 250],
each [identities]
),
// Combine all results into a single list
combinedResults = List.Combine(results),
// Convert JSON to table and expand records
dataTable = Table.FromList(combinedResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
expandedTable = Table.ExpandRecordColumn(dataTable, "Column1", Record.FieldNames(dataTable{0}[Column1]))
in
expandedTable
Click on save and apply, it will allow query to execute, it will generate table
Creating Dashboards with Power BI
Here I used basic drop-down dashboards to display status of campaigns, you can use different dashboards based on your requirements.
Happy to Answer your questions and accept your suggestions 