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

[at999]

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

[somesoni2 · Accepted solution]

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]

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

[somesoni2]

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?

[at999]

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

[at999]

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

[somesoni2]

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)

[at999]

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

[somesoni2]

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

[at999]

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!

[at999]

Thanks so much