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!

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...

IM Landing Page Filter - Now Available

We’ve added the capability for you to filter across the summary details on the main Infrastructure Monitoring ...

Dynamic Links from Alerts to IM Navigators - New in Observability Cloud

Splunk continues to improve the troubleshooting experience in Observability Cloud with this latest enhancement ...