Getting Data In

Need help joining 2 sets of data where the timestamp is off by a few seconds

balbano
Contributor

Hi,

I am trying to correlate 2 sets of data together via join search statement, however I need to do a join based on 2 main variables ("vpnIp ON Address" AND " ts ON event_timestamp") on both sets of data.

An example would be the following:

I would like to join data from the following raw line:

Data Set 1: search (host=$host1 HostInfoParser)


Jul 20 22:20:45 $my_host HostInfoParser[13143]: [WorkstationInfo] loggedOnUser=MY-Domain\my_pc systemModel=MacBook serviceTag=123456abc systemHostname=my_pc_MacBook vpnIp=12.34.56.789 ts=20120720_22:20:45

to another data set below:

Data Set 2: search (host=$host2 firewall_code)


Jul 20 22:20:48:my_firewall:[local7][warning]: %firewall_code: Group User IP <$external_ip> Address <12.34.56.789> assigned to session

to produce joined sets of data similar to the following below:

Joined Search

host=$host1 HostInfoParser | join vpnIp [search host=$host2 %firewall_code ] | top loggedOnUser,systemHostname,vpnIp,systemModel,serviceTag,vpnGroup


loggedOnUser systemHostname vpnIp systemModel serviceTag vpnGroup
MY-Domain\my_pc my_pc_MacBook 12.34.56.789 MacBook 123456abc my_group

Right now we have the current join above working with the "vpnIP ON Address" portion of the join.

However,
I would like to join based on both the vpnIp (i.e. 12.34.56.789) and the time of the event generated (ts ON event_timestamp). In the example above, the timestamp shown if off by a few seconds.

What would be the best way to overcome what appears to be to be a minor obstacle? Not sure how to match the times up correctly when the timestamp between the 2 events are off by a few seconds.

Any help you can provide in this would be great.

Thanks.

Brian

Tags (3)

dwaddle
SplunkTrust
SplunkTrust

You might find this easier with a transaction instead. I would try a search similar to this:

( host=$host1 HostInfoParser ) OR ( host=$host2 %firewall_code ) 
| transaction maxspan=30s startswith=HostInfoParser endswith=%firewall_code vpnIp 
| table _time,loggedOnUser,systemHostname,vpnIp,systemModel,serviceTag,vpnGroup
0 Karma

kjiwatrakan
Explorer

Thanks dwaddle! This will definitely get me somewhere.

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.