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

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

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

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!

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