Total and Error count by Provider

[rajkskumar]

I am trying to create a table which has Total number of events and the Error count in the events. The field 'services.errorCode' will be filled if there is an error, it will not be filled if the event is a success.

The below query gives me the correct count of Total, but the Error Count is always 0. I have verified in the Events and there are many events with the field errorCode filled.

index=prod | stats count as "Total", count(eval("services.errorCode"!=null)) as "Error Count" by services.serviceProviderName

Please guide me on how this could be done.

[ITWhisperer]

Try single quotes around the field name

index=prod | stats count as "Total", count(eval('services.errorCode'!=null)) as "Error Count" by services.serviceProviderName

[rajkskumar]

Tried with single quotes, unfortunately, getting the same results.

[ITWhisperer]

Other people have reported problems with eval within counts so you could try

index=prod 
| eval error=if(isnull(services.errorCode),0,1)
| stats count as "Total", sum(error) as "Error Count" by services.serviceProviderName

[rajkskumar]

It was not working. I have put the field name within Double quotes. Now, everything gets counted as error.

index=prod 
| eval error=if(isnull("services.errorCode"),0,1)
| stats count as "Total", sum(error) as "Error Count" by services.serviceProviderName

Instead of isnull, I also tried with checking the length, if it is zero.  That is not working as well. Total Event count and Error count is coming out the same.

[ITWhisperer · Accepted solution]

Putting the fieldname in double quotes just converts it to a string with the name of the field in. Try using single quotes as I suggested earlier

index=prod 
| eval error=if(isnull('services.errorCode'),0,1)
| stats count as "Total", sum(error) as "Error Count" by services.serviceProviderName

Single quotes are often needed for field names with dots in. 

[rajkskumar]

Could you please suggest to me how to add the percentage of Errors against Total events?

index=ladedienst-prod 
| eval errorCount=if(isnull('services.errorCode'),0,1)
| stats count as "Total", sum(errorCount) as "Error Count" by services.serviceProviderName

I have tried with eval before the stats command. But, it was not working. 

[ITWhisperer]

index=ladedienst-prod 
| eval errorCount=if(isnull('services.errorCode'),0,1)
| stats count as "Total", sum(errorCount) as "Error Count" by services.serviceProviderName
| eval percent=round(('Error Count' * 100) / Total,2)

The calculation should be done after the stats command