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

[Ste]

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. 

[PickleRick]

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.

[Ste]

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.

[PickleRick]

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]

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

[erikwie]

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

[Ste]

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.

[isoutamo]

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.

[gcusello · Accepted solution]

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]

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.