topic Re: Data Enrichment from lookup with column header containing spaces in Splunk Search

Data Enrichment from lookup with column header containing spaces

jacqu3sy — Fri, 07 Apr 2017 15:28:04 GMT

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.

Re: Data Enrichment from lookup with column header containing spaces

DalJeanis — Fri, 07 Apr 2017 15:38:28 GMT

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

Re: Data Enrichment from lookup with column header containing spaces

jacqu3sy — Fri, 07 Apr 2017 16:13:45 GMT

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.

Re: Data Enrichment from lookup with column header containing spaces

somesoni2 — Fri, 07 Apr 2017 16:23:28 GMT

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

| inputlookup myfile.csv  | table Employee*

Re: Data Enrichment from lookup with column header containing spaces

masonmorales — Fri, 07 Apr 2017 23:26:44 GMT

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.

Re: Data Enrichment from lookup with column header containing spaces

woodcock — Sat, 08 Apr 2017 15:02:41 GMT

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.

Re: Data Enrichment from lookup with column header containing spaces

jacqu3sy — Mon, 10 Apr 2017 08:29:55 GMT

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

Re: Data Enrichment from lookup with column header containing spaces

woodcock — Mon, 10 Apr 2017 13:00:56 GMT

So where was the breakdown in your first attempt?

Re: Data Enrichment from lookup with column header containing spaces

jacqu3sy — Mon, 10 Apr 2017 15:04:32 GMT

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.