How to create a search to compare a lookup CSV file of blacklisted IPs with firewall logs?

[sumit29]

Hi All,

I have a blacklisted IP CSV file (Placed in lookup folder of search(app)). I need to compare with firewall logs if any blacklisted IP is allowed in our network. Please help to create the search to display the results.

[renjujacob88]

| inputlookup ipblacklist.csv | search [ search index=firewall | dedup src/dstip | fields srcip ]

[jet1276]

your search | rename src as IP (rename your search's field name you want to compare such as source or destination IP address with what you have in your lookup) | join IP [ inputlookup blacklistedips.csv ] | stats count by (field(s) you want in your output)

[woodcock]

Assuming both of your sources have a field called ip (if not, you will have to do some name normalization), like this:

... | eval type="firewall" | appendpipe [|inputlookup ipblacklist.csv | eval type="blacklist"] | stats values(*) AS * dc(type) AS numTypes by ip | where numTypes=2

[DMohn]

You can use inputlookupin conjunction with a join to solve this task.

Depending on the field names in your search (they need to be identical - for the example I will asssume that the field is named ip) your search can look like this:

 | inputlookup ipblacklist.csv | join type=inner ip [ search your_firewall_search | fields ip ]

This will return all IP addresses in both your inputlookup and your search,