Walkthrough: Using the REST API to feed GRC data into our Power BI dashboards.

Alright, I know this is a bit outside my usual wheelhouse of Terraform and cloud provisioning, but I’ve been deep in the weeds lately trying to connect our ServiceNow GRC instance to some custom Power BI dashboards for our risk reporting. The goal was to get near-real-time visualizations without manual CSV exports—because, let's be honest, nobody has time for that.

I figured the REST API would be the way to go, but the documentation felt a bit... scattered for this specific use case. After a fair bit of experimentation (and a few cups of coffee), I managed to stitch together a pipeline. I thought I'd walk through the key parts, especially around authentication, querying, and shaping the data for Power BI. I'm curious if anyone else has tackled this and what your approach looked like.

Here’s the basic flow I landed on:

* **Authentication:** Using OAuth 2.0 (Client Credentials flow) was cleaner for an automated pipeline than basic auth. You'll need to set up an OAuth entity in ServiceNow.
* **API Endpoint & Querying:** The `/api/now/table` endpoints are your friend. For GRC-specific data, you’ll likely be hitting tables like `sn_grc_risk` or `sn_grc_control`. Using sysparm_query to filter and sysparm_display_value to get the human-readable values was crucial.
* **Data Transformation:** The JSON output often has nested structures and reference fields. I used Power Query in Power BI to flatten and clean it, but you could also do this with a lightweight Python script as a middle layer.

Here's a snippet of the core Power Query M code I used to call the API and start the transformation. This fetches active risks:

```powerquery-m
let
baseUrl = "https://your-instance.service-now.com",
endpoint = "/api/now/table/sn_grc_risk",
queryParams = "?sysparm_query=active%3Dtrue&sysparm_display_value=true&sysparm_limit=1000",
token = GetAccessToken(), // This calls a separate function for the OAuth token
source = Json.Document(
Web.Contents(baseUrl, [
RelativePath = endpoint,
Query = Uri.BuildQueryString([sysparm_query:"active=true", sysparm_display_value:"true", sysparm_limit:"1000"]),
Headers = [Authorization="Bearer " & token]
])
),
result = source[result],
#"Converted to Table" = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"number", "short_description", "state", "risk_score", "assigned_to"}, {"number", "short_description", "state", "risk_score", "assigned_to"})
in
#"Expanded Column1"
```

Some pitfalls I ran into:
* **Pagination:** Remember to handle `sysparm_limit` and check for the `next_link` field in the response if you have large datasets.
* **Performance:** Complex queries with many related fields can get slow. Be specific in your `sysparm_fields` to only pull what you need for the dashboard.
* **Data Freshness:** This is a pull model. For true real-time, you'd need to look at ServiceNow's Event Management or set up a more frequent refresh schedule in Power BI service, which has its own limitations.

I’m still tweaking the module—I mean, the script—to handle incremental loads better. Has anyone else built something similar? I’d be particularly interested in how you’ve managed schema changes in the GRC tables or if you’ve found a more efficient way to pull linked records (like controls for a risk) in a single query.