Using Cimera > Searching and Querying > Querying > CQL Query

The syntax of a CQL query is:

FIND item-type [VERSION] [ WHERE [NOT] condition [logical-operator [NOT] condition...] ]

Where:

•Square brackets [ ] indicate an optional clause

•'...' indicates a repeating (0 or more occurences) clause

•The Version keyword is used when the Item Type being queried is Versionable and you want to find specific versions, and not the Stem

•logical-operator is AND or OR

•Conditions can be surrounded by parentheses to give the correct precedence, e.g. A AND (B OR C)

•Use single quotes to delimit string values, e.g. 'In Use'

•Use square brackets to specify Item Types, Field or Link names that contain spaces, e.g. [Environment Component], [Technical Change]

•Comments can be included within queries between /* and */

Note: queries are case insensitive, keywords are represented here in UPPERCASE merely for clarity

Version

See CQL Syntax Versions

Condition syntax

For Fields:

fieldname-or-id operator [value-or-valuelist]
Note: The fieldname can be

•the field's form label - the label as it appears on the Item Details (e.g. Name, [Item Reference Number]).

•its unique fieldname (e.g. CIName)

•its unique ID (e.g. FLD:A$CINAME)

If there are duplicate labels then you must use the unique fieldname or Unique Id

Operators:
Operators that take no values:

ISNULL and ISNOTNULL

e.g. [defect priority] ISNULL

Operators that take a single value:

= , <> , > , >= , < , <= , LIKE

e.g CINAME LIKE 'ab%'

Operators that take multiple values:

IN , NOTIN , INLIKE

e.g. CISTATUS in ('in use', 'out of service')

For LIKE use % to represent multiple wild characters and _ as a single wild character, e.g. LIKE 'A%' or LIKE 'A_S'
Specify multiple values in parentheses, e.g. ('value1','value2','value3')

LIKE has special usage for combined group/user fields:
Use LIKE 'group-name/%' to specify all users within group-name
Use LIKE '%/user-name' to specify a particular user regardless of group

You cannot use the LIKE operator against part of a user's name. e.g. LIKE 'fred%' is not permitted

For Links:

See CQL Syntax Links

Boolean / Yes/No Values

If the field is a boolean then the query value is true or false irrespective of the text set up for the field

For example

Find Problems that do not have a workaround or where a workaround decision has not been made

FIND Problem WHERE NOT [Workaround Available?] = 'true'

FIND Problem WHERE [Workaround Available?] = 'false'

FIND Problem WHERE [Workaround Available?] ISNULL

Dates and Times

Standard date formats are accepted, for example: '3/8/2012', 3-8-2012, '3/8/2012 22:00', '3/8/2012 10:00PM'

When comparing date and time fields remember that a time is involved and that an equality may not do what you intend

e.g.

FIND [Technical Change] WHERE ciupdated = '26/5/2014'

will not find all the Technical Changes last updated on that date. A date and time comparison is being performed, with a default time of 00:00:00 taken. So only items created at exactly that time are selected.

Use either one or two inequalities to find the required items

FIND [Technical Change] WHERE [Last Updated] >= '26/5/2014' and optionally add AND [Last Updated] < '27/5/2014'

NOW & TODAY

There are two special date values: NOW and TODAY

NOW represents the current date and time and

TODAY represents midnight today

Days can be added or subtracted, for example: NOW+1 or TODAY-7
For NOW a suffix of D, H or M can be added to indicate Days (default), Hours or Minutes, e.g. NOW+30M or NOW-12H

Examples

Find all servers
FIND server

Find servers added in the last week with a name beginning SVR:
FIND server WHERE name like 'svr%' AND created > today-7

Find servers that are in use containing application builds that aren't in a released state:
FIND server WHERE cistatus='In Use' AND hosts(FIND application VERSION where cistatus <> 'released')

Find servers that are in use but don't contain an application build:
FIND server WHERE status='In Use' AND NOT hosts(FIND application VERSION)

Find servers containing application builds developed for a particular change request:
FIND server

WHERE hosts

(FIND application VERSION

WHERE [built for]

(FIND [change request]

WHERE ciname='CR-73'))

Note that F5 executes the query as well as the 'Run Query' button. And if a portion of text is selected then only this text will be executed.
This can be used to execute sub-queries in isolation whilst building more complex queries