0

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.

Badjjl
  • 1

2 Answers2

0

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

user19190231
  • 1
  • Thanks user19190231 I tried using the view list filter to filter just on assignment group and opended date yeterday but the query returned everything back. Thanks :) https://asosdev.service-now.com/api/now/table/incident? sysparm_query=assignment_group%3D1fa96ee891811000b12227f3690ef9be%5Eopened_atONYesterday%40javascript%3Ags.beginningOfYesterday()%40javascript%3Ags.endOfYesterday()&sysparm_display_value=true&sysparm_exclude_reference_link=true&sysparm_fields=number%2Copened_by%2Cshort_description%2C opened_at%2C incident_state%2Ccaller_id%2C assignment_group&sysparm_limit=500 – Badjjl May 27 '22 at 09:16
0

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.
JockTheKiwi
  • 3
  • 5
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 26 '22 at 06:59
  • Thanks JockTheKiwi but the page does not give any simple query examples Is there a way say 'Assignment group' = 'tech' and opened is yesterday or Last 12 months etc Many Thanks :) asosdev.service-now.com/api/now/table/incident? sysparm_query=assignment_group%3D1fa96ee891811000b12227f3690ef9be%5Eopened_atONYesterday%40javascript%3Ags.beginningOfYesterday()%40javascript%3Ags.endOfYesterday()&sysparm_display_value=true&sysparm_exclude_reference_link=true&sysparm_fields=number%2Copened_by%2C opened_at%2C incident_state%2Ccaller_id%2C assignment_group&sysparm_limit=500 – Badjjl May 27 '22 at 09:13