How do you get orderby to work with query parameter?

[dfournie]

When I use a query parameter (in this case company) with a sort order (in this case sys_id) it will ignore whichever comes last in the statement when.   Never can I get this statement to produce sorting and the query both.   Does anyone know how to get both to work so I can use this to pull consecutive extracts?

https://<system>.service-now.com/sys_user.do?CSV&sysparm_default_export_fields=all&sysparm_query=com...

[ChrisBurks · Accepted solution]

From your example you posted you are using the sysparm_query multiple times. You only need to use sysparm_query once.

In order to connect queries use the caret ^ symbol. Also sys_id's are unique so I would think that supplying a sys_id would only produce a result of one record. Not sure how you would order by one record. Or maybe I don't fully grasp what you're trying to do.

I would do my query like this

....&sysparm_query=company.nameLIKEACME^ORDERBYsys_id&sysparm_record_count=400

I use the LIKE operator just in case you're testing in a Demo instance. I don't think there is just a company with ACME unless someone has changed it. LIKE about the same as using CONTAINS where it search the data to see if it contains the given criteria. Also for the order by it isn't a separate parameter like you have in your example "sysparm_orderby". It could be a parameter but not for using with the sysparm_query. Once you add another ampersand after starting the query you're making a separate parameter not associated with the query itself. So from my example above I added color to the sysparm_query parameter and once I get to the next ampersand "&", I'm stepping out of the sysparm_query.

Another issue I see in your given example is that the sys_id value has some encoded characters %3E and %3D which equate to > and =. So you even if you had your syntax correct for the sys_id query, the sys_id itself would be incorrect because it would look like this to the system:

                                              sysparm_query=sys_id>=3D0482d2536f8a11001a72ee4dbb3ee4c5

Which having the right angle bracket '>' after sys_id will cause the system to ignore or not understand it.

In conclusion you use the caret ^ to concatenate the query conditions for sysparm_query and use the ampersand to concatenate the different parameters that can be placed in the URL.

I hope that helps.

[dfournie]

Thanks Chris. This was very helpful. I think I am almost there now. The

first part of the query works fine now to extract the first 400 records (or

more typically I will use this for the first 10K records to reach the

maximum limit set in our system). But for the second part I am trying to

extract the next 400 records (or more typically the next 10K records for my

large extracts). So if I cannot use the %3E and %3D to set my >= parameter

to the last sys_id from the first record, how can I write the second query

to ensure I am extracting the next 10K records beginning with the last

record from the first extract (I like to include the last record from the

first extract in the next sequential extract as a checkpoint/validation

that I am pulling the very next record).

So I have for my first query:

https://.service-now.com/sys_user.do?CSV&sysparm_default_export_fields=all&sysparm_query=sys_id%

3E%3D0482d2536f8a11001a72ee4dbb3ee4c5

^{sysparm_query=company.nameLIKEACMEl}ORDERBYsys_id&sysparm_record_count=400

Could you provide help on my second query to get the next set of records?

Darrin Fournier

CSC | Deployment, Service Management GDN | dfournie@csc.com

ITIL ® V3 Expert

ServiceNow System Administrator

Out of Office Dates:

This is a PRIVATE message. If you are not the intended recipient, please

delete without copying and kindly advise us by e-mail of the mistake in

delivery. NOTE: Regardless of content, this e-mail shall not operate to

bind CSC to any order or other contract unless pursuant to explicit written

agreement or government initiative expressly permitting the use of e-mail

for such purpose.

[ChrisBurks]

Ah, I see what you're doing now. Alright I'm going to step back on one thing that I wrote then. You can use the right angle after the sys_id then. I apologize for that.

Here's what worked for me.

service-now.com/sys_user.do?CSV&sysparm_default_export_fields=all&sysparm_query=company.nameLIKEACME^sys_id%3E%3D3D0482d2536f8a11001a72ee4dbb3ee4c5^ORDERBYsys_id&sysparm_record_count=400

[dfournie]

Thanks Chris. I tried that query and I get the same results that our data

analyst was getting with his test queries last week. What we see is that

depending on where we place the query order for the three queries (ORDERBY,

>=, Company Name) we either get the next set of records sorted by sys_id

but the Company Name query is ignored or the Company Name query remains

valid but the >= sys_id is ignored and we get the same data set that was

pulled in the first extract.

We are an MSP with Domain separation on Eureka so I'm not sure if there is

something unique about our system that causes this bizzare behaviour. But

this is where I am stumped now. This is great progress though now getting

these queries cleaned up and understanding where to use the ^ and the &

properly. If I can just get both of those queries to work together this

will be a huge relief for me as I need to pull back some queries on

datasets exceeding our 10k limit.

Darrin Fournier

CSC | Deployment, Service Management GDN | dfournie@csc.com

ITIL ® V3 Expert

ServiceNow System Administrator

Out of Office Dates:

This is a PRIVATE message. If you are not the intended recipient, please

delete without copying and kindly advise us by e-mail of the mistake in

delivery. NOTE: Regardless of content, this e-mail shall not operate to

bind CSC to any order or other contract unless pursuant to explicit written

agreement or government initiative expressly permitting the use of e-mail

for such purpose.