Join events by closest time

[pembleton]

Hello, lets say I have events from two sourcetypes:

1. time, ip, hostname
2. time, ip, username

Now I want to match username to hostname based on the time and ip field in the following manner:
ip has to be the same, time has to be the closest time (before or after). Any easy out of the box way for doing that?

[martin_mueller]

If you know the two events are less than n seconds apart you could use transaction on the field ip with a maxspan of n seconds.

[pembleton]

oh, but what I still want is to find the closest one between them, this just gives me a great big list

[martin_mueller]

Maybe this is what you need then:

... | eventstats values(username) as users values(hostname) as hosts by ip

[pembleton]

ok, so what i meant is that an event can have numerous partners, meaning many users can come from the same host. and a user could use many hosts.

[martin_mueller]

Event 12:09 is now closer to 12:11 than to 12:05 so 12:09 must be associated with 12:11 and 12:05 loses its "partner".

[pembleton]

First question - yes
Second - no, why has anything changed?

I'll look into streamstats and come back

[martin_mueller]

You could try using streamstats by ip to add recent usernames and recent hostnames to neighbouring events.

Whether that works depends on your data. For example, if you have this set at 12:10:

12:00 1.2.3.4 host=foohost
12:05 1.2.3.4 user=foouser
12:09 1.2.3.4 host=barhost

You would associate 12:05 with 12:09?
What if at 12:11 you get another event like this:

12:11 1.2.3.4 user=baruser

Would you now associate 12:05 with 12:00?

[pembleton]

I have that one ready, but I don't know how far apart they are, I want to find the closest one for each event, with no limit.