https://community.splunk.com/t5/Splunk-Search/How-to-filter-success-fail-status-when-using-data-model/m-p/211236#M61759 <P>I want to calculate successRate for a combination of hotelId and useId with data model.<BR /> It works with following query. But the problem is that the query runs really slowly due to the join command.</P> <P>Query: <BR /> |tstats count as Total from datamodel="xxx" by hotelId, useId | join ... [|tstats count AS Failed from datamodel="xxx" where status=false by hotelId, useId|...</P> <P>How can I count the Total and Failed with only one "tstats"? It there any command similar with "count(eval(status=false))"?</P> Wed, 30 Dec 2015 11:08:54 GMT amylala 2015-12-30T11:08:54Z https://community.splunk.com/t5/Splunk-Search/How-to-filter-success-fail-status-when-using-data-model/m-p/211236#M61759 <P>I want to calculate successRate for a combination of hotelId and useId with data model.<BR /> It works with following query. But the problem is that the query runs really slowly due to the join command.</P> <P>Query: <BR /> |tstats count as Total from datamodel="xxx" by hotelId, useId | join ... [|tstats count AS Failed from datamodel="xxx" where status=false by hotelId, useId|...</P> <P>How can I count the Total and Failed with only one "tstats"? It there any command similar with "count(eval(status=false))"?</P> Wed, 30 Dec 2015 11:08:54 GMT https://community.splunk.com/t5/Splunk-Search/How-to-filter-success-fail-status-when-using-data-model/m-p/211236#M61759 amylala 2015-12-30T11:08:54Z https://community.splunk.com/t5/Splunk-Search/How-to-filter-success-fail-status-when-using-data-model/m-p/211237#M61760 <P>Have you tried:</P> <PRE><CODE>| tstats count as Total from datamodel="xxx" by hotelId, useId, status | addtotals </CODE></PRE> <P>[EDITED]</P> <PRE><CODE>| 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 </CODE></PRE> Wed, 30 Dec 2015 11:17:32 GMT https://community.splunk.com/t5/Splunk-Search/How-to-filter-success-fail-status-when-using-data-model/m-p/211237#M61760 javiergn 2015-12-30T11:17:32Z https://community.splunk.com/t5/Splunk-Search/How-to-filter-success-fail-status-when-using-data-model/m-p/211238#M61761 <P>You can try addcoltotals too to see which one works better for you</P> Wed, 30 Dec 2015 11:23:07 GMT https://community.splunk.com/t5/Splunk-Search/How-to-filter-success-fail-status-when-using-data-model/m-p/211238#M61761 javiergn 2015-12-30T11:23:07Z https://community.splunk.com/t5/Splunk-Search/How-to-filter-success-fail-status-when-using-data-model/m-p/211239#M61762 <P>Thanks, javiergn. <BR /> addtotals and addcoltotals do not work for me.</P> <P>What i want is: <BR /> hotelId useId TotalCount FailCount Success%<BR /> 12345 111 100 2 98<BR /> 12345 112 150 100 33.33</P> <P>But here is what i get with addtotals.</P> <P>hotelId useId Status Total<BR /> 12345 111 success 98<BR /> 12345 111 fail 2<BR /> 12345 112 success 50<BR /> 12345 112 fail 100</P> <P>Do you know how to convert this table to the one i expected?</P> Thu, 31 Dec 2015 03:59:28 GMT https://community.splunk.com/t5/Splunk-Search/How-to-filter-success-fail-status-when-using-data-model/m-p/211239#M61762 amylala 2015-12-31T03:59:28Z https://community.splunk.com/t5/Splunk-Search/How-to-filter-success-fail-status-when-using-data-model/m-p/211240#M61763 <P>I see, then try the following:</P> <PRE><CODE>| 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 </CODE></PRE> Thu, 31 Dec 2015 09:38:42 GMT https://community.splunk.com/t5/Splunk-Search/How-to-filter-success-fail-status-when-using-data-model/m-p/211240#M61763 javiergn 2015-12-31T09:38:42Z