topic Re: How to show lookup value if not present in subsearch in Splunk Search

How to show lookup value if not present in subsearch

hank72 — Tue, 18 Jan 2022 05:32:07 GMT

Please help!
I have a lookup table and some data in two different indexes. Please help with a search that will produce an output like the following? I need to show "Foo Bar", which is present in the lookup, but has no values associated with the name in either index.
name           id      action
Tom Brady      tom     deleted
Foo Bar        N/A     N/A
Aaron Rodgers aaron   added

inputlookup=player.csv, column heading is name
Tom Brady
Foo Bar
Aaron Rodgers

index=a
name="Tom Brady" id=tom
name="Aaron Rodgers" id=aaron

index=b
user=tom action=deleted
user=aaron action=added

This is where I’m stuck. How can I also show "Foo Bar" as N/A ?

Re: How to show lookup value if not present in subsearch

ITWhisperer — Tue, 18 Jan 2022 06:18:32 GMT

Rather than using join, you could try using append and stats, first to "join" the two index searches, then the "lookup" table

``` this makeresults represents the index a search ``` | makeresults | eval _raw="user action tom deleted aaron added" | multikv forceheader=1 ``` rename user field so it matches with field in index b ``` | rename user as id | append ``` this makeresults represents the index b search ``` [| makeresults | eval _raw="name,id Tom Brady,tom Aaron Rodgers,aaron" | multikv forceheader=1 ] ``` remove superfluous fields ``` | fields - _raw _time linecount ``` "join" by id ``` | stats values(*) as * by id | append ``` this makeresults represents the inputlookup ``` [| makeresults | eval _raw="name Tom Brady Foo Bar Aaron Rodgers" | multikv forceheader=1 ] ``` remove superfluous fields ``` | fields - _raw _time linecount ``` "join" by name ``` | stats values(*) as * by name ``` fill the empty fields ``` | fillnull value="N/A" action id

Re: How to show lookup value if not present in subsearch

hank72 — Wed, 19 Jan 2022 08:29:14 GMT

Thank you very much. Your suggestion and example was of great help to me.