topic Re: How to show results of multiple pie charts into one single table? in Splunk Search

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

avni26 — Wed, 30 Sep 2020 02:43:54 GMT

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.

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

avni26 — Thu, 31 Oct 2019 07:25:46 GMT

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

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

renjith_nair — Thu, 31 Oct 2019 09:05:47 GMT

@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

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

avni26 — Fri, 01 Nov 2019 06:27:46 GMT

@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

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

woodcock — Sat, 02 Nov 2019 19:13:28 GMT

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

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

avni26 — Mon, 04 Nov 2019 08:56:30 GMT

@renjith.nair Please suggest.

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

avni26 — Mon, 04 Nov 2019 08:57:07 GMT

@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

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

to4kawa — Mon, 04 Nov 2019 09:38:39 GMT

 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

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

woodcock — Fri, 08 Nov 2019 02:27:44 GMT

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