Splunk Enterprise Security

Correlating multiple logs to get combined data for Active Directory Events

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

0 Karma

lkutch_splunk
Splunk Employee
Splunk Employee

Which version of ES are you using? Maybe you could do it with a sequence template?
https://docs.splunk.com/Documentation/ES/6.0.0/Admin/Sequencecorrelationsearches

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