Splunk Search

How to show lookup value if not present in subsearch

hank72
Explorer


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
Explorer

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!

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...