Getting Data In
Highlighted

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

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 $myhost HostInfoParser[13143]: [WorkstationInfo] loggedOnUser=MY-Domain\mypc systemModel=MacBook serviceTag=123456abc systemHostname=mypcMacBook 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:myfirewall:[local7][warning]: %firewallcode: 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\mypc mypcMacBook 12.34.56.789 MacBook 123456abc mygroup

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)
Highlighted

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

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
Highlighted

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

Explorer

Thanks dwaddle! This will definitely get me somewhere.

0 Karma