How to add row to chart from lookup file?

[boxmetal]

Hi Splunk community,

I want to chart the data retrieved from index, filter the app_name field to match with ones in the lookup file. There will be some app_name values in lookup file not in the index, and they need to be added as new rows and labeled "Not executed" for their status.

My SPL looks like below:

index="my_index" 
| search 
    [ inputlookup my_lookup 
    | table "App Name" 
    | rename "App Name" as app_name] 
| table app_name stage_name stage_status 
| eval stage_name = "Stage - " + stage_name
| rename app_name as App 
| chart values(stage_status) by App, stage_name useother=f limit=0

Here what I got:

App	Stage A	Stage B	Stage C	Stage D
App_A	PASSED	FAILED	PASSED	PASSED

And I want it to look like this:

App	Stage A	Stage B	Stage C	Stage D
App_A	PASSED	FAILED	PASSED	PASSED
App_B	Not executed	Not executed	Not executed	Not executed
...	Not executed	Not executed	Not executed	Not executed

Please help and advise,

Thanks!

[ITWhisperer]

Try something like this

index="my_index" 
| search 
    [ inputlookup my_lookup 
    | fields "App Name" 
    | rename "App Name" as app_name] 
| table app_name stage_name stage_status 
| eval stage_name = "Stage - " + stage_name
| rename app_name as App 
| chart values(stage_status) by App, stage_name useother=f limit=0
| append
    [| inputlookup my_lookup 
    | fields "App Name" 
    | rename "App Name" as App]
| stats values(*) as * by App
| fillnull value="Not Executed"

[boxmetal]

I tried to add the append command under the subsearch, but it does not chart as expected. 

The "Not Executed" values is added to stage_name field, and all previous field become null.

App	Stage - Not Executed	NULL
App_A	Not Executed	FAILED PASSED
App_B	Not Executed	Not Executed
...	Not Executed	Not Executed

[ITWhisperer]

What was the search you used for this result?

[boxmetal]

I added it append command under the subsearch like this:

index="my_index" 
| search 
    [ inputlookup my_lookup 
    | table "App Name" 
    | rename "App Name" as app_name] 
| append
    [| inputlookup my_lookup 
    | fields "App Name" 
    | rename "App Name" as app_name]
| stats values(*) as * by app_name
| fillnull value="Not Executed"
| table app_name stage_name stage_status 
| eval stage_name = "Stage - " + stage_name
| rename app_name as App 
| chart values(stage_status) by App, stage_name useother=f limit=0

And for your provided search, the chart result I got only has app_name field. So I changed it like above but seem no luck so far

[ITWhisperer · Accepted solution]

index="my_index" 
| search 
    [ inputlookup my_lookup 
    | table "App Name" 
    | rename "App Name" as app_name] 
| table app_name stage_name stage_status 
| eval stage_name = "Stage - " + stage_name
| rename app_name as App 
| chart values(stage_status) by App, stage_name useother=f usenull=f limit=0
| append
    [| inputlookup my_lookup 
    | fields "App Name" 
    | rename "App Name" as App]
| stats values(*) as * by App
| fillnull value="Not Executed"