Hi,

Quite a long time I'm trying to find a good way to determine the best way to create a query flexible an accurate enough. Here is the basic search I think everyone familiar with 

SELECT LOGSOURCENAME(logsourceid) AS "Log Source", SUM(eventcount) AS "Number of Events in Interval",
SUM(eventcount) / 14400
AS "EPS in Interval" FROM events GROUP BY "Log Source" ORDER BY "EPS in Interval"
DESC LAST 4 HOURS

There some issues with this. First of all, if I would like to query a longer period (e.g. 7 days), it will include off-hours (nights, weekends) as well, but I more interested of the averages of the working hours. Is there any easy way to extend AQL query to achieve this?

Also sometimes I need only EPS for a logsource group, tenant or even a single logsource or logsource-type. Can someone put some example here for this?

Last, but not least, I need to present peek EPS as well. So, I'm looking for a query showing me the peek EPS for a logsource, logsource group or tenant for a longer period

Thank you

------------------------------
Laszlo Pal
------------------------------

​This is not easy if you introduce gaps in your time series. Do for a single time series, export, then cut out what you're looking for.

Below is a bit of AQL which you can use for EPS (not peak which is different).

Change the number of days, but you will need to adjust that in the seconds-per-day divisor. This lists EPS by log source over the period of days from highest to lowest.

SELECT LOGSOURCENAME(logsourceid) AS 'Log Sources', SUM(eventcount)/(1*(24*3600))
AS EPS
FROM events
GROUP BY logsourceid
ORDER BY EPS DESC LAST 1 DAYS

Add a "where" clause for the log source type or domain of interest.

Good luck!

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

​... Looking at this a different way and using the above as a start, here's a specific domain for a week across all the log sources:

SELECT LOGSOURCENAME(logsourceid) AS 'Log Source', SUM(eventcount)/(7*(24*3600)) AS EPS, DOMAINNAME(domaindid)
FROM events
where
DOMAINNAME(domaind) like 'MyDomainName'
GROUP BY logsourceid
ORDER BY EPS DESC LAST 7 DAYS

​... I've used "like" here to but you should get the idea.


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

Original Message:
Sent: Mon January 27, 2020 07:54 AM
From: Darren H.
Subject: EPS query finetune

​This is not easy if you introduce gaps in your time series. Do for a single time series, export, then cut out what you're looking for.

Below is a bit of AQL which you can use for EPS (not peak which is different).

Change the number of days, but you will need to adjust that in the seconds-per-day divisor. This lists EPS by log source over the period of days from highest to lowest.

SELECT LOGSOURCENAME(logsourceid) AS 'Log Sources', SUM(eventcount)/(1*(24*3600))
AS EPS
FROM events
GROUP BY logsourceid
ORDER BY EPS DESC LAST 1 DAYS

Add a "which" clause for the log source type or domain of interest.

Good luck!

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

Hi All,

Is it possible to get the EPS based on Event collector. We have 4 event collector distributed across and we need to calculate a EPS per EC. Can you pleas provide some insight on this

------------------------------
Punith Rajanna
------------------------------

Original Message:
Sent: Mon January 27, 2020 08:46 AM
From: Darren H.
Subject: EPS query finetune

​... Looking at this a different way and using the above as a start, here's a specific domain for a week across all the log sources:

SELECT LOGSOURCENAME(logsourceid) AS 'Log Source', SUM(eventcount)/(7*(24*3600)) AS EPS, DOMAINNAME(domaindid)
FROM events
where
DOMAINNAME(domaind) like 'MyDomainName'
GROUP BY logsourceid
ORDER BY EPS DESC LAST 7 DAYS

​... I've used "like" here to but you should get the idea.


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


Original Message:
Sent: Mon January 27, 2020 07:54 AM
From: Darren H.
Subject: EPS query finetune

​This is not easy if you introduce gaps in your time series. Do for a single time series, export, then cut out what you're looking for.

Below is a bit of AQL which you can use for EPS (not peak which is different).

Change the number of days, but you will need to adjust that in the seconds-per-day divisor. This lists EPS by log source over the period of days from highest to lowest.

