IBM Security QRadar

Hi Community,

i am struggling to understand the AVG Function in AQL.

I use the following query to see how many events arrived in the last 1440 Minutes and the average EPS.

SELECT
LOGSOURCENAME(logsourceid) AS "Log Source",
SUM(eventcount) AS "Number of Events in Interval",
SUM(eventcount) / 86400 AS "EPS in Interval",
AVG(eventcount) as "Average"
FROM
events
GROUP BY
"Log Source"
ORDER BY "EPS in Interval"
DESC LAST 1440 MINUTES


I get as Average 1.0. What does that mean?

Besides..in the AQL-documentation  there is

To view the number of average events from a source IP:
select avg(eventCount) from events group by sourceIP

I only see 1.0

Have I slipped up somewhere?


I use AVG for other calculations such as here below and it works fine:

select SUM("Value") AS "Summe", AVG("Value") AS "Mittelwert", "Metric ID", "Hostname"

from events

where LOGSOURCENAME(logsourceid) ILIKE '%%health%%'

group by "Metric ID", "Hostname"

last 2 minutes

Thank you in advance

Regards,

Bruno

------------------------------
Bruno Oliveira
------------------------------

Hi Bruno,

Firstly, your query works fine for me on a real environment. You have not slipped up from what I can see.

Secondly, 1.0 is what you should expect. You may find "health metrics" and "system notifications" funnily enough are one per second. Use experience centre to create other traffic and it will be more obvious.

Like you, I've used health metrics from AQL before and this is a good AQL query for a 7.3.2 system:

SELECT Hostname, \"Metric_ID\", AVG(Value) AS Avg_Value, Element FROM events WHERE LOGSOURCENAME(logsourceid) ILIKE \'%%health%%\' GROUP BY Hostname, \"Metric_ID\", Element ORDER BY Hostname last 1 minutes

I have also written a dashboard to calculate average payload latency by polling a piece of AQL and it's on github here:
https://github.com/darrenhatcher/quartz

Regards,

------------------------------
Darren H.
------------------------------

Original Message:
Sent: Wed June 10, 2020 05:44 AM
From: Bruno Oliveira
Subject: AQL - Average Function

Hi Community,

i am struggling to understand the AVG Function in AQL.

I use the following query to see how many events arrived in the last 1440 Minutes and the average EPS.

SELECT
LOGSOURCENAME(logsourceid) AS "Log Source",
SUM(eventcount) AS "Number of Events in Interval",
SUM(eventcount) / 86400 AS "EPS in Interval",
AVG(eventcount) as "Average"
FROM
events
GROUP BY
"Log Source"
ORDER BY "EPS in Interval"
DESC LAST 1440 MINUTES

I get as Average 1.0. What does that mean?

Besides..in the AQL-documentation  there is

To view the number of average events from a source IP:
select avg(eventCount) from events group by sourceIP

I only see 1.0

Have I slipped up somewhere?


I use AVG for other calculations such as here below and it works fine:

select SUM("Value") AS "Summe", AVG("Value") AS "Mittelwert", "Metric ID", "Hostname"

from events

where LOGSOURCENAME(logsourceid) ILIKE '%%health%%'

group by "Metric ID", "Hostname"

last 2 minutes

Thank you in advance

Regards,

Bruno

------------------------------
Bruno Oliveira
------------------------------

Hey Darren,

thank you for your reply. Regarding the first query:

SELECT
LOGSOURCENAME(logsourceid) AS "Log Source",
SUM(eventcount) AS "Number of Events in Interval",
SUM(eventcount) / 86400 AS "EPS in Interval",
AVG(eventcount) as "Average"
FROM
events
GROUP BY
"Log Source"
ORDER BY "EPS in Interval"
DESC LAST 1440 MINUTES

How am I supposed to understand the 1.0 value there?
Does this mean that AQL gets the event count and divide by the amount of events. Therefore, it will always show 1.0?

