topic Re: Count one column, sum another, display average on a third then display group by columns in Splunk Search

Count one column, sum another, display average on a third then display group by columns

benj851 — Mon, 19 Oct 2020 15:23:51 GMT

Hello;

I'm a bit stuck and looking for assistance.

Base query returns the following values: Brand SystemId ResponseStatus Amount UniqueIdentifier

I would like to perform work to make the query return stats on top of it:
1. Count the UniqueIdentifier in a new column.
2. Sum the Amount grouping by Brand SystemId ResponseStatus
3. Percent of Total Sum(Amount) by Brand SystemId ResponseStatus
4. Percent of Total count(UniqueIdentifier) by Brand SystemId ResponseStatus

Then I would like the final result to appear like:

1.2.3.4, then Brand SystemId ResponseStatus --> to demonstrate the groupings.

In splunk I know I can perform counts, and sum, using a grouping, then sum(counts) by, and create averages. What I can't determine is how to line them all together in a single resultset. Further, if I try to do stats on multiple columns and set it to a new column, it won't accept it (or doing it wrong)

I would appreciate any pointers.

Re: Count one column, sum another, display average on a third then display group by columns

ITWhisperer — Mon, 19 Oct 2020 16:14:38 GMT

Can you provide some sample data that could be returned by your basic query and how that might translate into your final result?

Is 1 a count of uniqueids grouping by Brand SystemId ResponseStatus as well?

Does each event have a unique identifier? (That may sound obvious but needs confirming.)

Re: Count one column, sum another, display average on a third then display group by columns

ITWhisperer — Mon, 19 Oct 2020 16:20:59 GMT

-- base query | stats count sum(Amount) as Amount by Brand SystemId ResponseStatus | eventstats sum(Amount) as TotalAmount sum(count) as Total | eval PercentAmount=100*Amount/TotalAmount | eval PercentCount=100*count/Total | fields count, Amount, PercentAmount, PercentCount, Brand, SystemId, ResponseStatus

Re: Count one column, sum another, display average on a third then display group by columns

benj851 — Mon, 19 Oct 2020 16:49:20 GMT

Type	SystemId	ResponseStatus	ApprovedAmount	TRI
V	System1	Good	34.93	UniqueID1
A	System2	Good	11.94	UniqueID2
V	System3	Good	6.06	UniqueID3
V	System2	Bad	13.44	UniqueID4
A	System2	Good	11.94	UniqueID5
V	System3	Good	6.06	UniqueID6
M	System3	Good	1	UniqueId7

Re: Count one column, sum another, display average on a third then display group by columns

ITWhisperer — Mon, 19 Oct 2020 17:11:33 GMT

| makeresults | eval events="V System1 Good 34.93 UniqueID1 A System2 Good 11.94 UniqueID2 V System3 Good 6.06 UniqueID3 V System2 Bad 13.44 UniqueID4 A System2 Good 11.94 UniqueID5 V System3 Good 6.06 UniqueID6 M System3 Good 1 UniqueId7" | rex field=events max_match=0 "(?<events>[^\r\n]+)" | mvexpand events | rex field=events max_match=0 "(?<Brand>\S+)\s+(?<SystemId>\S+)\s+(?<ResponseStatus>\S+)\s+(?<Amount>\S+)\s+(?<TRI>\S+)" | stats count sum(Amount) as Amount by Brand SystemId ResponseStatus | eventstats sum(Amount) as TotalAmount sum(count) as Total | eval PercentAmount=100*Amount/TotalAmount | eval PercentCount=100*count/Total | fields count, Amount, PercentAmount, PercentCount, Brand, SystemId, ResponseStatus