topic Re: Can I chain fields together between two different lookups (.csv files)? in Getting Data In

Can I chain fields together between two different lookups (.csv files)?

maverick — Fri, 20 May 2011 21:37:43 GMT

Can I perform a lookup using one csv file and then use a returned field from that one to perform a second lookup in a different csv file?

Re: Can I chain fields together between two different lookups (.csv files)?

lguinn2 — Sat, 21 May 2011 01:00:51 GMT

Yes, you can! When I did it, I defined the fields in the csv file with matching names, to avoid having to rename fields. I also created the definitions in order. First I created the lookup that used a field from the event to match a field in lookup table A. Then I defined a second lookup, which used the field name that was created in lookup table A to find a field in lookup table B. In other words

Event field X -> Lookup Table A field Y -> Lookup Table B field Z

LookupTable A has 2 fields: X,Y
Lookup Table B has 2 fields: Y,Z

After the automatic lookups were set up, fields X, Y and Z were all available in the field picker.

I don't know if order matters in the configuration files for lookups, but it might. I'd also be careful that you have 1-to-1 matching; this might not work with a 1-to-many match.

Re: Can I chain fields together between two different lookups (.csv files)?

smuld2 — Tue, 24 May 2011 13:35:54 GMT

This doesn't seem to work. Here's the way I configured it, maybe you can tell me where I went wrong.

I have two .csv files, user_lookup (referencing the first .csv) containing a column with UserID and Department. The second, department_lookup contains DivDept and DivisionDepartmentName. DivDept corresponds to values from Department and vice versa. The first lookup works, as the Department field shows up properly in my searches.

First lookup I used was:

WinEventLog:Security : LOOKUP-user_department_lookup  user_lookup UserID AS Account_Name OUTPUTNEW Department AS Department

Then I set up the second lookup:

WinEventLog:Security : LOOKUP-map_dept_to_divdept  department_lookup DivDept AS Department OUTPUTNEW DivisionDepartmentName AS DivDept

But no matter how I rearrange this, I can only get the first lookup to work. As it is, it doesn't throw an error, but the second lookup isn't producing any new fields. I also tried mapping the second lookup to overwrite the fields produced by the first, but that didn't work either. Suggestions?

Re: Can I chain fields together between two different lookups (.csv files)?

hazekamp — Tue, 24 May 2011 20:45:32 GMT

This can easily be done via props.conf, but it is imperative that the lookups are run in the proper order. The easiest way to make sure they are run in the proper order is to use alphanumeric precedence via property names:

## props.conf
LOOKUP-0first_lookup = my_first_lookup A OUTPUT B
LOOKUP-1second_lookup = my_second_lookup B OUTPUT C

Re: Can I chain fields together between two different lookups (.csv files)?

lguinn2 — Tue, 24 May 2011 21:57:21 GMT

I thought that Splunk would do the lookups in a particular order, but I didn't have time to research it.

Thanks for explaining this.

Re: Can I chain fields together between two different lookups (.csv files)?

lguinn2 — Mon, 28 Sep 2020 09:36:23 GMT

Find the props.conf file that contains the lookups you have configured. Look at hazekamp's answer below. Note the numbers in the LOOLIP-xx entries. Name your first lookup: LOOKUP-0user_department_lookup. Name the second on: LOOKUP-1map_dept_to_divdept.

I think Splunk runs your lookups in the wrong order because "map_dept..." comes before "user_dep..." in the ASCII sort.

Also, you use DivDept twice in the second lookup. That might also be a problem; shouldn't it be

LOOKUP-map_dept_to_divdept department_lookup Department AS Department OUTPUTNEW DivisionDepartmentName AS DivDept

Re: Can I chain fields together between two different lookups (.csv files)?

dmlee — Mon, 28 Sep 2020 09:36:28 GMT

I agree with lguinn, below are my lookup setting in props.conf , I am sure they works :

LOOKUP_1 = service_tree SERVICE_ID AS LOG_PAGE HANDSETTYPE as Platform OUTPUT NAME AS SERVICE_NAME_L10, PARENT_ID AS SERVICE_PARENT_ID_L9

LOOKUP_2 = service_tree SERVICE_ID as SERVICE_PARENT_ID_L9 HANDSETTYPE as Platform OUTPUT NAME as SERVICE_NAME_L9, PARENT_ID as SERVICE_PARENT_ID_L8

LOOKUP_3 = service_tree SERVICE_ID as SERVICE_PARENT_ID_L8 HANDSETTYPE as Platform OUTPUT NAME as SERVICE_NAME_L8, PARENT_ID as SERVICE_PARENT_ID_L7

Re: Can I chain fields together between two different lookups (.csv files)?

smuld2 — Wed, 25 May 2011 13:40:59 GMT

Looks like that solved everything. Seems the items in the config file are handled alphabetically, not based on the order they appear in the file.

Re: Can I chain fields together between two different lookups (.csv files)?

lguinn2 — Thu, 02 Jun 2011 18:45:10 GMT

That is almost correct - they are handled in ASCII order, so watch out for case --upper case sorts ahead of lowercase.

When order of processing is important, I always number the lookups.