How to calculate Average with condition based?

[vineela]

Hi All,

i am writing a query with the following:

index=a0_payservutil_generic_app_audit_npd "kubernetes.labels.release"="mms-au" MNDT|rex field=log "eventName=\"*(?<eventName>[^,\"\s]+)"|rex field=log "serviceName=\"*(?<serviceName>[^\"]+)"|rex field=log "elapsed=\"*(?<elapsed>[^,\"\s]+)" |search eventName="ACCOUNT_DETAIL" AND serviceName="Alias " | eval newtime=round((elapsed/1000),2)|stats count(newtime) AS TotalNoOfEvents,count(eval(newtime>=1)) AS SLACrossedEvents|eval perc=((SLACrossedEvents/TotalNoOfEvents)*100)|where perc>1|stats avg(newtime) as avg |eval calc=if(newtime>=1,avg,0)|eval eventName="ACCOUNT_DETAIL"|eval serviceName="Alias"|fields eventName serviceName TotalNoOfEvents SLACrossedEvents perc calc

i need to calculate average time of events which crossed SLA, 
for ex: if 2 events crossed SLA (elapsedtime is greater than 1 sec)..in that one event took 3 sec and another event took 2 seconds then we should display 2.5 as average in particular time.i am not able to fetch it.Can you please help me on the same.

Thanks in advance

[vineela]

HI Sir,

Error in 'stats' command: The eval expression for dynamic field 'eval(if(newtime>=1),newtime,"")' is invalid. Error='The operator at ',newtime,""' is invalid.'.

The search job has failed due to an error. You may be able view the job in the 

[vineela]

I am receiving error like this:

Error in 'stats' command: The eval expression for dynamic field 'eval(if(newtime>=1),newtime,"")' is invalid. Error='The operator at ',newtime,""' is invalid.'.

The search job has failed due to an error. You may be able view the job in the

[gcusello]

HI @vineela,

you have to add to your stats command other values using eval,if, something like this:

index=a0_payservutil_generic_app_audit_npd "kubernetes.labels.release"="mms-au" MNDT
| rex field=log "eventName=\"*(?<eventName>[^,\"\s]+)"
| rex field=log "serviceName=\"*(?<serviceName>[^\"]+)"
| rex field=log "elapsed=\"*(?<elapsed>[^,\"\s]+)" 
| search eventName="ACCOUNT_DETAIL" AND serviceName="Alias " 
| eval newtime=round((elapsed/1000),2)
| stats 
   count(newtime) AS TotalNoOfEvents
   count(eval(newtime>=1)) AS SLACrossedEvents
   avg(eval(if(newtime>=1),newtime,"")) AS average
| eval perc=((SLACrossedEvents/TotalNoOfEvents)*100)
| where perc>1
| stats avg(newtime) as avg values(average9 AS average
| eval calc=if(newtime>=1,avg,0)
| eval eventName="ACCOUNT_DETAIL"
| eval serviceName="Alias"
| fields eventName serviceName TotalNoOfEvents SLACrossedEvents perc calc average

See how to use this approach.

Ciao.

Giuseppe