Today vs. Yesterday vs. Last Week

[ctripod]

Hi All,

I have a need to display a timechart which contains negative HTTP status codes (400's and 500's) today, yesterday, and same time last week. I've used append, appendcol, stats, eval, addinfo, etc. and I can't seem to get the best fit. Some timeout on subsearches, some don't make the _time readable and I've tried just about every example possible.

I've tried to break the 3 searches into individual saved searches and build acceleration for each. Any help would be greatly appreciated. Here is the best performing one; that is only for today vs. yesterday which I cannot get the time series display to render nicely, but it performs the best (returning under 5 seconds).

index=access_json status>=400 earliest=-2h@h latest=@h 
| timechart span=1m count as metric | addinfo  
| eval marker = if(_time < info_min_time + 3600, "Last hour", "This hour") 
| eval _time = if(_time < info_min_time + 3600, _time + 3600, _time) 
| chart median(metric) by _time marker

Thanks!

[carasso · Accepted solution]

Comparing week-over-week results used to a pain in Splunk, with complex date calculations. No more. Now there is a better way.

I wrote a convenient search command called "timewrap" that does it all, for arbitrary time periods.

... | timechart count span=1h | timewrap d

That's it!

http://apps.splunk.com/app/1645/

[lguinn2]

Try this

index=access_json status>=400 earliest=-7d@d latest=@h 
| eval startToday = relative_time(now(),"-24h@h")
| eval startYesterday = relative_time(now(),"-48h@h")
| eval endLastWeek = relative_time(now(),"-168h@h")
| eval marker = case(_time >= startToday, "Today",
                     _time >=startYesterday,"Yesterday",
                     _time <= endLastWeek,"Last Week",
                     1=1,"Outside Range")
| where marker != "Outside Range"
| eval _time = case(marker="Today",_time,
                    marker="Yesterday",_time+86400,
                    marker="Last Week",_time+(7*86400) )
| timechart fixedrange=f count by marker

I might be a bit off on the time math, but you get the idea. BTW, now() is the time that the search started; you don't have to use addinfo to get it...

[tpflicke]

index=access_json status>=400 earliest=-192h@h latest=@h
| eval startToday = relative_time(now(),"-24h@h")
| eval startYesterday = relative_time(now(),"-48h@h")
| eval endLastWeek = relative_time(now(),"-168h@h")
| eval marker = case(_time >= startToday, "Today",
_time >=startYesterday,"Yesterday",
_time <= endLastWeek,"Last Week",
1=1,"Outside Range")
| where marker != "Outside Range"
| eval _time = case(marker="Today",_time,
marker="Yesterday",_time+86400,
marker="Last Week",_time+(7*86400) )
| timechart span=1h fixedrange=f count by marker

[john_q]

i would like to include prior week as well. i have modified query to like below mentioned but not working??

index=access_json status>=400 earliest=-360h@h latest=@h
| eval startToday = relative_time(now(),"-24h@h")
| eval startYesterday = relative_time(now(),"-48h@h")
| eval endLastWeek = relative_time(now(),"-168h@h")
| eval priorLastWeek = relative_time(now(),"-336h@h")
| eval marker = case(_time >= startToday, "Today",
_time >=startYesterday,"Yesterday",
_time <= endLastWeek,"Last Week",_time <= endLastWeek,"Prior Week",
1=1,"Outside Range")
| where marker != "Outside Range"
| eval _time = case(marker="Today",_time,
marker="Yesterday",_time+86400,
marker="Last Week",_time+(7*86400), marker="Last Week",_time+(14*86400) )
| timechart span=1h fixedrange=f count by marker

[richgalloway]

@john_q This thread is more than four years old with an accepted answer. You have a better chance at getting a good response to your problem if you post a new question.

[tpflicke]

I changed a couple of things to get the query to do exactly as desired: endLastWeek using 168h (i.e. 7 x 24) and earliest=-192h.
The latter got around the unsightly issue of an extra bar due to changing from Summer to Wintertime last weekend. Took me a while to figure where that rogue bar came from.