Splunk Search

How do I take the ratio of (sum of 2 categories) / (total categories)?

at999
New Member

Though this question seems similar to the other discussions, I'm having a hard time relating to them.

A network service has sites across the country in different cities. For example if there are 2 sites ( lets say seattle and woburn) I want to compare the total number of unique users from these two cities with the rest of the cities. So far, I'm only able to get the sum of the number of users from (woburn and seattle). Here's my search:

index=[] sourcetype=[] shn=$[]$ tier=[] | eval overseas_site=case(site=="us-ma-woburn" , "Overseas", site=="us-wa-seattle", "Overseas") | stats dc(chi) AS "OverseasUsers" by overseas_site

Currently this is only populating the total # of unique users from woburn and seattle. My goal is to maybe create another bucket that will store the total # of users from all the other sites. Then get the ratio of (users from seatle/woburn) to (rest of the cities). Basically trying to get the traffic ratio. Hope that makes sense and any help is appreciated! Thanks

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

index=[] sourcetype=[] shn=$[]$ tier=[] | eval Site=if(site="us-ma-woburn" OR site="us-wa-seattle", "Overseas","Others") | stats dc(chi) AS "OverseasUsers" by Site

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Give this a try

index=[] sourcetype=[] shn=$[]$ tier=[] | eval Site=if(site="us-ma-woburn" OR site="us-wa-seattle", "Overseas","Others") | stats dc(chi) AS "OverseasUsers" by Site

at999
New Member

That's helpful however, I think now its giving me the total of the "others" sites. Before, I was getting total users from just seattle and woburn.
I want to find out ((users from seattle + woburn) / users from "others"). Does that make sense? Sorry I should've been more clearer.

/= divided by

0 Karma

somesoni2
SplunkTrust
SplunkTrust

You should get two rows, one with Site=Overseas and OverseasUsers ( I should've renamed that column) as unique users from woburn and seattle., and other with Site=Others and corresponding unique users count. Are you not getting that?

0 Karma

at999
New Member

No its just displaying the total number of users for "others" 😕

0 Karma

at999
New Member

Oh got that to work!! Thank you.
Going back to the original problem, now that we have two different values (one for others and one for (overseas). Any idea how I can divide overseas by others to get a value?

Appreciate all your help

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Use like this

  index=[] sourcetype=[] shn=$[]$ tier=[] | eval Site=if(site="us-ma-woburn" OR site="us-wa-seattle", "Overseas","Others") | stats dc(chi) AS Users by Site | eval t=1 | chart values(Users) over t by Site | eval Ratio=round(Overseas/Others,2)
0 Karma

at999
New Member

Hey so the search you provided did give me some sort of ratio, however, I'm not sure if that's correct. It gave me a ratio of 1.
I want to display a single value on the dashboard that shows the ratio (i.e: 4.5 or something). I played around and came up with this search, but I'm certain it's wrong.

index=index_ats sourcetype=custom_ats_2 shn=$shn_token$ tier=EDGE | eval site_type=if(site=="us-ma-woburn" OR site=="us-wa-seattle" OR site=="us-fl-naples",  "Overseas", "Domestic") | stats dc(chi) AS Users  | chart values(site_ratio) | eval site_ratio = (Overseas/(Overseas+Domestic)*100)

I don't want to display how many users by site_type. Just want to know the ratio( a single value).

Hope that makes sense

0 Karma

somesoni2
SplunkTrust
SplunkTrust

You need to use site_type till you calculate ratio and then remove it from display using table command.

index=index_ats sourcetype=custom_ats_2 shn=$shn_token$ tier=EDGE | eval site_type=if(site=="us-ma-woburn" OR site=="us-wa-seattle" OR site=="us-fl-naples", "Overseas", "Domestic") | stats dc(chi) as Users by site_type | eval t=1 | chart values(Users) over t by site_type | eval site_ratio = (Overseas/(Overseas+Domestic)*100) | table site_ratio
0 Karma

at999
New Member

Thank you!! I tried to award points to you but unable to do so. Probably because I just joined this forum.
have a nice day!

0 Karma

at999
New Member

Thanks so much

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