How do you count the number of occurrences and compare with the total?

[tsushi]

I have some call logs that I want to sort.

I have fields for remote_alias and disconnect_reason. There are many different disconnect reasons, but I want to see how many calls have disconnect_reason="Dialog has failed" as compared to the total, and then sort it by remote_alias; then, we could see how many times a user has had this reason code compared to the number of times they have called.

host="Pexip Participant History" local_alias="*video.difi.no*" protocol="MSSIP" | stats list(disconnect_reason) by remote_alias

If i run this command, then I'll see results in a list like this:

michal here has 9 events, where 1 of them is "dialog has failed". Is there any way to get this all in two columns? One for the total and the other for number of "dialog has failed" occurrences pr remote alias ?

[DMohn]

This should give you the desired results:

host="Pexip Participant History" local_alias="*video.difi.no*" protocol="MSSIP" | stats list(disconnect_reason) as reasons count(eval(disconnect_reason=="Dialog has failed")) as failed_diag count(*) as total by remote_alias | eval perc_failed=(failed_diag/total*100) 

If you want to hide one or more of the calculated fields you can always exclude them with fields -fieldname

[harsmarvania57 · Accepted solution]

Hi,

Try this

host="Pexip Participant History" local_alias="*video.difi.no*" protocol="MSSIP" | stats list(disconnect_reason), count as Total_Count, sum(eval(if(disconnect_reason="dialog has failed",1,0))) as sum_dialog_failed by remote_alias

[tsushi]

Awe heck, that was fast. And it works perfectly as well. Thank you !

[harsmarvania57]

Welcome 🙂