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