Splunk Search

How to group over multiple fields for stats?

nmarun
Explorer

Hi,

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

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

nmarun_0-1655385037486.png

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

nmarun_1-1655385094069.png

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

Labels (1)
0 Karma

nmarun
Explorer

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

0 Karma

nmarun
Explorer

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

index=nprod sourcetype="xxxxxx" "Server|Transaction" | rex field=msg "Transaction\|[\"]?(?P<verb>[\w]*)[\"]?\|[\"]?(?P<correlationId>[a-zA-Z0-9-]*)[\"]?\|[\"]?(?P<clientName>[\w]*)[\"]?\|(?P<httpStatus>\d*)\|(?P<timeTaken>[\d.]*)\|[\"]?(?P<clientIP>[\d.]*)[\"]?\|[\"]?(?P<apiMethod>[\w]*)[\"]?\|[\"]?(?P<sourceSystem>[\w]*)[\"]?\|(?:(?P<version>[\d]+)?)" | eval version=coalesce(version, "N/A") | table clientName, apiMethod, sourceSystem, httpStatus, version, timeTaken | stats count as totalCalls avg(timeTaken) as avgTimeTaken by clientName, apiMethod, sourceSystem, httpStatus, version | eval avgTimeTaken=round(avgTimeTaken, 2)

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma

nmarun
Explorer

@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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

somesoni2
Revered Legend

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
0 Karma
Get Updates on the Splunk Community!

Splunk AI Assistant for SPL | Key Use Cases to Unlock the Power of SPL

Splunk AI Assistant for SPL | Key Use Cases to Unlock the Power of SPL  The Splunk AI Assistant for SPL ...

Buttercup Games: Further Dashboarding Techniques (Part 5)

This series of blogs assumes you have already completed the Splunk Enterprise Search Tutorial as it uses the ...

Customers Increasingly Choose Splunk for Observability

For the second year in a row, Splunk was recognized as a Leader in the 2024 Gartner® Magic Quadrant™ for ...