Which means that the average function is not useful for calculating the average of the events?
If coalescing is active, then I might get different values, right?

Thank you,

Regards,
Bruno






------------------------------
Bruno Oliveira
------------------------------

Original Message:
Sent: Wed June 10, 2020 06:30 AM
From: Darren H.
Subject: AQL - Average Function

Hi Bruno,

Firstly, your query works fine for me on a real environment. You have not slipped up from what I can see.

Secondly, 1.0 is what you should expect. You may find "health metrics" and "system notifications" funnily enough are one per second. Use experience centre to create other traffic and it will be more obvious.

Like you, I've used health metrics from AQL before and this is a good AQL query for a 7.3.2 system:

SELECT Hostname, \"Metric_ID\", AVG(Value) AS Avg_Value, Element FROM events WHERE LOGSOURCENAME(logsourceid) ILIKE \'%%health%%\' GROUP BY Hostname, \"Metric_ID\", Element ORDER BY Hostname last 1 minutes

I have also written a dashboard to calculate average payload latency by polling a piece of AQL and it's on github here:
https://github.com/darrenhatcher/quartz

Regards,

------------------------------
Darren H.

Original Message:
Sent: Wed June 10, 2020 05:44 AM
From: Bruno Oliveira
Subject: AQL - Average Function

Hi Community,

i am struggling to understand the AVG Function in AQL.

I use the following query to see how many events arrived in the last 1440 Minutes and the average EPS.

SELECT
LOGSOURCENAME(logsourceid) AS "Log Source",
SUM(eventcount) AS "Number of Events in Interval",
SUM(eventcount) / 86400 AS "EPS in Interval",
AVG(eventcount) as "Average"
FROM
events
GROUP BY
"Log Source"
ORDER BY "EPS in Interval"
DESC LAST 1440 MINUTES

I get as Average 1.0. What does that mean?

Besides..in the AQL-documentation  there is

To view the number of average events from a source IP:
select avg(eventCount) from events group by sourceIP

I only see 1.0

Have I slipped up somewhere?


I use AVG for other calculations such as here below and it works fine:

select SUM("Value") AS "Summe", AVG("Value") AS "Mittelwert", "Metric ID", "Hostname"

from events

where LOGSOURCENAME(logsourceid) ILIKE '%%health%%'

group by "Metric ID", "Hostname"

last 2 minutes

Thank you in advance

Regards,

Bruno

------------------------------
Bruno Oliveira
------------------------------

Hi Bruno,

Suggest unwinding the full AQL to understand what AVG is doing.

The AQL reference says for AVG():

Returns the average value of the rows in the aggregate.

So create a piece of AQL on the data set before [eventcount] to get a list of the aggregate then review the results. In this case eventcount is the count of events in the periods measured by the row returned (not the overall period which it knows nothing about). 

Try running this for the Health log source:

select LOGSOURCENAME(logsourceid) as 'Log Sources', eventcount from events where logsourcename(logsourceid) like '%%Health%%' group by logsourceid last 24 hours

I always get 1.0 for the eventcount for that source (the aggregate of the rows returned) for that point in time. That gives an average of 1.0 for 1 row is 1.0.

To see the others, place the hostname of the QRadar host in the string '%%<your host>%%' for all the internal log sources:

• Custom Rule Engine-8
• Asset Profiler-2
• SIM Generic Log DSM-7
• SIM Audit-2
• System Notification-2
• Health Metrics-2

Strongly suggest having a trial run of the queries with other log sources to verify what you are seeing.

From what I have found, AVG(eventcount) is not the average of all the events per second, its the average of the events counted at points in time. To do an average EPS, currently we have to add them all up and then do the division as per your other query that appears as expected.

Welcome corrections/additions.

Regards,

------------------------------
Darren H.
------------------------------

Original Message:
Sent: Wed June 10, 2020 07:00 AM
From: Bruno Oliveira
Subject: AQL - Average Function

Hey Darren,

thank you for your reply. Regarding the first query:

