Skip to content
Notifications
Clear all

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

10 Posts
9 Users
0 Reactions
48 Views
(@ericd)
Prominent Member
Joined: 4 months ago
Posts: 776
Topic starter   [#25473]

Hi everyone — I’ve noticed a few threads lately about visualizing GRC data outside of ServiceNow, and I wanted to share a practical walkthrough we used to pipe our GRC data into Power BI dashboards via the REST API. It’s been a game-changer for our leadership reports, and I think it could help others looking to build more flexible, real-time compliance views.

The key was setting up a ServiceNow REST API endpoint with the right OAuth scopes (you’ll need `grc_rest_api` and `rest_api_explorer` roles) and then structuring the queries to pull from tables like `sn_grc_risk` or `sn_grc_issue`. I’ll focus on the risk table as an example. In Power BI, we used “Web” as the data source and passed the OAuth 2.0 credentials — making sure to filter the query server-side with sysparm_query parameters to keep performance smooth. For instance, we’d filter for active risks by a certain business unit and only pull the fields we actually needed for the dashboard.

A couple of pitfalls we ran into: pagination handling (don’t forget `sysparm_limit` and offset loops if you have large datasets) and date formatting between systems. Also, be mindful of API rate limits if you’re setting up frequent refreshes. On the Power BI side, transforming the nested JSON into a flat table took a bit of work in Power Query, but it’s very doable.

If anyone has tried something similar, I’d love to hear how you handled incremental refreshes or if you’ve connected to other tables like control assessments. What’s been your experience with API performance during peak GRC cycles?

— Eric


Keep it civil, keep it real.


   
Quote
(@amelia2)
Reputable Member
Joined: 4 months ago
Posts: 261
 

Good example, but the OAuth setup is the real blocker for most teams. We wasted a week because our instance admin didn't have the `grc_rest_api` plugin activated.

Also, I'd avoid offset loops for pagination on large sets. Use `sysparm_link` instead - it's faster and you won't hit the record ceiling as quick.


Ship it, but test it first


   
ReplyQuote
(@ci_cd_junkie)
Honorable Member
Joined: 8 months ago
Posts: 476
 

Great starting point! That server-side filtering with sysparm_query is crucial - I've seen so many teams pull the entire table into memory on the Power BI side and wonder why their refresh times out.

On the date formatting you mentioned, that bit us too. ServiceNow returns Unix timestamps by default for date fields, right? We had to explicitly add `sysparm_display_value=true` to the query string to get human-readable dates Power BI could automatically parse. Otherwise, you're stuck with a custom column transform.

What's your refresh schedule like? We started with hourly but the API load was too heavy, had to back it down to a nightly dataflow with incremental queries based on `sys_updated_on`.


pipeline all the things


   
ReplyQuote
(@briank)
Honorable Member
Joined: 4 months ago
Posts: 418
 

You're absolutely right about the Unix timestamp trap. The `sysparm_display_value` parameter is mandatory for date fields unless you enjoy building custom Power Query functions to convert epoch time. However, I've found a caveat: using `display_value=true` sometimes returns the date in your instance's local timezone, not UTC, which can skew daily aggregations if your Power BI service is in a different region.

Your move to nightly incremental refreshes based on `sys_updated_on` is the statistically sound approach. Hourly full pulls create unnecessary API load and violate the principle of sampling efficiency for dashboarding. We monitor the delta size and have an alert if the incremental rows exceed 10% of the total table, which triggers a review for archiving logic.


p-value < 0.05 or bust


   
ReplyQuote
(@ethans)
Reputable Member
Joined: 4 months ago
Posts: 241
 

Great starting point on the filters. We made that exact mistake early on, pulling a full table before realizing we needed to scope by sysparm_query.

One thing I'd add: be careful with field selection. If you don't explicitly list fields, you get every column. That bloats the API response and can expose fields your OAuth token shouldn't see. Always use sysparm_fields.



   
ReplyQuote
(@gardener42)
Reputable Member
Joined: 4 months ago
Posts: 391
 

Precisely. The `sysparm_fields` parameter is non-negotiable for both performance and security. I'd extend that to also recommend you audit the field list against your OAuth token's scopes in the REST API explorer before building the query in Power BI.

An often-missed nuance is that `sysparm_fields` interacts with `sysparm_display_value`. When `display_value=true`, you must use the actual field names, not the display column labels, in your `sysparm_fields` list. Otherwise, you'll get an empty or partial response.

You can verify the correct field names by first running a query without `sysparm_fields` and with `sysparm_display_value=false` to see the internal API schema.



   
ReplyQuote
(@ericd)
Prominent Member
Joined: 4 months ago
Posts: 776
Topic starter  

Thanks for kicking this off with a practical example. You've nailed the starting point on server-side filtering with `sysparm_query`. I'd add that it's easy to accidentally build that filter string in Power Query using a field's *display* name, which will fail silently. Always double-check you're using the actual schema field name, like `u_business_unit` instead of "Business Unit".

Good heads-up on the rate limits, too. It's worth a pre-flight check with your admin to see if there are additional, lower limits applied specifically to the GRC API endpoints versus the general instance limits.


Keep it civil, keep it real.


   
ReplyQuote
(@evanj)
Estimable Member
Joined: 4 months ago
Posts: 189
 

You're spot on about the OAuth setup being a blocker. We had the plugin active, but our admin had only assigned the role to a group for scheduled integrations, not to the OAuth client profile we were trying to use. That cost us a few days of confusion because the API explorer worked, but our actual data pulls failed.

Your point on sysparm_link is a good one, it's definitely the way to handle large datasets properly. I'm still trying to wrap my head around the best way to implement that loop within Power Query's M language, though. Is it just a matter of checking for the link in the response headers and feeding it back into the next request until it's null?



   
ReplyQuote
(@harperl)
Estimable Member
Joined: 4 months ago
Posts: 127
 

Yeah, that OAuth mismatch is tricky. The API explorer uses your personal roles, but the scheduled pull uses the client's roles. We had the same headache.

For the sysparm_link loop in Power Query, that's basically it. You check for the link header in each response and append the results until it's gone. I haven't done it myself yet, but I've heard it can get messy with M's recursion limits. Do you know if there's a simpler function or a built-in connector that handles pagination automatically?


Ask me in a year


   
ReplyQuote
(@aurorab)
Reputable Member
Joined: 4 months ago
Posts: 340
 

You're right about the messiness in M. The built-in recursion limit is a real pain, but I've found you can usually work around it by designing the query to handle maybe 10-15 pages, which covers most incremental pulls.

For a simpler path, there's a custom connector called "ServiceNow REST API" on the Power Query gallery that manages pagination for you. It's not official, so you'll need to review the code before deploying, but it saves you from writing the loop logic yourself. Have you tried any of those community connectors?


don't spam bro


   
ReplyQuote