How can I use Subnets.csv file of 100 subnets, instead of adding each in IP search command?

[utk123]

I want to create a Splunk dashboard for 100 subnets in the CSV file. But not able to use this CSV file in my Splunk query.

Example:

CSV file: Subnets.csv
Simple splunk query to generate results without csv file for only 3 subnets:
index=firewall (IP="10.10.10.*" OR IP="10.10.20.*" OR IP="100.100.20.*") (Status=allow) 
| stats count(IP) by Status

How can I use Subnets.csv file of 100 subnets, instead of adding each in IP search command?

[to4kawa]

UPDATE:

index=firewall [| inputlookup Subnets.csv | fields IP | format] Status=allow | stats count(IP) by Status

[utk123]

This doesn't give any results. 😞

[manjunathmeti]

Assuming IP field with subnet values exist in Subnets.csv like below.

IP
10.10.10.*
10.10.20.*
00.100.20.*
......

Try this search:

 index=firewall [| inputlookup Subnets.csv | eval IP=replace(IP,"\d+/\d+", "*") | fields IP] Status=allow | stats count(IP) by Status

[utk123]

Actually the Subnets are like below in CIDR format.
10.10.10.0/24
10.10.20.128/28
00.100.20.0/25

so above search doesn't work. Any suggestions ?

[manjunathmeti]

Better query:

index=firewall [| inputlookup Subnets.csv | eval IP=replace(IP,"\d+/\d+", "*") | fields IP | format] Status=allow | stats count(IP) by Status

[utk123]

Tried both methods, but it's not giving expected results. So I have just used notepad++ feature to replace all subnets ending with "star" from column to single row. and now all 100 subnets ending with "star" are in single row.

Like:
IP="10.10.10." OR IP="10.10.20." OR IP="100.100.20.*".....

Although it's not 100% exact, but close to exact, and working.

Thanks manjunathmeti.

[manjunathmeti]

Check this query:

index=firewall [| inputlookup Subnets.csv | eval IP=replace(IP,"\d+/\d+", "*") | fields IP] Status=allow | stats count(IP) by Status

This will run as:

index=firewall (IP="10.10.10.*" OR IP="10.10.20.*" OR IP="100.100.20.* OR ....") Status=allow | stats count(IP) by Status

[manjunathmeti]

Replace last part (0/24) of IP with * and use.

index=firewall [| inputlookup Subnets.csv | eval IP="IP=".replace(IP,"\d+/\d+", "*") | fields IP | mvcombine delim=" OR " IP | nomv IP | return $IP] Status=allow | stats count(IP) by Status