CSV lookup not working

[Linedo (Linden)]

Hi, I have created a lookup table in excel, I then save it as CSV UTF-8, I then import it into Linux (Ubuntu) where the file type is “CSV text”. See below for the CSV file in linux:

Quote = "
Delimiter = ,

I’m pretty sure the data adapter is working as I had it working previously and it is pointing to the correct file/location.

However when I lookup a key e.g. 127.0.0.1 it returns null values, I am expecting it to return “localhost”.

I know the layout of the file is correct and the lookup table is not showing errors in the web interface.

Does anyone have any suggestions? Is the file type correct?

I’m happy to provide more info if needed.

Thank you in advance!

[faen]

Have you tried restarting the graylog service after setting up the data adapter?

[Linedo (Linden)]

@faen Thanks for your response, I have yes, it had no impact.

[gsmith]

Hello @Linedo

I tested this out in my lab. Here is the results.

CSV File

[root@graylog graylog]# cat lookup_ip.csv
"ipaddr","hostname","netlocation"
"127.0.0.1","localhost",
"10.0.0.1","server1",


[root@graylog graylog]#

But twhen I tested it with another ip addrss I got this.

So I went back to the CSV file and used the Documentation making sure I didnt Copy & paste along with any white spaces.

[root@graylog graylog]# cat lookup_ip.csv
"ipaddr","hostname"
"127.0.0.1","localhost"
"10.0.0.1","server1"
"10.0.0.2","server2"
[root@graylog graylog]#

Resolved:

This it maybe because of three collums OR spaces not sure. Before with your config, I was recieveing error

index 1 Out of bounds for lenth 1

Now I tried all three IP address without issues.

You have three columns, and I guess that worked before but the data tables shows only two columns.

[Linedo (Linden)]

Hi @gsmith, you are a legend for this reply! This really helped and pointed me in the right direction, although did not quite resolve the issue.

It turns out it is the separators and quote characters when importing the file from Windows are not recognised by Linux despite them looking the same.

I have tried things such as:

sed -i 's/"/$/g; s/,/;/g' *file.csv*

To replace the quote/separator characters with what I thought would be recognised characters, then changing the quote/separator characters in the data adapters to $ and ; and then rebooting the server, but this does not work either.

Non-ideal Resolution:
The only way I can get it to work is by deleting and replacing each quote/separator character manually (using nano), however as we have many thousands of IP addresses I would really rather not have to do this.

Do you have any other suggestions?

[Linedo (Linden)]

Resolution
Yay! I got it working. This is what I did:

I imported the file without quotes and added them in Linux with the following command, also the input file has carriage returns at the end of the lines, this command also removes them.

sed -i 's/\(.*\)\r/"\1"/g;s/,/","/g'

Then for some reason, which I still don’t understand, I had to completely remove the “key”,“value” line (first line) from the imported file and type it out manually (without editing any of the other lines).

I’m very happy, this has been bugging me for the last two days. I hope this helps someone else in the future.