Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

 
ON THIS PAGE
 

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

  • 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

    COUNT Function

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

    GROUP BY Clause

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

    HAVING Clause

    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

Related Documentation