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

[Hartmannish]

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?

[echozero39]

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

[chimell]

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 

[chimell]

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

[Hartmannish]

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.

[chimell]

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

[Hartmannish]

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

[Hartmannish]

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.

[richgalloway]

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.

[MuS]

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.

[Hartmannish]

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?

[Hartmannish]

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.

[richgalloway]

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 | ...

[Hartmannish]

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?

[richgalloway]

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

[Hartmannish]

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 😞

[richgalloway]

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.