SELECT
LOGSOURCENAME(logsourceid) AS "Log Source",
SUM(eventcount) AS "Number of Events in Interval",
SUM(eventcount) / 86400 AS "EPS in Interval",
AVG(eventcount) as "Average"
FROM
events
GROUP BY
"Log Source"
ORDER BY "EPS in Interval"
DESC LAST 1440 MINUTES

How am I supposed to understand the 1.0 value there?
Does this mean that AQL gets the event count and divide by the amount of events. Therefore, it will always show 1.0?

Which means that the average function is not useful for calculating the average of the events?
If coalescing is active, then I might get different values, right?

Thank you,

Regards,
Bruno






------------------------------
Bruno Oliveira

Original Message:
Sent: Wed June 10, 2020 06:30 AM
From: Darren H.
Subject: AQL - Average Function

Hi Bruno,

Firstly, your query works fine for me on a real environment. You have not slipped up from what I can see.

Secondly, 1.0 is what you should expect. You may find "health metrics" and "system notifications" funnily enough are one per second. Use experience centre to create other traffic and it will be more obvious.

Like you, I've used health metrics from AQL before and this is a good AQL query for a 7.3.2 system:

SELECT Hostname, \"Metric_ID\", AVG(Value) AS Avg_Value, Element FROM events WHERE LOGSOURCENAME(logsourceid) ILIKE \'%%health%%\' GROUP BY Hostname, \"Metric_ID\", Element ORDER BY Hostname last 1 minutes

I have also written a dashboard to calculate average payload latency by polling a piece of AQL and it's on github here:
https://github.com/darrenhatcher/quartz

Regards,

------------------------------
Darren H.

Original Message:
Sent: Wed June 10, 2020 05:44 AM
From: Bruno Oliveira
Subject: AQL - Average Function

Hi Community,

i am struggling to understand the AVG Function in AQL.

I use the following query to see how many events arrived in the last 1440 Minutes and the average EPS.

SELECT
LOGSOURCENAME(logsourceid) AS "Log Source",
SUM(eventcount) AS "Number of Events in Interval",
SUM(eventcount) / 86400 AS "EPS in Interval",
AVG(eventcount) as "Average"
FROM
events
GROUP BY
"Log Source"
ORDER BY "EPS in Interval"
DESC LAST 1440 MINUTES

I get as Average 1.0. What does that mean?

Besides..in the AQL-documentation  there is

To view the number of average events from a source IP:
select avg(eventCount) from events group by sourceIP

I only see 1.0

Have I slipped up somewhere?


I use AVG for other calculations such as here below and it works fine:

select SUM("Value") AS "Summe", AVG("Value") AS "Mittelwert", "Metric ID", "Hostname"

from events

where LOGSOURCENAME(logsourceid) ILIKE '%%health%%'

group by "Metric ID", "Hostname"

last 2 minutes

Thank you in advance

Regards,

Bruno

------------------------------
Bruno Oliveira
------------------------------

Hey Darren,

thank you!

Returns the average value of the rows in the aggregate.

It works exactly as documented.

If I don't have coalescing active and do AVG(eventcount) I only get 1.0. However, if coalescing is active we have different event counts for a less number of rows, which leads to a different AVG(eventcount).

Without coalescing

Event  -   Event Count

Event A - 1
Event B - 1
Event A - 1
Event C - 1
Event B - 1

AVG(eventcount) = 5/5 = 1.0


With coalescing

Event  -   Event Count

Event A - 2
Event B - 2
Event C - 1

AVG(eventcount) = 5/3 = 1.666

Regards,
Bruno

------------------------------
Bruno Oliveira
------------------------------

Original Message:
Sent: Wed June 10, 2020 10:47 AM
From: Darren H.
Subject: AQL - Average Function

Hi Bruno,

Suggest unwinding the full AQL to understand what AVG is doing.

The AQL reference says for AVG():

Returns the average value of the rows in the aggregate.

