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

[benj851]

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. 

[benj851]

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

[ITWhisperer · Accepted solution]

| 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

[ITWhisperer]

-- 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

[ITWhisperer]

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.)