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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...