So create a piece of AQL on the data set before [eventcount] to get a list of the aggregate then review the results. In this case eventcount is the count of events in the periods measured by the row returned (not the overall period which it knows nothing about). 

Try running this for the Health log source:

select LOGSOURCENAME(logsourceid) as 'Log Sources', eventcount from events where logsourcename(logsourceid) like '%%Health%%' group by logsourceid last 24 hours

I always get 1.0 for the eventcount for that source (the aggregate of the rows returned) for that point in time. That gives an average of 1.0 for 1 row is 1.0.

To see the others, place the hostname of the QRadar host in the string '%%<your host>%%' for all the internal log sources:

• Custom Rule Engine-8
• Asset Profiler-2
• SIM Generic Log DSM-7
• SIM Audit-2
• System Notification-2
• Health Metrics-2

Strongly suggest having a trial run of the queries with other log sources to verify what you are seeing.

From what I have found, AVG(eventcount) is not the average of all the events per second, its the average of the events counted at points in time. To do an average EPS, currently we have to add them all up and then do the division as per your other query that appears as expected.

Welcome corrections/additions.

Regards,

------------------------------
Darren H.

Original Message:
Sent: Wed June 10, 2020 07:00 AM
From: Bruno Oliveira
Subject: AQL - Average Function

Hey Darren,

thank you for your reply. Regarding the first query:

SELECT
LOGSOURCENAME(logsourceid) AS "Log Source",
SUM(eventcount) AS "Number of Events in Interval",
SUM(eventcount) / 86400 AS "EPS in Interval",
AVG(eventcount) as "Average"
FROM
events
GROUP BY
"Log Source"
ORDER BY "EPS in Interval"
DESC LAST 1440 MINUTES

How am I supposed to understand the 1.0 value there?
Does this mean that AQL gets the event count and divide by the amount of events. Therefore, it will always show 1.0?

Which means that the average function is not useful for calculating the average of the events?
If coalescing is active, then I might get different values, right?

Thank you,

Regards,
Bruno






------------------------------
Bruno Oliveira

Original Message:
Sent: Wed June 10, 2020 06:30 AM
From: Darren H.
Subject: AQL - Average Function

Hi Bruno,

Firstly, your query works fine for me on a real environment. You have not slipped up from what I can see.

Secondly, 1.0 is what you should expect. You may find "health metrics" and "system notifications" funnily enough are one per second. Use experience centre to create other traffic and it will be more obvious.

Like you, I've used health metrics from AQL before and this is a good AQL query for a 7.3.2 system:

SELECT Hostname, \"Metric_ID\", AVG(Value) AS Avg_Value, Element FROM events WHERE LOGSOURCENAME(logsourceid) ILIKE \'%%health%%\' GROUP BY Hostname, \"Metric_ID\", Element ORDER BY Hostname last 1 minutes

I have also written a dashboard to calculate average payload latency by polling a piece of AQL and it's on github here:
https://github.com/darrenhatcher/quartz

Regards,

------------------------------
Darren H.

Original Message:
Sent: Wed June 10, 2020 05:44 AM
From: Bruno Oliveira
Subject: AQL - Average Function

Hi Community,

i am struggling to understand the AVG Function in AQL.

I use the following query to see how many events arrived in the last 1440 Minutes and the average EPS.

SELECT
LOGSOURCENAME(logsourceid) AS "Log Source",
SUM(eventcount) AS "Number of Events in Interval",
SUM(eventcount) / 86400 AS "EPS in Interval",
AVG(eventcount) as "Average"
FROM
events
GROUP BY
"Log Source"
ORDER BY "EPS in Interval"
DESC LAST 1440 MINUTES

I get as Average 1.0. What does that mean?

Besides..in the AQL-documentation  there is

To view the number of average events from a source IP:
select avg(eventCount) from events group by sourceIP

I only see 1.0

Have I slipped up somewhere?


I use AVG for other calculations such as here below and it works fine:

