Getting Data In

match an IP with a CIDR mask into a CSV file

Communicator

Hello,

I want to make match my IP in my logs with subnets specified into a CSV file.

Here's is my CSV file

subnet,building
10.1.0.0/16,Building 1
10.3.0.0/16,Building 2
10.4.0.0/16,Building 3
10.5.0.0/16,Building 4

I think my issue comes from the tranforms.conf file where i added the following lines

[dshq]
filename = dshq.csv
match_type = CIDR(host)
fileds_list = subnet, building

I want to count the number of hosts grouped by buildings using the IP range

And here is my search

sourcetype=nessus N_cvss>9 N_dnt=0 | rex "(?i)^[^\t]*\t(?P[^\t]+)" | lookup dshq subnet as subnet OUTPUT building as building | sort - count | chart count over building by subnet

I have no building field generated, could you please tell me why ?

Thanks.

EDIT : The regex (subnet) is my search refers to the host IP in my logs

Tags (3)

Splunk Employee
Splunk Employee

it failed because the match_type has to apply on the field in the lookup table.
use match_type = CIDR(subnet) instead.

Here's is my CSV file

subnet,building
10.1.0.0/16,Building 1
10.3.0.0/16,Building 2
10.4.0.0/16,Building 3
10.5.0.0/16,Building 4

Transforms.conf

[dshq]
filename = dshq.csv
match_type = CIDR(subnet)
fileds_list = subnet, building

search example with a field IP

* | lookup dshq subnet as IP OUTPUT building

Communicator

Suggested correction: should use fields_list = subnet, building.

0 Karma

Communicator

Hello Will

Thanks for the answer.
I tried the python script to do my cidr matching but it doesn't work.
It seems my csv file column are well recognized by splunk but when i do my search and specified in output the subnet_name i only have the orginal logs.

Something must be wrong ...

0 Karma

Explorer

@rbw78, Did you reformat your csv file to be the same as the example? What happens when "it doesn't work"?

0 Karma

Explorer

Hello rbw78,

I had this same problem and here's what I found on splunk-base.

http://splunk-base.splunk.com/answer_link/5938/

Hope it helps.

Thanks,
Will

0 Karma

Communicator

someone could help ? 🙂

0 Karma

Communicator

Thanks for the answer MHibbin 🙂

in fact, I don't want to use the cidrmtach function in the search because i have too much subnets.
I made an exemple with 4 subnets but i need to register about 50, that's why i prefer using a CSV file for cidr matching.
Is possible to do it that way ? 🙂

0 Karma

Influencer

There is a spelling issue with your transforms.conf... "fileds_list" should be "fields_list". And also, not sure if it is because you haven't defined a full file path to the lookup (I know this shouldn't matter... I find it helps though).

When troubleshooting csv lookups... I always start with a command like "|inputlookup <csvfile>" to make sure the is appearing as expected (sanity check)

Also have you checked your regex works correctly... could you not include the following to be a little more exact "(?P<ip>\d+\.\d+\.\d+\.\d+)" (or even replace the "+" in the regex with "{1,3}" as an ip would only have between one and three digits).

The way I normally do something like this would be to use the search syntax a little more than the conf files. But my setup would be like this..

[<csv_file_without_".csv"_extension>]
filename = $SPLUNK_HOME/etc/apps/<app_dir>/lookups/<csvfile>.csv
max_matches = 1000

e.g.

[dshq]
filename = $SPLUNK_HOME/<full_path_to_csv>/dshq.csv
max_matches = 1000

And then if I need to do a cidr match, I would use "where cidrmatch(<field1>, <field2>)", to match only those with a match CIDR Block.

Also in your lookup command, as part of your search syntax,you do not need to rename fields if the fieldnames match up... i.e. you have named your CSV fields to match those in Splunk... so you simply need to change the lookup part from ...

"lookup dshq subnet as subnet OUTPUT building as building"

to...

"lookup dshq subnet OUTPUT building"

Not really much difference just less search syntax 🙂

Hope this helps. I know it's not an answer as such, just some tips (which may be rubbish) 🙂 .

MHibbin

0 Karma

Communicator

We're gonna buy your product, some help would be appreciate 😉

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!