topic Re: STATS COUNT on same field before and after WHERE / Condition? in Splunk Search

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

vaibhavvijay9 — Thu, 14 Feb 2019 19:15:47 GMT

Hi All,

What I want is :

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.

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

chrisyounger — Thu, 14 Feb 2019 19:24:01 GMT

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!

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

vaibhavvijay9 — Thu, 14 Feb 2019 19:26:18 GMT

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!

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

vaibhavvijay9 — Thu, 14 Feb 2019 19:38:56 GMT

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.