Splunk Search

table lookup issues

loveforsplunk
Explorer

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 ?

Tags (1)
0 Karma
1 Solution

dineshraj9
Builder

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 

View solution in original post

0 Karma

dineshraj9
Builder

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 
0 Karma

loveforsplunk
Explorer

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!

0 Karma

dineshraj9
Builder

Sure.. no problem 🙂

0 Karma

loveforsplunk
Explorer

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.

0 Karma

dineshraj9
Builder

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.

0 Karma

loveforsplunk
Explorer

Thank You!!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...