topic Re: group over multiple fields for stats in Splunk Search

How to group over multiple fields for stats?

nmarun — Tue, 21 Jun 2022 15:46:15 GMT

Hi,

I'm able to get the response in a tabular format using the command:

table clientName, apiMethod, sourceSystem, httpStatus, version, timeTaken

What I want is to do some aggregation on them and get the result like:

Basically, group by clientName, apiMethod, sourceSystem, httpStatus, and version to get the total calls and the average time.

The below command is clearly misleading:

stats count(clientName) as TotalCalls, avg(timeTaken) as avgTimeTakenS by clientName, apiMethod, sourceSystem, httpStatus, version

Please help.

Thanks,

Arun

Re: group over multiple fields for stats

somesoni2 — Thu, 16 Jun 2022 13:57:37 GMT

Give this a try

Your base search | table clientName, apiMethod, sourceSystem, httpStatus, version, timeTaken | stats count as totalCalls avg(timeTaken) as avgTimeTaken by clientName apiMethod sourceSystem httpStatus version

Re: group over multiple fields for stats

nmarun — Thu, 16 Jun 2022 14:15:50 GMT

No sir, that didn't work. It still gave me a much higher number for the total calls.

Adding more details, this is how the message looks like:

[11:06:04 INF 7166e33f-bde7-49dd-aa72-523d8e7501a9] Server|Transaction|POST|aa72-523d8e7501a9|c1|200|0.2|127.0.0.1|GetProduct|ElasticSearch|3|/v1/getproduct

The version is an optional field so I'm coalescing it to 'NA' when it's not present.

Re: group over multiple fields for stats

ITWhisperer — Thu, 16 Jun 2022 14:55:43 GMT

Given that clientIP and correlationId both could be zero length, they could still appear in your numbers - could this account for the discrepancy?

If not, perhaps if you could reduce your search so you only have a small number of events with a discrepancy, and look at the actual events that are being included in the count to see if you can spot a pattern.

Re: group over multiple fields for stats

nmarun — Thu, 16 Jun 2022 14:59:59 GMT

@ITWhisperer, neither of those fields can be zero length, but I'll try your suggestion of limiting the fields and seeing which one breaks it.

Thanks,

Arun

Re: group over multiple fields for stats

ITWhisperer — Thu, 16 Jun 2022 15:10:53 GMT

If they can't be zero length, use "+" instead of "*" - "+" means 1 or more, "*" means zero or more

Re: group over multiple fields for stats

nmarun — Mon, 20 Jun 2022 06:29:51 GMT

I've sent these details to our internal Splunk team and am waiting for their response.

In the meantime, I can accept both your answers as the solution since you took the time to provide some assistance, or can wait till I hear back from my team.

Please advise.

Thanks,

Arun