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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...