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
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
Thank you very much. Your suggestion and example was of great help to me.
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