Audit Query Language overview

You can use the Audit Query Language to search for audited sessions with Find Sessions from a command line interface.

The Audit Query Language (AQL) serves two purposes:

Query definition: The Audit Management Server database stores the query definition as an AQL statement.
Query language: In order to query for audit information, the audit & monitoring service sends AQL statements to the Audit Management Server database.

When you enter an AQL query, the system stores this as the query definition. The query definition defines what information is of interest and how to group the results. In some cases, you might retrieve the results over multiple phases, depending on how you want to present the information.

For example: The query "get all Windows audit sessions, grouped by user" has two phases:

Gather a list of all users who have Windows audit sessions
Show all the Windows sessions for each user who is listed in Step 1.

In each phase, the Audit & Monitoring generates the AQL statement and sends it to the Audit Management Server database in order to query for audit information. This part is when the AQL statements function as a query language.

Here is an AQL statement example:

1 Type=wingui; orderby=time DESC; time is in this week; user=”joe*”,”mark*”;machine=”domaincontroller”’

The example query would return audited Windows sessions in the last week where Joe or Mark logged in to the domain controller system, and the results would be listed in descending order of when they occurred.

In general, the format of an AQL statement can either be just some quick search terms or a statement with the following parts:

Audit trail types
Group-by
Order-by
Predicates

Backus-Naur Form (BNF) definition of AQL

Here is the Backus-Naur Form (BNF) definition of the AQL language syntax so that you can see how the query language is constructed.

<aql> ::= <version> {<quick_terms>} ZZ_BAR_ZZ {<type> ZZ_BAR_ZZ <groupby> ZZ_BAR_ZZ <filter>}

<version> ::= any numeric number. Currently, we support only 1.

<quick_terms> ::= <word> ZZ_BAR_ZZ <and_words> ZZ_BAR_ZZ <or_words> ZZ_BAR_ZZ <exact_combined_words>

<and_words> ::= <word> (" " <word>)+

<or_words> ::= <word> ("OR" <word>)+

<exact_combined_words> ::= """ <and_words> """

<word> ::= any printable string except white spaces

<type> ::= "type" ("=" | ":") <typename> {"," <typename>}

<groupby> ::= "groupby" ("=" | ":") <groupname> {"," <groupname>}

<orderby> ::= “orderby” (“=”ZZ_BAR_ZZ”:”) <columnname> {“ASC”ZZ_BAR_ZZ”DESC”}

<normal_filter> ::= <string_filter> ZZ_BAR_ZZ <time_filter> ZZ_BAR_ZZ <number_filter> ZZ_BAR_ZZ <enum_filter> ZZ_BAR_ZZ

<boolean_filter> | <ip_filter>

<negative_filter> :: = "not(" <normal_filter> ")"

<string_filter> :: = <string_field> <string_op> """ <string_val> """ {"," """ <string_val> """}

<string_op> ::= "=" | "!="

<time_filter> ::= <single_time_filter> ZZ_BAR_ZZ <between_time_filter> ZZ_BAR_ZZ <in_predefined_time_filter> |

<in_past_filter>

<single_time_filter> ::= <time_field> <single_time_op> <single_time_val> <!-- single_time_val is in

format of "yyyy-mm-dd hh:mm:ss" -->

<time_op> :: = "is before" ZZ_BAR_ZZ "is after" ZZ_BAR_ZZ "is not before" ZZ_BAR_ZZ "is not after"

<between_time_filter> ::= <time_field> <between_time_op> <between_time_val>

<between_time_op> ::= "is between" | "is not between"

<between_time_val> ::= <single_time_val> " and " <single_time_val>

<in_predefined_time_filter> ::= <time_field> <in_predefined_time_op> <predefined_time_val>

<in_predefined_time_op> ::= "is in" | "is not in"

<predefined_time_val> ::= "today" ZZ_BAR_ZZ "yesterday" ZZ_BAR_ZZ "this week" ZZ_BAR_ZZ "last week" ZZ_BAR_ZZ "this month" ZZ_BAR_ZZ "last month" ZZ_BAR_ZZ

"this year" | "last year"

