Using Orchestration Query Language from command line

You can use the essential Orchestration Query Language keywords and syntax to effectively write queries from command line.

You can monitor the HCL Workload Automation production plan environment by using the OQL, which applies to REST API V2. With the OQL, you can write queries for items in your database. Also with the OQL, you can retrieve required results quickly and easily.

An OQL query begins with an expression, which is composed of different conditions, known as queryTerms. A queryTerm is the minimum condition of a OQL construction, for example job_name = test1.

QueryTerms include three main elements: a field, a comparison_operator, and a value. The fields query are case-insensitive. The values are case-sensitive.

The field must begin with a letter.

After the first letter, the field can contain the following characters:

  • 0 - 9
  • a - z
  • A - Z
  • _
  • -
  • .
You can express the value in one of the following ways:
  • Integers, both positive and negative.
  • Strings in single quotation marks (').

The following table lists OQL keywords with descriptions.

Table 1. OQL syntax keywords
Keywords Description
AND Returns the results that satisfy all the conditions separated by AND. It is case insensitive.
OR Returns the results that satisfy at least one of the conditions separated by OR. It is case insensitive.
( Opens a priority expression.
) Closes a priority expression.
[ Opens a list.
] Closes a list.
= Returns all the elements equal to the specified element. Numbers, strings, or one of these keywords can follow the keyword: true, false, or null.
!= Returns all the elements different from the given one. Numbers, strings, or one of these keywords can follow the keyword: true, false, or null.
<= Returns all the elements that are less than or equal to the specified keyword. Only numbers or strings can follow.
>= Returns all the elements that are greater than or equal to the specified keyword. Only numbers or strings can follow.
< Returns all the elements that are less than the specified keyword. Only numbers or strings can follow.
> Returns all the elements that are greater than the specified keyword. Only numbers or strings can follow.
IN Returns items that the specified list includes.
NOT IN Returns items that the specified list does not include.
LIKE Returns elements that match the specified pattern. The accepted characters are as follows:
  • @: matches all characters.
  • ?: matches a single character in a specific position.
NOT LIKE Returns elements that do not match the specified pattern. The accepted characters are as follows:
  • @: matches all characters.
  • ?: matches a single character in a specific position.
ORDER BY Orders the query results according to the specified fields. You can use the keyword at the end of the construction. By default, the field is sorted in ascending order. The accepted values are as follows:
  • ASC: Sorts the results in ascending order.
  • DESC: Sorts the results in descending order.
, Separates values inside a list.
. Specifies a subfield of an item.
true Represents the Boolean value true.
false Represents the Boolean value false.
null Represents the null value.
The following examples show various queries:
AND or OR
The following example explains how to find a job stream that does not have the name "accounting" and that contains from 5 to 10 jobs. If this condition is not met, the query searches for a job stream that contains a job specified in the following list: ['job1', 'job\'3\'', 'job4'].
(jobStreamName != 'accounting' AND (totalJobs > 5 AND totalJobs <= 10)) OR jobName IN ['job1', 'job\'3\'', 'job4']
LIKE
The following example shows how to find all the job streams with names that start with "test1"::
jobStreamName LIKE 'test1@
IN or NOT IN
The following example shows how to find a job stream named "accounting". If this condition is not met, the query searches for a job stream that contains a job in the specified list and that is not on the workstations specified in the previous list:
JobStreamName = 'accounting' OR jobName IN ['italy','united_kingdom'] AND workstation NOT IN ['wks2', 'wks5']
ORDER BY
To put field1 data in ascending order, field2 data in descending order, and field3 data in ascending order, you can write a query as follows:
field1 = 'x' or field2 = 'y' and field3 = 'z' ORDER BY field1, field2 DESC, field3
Specify subfields (.)
The following example uses the period character (.) to specify the type subfield of the dependencies item, and the ID of the predecessor job or job stream:
(dependencies.type = EXTERNAL_JOB AND dependencies.jobId = <job_id>) OR (dependencies.type = EXTERNAL_JOBSTREAM AND dependencies.jobstreamId = <js_id>)