Average EPS Count over 30 Days, Broken down into daily averages;

[CharlieK95]

Hey!

We've recently moved from QRadar to Sentinel and we're currently trying to replicate our reports on Sentinel, but are struggling with Average EPS Count. Our previous queries, would query 30 days worth of "all data", average the Events per second for that day, and do it for each day in those 30 days. With those numbers, we could make a chart into PowerBI. 

In Sentinel, I've only been able to get as far as getting an average EPS, which appears to be wrong anyway, but this is the KQL I used;

union withsource=_TableName1 *
| where TimeGenerated > ago(30d)
| summarize count() by bin(TimeGenerated, 1m), Type
| extend counttemp =count_ / 60
| summarize
['Average EPS'] = avg(counttemp), ['Minimum EPS']=min (counttemp),
['Maximum EPS']=max(counttemp)

It's probably quite obvious to someone why this isn't working, but any help would be good! 

So the main question I have is, how can we get a result out of a query in Log Analytics, to show a daily breakdown of the average EPS for that day, over a month. For example, the output should be 30 separate days (over the last month), with a "Average EPS" per day listed so a chart can be made! 

Does anyone have any advice for this?

Kind Regards,

Charlie 

[#2 · Accepted solution]

@CharlieK95 

Two methods I used before: 

union *
// filter on last 30 whole days - midnight --> midnight 
| where TimeGenerated between ( startofday(ago(30d)) ..endofday(ago(1d)) )
// bin by 1d interval
| summarize count() by bin(TimeGenerated,1d), Type
    | extend avgEventPerDay  = count_ / 7
    | extend avgEventPerHour = avgEventPerDay / 24
    | extend eps    = avgEventPerHour /60
| project TimeGenerated, eps, Type
| render timechart with (title="EPS per day - per Table")

union *
// filter on last 30 whole days - midnight --> midnight 
| where TimeGenerated between ( startofday(ago(30d)) ..endofday(ago(1d)) )
// bin by 1d interval
| summarize count() by bin(TimeGenerated,1d), Type
    | extend avgEventPerDay  = count_ / 7
    | extend avgEventPerHour = avgEventPerDay / 24
    | extend eps    = avgEventPerHour /60
| summarize sum(eps) by TimeGenerated
| render timechart with (title="EPS - sum of all Tables")

[#3]

Why is the total event count of a day divided by 7?

| extend avgEventPerDay  = count_ / 7

@Clive_Watson

[#4]

@FlyingCoffee 

hmmmm - good question, this was a while ago. I could well have pasted in something from my archive.

This would be better:

union *
| where TimeGenerated between ( startofday(ago(30d)) ..endofday(ago(1d)) )
| summarize count() by bin(TimeGenerated,1m), Type
| extend counttemp =count_ / 60
| summarize 
           ['Average Events per Second (eps)'] = avg(counttemp), ['Minimum eps']=min (counttemp),
           ['Maximum eps']=max(counttemp)
  by ['Table Name']=Type, bin(TimeGenerated,1d)
| order  by ['Average Events per Second (eps)'] desc
| render timechart with (title="EPS - sum of all Tables")

,