<in_past_filter> ::= <time_field> <in_past_op> <digit>+ <unit_of_time>

<in_past_op> ::= "is in_past" | "is not in_past"

<unit_of_time> ::= "day" ZZ_BAR_ZZ "hour" ZZ_BAR_ZZ "minute"

Note: Currently, AQL has filters only for strings and time.

AQL usage examples

/installation

/aql

/user

/machine

/activetime/

/suppresswarning

/sw

/delete

/export

/role

/path

/format

/ticket

Find Sessions Usage formats:

FindSessions /i=<installationName> /a=<AQL query>

FindSessions /i=<installationName> /u=<user or semi-colon-separated list of users> /m=<machine or semi-colon-separated list of machines> /t=<YYYY-MM-DD HH:MM:SS>

FindSessions /i=<installationName> /a=<AQL query> /{delete}

FindSessions /i=<installationName> /a=<AQL query> /export=<SessionList> /format=<htmlZZ_BAR_ZZhtmZZ_BAR_ZZcsvZZ_BAR_ZZpdfZZ_BAR_ZZxml> /sw /path=<folderPath>

FindSessions /i=<installationName> /a=<AQL query> /export=<SessionListZZ_BAR_ZZWashEventsZZ_BAR_ZZUnixCommandZZ_BAR_ZZUnixInputZZ_BAR_ZZUnixInputOutput> /sw /path=<folderPath>

FindSessions /i=<installationName> /a=<AQL query> /export=<UnixCommand|UnixInput\UnixInputOutput> /sw /path=<folderpath> /r="role1;role2" /"ticket1;ticket2"

Find Sessions: Usage examples without AQL:

FindSessions /i="DirectAudit" /user="user sample" /machine="machine sample" /activetime="2018-12-24 15:30:45"

FindSessions /i="DirectAudit" /user="maya;fred" /machine="KH-Win7;KH-Win8" /activetime="2018-12-24 15:30:45"

Find Sessions: Usage examples with AQL:

FindSessions /i="DirectAudit" /aql="1 time is in this week"

FindSessions /i="DirectAudit" /aql="1 module = \"Windows PowerShell\"\"" /delete

FindSessions /i="DirectAudit" /aql="1 text=\"dzdo\"\"" /export="UnixCommand" /path="folder path"

FindSessions /i="DirectAudit" /aql="1 inputcommand = \"dzdo*\"\"" /suppresswarning /export="UnixInputOutput" /path="folder path"

FindSessions /i="DirectAudit" /aql="1 sessionid=\"D108F7B2-F4FB-FB42-A6E7-A40454780690\"\"

AQL quick search terms

ou can just enter a series of keywords if you just want to do a quick search.

Here are some examples:

joe : any data fields that contain the word ‘joe’
joe john : any data fields that contain both words ‘joe’ and ‘john’
joe OR john : any data fields that contain ‘joe’ or ‘john’
"joe john" : any data fields that contain the exact phrase “joe john”

The default operator designated by a space between terms is evaluated as an “AND” operator, so there is no need to include “AND” between terms. Explicit operator takes precedence over implicit operator, thus “OR” is always evaluated before the absence of an operator.

Here are some examples of quick search term queries in AQL:

The database searches the following data fields with the keywords in quick search terms:

User (username)
Machine (machine name)
Time (audit trail data record start time)
Module
Text

AQL audit trail types example

If desired, you can refer to the types of audit trails to include in the results, such as Windows sessions or UNIX sessions. You can specify one or more audit trail types with the "type:" parameter.

If you don't include this parameter, the results include all audit trail types.

If you specify more than audit trail type, the results are those that fit all specified parameters.

Example:

type=wingui, shellui

AQL group-by example

If desired, you can specify how to group the results.

Example:

groupby=user, date

When you specify multiple groupby criteria, the database groups the results by the first criterion and displays the immediate result by ignoring the remaining criteria. When you double-click the results, then the database displays more results according to the remaining criteria.

FindSessions does not support the use of groupby.

AQL order-by example

If desired, you can specify how to sort the AQL query results by using orderby.

For example:

orderby=time, user ASC, machine DESC

