Splunk Search

How to show lookup value if not present in subsearch

hank72
Path Finder


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 ?

index=b 
| join type=inner user
   [ | search index=a 
    [| inputlookup player.csv 
    | fields name ]  
| rename id AS user ]
| table name, user, action

 

Labels (4)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

 

View solution in original post

hank72
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

 

Get Updates on the Splunk Community!

Splunk + ThousandEyes: Correlate frontend, app, and network data to troubleshoot ...

 Are you tired of troubleshooting delays caused by siloed frontend, application, and network data? We've got a ...

Splunk Observability for AI

Don’t miss out on an exciting Tech Talk on Splunk Observability for AI!Discover how Splunk’s agentic AI ...

🔐 Trust at Every Hop: How mTLS in Splunk Enterprise 10.0 Makes Security Simpler

From Idea to Implementation: Why Splunk Built mTLS into Splunk Enterprise 10.0  mTLS wasn’t just a checkbox ...