topic Re: Comparing the firewall ip to the inputlook which contains the blacklisted ip and need to display the count and source in Splunk Search

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

renjujacob88 — Tue, 04 Apr 2017 18:37:47 GMT

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

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

bshuler_splunk — Tue, 29 Sep 2020 13:27:36 GMT

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

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

renjujacob88 — Tue, 29 Sep 2020 13:32:08 GMT

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 ]

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

bshuler_splunk — Tue, 29 Sep 2020 13:32:11 GMT

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

vgunti — Tue, 29 Sep 2020 13:32:14 GMT

try this

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.

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

jstoner_splunk — Tue, 04 Apr 2017 19:03:38 GMT

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.

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

renjujacob88 — Tue, 04 Apr 2017 19:05:57 GMT

@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

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

somesoni2 — Tue, 04 Apr 2017 19:08:45 GMT

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)

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

renjujacob88 — Tue, 04 Apr 2017 19:17:27 GMT

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

Need help

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

woodcock — Tue, 04 Apr 2017 19:19:00 GMT

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)

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

renjujacob88 — Tue, 29 Sep 2020 13:32:17 GMT

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