Splunk Search

How to add row to chart from lookup file?

boxmetal
Path Finder

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!

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
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"

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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"
0 Karma

boxmetal
Path Finder

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.

AppStage - Not ExecutedNULL
App_ANot Executed
FAILED
PASSED
App_BNot ExecutedNot Executed
...Not ExecutedNot Executed
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

What was the search you used for this result?

0 Karma

boxmetal
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
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"
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...