All, Need help with combining logs from Load Balancer/SNAT and AD Domain Controller to get the combined results in a single query. I could come up with separate queries and looking for help combining them
Query 1: AD Domain Controller logs that gives the logged in user details and LB ip and outbound port
source = WinEventLog:Security AND EventCode=4624 AND Account_Domain=XYZINC AND Source_Network_Address != null AND Source_Network_Address!="-" | rex field=_raw "Source Port:<(?<src_port>.*)>" | eval num = mvcount(Account_Name) | eval account= if( num=2, mvindex(Account_Name, 1), mvindex(Account_Name, 0) ) | search (NOT account="*\$*") | stats list(account) as al count by _time,src_ip,src_port | table _time count al src_ip src_port
Sample Data
_time count al src_ip src_port
2019-11-10 14:54:36 1 xyzlogin 10.173.22.117 62623
Query 2: Load Balancer logs contain the real client ip and LB ip and outbound port
sourcetype="cisco_syslog" source="/opt/syslog-ng/log/loadbal*.log" \<-\> | rex field=_raw "^(?P<Time>\w+\s+\d+\s+\d+:\d+:\d+)[^:\n]*:(?P<Client_IP>[^%]+)%\d+:(?P<NAT_PORT>\d+)(?:[^ \n]* ){4}(?P<NAT_IP>[^%]+)" | table _time Client_IP NAT_IP NAT_PORT
_time Client_IP NAT_IP NAT_PORT
2019-11-10 14:54:25 10.173.128.78 10.173.22.117 62623
Assuming Query1 result as A and Query to result as B
matching criteria for both the queries is
A.src_ip = B.NAT_IP AND A.src_port = B.NAT_PORT AND A._time ~ B._time +20s (check for the events in the range of 20 secs for match, because request hits LB first and then goes to AD Domain controller)
Now I'm looking for combining these two in an effective way to get output in the format
_time count al client_ip
2019-11-10 14:54:36 1 xyzlogin 10.173.128.78
Any help will be appreciated. Thanks
