REST API & the dreaded "maximum execution time exceeded"

[Steve C_]

I am trying to hit my organizations instance of SNow and get a list off all current CI's.

Understandably this is a rather large list and I am getting the referenced Transaction Cancelled error prior to getting said list.

Working with SNow in this capacity & using REST API's are both quite new to me so I am about 110% sure I'm not doing it the correct / best way.

This is what I'm trying currently

https://<SNow Instance Name>/api/now/table/cmdb_ci?sysparm_fields=name,install_status,asset_tag

Is there in fact a better way to do this?

Or is there a way I can call this iterativly getting X number of records per call till I have them all?

Or some other really neat solution?

I am programming in VB using an HTTPClient calling the .GetAsync method on the URL above and deserializing the resulting HTTPResponseMessage object Content property

Thanks in advance for any information / assistance you can offer.

[Prateek kumar]

You can use sysparm_query in your call.

https://developer.servicenow.com/app.do#!/rest_api_doc?v=jakarta&id=r_TableAPI-GET

[A-N · Accepted solution]

You are on the right track

I'd recommend you to use Use sysparm_limit and sysparm_offset in your query

Increment the sysparm_offset by the count in your sysparm_limit each time to iterate

// Eg Iteration 1 - Retrieves first 5K records

https://<SNow Instance Name>/api/now/table/cmdb_ci?sysparm_fields=name,install_status,asset_tag&sysparm_limit=5000&sysparm_offset=0

// Eg Iteration 2 - Retrieves next 5K records

https://<SNow Instance Name>/api/now/table/cmdb_ci?sysparm_fields=name,install_status,asset_tag&sysparm_limit=5000&sysparm_offset=5000

// Eg Iteration 3 - Retrieves next 5K records

https://<SNow Instance Name>/api/now/table/cmdb_ci?sysparm_fields=name,install_status,asset_tag&sysparm_limit=5000&sysparm_offset=10000

// I'd recommend you to use a loop and update the sysparm_offset to avoid running into the dreaded timeout error

[Steve C_]

Thanks for your reply, I ended up trying this but had mixed results.

On my first attempt I had variables for Limit, Offset, and a counter. I was looping through getting a block of records, checking the result count and if it was <= to the limit I would increment the counter and loop again. (Offset was calculated based on Counter * Limit and always started at Zero) so in theory it would look like this

Counter 0 - Limit 2500 - Offset 0 - Result Count 2500 - Loop

Counter 1 - Limit 2500 - Offset 2500 - Result Count 2500 - Loop

Counter 2 - Limit 2500 - Offset 5000 - Result Count 2500 - Loop

Counter 3 - Limit 2500 - Offset 7500 - Result Count 1500 - Exit Loop

In reality what happened was

Counter 0 - Limit 2500 - Offset 0 - Result Count 2488 - Exit Loop

But the result set did not contain a CI that I knew to be present. So to test the looping I dropped my limit to 2000

Counter 0 - Limit 2000 - Offset 0 - Result Count 1991 - Exit Loop

Hmm - I just got 2488 records before so I know there's more than 2000 so why is my result set smaller than my Limit?

So to account for this, I dropped the counter and manually tracked the offset based on the Result Count (Offset = Offset + Result Count) and if Result Count > 0 then loop. The final version of which is shown below (Declarations outside the loop not shown)

            Do While Not done
                results_batch = _apimanager.GetRestAPI(Of LoopGetterRoot(Of T))(String.Format(APIBase & "?sysparm_limit={0}&sysparm_offset={1}", Limit, Offset))

                If results_batch IsNot Nothing AndAlso results_batch.Result.Count > 0 Then
                    results_all.AddRange(results_batch.Result)

                    Debug.WriteLine("Batch Count: " & results_batch.Result.Count & " - All Count: " & results_all.Count)

                    If results_batch.Result.Count = 0 Then
                        done = True
                    Else
                        done = False
                        Offset = Offset + results_batch.Result.Count
                        results_batch = Nothing
                    End If

                End If
            Loop

This seemed to work ok but it was very slow. In hindsight I was JSON converting every chunk - it might be faster to "stitch" all the raw data together then convert the whole block at once.

I was doing this in an attempt to get a "master list" of all CI's and then use Lync to parse through the list to check my list of names to see if they were valid. Thinking it would be faster to make one API call at the start rather than 1 per name to test. Now that my list is fairly small in comparison it may actually be faster to do the one per name & store the results of each call.

Either way I wanted to acknowledge that your suggestion does work (although oddly for me), I just don't think I'm going to be able to do what I thought I was with it.

Thank you for your help, this has at least been a good learning experience for me.

[Mwatkins]

In many cases this can make your REST call twice as fast!!

As of Orlando, the REST API supports the URL parameter sysparm_no_count=true. This parameter will stop the 2-pass COUNT(*) query that often is not actually a requirement of the integration design. If the parameter is set to "true" then X-Total-Count response will always show 1 instead of showing the total matching record count. 

See KB0817996 for more details.

Please ✅ Correct if this solves your issue and/or 👍 if Helpful

"Simplicity does not precede complexity, but follows it"