devicetime is extracted from the payload, so if you have dates in 2023, maybe a log source has an incorrect date, or the DSM parses it incorrectly. Use starttime for the time the event was received by QRadar. I think starttime is what START and STOP are working with.
Since you want a monthly report, maybe use this:
DATEFORMAT( devicetime, 'yyyy-MM')
If you have event coalescing, you want SUM(eventcount) instead of just COUNT(). It's good practice to always use SUM(eventcount) as it will be accurate whether there's event coalescing or not. That might explain the lower EPS than what you were expecting.
Note that a monthly EPS average will often be much lower than the typical EPS in business hours as the nights and weekends usually have lower EPS and reduce the average. Unless you have constant 24/7 EPS of course.
------------------------------
Raphaël Langella
SIEM Architect
IMS Networks
------------------------------
Original Message:
Sent: Wed February 17, 2021 11:31 PM
From: Vidura Ehalapitiya
Subject: EPS for 6 months based on the month
Hi guys,
I need to create a report based on EPS per month for 6 months. And I tweaked one query in IBM AQL support.
But it seems like I did something wrong since I'm having dates to 2023 and after exporting it to excel the calculation comes around 900 eps(consumes around 2.4k).
My approach was getting total count per month then dividing it by seconds.
SELECT DATEFORMAT( devicetime, 'dd-MM-yyyy') AS 'Date of log source', QIDDESCRIPTION(qid) AS 'Description of event', COUNT() FROM events WHERE devicetime >( now() -(28*7*24*3600*1000) ) GROUP BY "Date of log source", qid START '2020-08-01 12:00:00' STOP '2021-02-1 12:00:00'
Is there something I went wrong or is there a different approach? Ultimately I want a bar chart for each month.
Thank you kindly
------------------------------
Vidura Ehalapitiya
------------------------------