Let us know what you think.

Do you have time for a two-minute survey?

# AQL Logical and Comparison Operators

Operators are used in AQL statements to determine any equality or difference between values. By using operators in the WHERE clause of an AQL statement, the results are filtered by those results that match the conditions in the WHERE clause.

The following table lists the supported logical and comparison operators.

Table 1: Logical and Comparison Operators

Operator

Description

Example

`* `

Multiplies two values and returns the result.

SELECT * FROM flows WHERE sourceBytes * 1024 < 1

`=`

The equal to operator compares two values and returns true if they are equal.

SELECT * FROM EVENTS WHERE sourceIP = destinationIP

`!=`

Compares two values and returns true if they are unequal.

SELECT * FROM events WHERE sourceIP != destinationip

`< AND <= `

Compares two values and returns true if the value on the left side is less than or equal to, the value on the right side.

SELECT * FROM flows WHERE sourceBytes < 64 AND destinationBytes <= 64

`> AND >=`

Compares two values and returns true if the value on the left side is greater than or equal to the value on the right side.

SELECT * FROM flows WHERE sourceBytes > 64 AND destinationBytes >= 64

`/ `

Divides two values and returns the result.

SELECT * FROM flows WHERE sourceBytes / 8 > 64

`+ `

Adds two values and returns the result.

SELECT * FROM flows WHERE sourceBytes + destinationBytes < 64

`-`

Subtracts one value from another and returns the result.

SELECT * FROM flows WHERE sourceBytes - destinationBytes > 0

`^ `

Takes a value and raises it to the specified power and returns the result.

SELECT * FROM flows WHERE sourceBytes ^ 2 < 256

`% `

Takes the modulo of a value and returns the result.

SELECT * FROM flows WHERE sourceBytes % 8 == 7

`AND`

Takes the left side and right side of a statement and returns true if both are true.

SELECT * FROM events WHERE (sourceIP = destinationIP) AND (sourcePort = destinationPort)

`BETWEEN (X,Y)`

Takes in a left side and two values and returns true if the left side is between the two values.

SELECT * FROM events WHERE magnitude BETWEEN 1 AND 5

`COLLATE`

Parameter to order by that allows a BCP47 language tag to collate.

SELECT * FROM EVENTS ORDER BY sourceIP DESC COLLATE 'de-CH'

`INTO`

Creates a named cursor that contains results that can be queried at a different time.

SELECT * FROM EVENTS INTO 'MyCursor' WHERE....

`NOT`

Takes in a statement and returns true if the statement evaluates as false.

SELECT * FROM EVENTS WHERE NOT (sourceIP = destinationIP)

`ILIKE`

Matches if the string passed is LIKE the passed value and is not case sensitive. Use `%` as a wildcard.

SELECT * FROM events WHERE userName ILIKE '%bob%'

`IMATCHES `

Matches if the string matches the provided regular expression and is not case sensitive.

SELECT * FROM events WHERE userName IMATCHES '^.bob.\$'

`LIMIT `

Limits the number of results to the provided number.

SELECT * FROM events LIMIT 100 START '2015-10-28 10:00' STOP '2015-10-28 11:00'

Note: Place the LIMIT clause in front of a `START` and `STOP` clause.

`LIKE`

Matches if the string passed is `LIKE` the passed value but is case sensitive. Use `%` as a wildcard.

SELECT * FROM events WHERE userName LIKE '%bob%'

`MATCHES `

Matches if the string matches the provided regular expression.

SELECT * FROM events WHERE userName MATCHES '^.bob.\$'

`NOT NULL`

Takes in a value and returns true if the value is not null.

SELECT * FROM events WHERE userName IS NOT NULL

`OR`

Takes the left side of a statement and the right side of a statement and returns true if either side is true.

SELECT * FROM events WHERE (sourceIP = destinationIP) OR (sourcePort = destinationPort)

`TEXT SEARCH `

Full-text search for the passed value.

TEXT SEARCH is valid with `AND` operators. You can't use TEXT SEARCH with `OR` or other operators; otherwise, you get a syntax error.

Place TEXT SEARCH in the first position of the WHERE clause.

You can also do full-text searches by using the Quick filter in the JSA user interface. For information about Quick filter functions, see the Juniper Secure Analytics Users Guide.

SELECT * FROM events WHERE TEXT SEARCH 'firewall' AND sourceip='192.168.1.1' SELECT sourceip,url FROM events WHERE TEXT SEARCH 'download.cdn.mozilla.net' AND sourceip='192.168.1.1' START '2015-01-30 16:10:12' STOP '2015-02-22 17:10:22'

The following table lists the supported logical and comparison operators.

## Examples Of Logical and Comparative Operators

• To find events that are not parsed, type the following query:

SELECT * FROM events WHERE payload = 'false'

• To find events that return an offense and have a specific source IP address, type the following query:

SELECT * FROM events WHERE sourceIP = '192.0.2.0' AND hasOffense = 'true'

• To find events that include the text "firewall", type the following query:

SELECT QIDNAME(qid) AS EventName, * FROM events WHERE TEXT SEARCH 'firewall'