Splunk Search

Why is my search to compare data week over week using time modifiers showing incomplete results?

drodman29
Path Finder

I'm trying to apply the week over week design template from http://blogs.splunk.com/2012/02/19/compare-two-time-ranges-in-one-report/
but my counts are being truncated for the last week. (truncated meaning 0 records found for an hour when there should be values).

This fails to find records from "last week"

 host=a   earliest=-7d@d latest=now 
| eval marker="This week" 
| append [ search host=a  earliest=-14d@d latest=-7d@d 
| eval marker="Last week" 
| eval _time=_time+60*60*24*7] 
| timechart span=1h count(_raw) by marker

Why?

Splunk Version =6.2.0 Splunk Build =237341

1 Solution

sideview
SplunkTrust
SplunkTrust

There's a much better way to do this.

host=a earliest=-14d@d latest=now 
| eval marker=if(relative_time(now(),"-7d@d")<_time,"This week", "Last week")
| eval _time=if(relative_time(now(),"-7d@d")<_time,_time, _time+60*60*24*7)
| timechart span=1h count by marker

And I should mention that there's also an app called "timewrap" that provides a custom search command that pretty much does this work for you. https://splunkbase.splunk.com/app/1645/

And for what it's worth, and without taking away from timewrap, I think knowing how to use conditional eval statements like in my answer above, will get you further in the long run.

View solution in original post

sideview
SplunkTrust
SplunkTrust

There's a much better way to do this.

host=a earliest=-14d@d latest=now 
| eval marker=if(relative_time(now(),"-7d@d")<_time,"This week", "Last week")
| eval _time=if(relative_time(now(),"-7d@d")<_time,_time, _time+60*60*24*7)
| timechart span=1h count by marker

And I should mention that there's also an app called "timewrap" that provides a custom search command that pretty much does this work for you. https://splunkbase.splunk.com/app/1645/

And for what it's worth, and without taking away from timewrap, I think knowing how to use conditional eval statements like in my answer above, will get you further in the long run.

drodman29
Path Finder

I don't understand why one works and the other does not. This structure is logically equivalent to the original. Something weird in the appended stream when _time is modified?

0 Karma

sideview
SplunkTrust
SplunkTrust

The append command is intended as a last resort. In your question you were appending two raw-event searches, which is particularly egregious. In that case, Splunk is unable to push any of the computation out to the indexers, so all raw data has to come back to the search head. Furthermore the append version has to dispatch and run two searches instead of one. Also the append command has fundamental limits on memory usage where the stats version does not. In practical terms this means that your results are quite often truncated at 50,000 rows which might even happen without your being aware. Or if the inner search on an append takes too long, it'll be quietly autofinalized and your results will be a bit wrong. Or if you go down the road of trying to raise memory limits in limits.conf to "get around" this, you'll get in even deeper trouble and actually run out of memory! In short stats is the splunk way and join/append should be considered last resorts.

drodman29
Path Finder

Syntax in if statement should have a ) not a ]. also changed the eariest date to be -14d@d.

0 Karma

sideview
SplunkTrust
SplunkTrust

Right on. Sorry about that and I just fixed it in the answer proper.

0 Karma

woodcock
Esteemed Legend

Try this:

host=a earliest=-14d@d latest=now
| date_marker=relative_time(now(), "@w")
| eval marker=if((_time<date_marker), "Last week" , "This week")
| eval _time = _time + if((marker="Last week"), 60*60*24*7, 0)
| timechart span=1h count(_raw) by marker

Or, better yet, use the timewrap app.

0 Karma

drodman29
Path Finder

Changed latest to Now() and added an eval before date marker. result was just "last week" numbers looked good, but now this week was getting 0 record counts.

0 Karma

woodcock
Esteemed Legend

It is probably because I got cute and used @w which aligns the weeks fixedly instead of -7d@d.
Other than that, mine is the same as @sideview.

0 Karma
Get Updates on the Splunk Community!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...