Dashboards & Visualizations

How to create a pie chart with multiple searches?

cheanaydoo
Explorer

I need to create a pie chart from two different searches/indexes.

I gave two separate queries that show the total number from my search results.

 

Query 1:

index="first_index" 
| stats count by auth.metadata.role_name
| rex field=auth.metadata.role_name
| dedup auth.metadata.role_name
| stats count

 Query 2:

index="second_index" sourcetype="mysource" (request.path="my/path/*" OR request.path="my/path/sign/*") NOT (request.path="not/my/path" OR request.path="also/not/my/path") response 
| eval expired=if((now() > 'response.data.expiration'),1,0)
| table _time, request.data.common_name, expired, auth.metadata.role_name
| rename request.data.common_name as cn
| search "auth.metadata.role_name"="my_role_name"
| table cn
| dedup cn
| stats count

 

Where query 1 is = 100%

 

How can I make query 2 show a percentage using query 1 as the 100%

i.e if query 1 stats count  = 150

and query 2 stats count  = 75

then query 2 should print 50 %

 

Thanks

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

count1 exists in one event and count2 exists in the other event which is why the calculation doesn't work. You could try

| chart count by index

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this - include the index field on stats and table commands so it isn't lost

index="first_index" 
| stats count by auth.metadata.role_name index
| rex field=auth.metadata.role_name 
| dedup auth.metadata.role_name 
| append
[search index="second_index" sourcetype="mysource" (request.path="my/path/*" OR request.path="my/path/sign/*") NOT (request.path="not/my/path" OR request.path="also/not/my/path") response 
| eval expired=if((now() > 'response.data.expiration'),1,0)
| table _time, request.data.common_name, expired, auth.metadata.role_name index
| rename request.data.common_name as cn 
| search "auth.metadata.role_name"="my_role_name" 
| table cn index
| dedup cn ]
| stats count by index

cheanaydoo
Explorer

Hello,

Thanks for the tip, it was helpful.

Although I got a different result when I executed and did not get the percentage, but I did some twakings to finally get the percentage using this 

 

index="first_index" sourcetype="mysource" (request.path="my/path/*" OR request.path="my/other/path/*") NOT (request.path="not/my/path" OR request.path="not/my/path") response 
| eval expired=if((now() > 'response.data.expiration'),1,0)
| table _time, request.data.common_name, expired, auth.metadata.role_name
| rename request.data.common_name as cn
| search "auth.metadata.role_name"="myrole"
| stats count as count1
| appendcols
[ search index="second_index"
| stats count by auth.metadata.role_name
| dedup auth.metadata.role_name
| stats count as count2]
| eval PercenCount=round((count1/count2)*100)

 I see the correct percentage. However, when I select "pie chart" from the viaualization result, I only see one circle with the percentage, instead of seeing two pies. is there a way to make the pie chart show?

cheanaydoo_0-1673470333831.png

I get the expected result when I use filler gauge, but not sure why not for pie?

cheanaydoo_0-1673470469617.png

 

Thanks

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

count1 exists in one event and count2 exists in the other event which is why the calculation doesn't work. You could try

| chart count by index

cheanaydoo
Explorer

Thanks that did the trick

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