select SUM("Value") AS "Summe", AVG("Value") AS "Mittelwert", "Metric ID", "Hostname"

from events

where LOGSOURCENAME(logsourceid) ILIKE '%%health%%'

group by "Metric ID", "Hostname"

last 2 minutes

Thank you in advance

Regards,

Bruno

------------------------------
Bruno Oliveira
------------------------------

Original Message:
Sent: 6/10/2020 7:01:00 AM
From: Bruno Oliveira
Subject: RE: AQL - Average Function

Hey Darren,

thank you for your reply. Regarding the first query:

SELECT
LOGSOURCENAME(logsourceid) AS "Log Source",
SUM(eventcount) AS "Number of Events in Interval",
SUM(eventcount) / 86400 AS "EPS in Interval",
AVG(eventcount) as "Average"
FROM
events
GROUP BY
"Log Source"
ORDER BY "EPS in Interval"
DESC LAST 1440 MINUTES

How am I supposed to understand the 1.0 value there?
Does this mean that AQL gets the event count and divide by the amount of events. Therefore, it will always show 1.0?

Which means that the average function is not useful for calculating the average of the events?
If coalescing is active, then I might get different values, right?

Thank you,

Regards,
Bruno






------------------------------
Bruno Oliveira
------------------------------

Original Message:
Sent: Wed June 10, 2020 06:30 AM
From: Darren H.
Subject: AQL - Average Function

Hi Bruno,

Firstly, your query works fine for me on a real environment. You have not slipped up from what I can see.

Secondly, 1.0 is what you should expect. You may find "health metrics" and "system notifications" funnily enough are one per second. Use experience centre to create other traffic and it will be more obvious.

Like you, I've used health metrics from AQL before and this is a good AQL query for a 7.3.2 system:

SELECT Hostname, \"Metric_ID\", AVG(Value) AS Avg_Value, Element FROM events WHERE LOGSOURCENAME(logsourceid) ILIKE \'%%health%%\' GROUP BY Hostname, \"Metric_ID\", Element ORDER BY Hostname last 1 minutes

I have also written a dashboard to calculate average payload latency by polling a piece of AQL and it's on github here:
https://github.com/darrenhatcher/quartz

Regards,

------------------------------
Darren H.

Original Message:
Sent: Wed June 10, 2020 05:44 AM
From: Bruno Oliveira
Subject: AQL - Average Function

Hi Community,

i am struggling to understand the AVG Function in AQL.

I use the following query to see how many events arrived in the last 1440 Minutes and the average EPS.

SELECT
LOGSOURCENAME(logsourceid) AS "Log Source",
SUM(eventcount) AS "Number of Events in Interval",
SUM(eventcount) / 86400 AS "EPS in Interval",
AVG(eventcount) as "Average"
FROM
events
GROUP BY
"Log Source"
ORDER BY "EPS in Interval"
DESC LAST 1440 MINUTES

I get as Average 1.0. What does that mean?

Besides..in the AQL-documentation  there is

To view the number of average events from a source IP:
select avg(eventCount) from events group by sourceIP

I only see 1.0

Have I slipped up somewhere?


I use AVG for other calculations such as here below and it works fine:

select SUM("Value") AS "Summe", AVG("Value") AS "Mittelwert", "Metric ID", "Hostname"

from events

where LOGSOURCENAME(logsourceid) ILIKE '%%health%%'

group by "Metric ID", "Hostname"

last 2 minutes

Thank you in advance

Regards,

Bruno

------------------------------
Bruno Oliveira
------------------------------

Hi Ricardo,

thank you for your reply. You are right. I wrote above an example to make clear for others.
At First I was not sure, whether I could calculate the Average numbers of Events per Second, per Minute, per Day, Week and so on using the AVG function or not. Now I know, that is not that easy and therefore I should stick to the first query which I wrote in first post, which uses SUM(Eventcount) divided by the time.

Regards,

Bruno

------------------------------
Bruno Oliveira
------------------------------

