topic Re: Count matches between values from two different events with different time ranges in Splunk Search

Count matches between values from two different events with different time ranges

Hartmannish — Fri, 12 Jun 2015 12:55:39 GMT

Okay, this is a bit difficult to explain, which is also why I'm not sure it hasn't already been answered, but here goes:

I have two searches:

The first checks 30-60 days back:

host=server* Identifier=name EventType=Stuff 
| stats count AS occurences by DeviceId 
| eval occur=case(occurences >= 2 AND occurences <=3, "true") 
| stats count(occur) AS "Happened 2-3 times"

And this one checks 7 days back:

host=server* Identifier=name EventType=Stuff earliest=-7d@
| stats dc(DeviceId) AS happened_once

What I need is a count of DeviceId's that match both. That is, they occur 2-3 times the last 30 or 60 days (haven't decided), but at least one of those occurrences, happened in the last 7 days.

I tried to something with subsearches, but I can't seem to get the syntax right, or I'm going about it the wrong way.

Any help?

Re: Count matches between values from two different events with different time ranges

richgalloway — Fri, 12 Jun 2015 13:32:30 GMT

Have a look at the first() and latest() options to the stats command. They will get you the most recent DeviceId. Then you should be able to examine _time for that event to see if it occurred in the last 7 days.

Re: Count matches between values from two different events with different time ranges

Hartmannish — Mon, 15 Jun 2015 12:29:52 GMT

That's a good idea. Could you elaborate a little on how to do this? If I use the first() or last() command, I seem to only get a single value. And as far as I know, stats discards timestamps.

Re: Count matches between values from two different events with different time ranges

richgalloway — Mon, 28 Sep 2020 20:18:01 GMT

You're right, _time does not survive the stats command. If your data will allow it, you can try stats first(DeviceId) as DeviceId, first(_time) as Time to get both the device name and the time it arrived. This will only work if the first event has a DeviceId field.

Then you can see how old Time is this way: ... | eval SevenDaysAgo=relative_time(now(), "-7d") | where Time>SevenDaysAgo | ...

Re: Count matches between values from two different events with different time ranges

MuS — Mon, 15 Jun 2015 21:49:18 GMT

another more straight forward approach is to use something like this http://answers.splunk.com/answers/153336/is-it-possible-to-use-earliest-twice-in-one-search.html - please make sure to pay attention to the last comment.

Re: Count matches between values from two different events with different time ranges

chimell — Tue, 16 Jun 2015 10:24:39 GMT

Hi Hartmannish
Use set command with intersect operation
Try this search code

|set intersect [search  host=server* Identifier=name EventType=Stuff  earliest=-60d@ ][search   host=server* Identifier=name EventType=Stuff earliest=-7d@  ]|stats count(DeviceId ) as count_DeviceId

Re: Count matches between values from two different events with different time ranges

Hartmannish — Tue, 16 Jun 2015 11:01:36 GMT

I must admit, I simply am not fluent enough in splunk to understand how this helps me? Is "Device" different from "DeviceId"? Also, how does this work together with my eval case() command?

Re: Count matches between values from two different events with different time ranges

Hartmannish — Tue, 16 Jun 2015 11:02:56 GMT

I guess I'm in the same dilemma here as I was with richgalloway's suggestion. I simply don't understand where to start. How do I translate what your link says, to my issue?

Re: Count matches between values from two different events with different time ranges

chimell — Tue, 16 Jun 2015 12:15:28 GMT

What about my answer .Let examine it.And tell if it works

Re: Count matches between values from two different events with different time ranges

Hartmannish — Tue, 16 Jun 2015 12:24:27 GMT

It looks REALLY close to what I need, but I havn't been able to test it, since I'm having some local IT issues 🙂 I will defnitely get back once I get to try it for real.

Re: Count matches between values from two different events with different time ranges

richgalloway — Tue, 16 Jun 2015 12:37:33 GMT

Device and DeviceId are the same. I've edited my comment to use only DeviceId.

Re: Count matches between values from two different events with different time ranges

Hartmannish — Tue, 16 Jun 2015 13:05:22 GMT

Okay, seems I simply can't use the set intersect command. It always returns either exactly 10000, or 1, or 2, if I try to use dc(). Maybe there are some limitations to set intersect I don't understand.

Re: Count matches between values from two different events with different time ranges

echozero39 — Tue, 16 Jun 2015 13:15:49 GMT

Have you tried to use append to output your second command to your first command ?

Re: Count matches between values from two different events with different time ranges

chimell — Tue, 16 Jun 2015 13:45:13 GMT

Retry with union operation and add the expression |fields DeviceId in each subsearch

Try this

 |set union [search  host=server* Identifier=name EventType=Stuff  earliest=-60d@|fields DeviceId  ][search   host=server* Identifier=name EventType=Stuff earliest=-7d@|fields DeviceId ]|stats count(DeviceId ) as count_DeviceId

Don't add something else in the query

Re: Count matches between values from two different events with different time ranges

Hartmannish — Tue, 16 Jun 2015 14:41:43 GMT

Same issue, except I get 20000 now instead of 10000 😕

Re: Count matches between values from two different events with different time ranges

Hartmannish — Tue, 16 Jun 2015 14:49:53 GMT

Okay, thanks for the clarification 🙂 Still, I'm not quite sure how to fill in the blanks from your explanation 🙂 I'm not trying to make you do my work for me, there are just a lot of things I still don't understand about splunk 😞

Re: Count matches between values from two different events with different time ranges

richgalloway — Tue, 16 Jun 2015 15:02:23 GMT

I was trying to come up with a way to do the job with a single search. On further study, I believe my suggestion won't solve both parts of your problem. Sorry.