Splunk Search

Comparing the firewall ip to the inputlook which contains the blacklisted ip and need to display the count and source

Path Finder

Hi,

I have a blacklisted inputlookup csv which contains 20000 blacklisted ip. I need to compare the inputlookup with the fortinet firewall and display the count of the destination IP along with the srcip

As of now i'm having a query which will compare the firewall outbound traffic and display any blacklisted ip which is present in the inputlookup.

| inputlookup Blackipfortinet.csv | search [ search index=fortinet | dedup dstip | fields dstip ]

What i need is the count of the destination ip followed by the src ip and time? is it possible

0 Karma
1 Solution

Revered Legend

This should give you all the records from firewall outbound traffic logs which are going to blacklisted IPs

index=fortinet [| inputlookup Blackipfortinet.csv | table dstip]

Assuming above index includes srcip (check the field names), time (_time actually) and dstip, so you can generate the report/aggregation you want. For example

index=fortinet [| inputlookup Blackipfortinet.csv | table dstip]
| stats dc(dstip) as "DestIP count" min(_time) as "First Occurred" max(_time) as "Last Occurred" by srcip
| convert ctime(*Occurred) 

View solution in original post

Esteemed Legend

Assuming that your fortinet data is CIM-compliant and uses src_ip and dest_ipand also assuming that your lookup has a field dstip, then like this:

index=fortinet | lookup dstip AS dest_ip Blackipfortinet output dstip AS blacklist
| search blacklist="*"
| stats dc(src_ip) values(src_ip) min(_time) as "First Occurred" max(_time) as "Last Occurred" by dest_ip
| convert ctime(*Occurred) 

Revered Legend

This should give you all the records from firewall outbound traffic logs which are going to blacklisted IPs

index=fortinet [| inputlookup Blackipfortinet.csv | table dstip]

Assuming above index includes srcip (check the field names), time (_time actually) and dstip, so you can generate the report/aggregation you want. For example

index=fortinet [| inputlookup Blackipfortinet.csv | table dstip]
| stats dc(dstip) as "DestIP count" min(_time) as "First Occurred" max(_time) as "Last Occurred" by srcip
| convert ctime(*Occurred) 

View solution in original post

Path Finder

Thanks somesoni2. Query is working smooth

index=fortinet [| inputlookup Blackipfortinet.csv | table dstip] dstip!="10.11.1.251" | stats values(srcip) as "Source IP" min(_time) as "First Occurred" max(_time) as "Last Occurred" count(dstip) as destcount by dstip | convert ctime("First Occurred"), ctime("Last Occurred")

0 Karma

Splunk Employee
Splunk Employee

Depending on the number of logs in the subsearch, you may hit your head on a limit there.

You could use a straight lookup as well to get the matches like this:

index=fortinet | dedup dstip | lookup Blackipfortinet.csv ipfieldname AS dstip |search dstip=*

and then use the stats command as mentioned above. Keep in mind with the stats on time if you have a number of values, you could end up with a stack of timestamps to wade through, so perhaps getting an idea on connections and number of connections first might be desired and then drill into specifics from there, but that would depend on what you are trying to accomplish.

0 Karma

Engager

try this

| inputlookup Blackipfortinet.csv | join distip type=outer | search [ search index=fortinet | stats count(distip) as distip_count by srcip] | table srcip, distip_count

if not want the filed names of Blackipfortinet.csv as well. required matching filed from csv file, and same to be fortinet index to compare, based on you can get statistical data like count (distip) based on srcip.

Path Finder

Tried with the below query and showing some error: "Error in 'join' command: Usage: join ()? [subsearch]"

|inputlookup Blackipfortinet.csv | join dstip type=outer | search [ search index=fortinet | stats count(dstip) as countip by dstip] | table dstip

Need help

0 Karma

Splunk Employee
Splunk Employee

| stats count values(src_ip) as src_ip values(_time) as _time by dstip

0 Karma

Path Finder

Thanks for the reply. But iam not getting any results from the query.
| inputlookup Blackipfortinet.csv | search [ search index=fortinet | stats count values(srcip) as src_ip values(_time) as _time by dstip ]

0 Karma

Splunk Employee
Splunk Employee

| inputlookup Blackipfortinet.csv | search [ search index=fortinet | dedup dstip | fields dstip] | stats count values(srcip) as src_ip values(_time) as _time by dstip

0 Karma

Path Finder

@bshuler thanks the query is working but needs some tuning. As iam using dedup dstip . the count is giving one one. Can u recomend the same without the dedup. so that i can get the exact count

0 Karma