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
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
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
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
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