ServiceNOw and Power BI integration((Scripted REST API)

[AntaraB]

Hi All,

I am trying to create a scripted REST API call to integrate ServiceNow with Power BI.

so far, I have created a database view in ServiceNow and the below REST API call to get the data

(function process(/*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {

    var start = request.queryParams.start || 0; // starting record

    var limit = request.queryParams.limit || 10000; // number of records per page

    var totalRecords = 0;

    var result = [];

    do {

        var gr = new GlideRecord('u_db_view_task_task_sla');

        gr.addQuery('task_opened_at', '>=', '2022-01-01');

        gr.addQuery('task_opened_at', '<=', new GlideDateTime());

        gr.setLimit(limit);

        gr.setOffset(start);

        gr.query();

        while (gr.next()) {

            var record = {

                Ticket_Number: gr.getValue('task_number'),

                Ticket_Type: gr.getValue('task_sys_class_name'),

                Company: gr.getValue('task_company'),

                Priority: gr.getValue('task_priority'),

                Created_Date: gr.getValue('task_sys_created_on'),

                Closed: gr.getValue('cs_closed_at'),

                Ticket_Description: gr.getValue('task_description'),

                Ticket_short_description: gr.getValue('task_short_description'),

                Sla_definition: gr.getValue('sla_definition'),

                Ticket_business_duration: gr.getValue('task_business_duration'),

                Ticket_State: gr.getValue('task_state'),

                Category: gr.getValue('Category'),

                Subcategory: gr.getValue('Subcategory'),

                Practice_Area: gr.getValue('Practice Area')

            };

            result.push(record);

        }

        start += limit;

        totalRecords = gr.getRowCount();

    } while (totalRecords == limit); // Continue if there are more records

    response.setBody(result);

})(request, response);

After creating this I am testing it in REST API explorer and getting 10000 records.

I want to fetch all of them in power BI. what do I do next.

[Akash4]

Hello,

The sysparm_limit and sysparm_offset are usually used to achieve this in ServiceNow (while ensuring performance).

1. sysparm_limit - returns number of records in one call.

2. sysparm_offset - the records to exclude from the query.

You can try this in the URL - something like this:

On first attempt try URL1: /api/now/table/incident?sysparm_limit=10000&sysparm_offset=0

Next with URL2: /api/now/table/incident?sysparm_limit=10000&sysparm_offset=10000

Later: /api/now/table/incident?sysparm_limit=10000&sysparm_offset=20000

(Always sysparm_limit is 10,000 while offset increments by 10,000+)

Hope this helps!

Regards, Akash

_____________________________________________________________________________

Please mark it as helpful👍 or Accept Solution✔️ based on the response to your query.

[AntaraB]

Thank you Akash. So I have around 200000 records as of now.so I will be making around 200 API calls.

eg-URL200: /api/now/table/incident?sysparm_limit=10000&sysparm_offset=200000

my doubt is ,when I am feeding the data to a third party tool (Power BI),there should be 200 API calls for the data till date and then more calls for newer data. How to achieve this?

[AntaraB]

Hi Akash,

Thank you for responding.

so currently I have around 200000 records.so I have to create around 200 API calls and the last call should be something like below

URL200: /api/now/table/incident?sysparm_limit=10000&sysparm_offset=200000

Then I have to get the newer data in in power BI as well. I am trying to understand what will be the best way to achieve this.

Also,I notice the data in the second API call      (URL2: /api/now/table/incident?sysparm_limit=10000&sysparm_offset=10000) is the same as the first 

(URL1: /api/now/table/incident?sysparm_limit=10000&sysparm_offset=0)