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
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)
Assuming that your fortinet
data is CIM
-compliant and uses src_ip
and dest_ip
and 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)
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)
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")
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.
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.
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
| stats count values(src_ip) as src_ip values(_time) as _time by dstip
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 ]
| inputlookup Blackipfortinet.csv | search [ search index=fortinet | dedup dstip | fields dstip] | stats count values(srcip) as src_ip values(_time) as _time by dstip
@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