Splunk Enterprise Security

Correlating multiple logs to get combined data for Active Directory Events

New Member

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

Any help will be appreciated. Thanks

0 Karma

Splunk Employee
Splunk Employee

Which version of ES are you using? Maybe you could do it with a sequence template?

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!