Splunk Search

How to calculate Average with condition based?

vineela
Path Finder

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

Labels (1)
Tags (1)
0 Karma

vineela
Path Finder

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 
0 Karma

vineela
Path Finder

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
 
vineela_0-1658313430636.png

 

0 Karma

gcusello
Esteemed Legend

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

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...