SELECT LOGSOURCENAME(logsourceid) AS 'Log Sources', SUM(eventcount)/(1*(24*3600))
AS EPS
FROM events
GROUP BY logsourceid
ORDER BY EPS DESC LAST 1 DAYS

Add a "which" clause for the log source type or domain of interest.

Good luck!

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

What have you tried so far?

The AQL 7.3.2 reference has some examples from page 63 onwards you can learn from and tweak:

https://www.ibm.com/support/knowledgecenter/SS42VS_7.3.2/com.ibm.qradar.doc/b_qradar_aql.pdf

If you've not read or at least tried some of the examples out from the AQL reference, it is genuinely worth doing some experiments to understand what you can search for.

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

Original Message:
Sent: Tue January 28, 2020 06:19 AM
From: Punith Rajanna
Subject: EPS query finetune

Hi All,

Is it possible to get the EPS based on Event collector. We have 4 event collector distributed across and we need to calculate a EPS per EC. Can you pleas provide some insight on this

------------------------------
Punith Rajanna
------------------------------


Original Message:
Sent: Mon January 27, 2020 08:46 AM
From: Darren H.
Subject: EPS query finetune

​... Looking at this a different way and using the above as a start, here's a specific domain for a week across all the log sources:

SELECT LOGSOURCENAME(logsourceid) AS 'Log Source', SUM(eventcount)/(7*(24*3600)) AS EPS, DOMAINNAME(domaindid)
FROM events
where
DOMAINNAME(domaind) like 'MyDomainName'
GROUP BY logsourceid
ORDER BY EPS DESC LAST 7 DAYS

​... I've used "like" here to but you should get the idea.


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


Original Message:
Sent: Mon January 27, 2020 07:54 AM
From: Darren H.
Subject: EPS query finetune

​This is not easy if you introduce gaps in your time series. Do for a single time series, export, then cut out what you're looking for.

Below is a bit of AQL which you can use for EPS (not peak which is different).

Change the number of days, but you will need to adjust that in the seconds-per-day divisor. This lists EPS by log source over the period of days from highest to lowest.

SELECT LOGSOURCENAME(logsourceid) AS 'Log Sources', SUM(eventcount)/(1*(24*3600))
AS EPS
FROM events
GROUP BY logsourceid
ORDER BY EPS DESC LAST 1 DAYS

Add a "which" clause for the log source type or domain of interest.

Good luck!

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

I offer, without warranty as to usefulness and correctness, the following. Use at your own risk!  :)

I have had good luck with the following columns and filters.

I am not 100% on the regexes gathering the exact right data. IBM has been vague about how the information is reported, for instance, a stat event has the following payload (in part)

[-/- -]Events per second: 1s:54,105 (peak 588,962) (compression: 49%) 5s:48,115 (peak 275,454) (compression: 58%) 10s:55,115 (peak 159,242) (compression: 52%) 30s:48,101 (peak 91,148) (compression: 52%) 60s:43,85 (peak 66,136) (compression: 49%)

This will be read as raw events last second 105
Coalesced events last second 54

same goes for 60s timing, my assumption is that the

as best I can tell, the compression ratio for more than 1 second is an average for the period, but that is a total guess.

This is how I THINK it works, hopefully somebody from IBM lurking here can chime in with corrections/amplifications. 



The best information I have about this is as follows:
Events per second:1s:54,104(peak588,962) can be interpreted this way:

54 coalesced events in the last second, 104 raw for the last second. Peak 


Search Criteria,

Time Range
Recent  Last 15 Minutes

Group By
Parent(Custom)

Columns.
Events per Second Coalesced-Peak 1 Sec (custom)
Events per second Raw- Peak 1 sec (custom)
Events per second Coalesced Average 1 min (custom)
Events per second Raw-Average 1 Min

Current Filters
Logs Source Type is System Notification
Payload Contains is Events per second
Payload Contains is StatFilter


Custom event properties are as follows (I think they are right):

