How to run a web url Query using PowerBI via servicenow REST API with Incident field opened_at between start and end date

Question

Can anyone tell me how to run a web url Query in PowerBI using a web URL servicenow REST API on the Incident table using field opened_at between start and end date ?

For example I have the below query

https://asosdev.service-now.com/api/now/table/incident?sysparm_display_value=true&sysparm_exclude_reference_link=true&sysparm_fields=number%2Copened_by%2Cshort_description%2C opened_at%&sysparm_limit=500

This returns number,opened by, short description and opened_at for a ,limit of 500 rows.

How do I update the query to return data between opened_at '01 Jan 2022' and '31 Mar 2022'

Is there some example queries anywhere on the web that shows how to write these Web url rest api queries to build a report in Power BI.

Also how to add where clause e.g. where short description contains 'john'

Many thanks in advance for your help and assistance.

Answer 1

For me, the easiest way to do this is to use the view list filter. Once you get it to show you the right records, you can then right click on the filter and select to copy query or url. You can then use this within SeviceNow REST Explore to further tune your API call

Answer 2

You can use the list filter as suggested or use the Rest API Explorer to quickly test and iterate your queries. ServiceNow REST API Explorer

To build queries using the standard list query to define exactly what you want and then capture the sysparm_query value by copying the query (right click the query and select Copy query (or get from the URL) and paste that into the API explorer.

Using a List Filter has a few advantages

1. It confirms your query is returning the correct results enter link description here
2. You become more familiar with the data. To see more data you can open an item and then select Show XML from the menu to see all the data for that form. This is a quick way to learn what data is not shown on screen.