Splunk Search

How to filter success/fail status when using data model

amylala
Explorer

I want to calculate successRate for a combination of hotelId and useId with data model.
It works with following query. But the problem is that the query runs really slowly due to the join command.

Query:
|tstats count as Total from datamodel="xxx" by hotelId, useId | join ... [|tstats count AS Failed from datamodel="xxx" where status=false by hotelId, useId|...

How can I count the Total and Failed with only one "tstats"? It there any command similar with "count(eval(status=false))"?

Tags (2)
0 Karma

javiergn
Super Champion

Have you tried:

| tstats count as Total from datamodel="xxx" by hotelId, useId, status 
| addtotals

[EDITED]

| tstats count as Total from datamodel="xxx" by hotelId, useId, status 
| eval Fail = if(Status="fail", Total, 0)
| stats sum(Total) as TotalCount, sum(Fail) as FailCount by hotelId, useId
| eval SuccessRate = (FailCount/TotalCount)*100 
0 Karma

javiergn
Super Champion

You can try addcoltotals too to see which one works better for you

0 Karma

amylala
Explorer

Thanks, javiergn.
addtotals and addcoltotals do not work for me.

What i want is:
hotelId useId TotalCount FailCount Success%
12345 111 100 2 98
12345 112 150 100 33.33

But here is what i get with addtotals.

hotelId useId Status Total
12345 111 success 98
12345 111 fail 2
12345 112 success 50
12345 112 fail 100

Do you know how to convert this table to the one i expected?

0 Karma

javiergn
Super Champion

I see, then try the following:

| tstats count as Total from datamodel="xxx" by hotelId, useId, status 
| eval Fail = if(Status="fail", Total, 0)
| stats sum(Total) as TotalCount, sum(Fail) as FailCount by hotelId, useId
| eval SuccessRate = (FailCount/TotalCount)*100 
0 Karma
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 ...