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

[avni26]

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.

[woodcock]

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

[avni26]

@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

[woodcock]

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

[renjith_nair]

@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

[avni26]

@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

[avni26]

@renjith.nair Please suggest.

[to4kawa]

 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

[avni26]

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