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:
Jul 20 22:20:45 $myhost HostInfoParser: [WorkstationInfo] loggedOnUser=MY-Domain\mypc systemModel=MacBook serviceTag=123456abc systemHostname=mypcMacBook vpnIp=126.96.36.1999 ts=20120720_22:20:45
to another data set below:
Jul 20 22:20:48:myfirewall:[local7][warning]: %firewallcode: Group
to produce joined sets of data similar to the following below:
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 188.8.131.529 MacBook 123456abc mygroup
Right now we have the current join above working with the "vpnIP ON Address" portion of the join.
I would like to join based on both the vpnIp (i.e. 184.108.40.2069) 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.
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