table lookup issues

[loveforsplunk]

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 ?

[dineshraj9 · Accepted solution]

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 

[loveforsplunk]

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!

[dineshraj9]

Sure.. no problem 🙂

[loveforsplunk]

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.

[dineshraj9]

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.

[loveforsplunk]

Thank You!!