topic Re: lookup multiple fields and tables in Splunk Search

lookup multiple fields and tables

bsteelz93 — Tue, 07 Jun 2011 22:54:51 GMT

I have a two part question about lookup tables:

Q1 - I have a 1 lookup table that has multiple fields. Sometimes the fields could be populated and other times the fields could be empty. For example

col1, col2, col3
d1,d2,d3
,d2,d3
d1,,d3

Can you search a lookup with multiple fields? Is there a way to search the data in a lookup regardless if the field is populated or not?

I tried inputlookup:
mysearch [| inputlookup mytable | fields field1,field2,field3]
This brought 0 events when I am certain that there are events

I also tried added a Flag column that eq true and running a lookup on table:
* | lookup mytable field1 fieldd2 field3 | search Flag=true
This somewhat worked because it brought data that was in field3 but not in the other fields

Q2 - I am populating 2 two different lookups. Each lookup has 2 columns. 1 column is the data that I am searching for and other column is a flag. I want to run a lookup on both tables and if the data in 1 lookup it will show the data in the output. Can you search 2 lookups in 1 search command?

I tried the following:
mysearch | lookup mycsv.csv fields AS field1 | lookup mycsv2.csv fields AS field2 field3| search Flag=true.

Can you search 2 lookups in 1 search command?

Re: lookup multiple fields and tables

lguinn2 — Tue, 07 Jun 2011 23:33:32 GMT

Q2 first: You can do 2 lookups in one search command, no problem.

I don't think your syntax is quite right, though. And if you fix that, maybe it will answer both questions for you.

Although you can do it all at once, I recommend that you set up your lookups in 3 steps: first, go to the Splunk Manager, then choose Lookups -> Lookup Table Files -> New. This is the place where you upload your .csv file to Splunk. Give it a filename.csv that you will use in subsequent commands.

Second, in the Splunk Manager, choose Lookups -> Lookup Definitions -> New. This is the place where you actually name the lookup in Splunk - this name does not need to be the same as the filename.csv (and usually isn't). I'll call it mylookupName in the example below.

Step 3 - Make sure you know the names of the fields. Your .csv file must have a first line that defines the column headings. For example,

col1,col2,col3,col4
100,102,203,good
500,400,300,bad

It is helpful, but not required, if the column names match the field names in your search - but let's assume that they don't. Your field names are field1, field2, field3 in your example above.

Now, you can actually use the lookup in a search:

mysearch | lookup mylookupName col1 as field1, col2 as field2, col3 as field3 OUTPUT col4

For each event, if there is a row in the .csv file where all the input values matches, there will be a resulting field, col4, available for that event.

Also look at this question: Can I chain fields together between two different lookups?

Finally, from your search, I wonder if what you want can be accomplished without a lookup command at all... can you give more details?

Re: lookup multiple fields and tables

prachi1501 — Tue, 10 Sep 2019 13:19:02 GMT

I also have similar kind of problem, I want to match two fields (mailaddress and domain) to my inputlookup, but not able to get exact result.
My lookup table have two different column for mailaddress and domain, i have to compare maliaddres field to lookup mailaddress field and domain field to lookup domain fields. And I have to match both fields in a single query... will you help me to make a query.

Re: lookup multiple fields and tables

prachi1501 — Tue, 10 Sep 2019 13:22:09 GMT

I also have similar kind of problem, I want to match two fields (mailaddress and domain) to my inputlookup, but not able to get exact result.
My lookup table have two different column for mailaddress and domain, I want to compare maliaddres field to lookup table mailaddress field and domain field to lookup table domain fields. And I have to match both fields in a single query... will you help me to make a query.