STATS COUNT on same field before and after WHERE / Condition?

[vaibhavvijay9]

Hi All,

What I want is :

1. Total no. of queues and total no. of queues with pending messages.
   Something like this :

   .... | xmlkv | table _time, qName, pendingMsgs | stats count(qName) as totalQueues | stats count(qName) where pendingMsgs > 0 as queuesWithPendingMessages
   I want to achieve the working of last pipe and also save the values to calculate the percentage.

Or Output to be like this :

totalQueues = 15
queuesWithPendingMessages = 4
Percentage (queuesWithPendingMessages / totalQueues) x 100 = 26.66

Please help.

Thanks in advance.

[vaibhavvijay9]

Got it!
Here is the solution :

.... | xmlkv | table _time, qName, pendingMsgs | stats count(qName) as totalQueues, count(eval(pendingMsgs > 0)) as queuesWithPendingMessages | eval percentage=(queuesWithPendingMessages/totalQueues)*100

But is it fully (logically) correct? Please share your views.

Awaiting your response.
Thanks!

[chrisyounger]

Try this:

.... 
| xmlkv 
| table _time, qName, pendingMsgs 
| eval hasPending = (pendingMsgs > 0,1,0)
| stats count(qName) as totalQueues sum(hasPending) as queuesWithPendingMessages
| eval percentage = (queuesWithPendingMessages / totalQueues) * 100

Hope it helps!

[vaibhavvijay9]

Hi @chrisyoungerjds,

Thanks a lot for the quick response!

Your provided string is displaying error as :

Error in 'eval' command: The expression is malformed. Expected ).

However, I have got the solution while trying it by myself, and I have posted it here as answer.

But is it fully (logically) correct? Please share your views.

Awaiting your response.
Thanks again.