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!

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...

New in Observability Cloud - Explicit Bucket Histograms

Splunk introduces native support for histograms as a metric data type within Observability Cloud with Explicit ...