How do I compare two time slices?

[asturt]

I have a search that I want to run twice, but for different time slices. The result of the two slices will then be compared to get a measure of the difference. My current code has the same search twice but with different earliest and latest values, associated using appendcols. What I want to know is if there is a way to write the search once (instead of twice) and reuse that code; something like a common table expression in SQL?

[woodcock · Accepted solution]

You can give the same base search 2 different earliest/latest specifiers like this:

index=YouShouldAlwaysSpecifyYourIndex AND sourcetype=AndSourcetypeToo
((earliest=@d latest=@d+1h) OR (earliest=-1d@d latest=-1d@d+1h))
| eval which=if(_time<relative_time(now(), "@d"), "yesterday", "today")
| stats count by which

[skoelpin]

You should use relative_time against each time slice. This will give you epoch time and you can compare against that

| eval today=relative_time(now(), "0d@d")
| eval yesterday=relative_time(today, "-1d@d")

http://docs.splunk.com/Documentation/Splunk/7.1.3/SearchReference/DateandTimeFunctions

[DalJeanis]

Appendcols is almost never the right answer. NO, there is no CTE in Splunk...you can achieve some of that with macros, but you don't need to for this use case.

Here's the pseudocode you need -

your search that gets ALL the records for both (or all)  time periods 
| fields  ...list the fields you need...
| eval timeframe= case(this is the first time frame, "flag1", 
                       this is the second time frame, "flag2", 
                       ...for however many timeframes you want)
| where isnotnull(timeframe)

| now do all your processing and setup for the records

| now do summary processing and presentation

---

1) Note that any records which don't match one of your desired timeframes get dropped.

2) If you are going to do a timechart, then you have two options. First, if appropriate, you can use the timewrap facility, or second, you can add time to all the earlier timeframes to make them align exactly with the current timeframe. Either method works.

[asturt]

Thank you. That's an elegant method for restricting the search to a single scan of the data.