How to group over multiple fields for stats?

[nmarun]

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

[nmarun]

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

[nmarun]

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.

[ITWhisperer]

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.

[nmarun]

@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

[ITWhisperer]

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

[somesoni2]

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