All Apps and Add-ons

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

glancaster
Path Finder

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!

Tags (2)
1 Solution

carasso
Splunk Employee
Splunk Employee

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

View solution in original post

agentelinux
Explorer

Is possible use | accum ?

carasso
Splunk Employee
Splunk Employee

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

View solution in original post

carasso
Splunk Employee
Splunk Employee

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

0 Karma

glancaster
Path Finder

@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

0 Karma

carasso
Splunk Employee
Splunk Employee

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

0 Karma

hcanivel
Explorer

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

0 Karma

carasso
Splunk Employee
Splunk Employee

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.

0 Karma

glancaster
Path Finder

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]

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.