Getting Data In

How to define a time range, filter and then apply a second time range?

markgize
Engager

I need to define an outer time range, simple:

earliest=-3h

I then want to filter the results, also simple:

earliest=-3h | stats count(_raw) as count by stuff | where count%2=1

But now the part I am struggling with. I want to define a time range that is even more restrictive:

earliest=-3h | stats count(_raw) as count by stuff | where count%2=1 | earliest=-2h

This syntax is invalid but hopefully describes what I am trying to achieve. I must have the outer time range, filter and then apply a further time range, is this possible?

Tags (2)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

Based on the bottom search, you're trying to find events that happen in the past two hours where the count of events by stuff is odd in the past three hours?

If so, consider this:

earliest=-3h | eventstats count by stuff | where count%2=1 AND _time >= relative_time(now(), "-2h")

As for your search failing, there's two things going on. First, you need a search command such as where or search after the last pipe. Second, at that point in the search pipeline the _time field is already discarded by stats so even with correct syntax you wouldn't be able to filter on time any more. eventstats takes care of that by adding a count field to the events without discarding anything.

View solution in original post

markgize
Engager

For future reference, the problem I was trying to solve is the pairing up of events that are separated by a matter of minutes. I can't use a fixed time range for this as it could split a valid pair. Therefore, I wanted to define a broad time range and then filter out non-pairs within this range.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Based on the bottom search, you're trying to find events that happen in the past two hours where the count of events by stuff is odd in the past three hours?

If so, consider this:

earliest=-3h | eventstats count by stuff | where count%2=1 AND _time >= relative_time(now(), "-2h")

As for your search failing, there's two things going on. First, you need a search command such as where or search after the last pipe. Second, at that point in the search pipeline the _time field is already discarded by stats so even with correct syntax you wouldn't be able to filter on time any more. eventstats takes care of that by adding a count field to the events without discarding anything.

View solution in original post

markgize
Engager

Thanks Martin you made a number of useful points:

  • eventstats to retain columns
  • count as the shorthand for count(_raw)
  • relative_time

The main problem I faced with my efforts was the use of stats which, as you say, meant that _time was no longer available. I am now up and running.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!