Correlating Data from 3 Indexes (Match Destination IP from Firewall & Resolve Hostnames using Symantec & Windows Logs)

[neokevin]

Hi All,

I want to create an SPL query that first returns data by matching the destination IP address from Palo Alto logs. Then, according to the destination IP, it will resolve the actual destination hostname from Symantec logs and Windows Event logs in separate fields. I was able to match the destination IP (dest_ip) from Palo Alto logs with Symantec logs and return the hostname (if available) from it.

However, I am struggling to do the same by joining Windows logs to return the values, which should be equal to the hostname in Symantec logs. Can someone kindly assist me in fixing this code to retrieve the expected results? 🙂

index=*-palo threat="SMB: User Password Brute Force Attempt(40004)" src=* dest_port=445 
| eval dest_ip=tostring(dest)
| join type=left dest_ip [ 
    search index=*-sep device_ip=*
    | eval dest_ip=tostring(device_ip)
    | stats count by dest_ip user_name device_name
]
| eval dest_ip=tostring(dest)
| join type=left dest_ip [ 
    search index="*wineventlog" src_ip=*
    | eval dest_ip=tostring(src_ip)
    | eval username=tostring(user)
    | stats count by dest_ip username ComputerName
]
| table future_use3 src_ip dest_ip dest_port user device_name user_name rule threat repeat_count action ComputerName username
| sort src_ip
| rename future_use3 AS "Date/Time" src_ip AS "Source IP" dest_ip AS "Destination IP" user AS "Palo Detected User" user_name AS "Symantec Detected User @ Destination" device_name AS "Symantec Destination Node" rule AS "Firewall Rule" threat as "Threat Detected" action as "Action" repeat_count AS "Repeated Times"

@eve

[yuanliu]

The requirement is a little vague.  Even so, you can probably do without join, which is more expensive than other options.  Most commonly, stats is your friend.

Best I can speculate your intention is to match select fields these source by a common data point you call dest_ip which can have a different field name in each data source.  Something like

(index=*-palo threat="SMB: User Password Brute Force Attempt(40004)" src=* dest_port=445)
OR (index=*-sep device_ip=*) OR (index="*wineventlog" src_ip=*)
| eval dest_ip=coalesce(dest, device_ip, src_ip)
| eval "Palo Detected User" = if(match(index, "-palo"), user, null())
| rename user as username
| fields future_use3 src_ip dest_ip dest_port "Palo Detected User" device_name user_name rule threat repeat_count action ComputerName username
| stats values(*) as * by dest_ip
| sort src_ip
| rename future_use3 AS "Date/Time" src_ip AS "Source IP" dest_ip AS "Destination IP" user_name AS "Symantec Detected User @ Destination" device_name AS "Symantec Destination Node" rule AS "Firewall Rule" threat as "Threat Detected" action as "Action" repeat_count AS "Repeated Times"

[neokevin]

Thank you very much for the support @yuanliu . Your query works perfectly.

However, it gave me more results than expected. Basically, I want only to see logs from Palo that only threat field equal to "SMB: User Password Brute Force Attempt(40004)" and resolve Windows logs fields (ComputerName & username) + Symantec logs fields (Symantec Detected User @ Destination & Symantec Destination Node) based on the dest_ip value of the Palo logs. 

For your understanding below is what I get from your query (10000 statistics - 24hrs):

I need something similar to the below table (106 statistics - Last 24hrs):

Once again thank you very much for your support in this. Hope you can figure out my real requirement with this?

Cheers,
NeoKevin

[yuanliu]

In that case, you select only those results with non-null value in fields unique to index=*-palo.  Based on your original sample code, dest_ip is the field that all three sources need to match, but threat and "Palo Detected User" in the end result only come from index=*-palo search.  Therefore,

(index=*-palo threat="SMB: User Password Brute Force Attempt(40004)" src=* dest_port=445)
OR (index=*-sep device_ip=*) OR (index="*wineventlog" src_ip=*)
| eval dest_ip=coalesce(dest, device_ip, src_ip)
| eval "Palo Detected User" = if(match(index, "-palo"), user, null())
| rename user as username
| fields future_use3 src_ip dest_ip dest_port "Palo Detected User" device_name user_name rule threat repeat_count action ComputerName username
| stats values(*) as * by dest_ip threat "Palo Detected User"
| sort src_ip
| rename future_use3 AS "Date/Time" src_ip AS "Source IP" dest_ip AS "Destination IP" user_name AS "Symantec Detected User @ Destination" device_name AS "Symantec Destination Node" rule AS "Firewall Rule" threat as "Threat Detected" action as "Action" repeat_count AS "Repeated Times"

Technically, you can filter based on previous results.  But Splunk's groupby clause only tabulates non-null values.  So, this is cheaper than getting all 10000 stats before filtering.