Splunk Search

How to show results of multiple pie charts into one single table?

avni26
Explorer

Hi ,
I have multiple pie charts showing count of completed and pending on some filter.
Now want to show the results in table.
For example, there is
1st pie chat name "build_chart"
search:

index="dx2" sourcetype=xyz
| table _time application status user build env
| search build !="NA" 
| stats count by status 
| eval stat = count." : ".status 
| table stat, count 

output is:
stat count
60:completed 60
20:pending 20

Another pie chart name "test_chart"

search:

index="dx2" sourcetype=xyz
| table _time application status user test env
| search test !="NA" 
| stats count by status 
| eval stat = count." : ".status 
| table stat, count 

output:
stat count
40:completed 40
10:pending 10

Want to show all pie charts result in one table like below
Chart_name Total completed pending
bulid_chart 80 60 20
test_chart 50 40 10

Please suggest some best approach.

0 Karma

woodcock
Esteemed Legend

You best bet it to use a base search and build a data cube like this:

index="dx2" sourcetype=xyz
| fields_time application status user build env

Then use a post-process search for your build_chart panel:

search build !="NA"
| stats count by status
| eval stat = count." : ".status
| table stat, count

And use a post-process search for your test_chart panel:

search test !="NA"
| stats count by status
| eval stat = count." : ".status
| table stat, count

And a new one for everything together:

| stats count by status
| eval stat = count." : ".status
| table stat, count
0 Karma

avni26
Explorer

@woodcock What if my base query of each pie chart where calculating status as completed/pending/in-progress based on condition matched.
Example:
index="dx2" sourcetype=xyz
| table _time application user build test env
| search "build" !="NA"
| eval status = CASE(match('build',"Clone") OR match('build',"Pipeline") OR match('build',"Use"), "Complete",'build'="Scheduled" OR match('build',"Progress"),"In-Progress",1=1,"Pending")
| stats count by status

And another query is
index="dx2" sourcetype=xyz
| table _time application user build test env
| search "test" !="NA"
| eval status = CASE(match('test',"Clone") OR match('test',"Use") OR match('test',"Pipeline") OR match('test',"Completed"), "Complete",'test'="Scheduled" OR match('test',"Progress"),"In-Progress",1=1,"Pending")
| stats count by status

0 Karma

woodcock
Esteemed Legend

So you put the case statement in the base search. It is the same thing.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@avni26 ,

Try

index="dx2" sourcetype=xyz
|stats count(eval(build!="NA")) as build_chart,count(eval(test!="NA")) as test_chart by status
|transpose header_field=status column_name=chart_name| addtotals row=true
Happy Splunking!
0 Karma

avni26
Explorer

@renjith.nair
What if my base query of each pie chart where calculating status as completed/pending/in-progress based on condition matched.
Example:
index="dx2" sourcetype=xyz
| table _time application user build test env
| search "build" !="NA"
| eval status = CASE(match('build',"Clone") OR match('build',"Pipeline") OR match('build',"Use"), "Complete",'build'="Scheduled" OR match('build',"Progress"),"In-Progress",1=1,"Pending")
| stats count by status

And another query is
index="dx2" sourcetype=xyz
| table _time application user build test env
| search "test" !="NA"
| eval status = CASE(match('test',"Clone") OR match('test',"Use") OR match('test',"Pipeline") OR match('test',"Completed"), "Complete",'test'="Scheduled" OR match('test',"Progress"),"In-Progress",1=1,"Pending")
| stats count by status

0 Karma

avni26
Explorer

@renjith.nair Please suggest.

0 Karma

to4kawa
Ultra Champion
 index="dx2" sourcetype=xyz
 | eval status =if(in(build,"Clone","Pipeline","Use","Complete","Scheduled","Progress"),"In-Progress","Pending")
 |stats count(eval(build!="NA")) as build_chart,count(eval(test!="NA")) as test_chart by status
 |transpose header_field=status column_name=chart_name| addtotals row=true

Hi, try it.

@renjith.nair , I'm sorry for interrupting the conversation

0 Karma

avni26
Explorer

@woodcock / @richgalloway / @renjith.nair . Someone please help.

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...