Splunk Search

How to filter success/fail status when using data model

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

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

SplunkTrust
SplunkTrust

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

0 Karma

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

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