The sort order options are as follows:

ASC: sort results in ascending order
DESC: sort results in descending order

If you don't specify a sort order, the system uses ‘ASC’ by default.

AQL Predicates

Using predicates in your AQL query is entirely optional. You can filter the result set by any number of predicates or none at all. Each predicate expresses a condition that must be true in order for the service to include a record in the result set.

There is an implicit ‘AND’ between each predicate. If you repeat a predicate for a field, there is an implicit 'OR' between them.

Each predicate refers to a field in the schema of an audit trail type. If the field name does not specify an audit trail type, then the field must exist for all selected audit trail types. If the field name specifies an audit trail type specified with the "type:" parameter, then the predicate applies only to that audit trail type.

AQL predicate behavior examples

Example A: Type=wingui, shellui; user = “joe”

The above example selects all Windows and UNIX sessions for joe.

Example B: Type=wingui, shellui; shellui.user = “joe”

The above example selects all Windows sessions but only UNIX sessions for joe.

The service categorizes predicates according to the field data type:

String
Number
Boolean
Date / time
IP
Enumeration

AQL string predicate behavior

Here are some examples of how to filter an AQL query based on string predicates:

AQL query	Filter behavior
field = "<string>",”<string2>”,…	exact match
field != "<string>", “<string2>”,…	not equals (exact match)
field = "<string>", “<string2>”,…	starts with
field != "<string>", “<string2>”,…	not starts with

AQL number predicate behavior

Here are some examples of how to filter an AQL query based on number predicates:

AQL query	Filter behavior
field = <number>	equals
field != <number>	not equal
field >= <number>	greater than or equal
field > <number>	greater than
field <= <number>	smaller than or equal
field < <number>	smaller than

You can replace <number> with any integer or floating point number, such as 1 or -3.14.

AQL Boolean predicate behavior

Here are some examples of how to filter an AQL query based on boolean predicates:

AQL query	Filter behavior
field = true	true
field != false>	false

AQL Date and time predicate behavior

Here are some examples of how to filter an AQL query based on date and time predicates:

AQL query	Filter behavior
field is (not) before <datetime>	before a specific date and time or not
field is (not) after <datetime>	after a specific date and time or not
field is (not) between <datetime> <datetime>	between two dates and time or not
field is (not) in_past <number> <unit>	in the past period of time or not, where the unit is day, hour, or minute
field is (not) in <predefined time>	field is not in the predefined time or not

Replace <datetime> with a particular date and time with the following format:

Y-M-D, for example 2019-12-15
Y-M-D h:m:s, for example 2019-12-15 15:30:00

Replace <predefined time> with any of the following values:

today
yesterday
this week
last week
this month
last month
this year
last year

AQL IP predicate behavior

Here are some examples of how to filter an AQL query based on IP address predicates:

AQL query	Filter behavior
field = <ip>	equals
field != <ip>	not equal
field >= <ip>	greater than or equal
field > <ip>	greater than
field <= <ip>	smaller than or equal
field < <ip>	smaller than

AQL enumeration predicate behavior

Here are some examples of how to filter an AQL query based on enum predicates:

AQL query	Filter behavior
field = <enum>	equals
field != <enum>	not equal

Replace <enum> with the values appropriate for the field you're querying against.

For example, filtering for a session state involves specifying an enum value:

state = Terminated

state != InProgress

AQL keywords

Keyword	Predicate type
Session time	Date/Time predicate
UNIX command time	Date/Time predicate
State	Enum predicate: Unknown, InProgress, Terminated, Disconnected, Completed, ToBeDeleted
Review status	Enum predicate: None, ToBeReviewed, Reviewed, PendingForAction, KeepForever, ToBeDeleted
Session size	Numeric predicate (in kilobytes)
Unix outputs and commands	String predicate
User	String predicate
Machine	String predicate
Auditstore	Number predicate
Parameters of commands and applications	String predicate
Unix command name	String predicate
Windows applications	String predicate
Comment	String predicate
Session Id	String predicate
Client name	String predicate
User display name	String predicate
Account	String predicate
Text	String predicate
Module	String predicate
Tag	String predicate