Splunk Search

How do I calculate the percentage of one row against another in a stats?

arist0telis
Explorer

I'm working with a table of conversation data, all conversations start out as a bot chat and can be escalated to a human agent. The ConversationId remains persistent through the escalation.

Each ConversationEntry is a message, inbound or outbound, in a MessagingSession.
ConversationId is the MessagingSession parent to the individual entries in/out
All MessagingSessions I'm looking at will have an EventType=ChatbotEstablished, not all will have an EventType=BotEscalated.

I can't figure out how to calculate the percentage of conversations that had an escalation. Below is my query and a stats output. I'm trying to figure out how I get BotEscalated/ChatbotEstablished.

index=sfdc sourcetype=sfdc:conversationentry EntryType IN ("ChatbotEstablished", "BotEscalated")
| stats count(ConversationId) as EntryCount by EntryType

EntryType EntryCount

BotEscalated3
ChatbotEstablished10
Labels (1)
0 Karma

efavreau
Motivator

HI@arist0telis !

A percentage is number of escalations out of the total established, times 100. Or with more math notation:
(BotEscalated/ChatbotEstablished) x 100 = Percentage Escalated

So we convert that to eval statements. I haven't tested it below, but it should be pretty close.

index=sfdc sourcetype=sfdc:conversationentry EntryType IN ("ChatbotEstablished", "BotEscalated")
| stats count(eval(EntryType=='BotEscalated')) as "BEcount", count(eval(EntryType=='ChatbotEstablished')) as "CEcount" ``` get the counts```
| eval mypercentage = round(('BEcount'/'CEcount')*100, 2) ```get the percentage and round to 2 places```
###

If this reply helps you, an upvote would be appreciated.
0 Karma

arist0telis
Explorer

I think I may have figured it out myself, just had to take a step away for a minute. Pasting what I got here in case this comes up in a Google search and someone else needs help.

index=sfdc sourcetype=sfdc:conversationentry EntryType IN ("ChatbotEstablished", "BotEscalated")
| stats count(eval(if(EntryType="ChatbotEstablished",1,null()))) as ChatCount count(eval(if(EntryType="BotEscalated",1,null()))) as EscalationCount by ConversationId
| stats sum(ChatCount) as sumChat sum(EscalationCount) as sumEscalation
| eval pctEscalation=round(((sumEscalation/sumChat)*100),2)
| table sumChat, sumEscalation, pctEscalation

Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...