Conditional Logic in AQL Queries
Use conditional logic in AQL queries by using IF and CASE expressions.
Use conditional logic in your AQL queries to provide alternative options, depending on whether the clause condition evaluates to true or false.
CASE expressions return a Boolean true or false result. When an expression is returned as true, the value of that CASE expression is returned and processing is stopped. If the Boolean result is false, then the value of the ELSE clause is returned.
In the following example, when the user name is root, the value of the CASE expression that is returned is Admin root. When the user name is admin, the value of the CASE expression that is returned is Admin user. If the CASE expressions return a Boolean false, the value of the ELSE clause is returned.
SELECT CASE username WHEN ’root’ THEN ’Admin root’ WHEN ’admin’ THEN ’Admin user’ ELSE ’other’ END FROM events
When the WHEN statement is true, the THEN statement is processed, otherwise processing finishes.
IF, THEN, ELSE Statements
Statements between THEN and ELSE are processed when the IF statement is true.
In this example, when the IF condition is true,
‘ADMIN’ is returned when the user name
'root', otherwise the user name is returned
from the events log.
SELECT sourceip, IF username = ’root’ THEN ’ADMIN’ ELSE username AS user FROM events
In the following example, if the log has no user name, then get it from the asset model. Otherwise, the user name is returned from the events log.
SELECT sourceip, IF username IS NULL THEN ASSETUSER(sourceip) ELSE username AS username FROM events GROUP BY username LAST 2 DAYS