Splunk Search

Today vs. Yesterday vs. Last Week

ctripod
Explorer

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!

Tags (1)
1 Solution

carasso
Splunk Employee
Splunk Employee

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/

View solution in original post

carasso
Splunk Employee
Splunk Employee

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
Legend

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
Path Finder

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

0 Karma

john_q
Explorer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

tpflicke
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...