How to Chart Average of Last 4 Thursdays vs Today in a Timechart?

[glancaster]

ok well replace Thursday with whatever "today" is. I am looking to track my bandwidth today with a timechart that also has the average of the last 4 "todays".

There are some searches in the ES app that do this with column and there is a nice search in the Deployment Monitor app titled "Today vs the same day a week ago" that shows kind of what I'm trying to get at but its been killing me.

The timewarp app has been awesome and Im looking to see if I can group the timecharts it produces into one (maybe an average?) and then put those results onto a timechart with my initial seach.

Thanks in advance!

[agentelinux]

Is possible use | accum ?

[carasso · Accepted solution]

I think this should do it:

index=_internal earliest=-4w  
| timechart count span=1h 
| where strftime(_time, "%A") == strftime(now(),"%A") 
| timewrap w 
| rename "* ago" as * | addtotals "2w" "3w" "4w" | eval avg=Total/3.0 
| table _time, _span, 1w, avg

1. get events for last month
2. generate timechart
3. filter out results not from today's day-of-the-week (keeps only thursday data if today is thursday)
4. timewrap all the days (e.g. thursdays) into the same day.
5. rename fields for ease, calculate the total and the avg
6. output just the columns needed to produce the chart

[carasso]

@glancaster, in the latest version of timewrap, I added an argument "series=short", which gives the series short names, like s0, s1, s2, s3, should would make your renames much simpler. (there's also series=exact, which gives things like "week_of_dec10").

[glancaster]

@carasso you're right and thank you for the feedback. I think it's getting close. I've ditched the appendcols and worked with the search you provided:

|timechart count(bytes_total) span=1h
| where strftime(_time, "%A") == strftime(now(),"%A")
| timewrap w
| rename count(bytes_total)_2weeks_before as AvgBytesTotal2 | rename count(bytes_total)_1week_before as AvgBytesTotal1| rename count(bytes_total)_3weeks_before as AvgBytesTotal3 | addtotals "AvgBytesTotal1" "AvgBytesTotal2" "AvgBytesTotal3"| eval avg=Total/3.0

| table _time, count(bytes_total)_latest_week, avg

[carasso]

I've since updated the names of the serieses to be more convenient -- no more whitespace -- and more clear and correct -- current_w, 1w_before, 2w_before

[hcanivel]

very elegant base search! definitely going to spin off of this for a per-sourcetype, per-index, per-host basis. great job, carasso!

[carasso]

Glancaster, your search with appendcols seems wrong.

1. "earliest=-7d latest=-6d earliest=-14d latest=-13d earliest=-21d latest=-20d" You need ORs, like "(earliest=-7d latest=-6d) OR (earliest=-14d latest=-13d) OR (earliest=-21d latest=-20d)"

2. appendcols will put the first value from the subsearch with the first value from the main search, then the second, etc. because the main search is over one day and the subsearch is over 3 days -- really 3 weeks -- the values won't correspond to eachother, so you're getting averages from the wrong time.

[glancaster]

Thanks! You've put out a great tool and thanks for the feedback. Heres what I ended up with:

index=op sourcetype=juniper:nsm dvc=TXHO01-FW earliest=-1d latest=now() | multikv | timechart avg(bytes_total) AS Today | appendcols [search index=op sourcetype=juniper:nsm dvc=TXHO01-FW earliest=-7d latest=-6d earliest=-14d latest=-13d earliest=-21d latest=-20d | multikv |timechart avg(bytes_total) AS AVG3Weeks]