topic How to stats count and still have all fields available afterwards? in Splunk Search

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

Ste — Mon, 20 Jan 2025 16:52:04 GMT

Dear experts

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

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.

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

gcusello — Mon, 20 Jan 2025 17:01:02 GMT

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

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

isoutamo — Mon, 20 Jan 2025 18:01:04 GMT

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.

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

PickleRick — Mon, 20 Jan 2025 20:33:12 GMT

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.

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

isoutamo — Mon, 20 Jan 2025 20:48:20 GMT

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.

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

Ste — Tue, 21 Jan 2025 13:05:58 GMT

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.

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

erikwie — Tue, 21 Jan 2025 13:17:04 GMT

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

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

gcusello — Tue, 21 Jan 2025 13:18:59 GMT

Hi @Ste ,

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

I prefer the other solution.

Ciao.

Giuseppe

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

PickleRick — Wed, 22 Jan 2025 14:40:19 GMT

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).

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

Ste — Thu, 23 Jan 2025 10:21:04 GMT

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.