AQL data aggregation functions

Ariel Query Language (AQL) aggregate functions help you to aggregate and manipulate the data that you extract from the Ariel database.

Data aggregation functions

Use the following AQL functions to aggregate data, and to do calculations on the aggregated data that you extract from the AQL databases:

AVG
COUNT
DISTINCTCOUNT
FIRST
GROUP BY
HAVING
LAST
MIN
MAX
STDEV
STDEVP
SUM
UNIQUECOUNT

`AVG`

Purpose: Returns the average value of the rows in the aggregate.

Example

SELECT sourceip, 
AVG(magnitude)
FROM events 
GROUP BY sourceip

`COUNT`

Purpose: Returns the count of the rows in the aggregate.

Example

SELECT sourceip, 
COUNT(*) 
FROM events 
GROUP BY sourceip

See more examples

`DISTINCTCOUNT`

Purpose: Returns the unique count of the value in the aggregate. Uses the HyperLogLog+ approximation algorithm to calculate the unique count. Operates with a constant memory requirement and supports unlimited data sets.

Example

SELECT username, 
DISTINCTCOUNTCOUNT(sourceip) 
AS CountSrcIP
FROM events 
GROUP BY username

`FIRST`

Purpose: Returns the first entry of the rows in the aggregate.

Example

SELECT sourceip, 
FIRST(magnitude)
FROM events 
GROUP BY sourceip

`GROUP BY`

Purpose

Creates an aggregate from one or more columns.

To return values other than the default first value, use functions such as COUNT, MAX, AVG.

Examples

SELECT sourceip, 
COUNT(*) 
FROM  events 
GROUP BY sourceip, destinationip

SELECT username, sourceip, 
COUNT(*) FROM events 
GROUP BY username 
LAST 5 minutes

The sourceip column is returned as FIRST_sourceip. Only one sourceip is returned per username, even if another sourceip exists.

SELECT username, 
COUNT(sourceip), 
COUNT(*) FROM events 
GROUP BY username 
LAST 5 minutes

The sourceip column is returned as COUNT_sourceip. The count for sourceip results is returned per username.

See more examples

`HAVING`

Purpose: Uses operators on the result of a grouped by column.

Example

SELECT sourceip, 
MAX(magnitude)
AS MAG 
FROM events 
GROUP BY  sourceip 
HAVING MAG > 5

See more examples

Saved searches that include the having clause and that are used for scheduled reports or time-series graphs are not supported.

`LAST`

Purpose: Returns the last entry of the rows in the aggregate.

Example

SELECT sourceip, 
LAST(magnitude)
FROM events 
GROUP BY sourceip

`MIN`

Purpose: Returns the minimum value of the rows in the aggregate.

Example

SELECT sourceip, 
MIN(magnitude)
FROM events 
GROUP BY sourceip

MAX

Purpose: Returns the maximum value of the rows in the aggregate.

Example

SELECT sourceip, 
MAX(magnitude)
FROM events 
GROUP BY sourceip

`STDEV`

Purpose: Returns the Sample Standard Deviation value of the rows in the aggregate.

Example

SELECT sourceip, 
STDEV(magnitude)
FROM events 
GROUP BY sourceip

`STDEVP`

Purpose: Returns the Population Standard Deviation value of the rows in the aggregate.

Example

SELECT sourceip, 
STDEVP(magnitude)
FROM events 
GROUP BY sourceip

`SUM`

Purpose: Returns the sum of the rows in the aggregate.

Example

SELECT sourceip, 
SUM(sourceBytes)
FROM flows 
GROUP BY sourceip

`UNIQUECOUNT`

Purpose: Returns the unique count of the value in the aggregate.

Example

SELECT username, 
UNIQUECOUNT(sourceip) 
AS CountSrcIP
FROM events
GROUP BY sourceip