Splunk Search

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

Explorer

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!

Tags (3)
1 Solution
Esteemed Legend

Like this:

``````|makeresults count=2
| streamstats count AS serial
| 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 *
``````
Esteemed Legend

Like this:

``````|makeresults count=2
| streamstats count AS serial
| 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 *
``````
Explorer

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
| 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\$"

SplunkTrust

Try this...

`````` | makeresults count=6
| streamstats count AS serial
| eval serial = serial-1
| 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
``````
Explorer

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
| 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\$"
``````
Esteemed Legend

Try this:

``````| makeresults count=6
| streamstats count AS serial
| eval serial = serial-1
| 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
``````
Explorer

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.

SplunkTrust

very nice technique.

Get Updates on the Splunk Community!

Enterprise Security Content Updates (ESCU) - New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 3 releases of new content via the Enterprise ...