Forum Discussion

Copper Contributor

Jan 13, 2022

Solved

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

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 day...

Clive_Watson

Jan 13, 2022

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")

Clive_Watson

Bronze Contributor

Jan 13, 2022

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")

FlyingCoffee

Copper Contributor

Nov 22, 2022

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

| extend avgEventPerDay  = count_ / 7

Clive_Watson

Bronze Contributor

Nov 22, 2022

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")