Advanced searching: AQL - Assets Query Language
Let's look at some example AQL searches to find objects in the following object schema.
Basic syntax
The basic syntax of an AQL query is <attribute> <operator> <value/function>. One or more objects is returned by the query when the attributes of these objects match the operator and value specified.
Example
A basic AQL query that returns all objects for which the Contact is "Jennifer Evans". Note the quotations around "Jennifer Evans" — they're needed because of a space in the value name.
Syntax for special characters
AQL has a defined syntax and must be entered exactly.
- AQL is case-sensitive.
- If you are using an expression where the value or attribute contains a space, you must include quotations surrounding the value, for example "Ted Anderson".
- If you are using an expression where the value or attribute contains quotation marks, you must escape the quotes by surrounding them with backslashes. For example, if you have an object name such as 15" Screen, to search for it enter: "15\" Screen"
- The attribute name that you specify in the AQL must exist in your Assets schema. If not, the AQL will be considered invalid. The attribute name is case sensitive.
Dot notation
Dot notation is used in AQL to travel down a reference chain of objects. The format <attribute>.<attribute> <operator> <value/function> will return information based upon objects referenced by the parent object.
Example
- In this case, the Connected Device object type has a referenced attribute called Host. The query returns all the Connected Devices which have the Host ATL_DEV02.
- Note that if the referenced attribute contains spaces, it needs to be enclosed with a pair of double quotes.
Keywords
You can use keywords in AQL to return one or more objects based upon properties of those objects instead of the object's attributes. The syntax looks like this: <keyword> <operator> <value/function>.
For example, you could return all objects of a specific object type using the objectType keyword, or all objects of any type with a certain attribute by using the anyAttribute keyword. The table below describes the keywords supported along with their respective examples.
| Keyword | Description |
|---|---|
| objectType | You can limit the search result to a specific object type name, e.g. objectType = "Employment Start Date". Note that the since the attribute contains spaces, it is enclosed within a pair of double quotes. |
| objectTypeId | You can limit the search result on object type ids. For example, objectTypeId in (1, 2). |
| anyAttribute | You can search all attributes on all objects for a relevant match. e.g. "anyAttribute = 123.123.123.123". Note that the use of this keyword may cause delays in searching results. The larger the Assets installation, the more time it will take to execute a query with this keyword. |
| object | You can limit the search to the object, for example, "object having inboundReferences()" will search all objects having any inbound references to it. |
| objectId | You can find an object by object Id, for example, "objectId = 114". Note that the object id is the number from the Key of the object, but without the prefix. For example, if the Key of your object is ITSM-1111, then the prefix is ITSM and the object id is 1111. Note that the Key could change when you move objects across schemas. |
Example
An AQL query that returns all objects with the "Device" object type.
Operators
Operators allow you to create more detailed logical expressions.
The table describes the operators supported in AQL.
| Operator | Description | Example AQL query |
|---|---|---|
| = | Equality test for case insensitive values. | "Office = Stockholm" Checks if the Office attribute has a value equal to Stockholm or STOCKHOLM |
| == | Equality test for case sensitive values. | "Office==Stockholm" Checks if the Office attribute has a value equal to Stockholm considering the case of the input provided. |
| != | Inequality test | objecttype=Employee and Office!=Stockholm Checks if the Employee object has an attribute Office whose value is NOT equal to Stockholm. |
| < | Less than test. | "Price < 2000" Checks if the Price is less than 2000 dollars. |
| > | Greater than test. | "Price > 2000" Checks if the Price is greater than 2000 dollars. |
| <= | Less than or equal to test | "Price <= 2000" Checks if the Price is less than or equal to 2000 dollars. |
| >= | Greater than or equal to test. | "Price >= 2000" Checks if the Price is greater than or equal to 2000 dollars. |
| like | Matches a value with any subset of input in the query. It is case insensitive. | "objecttype=Employees and Office like Stock" Returns all objects of Employees type which have an Office attribute value that contains the characters 'Stock' or 'STOCK' |
| not like | Excludes values which match with any subset of input in the query. | "objecttype=Employees and Office not like Stock" Returns all objects of Employees type which have an Office attribute value that DO NOT contain the characters 'Stock' |
| in() | Finds a match in the given arguments and returns results. | Office in ("Stockholm", "Oslo", "San Jose") Returns all objects of Office type which HAVE one of these values: Stockholm, Oslo or San Jose. |
| not in() | Excludes the results for which a match is found in the given arguments. | Office not in ("Stockholm", "Oslo", "San Jose") Returns all objects of Office type which DO NOT HAVE one of these values: Stockholm, Oslo or San Jose. |
| startswith | Finds a match whose value starts with the given input. It is case insensitive. | "Office startsWith St" Returns results which match values of Office type starting with the characters "St" or "ST" |
| endswith | Finds a match whose value ends with the given input. It is case insensitive. | "Office endsWith St" Returns results which match values of Office type ending with the characters "St" or "ST" |
| is | Helps test whether a value exists or not. | "Office is EMPTY" Checks whether the value of the Office type exists and returns results accordingly. "Office is not EMPTY" Checks whether the value of the Office type is not empty. |
| dot operator(.) | Helps navigate the Referenced types attributes for an object. This operator is commonly used in: inboundReferences() or inR() functions. outboundReferences() or outR() functions. Order by clause. | "Country.Office = Stockholm" The dot operator here navigates to the referenced object Office in the attribute Country and compares Office with the value Stockholm. |
| having | Used with either the inboundReferences() OR outboundReferences() functions | "object having inboundReferences()" Returns all objects having inbound references. |
| not having | Used with either the inboundReferences() OR outboundReferences() functions | "object not having inboundReferences()" Excludes all objects having inbound references and returns results. |
Example
An AQL query that uses the IN operator and objectType to return objects of three different object types.
Combination operators
You may use operators such as AND/OR to create larger and more complex AQL expressions.
Example
An AQL query that includes two statements linked with the AND operator.
Functions
You can use different functions to supply dynamic values to AQL expressions.
| Type | Function name | Description |
|---|---|---|
| Date | now() startOfDay() endOfDay() startOfWeek() endOfWeek() startOfMonth() endOfMonth() startOfYear() endOfYear() | You can use a range of functions to write queries which involve date and time. We use m for minutes, h for hours, d for days and w for weeks to represent relative time. For example, a query with a condition like: Created > "now(-2h 15m)" returns all objects created in the last 2 hours and 15 minutes. For example, a query containing something like: objectType = Employees and "Employment End Date" < endOfMonth(-90d) returns all Employee objects whose Employment End Date falls before 90 days from the current month's end date. You may also check for an upcoming date, for example, check when the license of a software expires by the end of the year. Your query can then include something like : licenseEndDate = endOfYear() You can use all other date functions in a similar manner. |
| IP Address | CIDR(IP RANGE) | CIDR(IP RANGE) - Filter on IP ranges For example |
| User | currentUser() | You can filter on user attributes connected to the current (logged in) user by invoking this function in your AQL query. Note that the attribute used in the query for filtering needs to be of type User. For example, objecttype = Computer and User = currentUser() This function will work when a currentUser is selected, ie. the user is logged in. |
currentReporter() | You can filter on user attributes connected to current reporter in custom fields by invoking this function in your AQL query. Note that the attribute used in the query for filtering needs to be of type User. For example, User = currentReporter() This function will only work when an issue is selected, and refers to the reporter that appears in the current issue. | |
user(user1, user2, ..) | You can filter on objects which have a reference to the users that you provide in the argument list of the function. The attribute used to filter must be of User type. This function will work with multiple arguments only if the User type attribute that you filter on allows multiple values, that is, the cardinality for it is more than one. For example, an object type Team has an attribute Member. This attribute is of User type. Additionally, this attribute has been configured to have a cardinality of 3. If you want to search a set of Team objects where the users: admin and manager are its members, you can write the following query: objecttype=Team and Member having user("admin", "manager") | |
| Group | group(group1, group2,...) | You can filter on any object connected to a user within a specific group. The attribute used to filter has to be of User type. For example, User in group("jira-users", "jira-administrators") |
| user(user1, user2, ...) | Filter on any object connected to a user within a specific group. The attribute used to filter has to be of Group type. For example, Group having user("currentReporter()") | |
| Project | currentProject() | Filter on any object connected to the currently selected Jira project. Works only in the context of a ticket. For example, Project = currentProject() |
Reference functions
Reference functions are functions that take two arguments - AQL and/or a reference-type argument. Essentially, you can use reference functions to run an AQL query on a subset of objects of a particular reference type. Such a query will run on a small subset of the total objects, which allows you to limit results and/or processing time.
- The AQL argument can be an arbitrary AQL including an AQL with reference function.
- The Reference Type argument is optional.
| Sr. no | Name | Description |
|---|---|---|
| a |
| Filter objects having inbound references where the referenced objects match the AQL query provided as an argument to the function. For example, an AQL query like: object having inboundReferences() will return all objects having inbound references since the empty AQL argument to the function will match all inbound referenced objects. But an example query like: object having inboundReferences(Name="John") will return all objects which have an inbound referenced object with an attribute Name and value for Name as "John". |
| b |
| This is a variant of the inboundReferences(AQL) function described in (a) Using this, you can filter the inbound referenced objects further by providing the Reference Type as a single or multiple value(s). You can do this with the help of the "IN" operator. Reference Type is the Additional Value field that you provide on an attribute when you define a referenced object for an object type as shown in the screenshot below ("Installed" reference type).
For example, an AQL query like this: object having inR(objectType = "File System", refType IN ("Depends")) will return objects which have inbound referenced objects of "File System" and only for those File System objects whose Reference Type is "Depends". Similarly, an AQL query like: object having inR(objectType = "File System", refType IN ("Depends", "Installed", "Using")) will return objects which have inbound referenced objects of File System and for those File System objects whose Reference Type is any of these: Depends, Installed, Using |
| c |
| Filter objects having outbound references where the referenced objects match the AQL query provided as an argument to the function. For example, an AQL query like: object having outboundReferences() will return all objects having outbound references since the empty AQL argument to the function will match all outbound referenced objects. But an example query like: object having outboundReferences(Name="John") will return all objects which have an outbound referenced object with an attribute Name and value for Name as "John". |
| d |
| This is a variant of the outboundReferences(AQL) function described in (b) Using this, you can filter the outbound referenced objects further by providing the Reference Type as a single or multiple value(s). You can do this with the help of the "IN" operator. Reference Type is the Additional Value field that you provide on an attribute when you define a referenced object for an object type. For example, an AQL query like this: object having outR(objectType = "Employees", refType IN ("Location")) will return objects which have outbound referenced objects of Employees and only for those Employees objects whose Reference Type is "Location". Similarly, an AQL query like: object having outR(objectType = "Employees", refType IN ("Location", "Country")) will return objects which have outbound referenced objects of Employees and for those Employees objects whose Reference Type is any of these: Location, Country |
Example
An AQL query that uses the inR function to return all objects that have inbound references.
An AQL query that uses the inR function to return all objects that are of object type "Connected Device", and have inbound references of type "Installed".
Using the connectedTickets() function
The connectedTickets() function is used to filter objects having tickets connected to them. Specific Jira issues may be selected by providing a proper Jira Query Language (JQL) query. If no JQL query is provided, all objects having Jira issues connected are returned.
| Name | Description |
|---|---|
| connectedTickets() | All objects having tickets connected to them are returned. |
| connectedTickets(JQL query) | Object having tickets connected to them that match given JQL query are returned. |
Example
This query runs a JQL query (labels IS empty) on all connected issues, and then returns objects based on the results.
Using the objectTypeAndChildren() function
This function is used to return objects (and their children) of a specific object type.
| Name | Description |
|---|---|
| objectTypeAndChildren(Name) | Filter objects based on the object type specified by the Name and its children. If the name contains spaces, make sure you enclose it within a pair of double quotes. |
| objectTypeAndChildren(ID) | Filter objects based on the object type specified by the ID and its children. |
Example
An AQL query that returns all objects and child objects from the "Host" object type.
Functions, references, and AQL can be combined in powerful ways. For example, you could add multiple object references to a custom field attached to an object, and then search those references for a specific key:
object HAVING inboundReferences(Key IN (${MyCustomField${0}}))Or a specific label, by using dot notation:
object HAVING inboundReferences(Label IN (${Portfolios.label${0}}))Note that the above two queries make use of Assets placeholders.
Ordering
You can order the results of your query by adding the following suffix to any AQL:
order by [AttributeName|label] [asc|desc]Good to know
- If you do not specify an order by clause, the default will be ascending order by the label attribute of an object type.
- If the attribute specified in the order by clause is of the object reference type you can use dot notation to order by attributes on the referenced object. For example, if you want to order by the referenced Department object of an Employee object, you can mention the clause in this way: order by Employee.Department. This can be done in unlimited depth. However, note that the every dot in the order by clause will decrease the performance of that particular AQL.
- Missing values will appear at the top of the list. This will hold true if the order in the query is ascending order : "asc".
- The attribute name specified in the AQL must exist in Assets. If not, the AQL will be considered invalid. The attribute name is case sensitive.
- If the results do not contain the attribute specified in the "order by" clause, the order of the objects returned will be arbitrary.
- The placeholder label can be used instead of the attribute name to order the objects by their configured label.
Example
These search results are ordered in descending order by their keys.
