Search for same time frame on the same day of different weeks

[wolfreb]

Hello! I've been spinning my wheels on this problem for a few hours. I have to build a report in Splunk that will show the count of particular events for a given time frame - entered by the user. Then I need to show the count of the same events occurring for the same time frame for the same day on the prior week. For example: A user-entered search for all events where eventCode=HM1 between 8 AM and 10 AM on Monday should return the count of those events plus the count of events occurring between 8 AM and 10 AM on the prior Monday.

I found that this article - https://www.splunk.com/blog/2012/02/19/compare-two-time-ranges-in-one-report/ - is very close to what I need, but it doesn't tell me how I can use the user-entered start and end times as the basis for the subsequent searches on prior weeks.

If anyone out there has some suggestions, I'd be super grateful!

[woodcock · Accepted solution]

Like this:

|makeresults count=2
| streamstats count AS serial
| addinfo
| eval info_min_time = if((serial=2), relative_time(info_min_time, "-7d"), info_min_time),
       info_max_time = if((serial=2), relative_time(info_max_time, "-7d"), info_max_time),
               label = if((serial=2), "One_Week_Back", "Original_Time")
| map search="search earliest=$info_min_time$ latest=$info_max_time$ Your Search Here | stats count AS $label$" 
| stats values(*) AS *

[wolfreb]

Thank you so much woodcock! That does the trick. it would have taken me 100 years to come up with that! The next thing I'm trying to address is the fact that the results of each of my searches are in separate columns and separate rows. I'd like them to appear in separate columns of the same row. I think i need to do something with the xyseries command, but I'm not sure if that's right. FYI, here's my current solution based on your advice:
|makeresults count=6
| streamstats count AS serial
| addinfo
| eval thisEvent="HM1"
| eval labels="Today:-1 Week:-2 Week:-3 Week:-4 Week:-5 Week"
| eval subtractDays="-7d:-14d:-21d:-28d:-35d"
| makemv delim=":" labels
| makemv delim=":" subtractDays
| eval info_min_time = if((serial>1), relative_time(info_min_time, mvindex(subtractDays,serial-2)), info_min_time),
info_max_time = if((serial>1), relative_time(info_max_time, mvindex(subtractDays,serial-2)), info_max_time),
label = if((serial>1), mvindex(labels,serial-1), mvindex(labels,0))
| map search="search earliest=$info_min_time$ latest=$info_max_time$ index=ktt-prod eventCode=$thisEvent$ | stats count AS $label$"

[DalJeanis]

Try this...

 | makeresults count=6
 | streamstats count AS serial
 | eval serial = serial-1
 | addinfo
 | eval thisEvent="HM1"
 | eval info_min_time=info_min_time-604800*serial
 | eval info_max_time=info_max_time-604800*serial
 | eval label=if(serial<1,"Today","-".tostring(serial)." Week")
 | map search="search earliest=$info_min_time$ latest=$info_max_time$ index=ktt-prod eventCode=$thisEvent$ | stats count AS $label$ by eventCode"
 | stats sum(Today) as Today, sum( * ) as  *  by eventCode

[wolfreb]

Thank you DalJeanis! It looks like a good solution, but the results only included the entry for "Today" and not the 5 previous weeks. I couldn't figure out what was wrong, but I'll keep looking at it. In the mean time, I've revised my solution to use a timechart command instead of stats and that cleared up my separate row issue. Now the date displays instead of "-1 Week", etc.

|makeresults count=6
 | streamstats count AS serial
 | addinfo
 | eval thisEvent="HM1"
 | eval thisEventName="Logons"
 | eval subtractDays="-7d:-14d:-21d:-28d:-35d"
 | makemv delim=":" subtractDays
 | eval info_min_time = if((serial>1), relative_time(info_min_time, mvindex(subtractDays,serial-2)), info_min_time),
        info_max_time = if((serial>1), relative_time(info_max_time, mvindex(subtractDays,serial-2)), info_max_time),
                label = if((serial>1), mvindex(labels,serial-1), mvindex(labels,0))
 | map search="search earliest=$info_min_time$ latest=$info_max_time$ index=ktt-prod eventCode=$thisEvent$ | timechart span=1d count(eventCode) as $thisEventName$"

[woodcock]

Try this:

| makeresults count=6
| streamstats count AS serial
| eval serial = serial-1
| addinfo
| eval thisEvent="HM1"
| eval info_min_time=info_min_time-604800*serial
| eval info_max_time=info_max_time-604800*serial
| eval label=if(serial<1,"Today","-".tostring(serial)." Week")
| map search="search earliest=$info_min_time$ latest=$info_max_time$ index=ktt-prod eventCode=$thisEvent$ | stats count AS $label$ BY eventCode"
 | stats first(*) AS * BY eventCode

[wolfreb]

BRAVO! That's perfect! That modification to the last STATS command did the trick. Thank you both so much for your assistance! It's nice of you to take time out of your day to help me.

[DalJeanis]

very nice technique.