Thanks!
Hi @ejohn,
if you grouped timestamps for hours using the bin command, you dont need the following commands, please try something like this:
<your_search>
| eventstats count AS total
| bin _time span=1h
| stats values(total) AS total count BY _time
| eval average=count/total
If I didn't center your requirement, please share a sample of the desidered output
Ciao.
Giuseppe
date | 01 | 12 | 17 |
08-01-2022 | 1 | ||
08-02-2022 | 1 | 2 | |
08-03-2022 | 1 | 7 | |
08-04-2022 | 1 |
date_hour | Σ events | # events | # days | standard avg | cumulative avg |
01 | 3 | 3 | 6 | 1 | 0.50 |
12 | 1 | 1 | 6 | 1 | 0.16667 |
17 | 9 | 2 | 6 | 4.5 | 1.5 |
The calculations would be executed separately so I can plot one line chart with Σ events vs standard avg and another with Σ events vs. cumulative avg. The table for standard average would look like this:
date_hour | Σ events | # events | standard avg |
01 | 3 | 3 | 1 |
12 | 1 | 1 | 1 |
17 | 9 | 2 | 4.5 |
Thanks for the help!
Hi @ejohn,
ok, your first table could be created using this search:
<your_search>
| eventstats count AS total
| bin _time span=1h
| chart count OVER _time BY hits
Instead to arrive to the final table, you could try something like this example:
index=_internal
| eventstats count AS total
| stats values(total) AS total dc(date_hour) AS date_hour count BY sourcetype
| eval average=round(count/total*100,2), cumulative=count/date_hour
in your case, you should try something like this.
<your_search>
| eventstats count AS total
| eval day=strftime(_time,"%m/%d/%Y")
| stats values(total) AS total dc(day) AS days count BY sourcetype
| eval average=round(count/total*100,2), cumulative=round(count/days,2)
Ciao.
Giuseppe
Hi @gcusello ,
You put me on the right track! I modified what you provided to calculate the average:
| eventstats count AS total by date_hour
| eval day=strftime(_time,"%m/%d/%Y")
| stats dc(day) AS days count BY date_hour
| eval average=round(count/days,2)
| sort by date_hour
| rename count as SumOfEvents, days as NumOfEvents
I used the syntax below separately to calculate the number of days in my selected date range. So for the month of August, TotalDays will have a value of 31.
| eventstats dc(date_mday) as daysNmonth
| timechart sum(daysNmonth)
| stats count(_time) as TotalDays
I'm having trouble incorporating TotalDays with the first block of syntax to calculate:
cumulative=round(SumOfEvents/TotalDays,2)
Hi @ejohn,
I cannot test your search, but it should run:
| eventstats count AS total by date_hour
| eval day=strftime(_time,"%m/%d/%Y")
| eventstats dc(date_mday) as daysNmonth
| eventstats count(_time) as TotalDays
| stats
dc(day) AS days
values(daysNmonth) AS daysNmonth
values(TotalDays) AS TotalDays
count
BY date_hour
| eval average=round(count/days,2)
| sort by date_hour
| rename count as SumOfEvents, days as NumOfEvents
| eval cumulative=round(SumOfEvents/TotalDays,2)
Ciao.
Giuseppe