Splunk Search

How to stats count and still have all fields available afterwards?

Ste
Path Finder

Dear experts

According to the documentation after stats, I have only the fields left used during stats. 

        | table importZeit_uF zbpIdentifier bpKurzName zbpIdentifier_bp status stoerCode
        
        | where stoerCode IN ("K02")
        | stats count as periodCount by zbpIdentifier 
        | sort -periodCount 
        | head 10
        | fields zbpIdentifier zbpIdentifier_bp periodCount importZeit_uF

To explain in detail:

After table the following fields are available: 
importZeit_uF zbpIdentifier bpKurzName zbpIdentifier_bp status stoerCode

After stats count there are only  zbpIdentifier periodCount left.

Question:  How to change the code above to get the count, and have all fields available as before?

Thank you for your support. 

 

Labels (2)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @Ste ,

you have to add to your stats command:

values(*) AS *

in your case:

        | table importZeit_uF zbpIdentifier bpKurzName zbpIdentifier_bp status stoerCode
        
        | where stoerCode IN ("K02")
        | stats count as periodCount values(*) AS * by zbpIdentifier 
        | sort -periodCount 
        | head 10
        | fields zbpIdentifier zbpIdentifier_bp periodCount importZeit_uF

but they are grouped for the zbpIdentifier.

Ciao.

Giuseppe

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

Depends on what the desired outcome looks like. Since stats produces aggregated results you have to ask yourself what is it you really want. If you just want to add some aggregated value to each results row - that's what eventstats is for (be careful with it though because it can be memory-hungry). If you want to get aggregated field values you might use values() or list() as additional aggregation functions.

0 Karma

Ste
Path Finder

Here's what I want to achieve:

We have several hundreds of boxes sending messages. The boxes are identified by the name in zbpIdentifier. 

I want to know the Top ten of the boxes, depending on the number of messages they have sent over a given period of time. 

For this Top ten, I want then to display some more data details, that is why I try to "recover" all the data no more available after stats count.

 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

There are several possible approaches but each of them has its own drawbacks.

The most obvious three are:

1) Use eventstats to add count to events, sort and limit by the count value. (might be memory-intensive as I said earlier)

2) Use subsearch to find the count, then search your whole body of data for those events (if you can't use "fast" commands like tstats for your subsearch you might hit all the subsearch-related problems; also you're effectively digging twice through your whole data set)

3) Add more values() aggregations to your stats listing specific fields (might cause problems with "linking" values from different fields; especially if potentially empty fields are involved).

gcusello
SplunkTrust
SplunkTrust

Hi @Ste ,

with my above solution you can reach your target, otherwise you can use a subsearch (less performant):

 <your_search> [ search  <your_search>
        | where stoerCode IN ("K02")
        | stats count as periodCount by zbpIdentifier 
        | sort -periodCount 
        | head 10
        | fields zbpIdentifier ]
| table importZeit_uF zbpIdentifier bpKurzName zbpIdentifier_bp status stoerCode
        

I prefer the other solution.

Ciao.

Giuseppe

0 Karma

erikwie
Path Finder

Maybe this will give you what you are looking for, use the stats to include all the fields, and if you dont want the count in the table add a fields command after like | fields - periodCount 

| stats count as periodCount by zbpIdentifier zbpIdentifier_bp periodCount importZeit_uF
| sort -periodCount
0 Karma

Ste
Path Finder

I've tried to test this, but it did not work for me.
The whole search was blocked and did not return any data. 
No need to dig in further here, as I had anyway to turn upside down the whole dashboard to solve performance issues. This turning upside down has also solved the issue discussed in here.

 

0 Karma

isoutamo
SplunkTrust
SplunkTrust
As you can check from there https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commandsbytype also this command move actions from indexers to SH side. And as @PickleRick said this command use lot of memory too.
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Ste ,

you have to add to your stats command:

values(*) AS *

in your case:

        | table importZeit_uF zbpIdentifier bpKurzName zbpIdentifier_bp status stoerCode
        
        | where stoerCode IN ("K02")
        | stats count as periodCount values(*) AS * by zbpIdentifier 
        | sort -periodCount 
        | head 10
        | fields zbpIdentifier zbpIdentifier_bp periodCount importZeit_uF

but they are grouped for the zbpIdentifier.

Ciao.

Giuseppe

isoutamo
SplunkTrust
SplunkTrust
One comment: Never use table before stats! After table all processing has moved into SH and it cannot utilize parallel processing with stats. If you want remove some fields before stats use always fields instead of table! You will get more performance that way. Of course after stats you processing continues on SH side, but stats use preprocessing part on each indexers at same time and only merging and final stats processing are done on SH side.
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...