Splunk Search

Add Rows from Lookup with no matches

Sivrat
Path Finder

I'm trying to use a lookup and some search results to display a table that includes both where something matched in the lookup, and where no matches were found in the lookup.

Sample search syntax would be:
[very long search, ends up in table with fieldA, fieldC]
| lookup MyLookup fieldA output fieldB
| chart values(fieldA) by fieldB, fieldC

This shows the values of field A, split by rows for the matching fieldB (pulled from lookup) and split by columns for fieldC.

I want to see all of the fieldB rows in the lookup added, even if there was no matching fieldA. Either blank or with a default value of 'None Found' or something in the chart results, since knowing something is 'missing' is important.

How can I do this? 

Labels (6)
0 Karma
1 Solution

scelikok
Champion

Hi @Sivrat,

Please try below;

[very long search, ends up in table with fieldA, fieldC]
| lookup MyLookup fieldA output fieldB
| inputlookup append=t MyLookup
| fillnull value="None Found" fieldB fieldC
| chart values(fieldA) by fieldB, fieldC
If this reply helps you an upvote is appreciated.

View solution in original post

scelikok
Champion

Hi @Sivrat,

Please try below;

[very long search, ends up in table with fieldA, fieldC]
| lookup MyLookup fieldA output fieldB
| inputlookup append=t MyLookup
| fillnull value="None Found" fieldB fieldC
| chart values(fieldA) by fieldB, fieldC
If this reply helps you an upvote is appreciated.

View solution in original post

Sivrat
Path Finder

This worked after very slight adjustments.

[very long search, ends up in table with fieldA, fieldC]
| lookup MyLookup fieldA output fieldB
| inputlookup append=t MyLookup
| fillnull value="None" fieldC
| chart values(fieldA) by fieldB, fieldC
| fields - None

Since I actually didn't want all of the fieldA's from the original search, just all of them that matched in the lookup.
But the inputlookup and adding in fillnull allowed all of the fieldB to be present, and I just needed to remove the column that was the filler at the end. 

Thanks @scelikok 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Have you tried fillnull?

Sample search syntax would be:
[very long search, ends up in table with fieldA, fieldC]
| lookup MyLookup fieldA output fieldB
| fillnull value="None Found" fieldB
| chart values(fieldA) by fieldB, fieldC
---
If this reply helps you, an upvote would be appreciated.
0 Karma

Sivrat
Path Finder

Thanks for the response.

Fillnull covers cases where a row is present, but the column is blank. As long as one of the columns has value, it works since that means the row will be present

If there are rows in lookup that don't have any matches in the values returned by the search, then those won't get populated at all.

I'm now trying to surround the [very long search] with a join using combination of inputlookup and appendcols to try and build out the initial table, then join the rest with subsearch. But haven't quite gotten it working. Really hoping there is an easier way.

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.