Display Total transactions with overall count and without taking where clause events in consideration.

[Gauri]

I want to display total transactions without where condition in result with other fields which has specific where condition,

for.eg 

| eval
totalResponseTime=round(requestTimeinSec*1000),
| convert num("requestTimeinSec")

| rangemap field="totalResponseTime" "totalResponseTime"=0-3000
| rename range as RangetotalResponseTime
| eval totalResponseTimeabv3sec=round(requestTimeinSec*1000)

| rangemap field="totalResponseTimeabv3sec" "totalResponseTimeabv3sec"=3001-60000
| rename range as RangetotalResponseTimeabv3sec

| eval Product=case(
(like(proxyUri,"URI1") AND like(methodName,"POST"))OR
(like(proxyUri,"URI2") AND like(methodName,"GET"))OR
(like(proxyUri,"URI3") AND like(methodName,"GET")),"ABC")
| bin span=5m _time
| stats count(totalResponseTime) as TotalTrans
count(eval(RangetotalResponseTime="totalResponseTime")) as TS<3S
count(eval(RangetotalResponseTimeabv3sec="totalResponseTimeabv3sec")) as TS>3SS
by Product URI methodName _time

| eval TS<XS=case( Product="ABC",'TS<3S')

| eval TS>3S = 'TotalTrans'-'TS<XS'

| eval SLI=case(Product="ABC",round('TS<3S'/TotalTrans*100,4))

| rename methodName AS Method
| where (Product="ABC") and (SLI<99)

| stats sum(TS>3S) As AvgImpact
count(URI) as DataOutage
by Product URI Method

| fields Product URI Method TotalTrans SLI AvgImpact DataOutage

| sort Product URI Method

[KendallW]

Hi @Gauri you can use "|eventstats" instead of "|stats" to keep the data in the pipeline for the later "|stats" command:

| eval totalResponseTime=round(requestTimeinSec*1000) 
| convert num("requestTimeinSec") 
| rangemap field="totalResponseTime" "totalResponseTime"=0-3000 
| rename range as RangetotalResponseTime 
| eval totalResponseTimeabv3sec=round(requestTimeinSec*1000) 
| rangemap field="totalResponseTimeabv3sec" "totalResponseTimeabv3sec"=3001-60000 
| rename range as RangetotalResponseTimeabv3sec 
| eval Product=case(
    (like(proxyUri,"URI1") AND like(methodName,"POST")) OR
    (like(proxyUri,"URI2") AND like(methodName,"GET")) OR
    (like(proxyUri,"URI3") AND like(methodName,"GET")), "ABC") 
| bin span=5m _time 
| stats count(totalResponseTime) as TotalTrans by Product URI methodName _time 
| eventstats sum(eval(RangetotalResponseTime="totalResponseTime")) as TS<3S by Product URI methodName 
| eventstats sum(eval(RangetotalResponseTimeabv3sec="totalResponseTimeabv3sec")) as TS>3S by Product URI methodName 
| eval SLI=case(Product="ABC", round('TS<3S'/TotalTrans*100,4)) 
| rename methodName AS Method 
| where (Product="ABC") and (SLI<99) 
| stats sum(TS>3S) as AvgImpact count(URI) as DataOutage by Product URI Method 
| fields Product URI Method TotalTrans SLI AvgImpact DataOutage 
| sort Product URI Method

[PickleRick]

And what is the question?

[Gauri]

No value is getting displayed in TotalTrans field when I am running the given query.

[PickleRick]

You can either start from the beginning adding subsequent commands to see when your results stop being what you wanted them to be or from the end - removing commands one by one untill your intermediate results start making sense.

[Gauri]

I am using two stats,

1. 1st stats has some fields filtered by _time 

      | stats count(totalResponseTime) as TotalTrans by Product URI methodName _time

2. 2nd stats has some fields filtered without time

    | stats sum(TS>3S) As AvgImpact
      count(URI) as DataOutage by Product URI Method 

I want the both stats fields to be displayed in the result.

for.eg , | fields TotalTrans Product URI Method AvgImpact DataOutage

How can I achieve this ?

[PickleRick]

OK. You can't do something with the data you already removed in your search pipeline. So you can't do two separate stats commands with different aggregations and different sets of "by" fields. Either rewrite your search to have a more granular set ot the "by" fields (but if you get too many of them you might get too many results) and then later additionally summarize your events (for example using eventstats) or simply use two separate searches.