Using the Server Query Option

Scope: This option only applies to ServiceNow, Cherwell, HPSM and Jira source servers

If your source server type is one of the above then you can use the Server Query button to make use of native query commands.   Note that the  query will not be validated before execution time and invalid queries can produce unexpected results.

When selected, Server Query appends @@QRY(‘’) to the Query string.  You can then enter a native query between the single quotes like this:

Notes:

1. You can specify more than one filter. If you add multiple filters for the same field, the result is an OR operation between those filters. If the fields are different, the result is an AND operation between those filters.
2. It is recommended to use Precision Bridge standard source filtering queries for constructing simple queries instead of server queries where possible.
3. If the query string contains a double quote, you will need to escape it by doubling. (see the Service Now example below)
4. Parameterised queries are supported in Precision Bridge 7.0 onwards for Service Now queries ONLY. See below for details.

Use of Server Query with ServiceNow source instances

For ServiceNow instances, the server query supports the use of ‘dot-walking’ to allow comparison with fields on referenced tables.    The Server Query can also be combined with other query expressions to limit the record selection. For example:

@@QRY(‘company.nameLIKECalbro’) AND [name] != NULL

Any single quotes in the query string must be escaped by doubling them for example:

@@QRY('company.nameLIKETom’’s Company’)

If the query string is invalid, by default the ServiceNow server will ignore any invalid parts of the query. Therefore you can end up in a situation where you get more results than expected, even the full table.

It is therefore recommended to set the ServiceNow system property glide.invalid_query.returns_no_rows=true. This will ensure that if the query is incorrect, NO rows will be returned.

Support for Embedded variables in server query strings

For ServiceNow instances, the server query supports the use of ‘dot-walking’ to allow comparison with fields on referenced tables. Variables (for example Project Variables, Temporary Variables or Values from the Current Record) can be embedded in the query stirng For example in the source filter string below:

@@QRY('u_key_string=#test#') AND [sys_id] != NULL

#test# is a Project variable. At execution time #test# will be replaced by the value defined for this project variable in the execution settings. In this case, the variable is given a value Child

@@QRY('u_key_string=Child') AND [sys_id] != NULL

 

Use of Server Query with HPSM, Cherwell or Jira source servers

For these server types, the Server Query must be used in isolation. It cannot be combined with other query expressions. These servers do NOT support embedded variables in the query string.

Use of Server Query with Cherwell source servers

This type of advanced query supports filtering, running Cherwell stored searches of type 'Global', and sorting. It must be in a format as specified below:

@@QRY('{
"filters":[
{ "fieldName": "FirstName",        
"operator": "eq",
"value": "Eric"
},
{ "fieldName": "LastName",
"operator": "STARTSWITH",
"value": "L"
},
{ "fieldName": "MiddleName",
"operator": "contains",
"value": "bert"
}
],
"searchName":"All Customers",
"sorting": [
{
"fieldName": "LastName",
"sortDirection": 0
}
]
}
')

Note the following with respect to Cherwell Server Query Strings:

1. The 'fieldName' value has to be the name of the field delimited by double quotes.
2. The 'searchName' value currently can support only 'Global' Cherwell searches. 
3. Filtering can be performed either using fields list in the filters or using search names.
4. Sorting can be accomplished by listing the fields and the direction of the sort (1: Ascending 0: Descending). This is optional.