WHERE clause

Filter your AQL queries by using WHERE clauses. The WHERE clause describes the filter criteria that you apply to the query and filters the resulting view to accept only those events or flows that meet the specified condition.

You can apply the WHERE clause to add a condition to search criteria in AQL queries, which filters the search results.

A search condition is a combination of logical and comparison operators that together make a test. Only those input rows that pass the test are included in the result.

You can apply the following filters when you use WHERE clause in a query:
  • Equal sign (=)
  • Not equal to symbol (<>)
  • Less than symbol (<)
  • Greater than symbol (>)
  • Less that or equal to symbol (<=)
  • Greater than or equal to symbol (>=)
  • BETWEEN between two values, for example (64 AND 512)
  • LIKE case sensitive match
  • ILIKE case insensitive match
  • IS NULL is empty
  • AND / OR combine conditions or either condition
  • TEXT SEARCH text string match

Examples of WHERE clauses

The following query example shows events that have a severity level of greater than nine and are from a specific category.

SELECT sourceIP, category, credibility 
FROM events 
WHERE
severity > 9 
AND 
category = 5013

Change the order of evaluation by using parentheses. The search conditions that are enclosed in parentheses are evaluated first.

SELECT sourceIP, category, credibility 
FROM events 
WHERE
(severity > 9 AND category = 5013) 
OR 
(severity < 5 AND credibility > 8)

Return events from the events database where the text 'typot' is found.

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

The following query outputs events from the events database where health is included in the log source name.

SELECT logsourceid, LOGSOURCEGROUPNAME(logsourceid), LOGSOURCENAME(logsourceid) 
FROM events 
WHERE LOGSOURCENAME(logsourceid) 
ILIKE '%%health%%'

The following query outputs events where the device type ID is equal to 11 (Linux Server DSM), and where the QID is equal to 44250002, which is the identifier for Cron Status.

SELECT * FROM events 
WHERE deviceType= '11' 
AND qid= '44250002'