Events per Second Coalesced Peak 1 Sec  Regex StatFilter.+1s\:(\d+)\,\d+\s
Events per Second Raw - Peak 1 sec     Regex: StatFilter.+1s\:\d+\,(\d+)\s
Events per second Coalesced Average 1 min Regex StatFilter.+60s\:(\d+)\,\d+\s
Events per Second Raw - Average 1 min Regex: StatFilter.+60s\:\d+\,(\d+)\s


Cheers,






------------------------------
_____________________
Daniel Sichel
------------------------------

Original Message:
Sent: Tue January 28, 2020 06:19 AM
From: Punith Rajanna
Subject: EPS query finetune

Hi All,

Is it possible to get the EPS based on Event collector. We have 4 event collector distributed across and we need to calculate a EPS per EC. Can you pleas provide some insight on this

------------------------------
Punith Rajanna
------------------------------


Original Message:
Sent: Mon January 27, 2020 08:46 AM
From: Darren H.
Subject: EPS query finetune

​... Looking at this a different way and using the above as a start, here's a specific domain for a week across all the log sources:

SELECT LOGSOURCENAME(logsourceid) AS 'Log Source', SUM(eventcount)/(7*(24*3600)) AS EPS, DOMAINNAME(domaindid)
FROM events
where
DOMAINNAME(domaind) like 'MyDomainName'
GROUP BY logsourceid
ORDER BY EPS DESC LAST 7 DAYS

​... I've used "like" here to but you should get the idea.


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


Original Message:
Sent: Mon January 27, 2020 07:54 AM
From: Darren H.
Subject: EPS query finetune

​This is not easy if you introduce gaps in your time series. Do for a single time series, export, then cut out what you're looking for.

Below is a bit of AQL which you can use for EPS (not peak which is different).

Change the number of days, but you will need to adjust that in the seconds-per-day divisor. This lists EPS by log source over the period of days from highest to lowest.

SELECT LOGSOURCENAME(logsourceid) AS 'Log Sources', SUM(eventcount)/(1*(24*3600))
AS EPS
FROM events
GROUP BY logsourceid
ORDER BY EPS DESC LAST 1 DAYS

Add a "which" clause for the log source type or domain of interest.

Good luck!

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

------Original Message------

I offer, without warranty as to usefulness and correctness, the following. Use at your own risk!  :)

I have had good luck with the following columns and filters.

I am not 100% on the regexes gathering the exact right data. IBM has been vague about how the information is reported, for instance, a stat event has the following payload (in part)

[-/- -]Events per second: 1s:54,105 (peak 588,962) (compression: 49%) 5s:48,115 (peak 275,454) (compression: 58%) 10s:55,115 (peak 159,242) (compression: 52%) 30s:48,101 (peak 91,148) (compression: 52%) 60s:43,85 (peak 66,136) (compression: 49%)

This will be read as raw events last second 105
Coalesced events last second 54

same goes for 60s timing, my assumption is that the

as best I can tell, the compression ratio for more than 1 second is an average for the period, but that is a total guess.

This is how I THINK it works, hopefully somebody from IBM lurking here can chime in with corrections/amplifications. 



The best information I have about this is as follows:
Events per second:1s:54,104(peak588,962) can be interpreted this way:

54 coalesced events in the last second, 104 raw for the last second. Peak 


Search Criteria,

Time Range
Recent  Last 15 Minutes

Group By
Parent(Custom)

Columns.
Events per Second Coalesced-Peak 1 Sec (custom)
Events per second Raw- Peak 1 sec (custom)
Events per second Coalesced Average 1 min (custom)
Events per second Raw-Average 1 Min

Current Filters
Logs Source Type is System Notification
Payload Contains is Events per second
Payload Contains is StatFilter


Custom event properties are as follows (I think they are right):

Events per Second Coalesced Peak 1 Sec  Regex StatFilter.+1s\:(\d+)\,\d+\s
Events per Second Raw - Peak 1 sec     Regex: StatFilter.+1s\:\d+\,(\d+)\s
Events per second Coalesced Average 1 min Regex StatFilter.+60s\:(\d+)\,\d+\s
Events per Second Raw - Average 1 min Regex: StatFilter.+60s\:\d+\,(\d+)\s


Cheers,






------------------------------
_____________________
Daniel Sichel
------------------------------