Original Message:
Sent: Wed June 10, 2020 07:12 AM
From: Ricardo Pesciotta
Subject: AQL - Average Function

Hi Bruno,

I don't have too much experience with direct (AQL) queries.

But using a generic SQL logic, I believe the result you are getting is correct.

The query will group results by Log Source, where there's I believe always 1 single log source name in the system. Thus, on the calculated result row (per log source), the average is going to be total count, divided by the count for that row, which is the same number, so 1.0.

Following this logic, if you grouped, say on Log Source Type instead of log source, then you could have several log sources grouped together (let's say 10), and the result would be the total event count for that log source type, divided by the number of log sources that were grouped (in this case 10).

Does that make sense?

In any case, just to be sure, what do you actually expect when using the average in this query?

Regards,

---

Ricardo Pesciotta
+41 79 938 55 61

Original Message:
Sent: 6/10/2020 7:01:00 AM
From: Bruno Oliveira
Subject: RE: AQL - Average Function

Hey Darren,

thank you for your reply. Regarding the first query:

SELECT
LOGSOURCENAME(logsourceid) AS "Log Source",
SUM(eventcount) AS "Number of Events in Interval",
SUM(eventcount) / 86400 AS "EPS in Interval",
AVG(eventcount) as "Average"
FROM
events
GROUP BY
"Log Source"
ORDER BY "EPS in Interval"
DESC LAST 1440 MINUTES

How am I supposed to understand the 1.0 value there?
Does this mean that AQL gets the event count and divide by the amount of events. Therefore, it will always show 1.0?

Which means that the average function is not useful for calculating the average of the events?
If coalescing is active, then I might get different values, right?

Thank you,

Regards,
Bruno






------------------------------
Bruno Oliveira

Original Message:
Sent: Wed June 10, 2020 06:30 AM
From: Darren H.
Subject: AQL - Average Function

Hi Bruno,

Firstly, your query works fine for me on a real environment. You have not slipped up from what I can see.

Secondly, 1.0 is what you should expect. You may find "health metrics" and "system notifications" funnily enough are one per second. Use experience centre to create other traffic and it will be more obvious.

Like you, I've used health metrics from AQL before and this is a good AQL query for a 7.3.2 system:

SELECT Hostname, \"Metric_ID\", AVG(Value) AS Avg_Value, Element FROM events WHERE LOGSOURCENAME(logsourceid) ILIKE \'%%health%%\' GROUP BY Hostname, \"Metric_ID\", Element ORDER BY Hostname last 1 minutes

I have also written a dashboard to calculate average payload latency by polling a piece of AQL and it's on github here:
https://github.com/darrenhatcher/quartz

Regards,

------------------------------
Darren H.

Original Message:
Sent: Wed June 10, 2020 05:44 AM
From: Bruno Oliveira
Subject: AQL - Average Function

Hi Community,

i am struggling to understand the AVG Function in AQL.

I use the following query to see how many events arrived in the last 1440 Minutes and the average EPS.

SELECT
LOGSOURCENAME(logsourceid) AS "Log Source",
SUM(eventcount) AS "Number of Events in Interval",
SUM(eventcount) / 86400 AS "EPS in Interval",
AVG(eventcount) as "Average"
FROM
events
GROUP BY
"Log Source"
ORDER BY "EPS in Interval"
DESC LAST 1440 MINUTES

I get as Average 1.0. What does that mean?

Besides..in the AQL-documentation  there is

To view the number of average events from a source IP:
select avg(eventCount) from events group by sourceIP

I only see 1.0

Have I slipped up somewhere?


I use AVG for other calculations such as here below and it works fine:

select SUM("Value") AS "Summe", AVG("Value") AS "Mittelwert", "Metric ID", "Hostname"

from events

where LOGSOURCENAME(logsourceid) ILIKE '%%health%%'

group by "Metric ID", "Hostname"

last 2 minutes

Thank you in advance

Regards,

Bruno

------------------------------
Bruno Oliveira
------------------------------