topic Re: Total and Error count by Provider in Splunk Search

Total and Error count by Provider

rajkskumar — Mon, 28 Sep 2020 08:52:33 GMT

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.

Re: Total and Error count by Provider

ITWhisperer — Mon, 28 Sep 2020 08:58:02 GMT

Try single quotes around the field name

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

Re: Total and Error count by Provider

rajkskumar — Mon, 28 Sep 2020 09:05:52 GMT

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

Re: Total and Error count by Provider

ITWhisperer — Mon, 28 Sep 2020 09:12:41 GMT

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

Re: Total and Error count by Provider

rajkskumar — Mon, 28 Sep 2020 11:44:07 GMT

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.

Re: Total and Error count by Provider

ITWhisperer — Mon, 28 Sep 2020 11:48:40 GMT

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.

Re: Total and Error count by Provider

rajkskumar — Mon, 28 Sep 2020 12:14:40 GMT

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.

Re: Total and Error count by Provider

ITWhisperer — Mon, 28 Sep 2020 12:23:41 GMT

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