topic Re: table lookup issues in Splunk Search

table lookup issues

loveforsplunk — Thu, 18 May 2017 05:47:52 GMT

I have a lookup table named lookupfile.csv
My file looks like this:
col1,col2,col3,col4
100,300,500,yes
200,400,600,yes1
300,100,500,yes3

My search is :
basesearch | lookup mylookup col1 , col2 , col3 OUTPUT col4 | stats count by col1, col2, col3, col4

For each event where all the input values matches, there will be a resulting field, col4, available for that event.
So if I have field1=100 AND field2=300 AND field3=500 then I will get back col4=yes or yes1 or yes2 based on the combination.

Now , suppose splunk gets a new combination for the base search whose details are not listed in the lookup file. Can I get the combination in statistics tab keeping the col4 field as blank ?

Re: table lookup issues

dineshraj9 — Thu, 18 May 2017 06:01:11 GMT

Before the stats command add a fillnull for field col4 -

basesearch | lookup mylookup col1 , col2 , col3 OUTPUT col4 | fillnull value="" col4 | stats count by col1, col2, col3, col4

I would suggest adding a character and not a blank space to make the output easier to read -

| fillnull value="-" col4

Re: table lookup issues

loveforsplunk — Thu, 18 May 2017 06:56:05 GMT

Ahhh.. Thank You! I was doing the same thing of adding fillnull command but my mistake was I was adding it at the end of the search string. Thanks buddy!

Re: table lookup issues

dineshraj9 — Thu, 18 May 2017 06:58:29 GMT

Sure.. no problem 🙂

Re: table lookup issues

loveforsplunk — Thu, 18 May 2017 07:11:45 GMT

I have another question .. do i have to update my csv file every time I get a new error or is there any other way that can also automatically do this . Just asking out of curiosity.

Re: table lookup issues

dineshraj9 — Thu, 18 May 2017 07:15:40 GMT

If you can form a query to identify new error and populate required fields, you can have another search running to update your lookup file with these fields using outputlookup command.

basesearch | lookup mylookup col1 , col2 , col3 OUTPUT col4 | search NOT col4=*

This will give you where there are no matches in the lookup and the fields that may have to be updated.

Re: table lookup issues

loveforsplunk — Sun, 21 May 2017 04:48:16 GMT

Thank You!!