IBM QRadar

Hi folks,

I've been asked a few times QRadar can easily report on when the count of something changes on a given day (or period) compared with the same interval over the last few weeks.  It's pretty easy to do in QRadar, if you know how.

Lets take for example, reporting on the number of offenses per day and generating a table for the last 7 days that shows the number of offenses per day vs. the average for that same day of the week over the last 4 weeks. 

Firstly, for this type of report, it is MUCH faster using a global view (aka aggregated data view) than querying and aggregating raw events every time. Why is this ? When you query raw events you are potentially scanning billions of events, all of which might have to be read from disk, and disks are only so fast ! An aggregated view is like a predefined summary view that QRadar is always evaluating when new data is ingested. When you query an aggregated view, the volume of data being pulled off the disk is a fraction compared to the raw events, and therefore is orders of magnitude faster. Click here to see how to create these.

So decide what it is you want to count (e.g. new offenses, logins, specific actions etc.) create the query and the global view. 

So once you have your global view, now it is time to query that global view to create your delta report. In this example. I'm going to create a delta on a number of offenses using the 'Offenses Over Time' global view. And in this report I'm going to show the last 7 days and how they compare with the same day average over the prior 4 weeks.

So firstly lets get the underlying data and the day of the week from the view. We can do this with the following query

select Time as dtime, DATEFORMAT(time*1000, 'EE') as 'Day',"SUM_Active Offense Count"/(24*60) as "Avg Active Offenses" from globalview('Offenses Over Time','DAILY')order by Time desc last 35 DAYS

Note we divide the "SUM_Active Offense Count" by the number of minutes in a day (24*60) to get the average open offenses throughout the day. Here it is running in a new Pulse item config

I've pulled the last 35 days worth of data, as I want to show the current week, and compare the average of the previous 4 weeks, so need 5 weeks worth of data. We also use the DateFormat function, to pull out the day of the week as we want to show the results for each day of the week. And finally the Time value because we want to do different math on the data depending if the data is in the last 7 days or the prior 4 weeks to that.

So now we have the data, we can query that (using a subquery) and do the math to get the delta. here is the query...

select day,
MAX(Dtime)*1000 as Dtime,
first("Avg Active Offenses") as Current_Count,
SUM(IF((now()-(Time*1000))>(24*7*3600*1000)) THEN LONG("Avg Active Offenses") ELSE 0 )/4 as Average_Count,
Current_Count - Average_Count as Change
from (select Time as DTime, DATEFORMAT(time*1000, 'EE') as 'Day',"SUM_Active Offense Count"/(24*60) as "Avg Active Offenses" from globalview('Offenses Over Time','DAILY') order by Time desc last 35 DAYS)
group by Day order by dtime

So lets take this apart on piece at a time. Firstly we have the sub query, the pulls data using the query shown above

select day,
MAX(Dtime)*1000 as Dtime,
first("Avg Active Offenses") as Current_Count,
SUM(IF((now()-(Time*1000))>(24*7*3600*1000)) THEN LONG("Avg Active Offenses") ELSE 0 )/4 as Average_Count,
Current_Count - Average_Count as Change
from (select Time as DTime, DATEFORMAT(time*1000, 'EE') as 'Day',"SUM_Active Offense Count"/(24*60) as "Avg Active Offenses" from globalview('Offenses Over Time','DAILY') order by Time desc last 35 DAYS)
group by Day order by dtime

Note here, we order the sub query by Time descending, so we get the current days week values first. 
Lets look at the outer query. Firstly we aggregate by Day, so we get one results for each day of the week, and we include that Day column as the first column in the result. We also pull out the time feild as DTime, so we can plot and sort the data by the time value.

select day,
MAX(Dtime)*1000 as Dtime,
first("Avg Active Offenses") as Current_Count,
SUM(IF((now()-(Time*1000))>(24*7*3600*1000)) THEN LONG("Avg Active Offenses") ELSE 0 )/4 as Average_Count,
Current_Count - Average_Count as Change
from (select Time as DTime, DATEFORMAT(time*1000, 'EE') as 'Day',"SUM_Active Offense Count"/(24*60) as "Avg Active Offenses" from globalview('Offenses Over Time','DAILY') order by Time desc last 35 DAYS)
group by Day order by dtime

Next we take the first value of our offense count for each day. Remember, we ordered descending by Time, so this will represent the value for the last 7 week days

select day,
MAX(Dtime)*1000 as Dtime,
first("Avg Active Offenses") as Current_Count,
SUM(IF((now()-(Time*1000))>(24*7*3600*1000)) THEN LONG("Avg Active Offenses") ELSE 0 )/4 as Average_Count,
Current_Count - Average_Count as Change
from (select Time as DTime, DATEFORMAT(time*1000, 'EE') as 'Day',"SUM_Active Offense Count"/(24*60) as "Avg Active Offenses" from globalview('Offenses Over Time','DAILY') order by Time desc last 35 DAYS)
group by Day order by dtime

The next piece of the query is very important and where the magic is ! Some important things here. Firstly 'Time' is the number of seconds since Epoch, and the now() function returns the number of milliseconds since Epoch. What we want to do is get the average of the prior 4 weeks. We do this with this part of the query

SUM(IF((now()-(Time*1000))>(24*7*3600*1000)) THEN LONG("SUM_Active Offense Count") ELSE 0 )/4

The IF statement basically assesses if the data is more than a week old then it sums up the count, and if it is in the last 7 days it adds 0 (i.e. ignores the value).Then we divide by 4 to get the average

Finally we subtract the two values to get the delta

select day,
MAX(Dtime)*1000 as Dtime,
first("Avg Active Offenses") as Current_Count,
SUM(IF((now()-(Time*1000))>(24*7*3600*1000)) THEN LONG("Avg Active Offenses") ELSE 0 )/4 as Average_Count,
Current_Count - Average_Count as Change
from (select Time as DTime, DATEFORMAT(time*1000, 'EE') as 'Day',"SUM_Active Offense Count"/(24*60) as "Avg Active Offenses" from globalview('Offenses Over Time','DAILY') order by Time desc last 35 DAYS)
group by Day order by dtime


We can then put this in a table, bar chart etc... 

And here it is on a time series chart


My system was switched off for a while so the data is kinda outa whack, but the query still works !

Finally, you can apply color thresholds, use parameters to vary the number of weeks etc. And in soon to be released version of pulse, configure drills downs.

You can use the above on any globalview  to produce a wide variety of delta graphs and reports !

Hope y'all find this useful !