correlate multiple destination fields based on single source field.

[jpena323]

Hi guys,

Would really appreciate your help looking for a splunk search to do some reporting/alerting for me based on my firewall logs... specifically having issues with correlating a single source field (such as IP address or hostname) to multiple other field counts (such as port or username).

I know there has to be a way to accomplish this but i've tried joins/appendpipe/and others with no luck...

Example of what I'm trying to accomplish:
Top list of source IP addresses hitting deny ACLs to multiple ports and total hits (such as someone scanning our network).

Source_IP - Source_Country - Total_Denies - Total_Port_Attempted
1.2.3.4 - - - - - - - - China - - - - - - - - 1678 - - - - - - - - - 64
9.8.7.6 - - - - - - - - Ukraine - - - - - - -1204 - - - - - - - - - 76
4.2.3.2 - - - - - - - - Netherlands - - - - 804 - - - - - - - - - - 4

Here is what I got so far:
sourcetype="juniper" reason="policy deny" src_zone="UNTRUST" | lookup geoip clientip as src_ip | stats dc(dest_port) as Total_Port_Attempted by src_ip,client_country

Problem here is that I lose the total count 😞 ...I've tried to get creative with the stats command but keep getting repeated group-by field error messages. Would be nice to even throw in a Total_DestIP_Attempted...

Any help is much appreciated!
Thanks!

P.S. Here is an example log entry:
*Apr 2 12:29:02 labsrx2 1 [src-ip="11.67.18.25" src-port="57900" dest-ip="11.21.8.188" dest-port="44256" type="TCP" policy="GLOBAL_DENY_LOG(global)" src-zone="UNTRUST" dest-zone="UNTRUST" reason="policy deny"]

[masonmorales]

sourcetype="juniper" reason="policy deny" src_zone="UNTRUST" | lookup geoip clientip as src_ip | stats count as Total_Attempts by dest_port, src_ip, client_country

[somesoni2 · Accepted solution]

How about this

sourcetype="juniper" reason="policy deny" src_zone="UNTRUST" | lookup geoip clientip as src_ip | stats dc(dest_port) as Total_Port_Attempted count as Tota_Denies dc(des_ip) as Total_DestIP_Attempted by src_ip,client_country