index=sample(Consumer="prod") ServiceName="product.services.prd.*"
| stats count(eval(HTTPStatus >= 400 AND HTTPStatus < 500)) AS 'fourxxErrors', count(eval(HTTPStatus >= 500 AND HTTPStatus < 600)) AS 'fivexxErrors', count AS 'TotalRequests'
| eval 'fourxxPercentage' = if('TotalRequests' > 0, ('fourxxErrors' / 'TotalRequests') * 100, 0), 'fivexxPercentage' = if('TotalRequests' > 0, ('fivexxErrors' / 'TotalRequests') * 100, 0)
| table "fourxxPercentage", "fivexxPercentage"
The result is showing as 0 for both fields inside the table "fourxxPercentage", "fivexxPercentage". Actually, fourxxErrors and fivexxErrors count are greater than 0. Is that because it's not showing the decimal values?
I don't have any 500's in my _internal index (this is not a flex...just a fresh install before I have had a chance to break anything). So this is what my results look like:
Maybe for the timerange you don't have any 5xx errors? If I flub the query a little more in my environment and change the boolean criteria a bit in the SPL to be >=300<400 (see highlighted section) then it works correctly for me:
Remove all the ticks/quotes from the field names in your SPL - for what you are doing they aren't necessary. I only quote my field names if they have any negative space characters. Once I did that I was able to "fake" your search in my environment and get results:
index=_internal status=*
| rename status AS HTTPStatus
| stats count(eval(HTTPStatus >= 400 AND HTTPStatus < 500)) AS fourxxErrors, count(eval(HTTPStatus >= 500 AND HTTPStatus < 600)) AS fivexxErrors, count AS TotalRequests
| eval fourxxPercentage = if(TotalRequests > 0, (fourxxErrors / TotalRequests) * 100, 0), fivexxPercentage = if(TotalRequests > 0, (fivexxErrors / TotalRequests) * 100, 0)
| table fourxxPercentage, fivexxPercentage
Also, I just added this at the beginning to turn misc data in _internal into events that "look" like yours for this example purpose:
index=_internal status=*
| rename status AS HTTPStatus
@_JP is correct that single quotes have special meaning in SPL. Have you tested
index=_internal status=*
| rename status AS HTTPStatus
| stats count(eval(HTTPStatus >= 400 AND HTTPStatus < 500)) AS fourxxErrors, count(eval(HTTPStatus >= 500 AND HTTPStatus < 600)) AS fivexxErrors, count AS TotalRequests
Can you share sample output from this?
@_JP Hi, the query which you shared me works same like the one which I shared. The percentage values are showing 0. Is it because we need to add decimal values after 0. I tried adding
(fourxxErrors / TotalRequests) * 100, 2)
instead of
(fourxxErrors / TotalRequests) * 100, 0)
But no use. Do you have any other idea?