Splunk Search

Data Enrichment from lookup with column header containing spaces

Path Finder

Is it possible to use a csv file in a lookup specifically for data enrichment whereby the column header contains spaces?

I'm trying to make the process of delivering a csv to us as painless as possible, so I dont really want to force the owner to have to edit the header to remove the spaces.

I've tried using quotes but this just errors;

my search| lookup myfile.csv Employee AS user OUTPUT "Employee ID"

Any ideas? Thanks.

0 Karma
1 Solution

Esteemed Legend

Do this:

| inputlookup myfile.csv

Examine the format of the field names and the output. Identify one Employee value to use as a test (e.g. gwoodcock) and then do this:

|makeresults | eval user = "gwoodcock" | lookup myfile.csv Employee AS user

You should get ALL of the fields as output. Then try this:

|makeresults | eval user = "gwoodcock" | lookup myfile.csv Employee AS user OUTPUT "Employee ID"

I tested this and it works.

View solution in original post

0 Karma

Esteemed Legend

Do this:

| inputlookup myfile.csv

Examine the format of the field names and the output. Identify one Employee value to use as a test (e.g. gwoodcock) and then do this:

|makeresults | eval user = "gwoodcock" | lookup myfile.csv Employee AS user

You should get ALL of the fields as output. Then try this:

|makeresults | eval user = "gwoodcock" | lookup myfile.csv Employee AS user OUTPUT "Employee ID"

I tested this and it works.

View solution in original post

0 Karma

Path Finder

Thanks, wasn't familiar with the 'makeresults' command, pretty sure that could come in handy in future as well so cheers, appreciate that.

0 Karma

Esteemed Legend

So where was the breakdown in your first attempt?

0 Karma

Path Finder

Still not sure to be honest. I think that because it was failing was causing me to question whether or not it was possible. The fact it was confirmed that it should work, in conjunction with your syntax and the use of the makeresults command for testing purposes allowed me to get it working in the end. Thanks.

0 Karma

SplunkTrust
SplunkTrust

Run this and tell us the field names that Splunk returns.

| inputlookup myfile.csv  | table Employee*
0 Karma

SplunkTrust
SplunkTrust

Just to verify... "Employee" and "Employee ID" are two different columns in the csv?

If so, then take a look at the csv using a plain text editor, and verify the exact spelling and capitalization of the user column "Employee" Make sure there's no space before or after the word. Go ahead and put it in quotes in the lookup command. Also, you can save yourself some trouble later if you go ahead and jerk that space out of the field name right here...

my search| lookup myfile.csv "Employee" AS user OUTPUT "Employee ID" AS EmployeeID
0 Karma

Path Finder

yes, 2 different columns. The search for Employee against the user field works fine. The problem is after the OUTPUT where that column contains the space between Employee and ID. I need to use the data in that column for data enrichment purposes.

If I remove the space, it works perfectly, but I'd have though the quotes would work and allow me to keep the sapce in place in the lookup. If that makes sense.

I tried your version ie OUTPUT "Employee ID" AS EmployeeID, but it errors.

0 Karma

Influencer

What error are you getting? In my testing, I was able to output field names containing spaces from the lookup using double quotes in the lookup command. Without seeing sample data from your events, or from the lookup, as far as I can tell, the syntax you are using should work.

0 Karma