NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.
DIFFICULTY LEVEL: INTERMEDIATE
Assumes a good knowledge and/or familiarity of ServiceNow Web Services and Scripting in ServiceNow. Assumes having taken the class SSNF and has good intermediate level of knowledge and/or familiarity with Scripting in ServiceNow.
In my previous article we used a SOAP Client to send a simple query to ServiceNow to retrieve a set of records. Building on what we did in that article I wanted to next tackle encoded queries.
In reality this isn't much different than GlideRecord encoded queries. ServiceNow has provided in each out-of-the-box table WSDL an encoded query parameter that allows you to do essentially the same thing.
Prerequisites:
If you have already performed the lab in my previous article you can skip the prerequisites.
1. Some sort of SOAP Client installed on your test machine. In the lab I will be using SoapUI.
2. Create a web service user with itil and soap roles in ServiceNow.
3. Perform the first lab: Mini-Lab: Web Services — Part 1: Using SoapUI to Test ServiceNow WSDL
Lab 2.1: Executing an a Date Range Query With a SOAP Client
In this lab we will be pulling back a set of records based on a date range.
1. Using our previous SoapUI project; edit the XML to look like the following:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:inc="http://www.service-now.com/incident">
<soapenv:Header/>
<soapenv:Body>
<inc:getRecords>
<__encoded_query>sys_updated_on>=2015-05-10 00:00:00^sys_updated_on<=2015-11-20 23:59:59</__encoded_query>
</inc:getRecords>
</soapenv:Body>
</soapenv:Envelope>
NOTE
This will retrieve all Incident records that were updated between the two dates mentioned.
2. Click on the play button to run the query. You should see your results appear in the results panel to the right of the query.
3. Look through the results and make sure that the updated date falls within the range specified in your query.
NOTE
As you can see the encoded query is actually pretty straight-forward. It is knowing the syntax that is secret.
Now let us try something a bit more complex.
Lab 2.2: Executing an Encoded Query With Additional Filters
Here we will pull back all records that are Active and were the Assigned To user ID is either Beth Anglin or David Woo. You might have to actually go assign a couple of incidents to Beth and David. 😁
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:inc="http://www.service-now.com/incident">
<soapenv:Header/>
<soapenv:Body>
<inc:getRecords>
<active>true</active>
<__encoded_query>assigned_to.user_idINbeth.anglin,david.loo</__encoded_query>
</inc:getRecords>
</soapenv:Body>
</soapenv:Envelope>
NOTE
A couple of things here. Note that you can mix in the fields from the WSDL; as in this case here the active field is used. This, of course, could just as easily been added into the encoded query, but I wanted to show what was possible. Also note that the assigned_to field is actually the sys_id of a user. So it will be necessary to dot-walk to the user id field (user_id) to get at the real-English names.
Encoded Query Reference
I thought I would throw in a "non-comprehensive, but overall pretty good" reference of what I have tested and found to work with the encoded query parameter.
- These are all URL encoded queries. It is not necessary to encode everything.
- Dates are any valid date format. I prefer: yyyy-mm-dd
- Dates and strings are not to be surrounded by quotes. Exception: When working with JavaScript values.
- The ">" symbol is encoded as >
- The "<" symbol is encoded as <
- Less than or equal to: <=
- Greater than or equal to: >=
- AND is represented by "^"
- OR is represented by "^OR"
- Booleans: false/true
- NOT is "<>" and is indicated thus <>
- Dot walking is allowed!
- NOTE: It is not allowed to use parenthesis in encoded queries.
- AND Query
sys_updated_on>2023-07-01^sys_updated_by.user_id=admin
This would return all records updated by user Admin on 10/5/2015.
- OR Query
sys_updated_on>2023-07-01^ORsys_updated_on<2015-12-31
This would return all records updated on 5/10 and 5/15/2015.
- IN Query
assigned_to.user_idINbeth.anglin,david.loo
- CONTAINS Query
sys_updated_by.nameCONTAINSbeth.anglin
- Starting a NEW Query (NQ)
sys_updated_by.nameCONTAINSbeth^NQcmdb_ci=55b35562c0a8010e01cff22378e0aea9
- LIKE Query (end wild card is implied)
sys_updated_by.nameLIKEbeth.a
- ROLE Query
roles=itil
- Choice Query (by value field)
install_status=1
- Javascript parameters are allowed
sys_updated_on>javascript:gs.dateGenerate('2023-05-10','23:59:59')
- ORDER BY Query
unit_name=day^ORDERBYvalue
- BETWEEN Query
What did work:
sys_updated_on<2023-07-01^sys_updated_on>2015-12-31
Interestingly, the following did not work (in 2015), but now DO work; which is goodness!
sys_updated_onBETWEEN2023-05-15^2023-08-15
sys_updated_onBETWEENjavascript:gs.dateGenerate('2023-07-01','00:00:00')@javascript:gs.daysAgoEnd(0)
- INSTANCEOF Query
sys_class_nameINSTANCEOFcmdb_ci_computer
NOTE: The previous query replaces something like the following; which doesn't work anyway:
sys_class_name=cmdb_ci_computer
^ORsys_class_name=cmdb_ci_server
^ORsys_class_name=cmdb_ci_win_server
^ORsys_class_name=cmdb_ci_unix_server
^ORsys_class_name=cmdb_ci_linux_server
Some great references
- https://docs.servicenow.com/bundle/vancouver-api-reference/page/integrate/inbound-soap/concept/c_Dir...
- https://docs.servicenow.com/bundle/tokyo-application-development/page/integrate/web-services-apis/re...
There you go! Now you can see how encoded queries can be used by an external Soap Client to retrieve information from a ServiceNow table.
In my next article I will show how what we have covered so far with WSDL and Soap Clients can help with the creation of ServiceNow Scripted Web Services.
Enjoy!
Steven Bell.
If you find this article helps you, don't forget to log in and mark it as "Helpful"!
Originally published on: 11-24-2015 10:09 PM
I updated the code, fixed broken links, and brought the article into alignment with my new formatting standard.
