Splunk Search

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

vaibhavvijay9
New Member

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.

Tags (1)
0 Karma

vaibhavvijay9
New Member

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!

0 Karma

chrisyounger
SplunkTrust
SplunkTrust

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!

0 Karma

vaibhavvijay9
New Member

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.

0 Karma
Get Updates on the Splunk Community!

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...