I'm trying to sum a count from one event and group all of these summations by another events unique ID. The two events share a common field.
Example
EventA
- EvID: 1
- ReqID: 123
EventA
- EvID: 1
- ReqID: 456
EventB
- ReqID: 123
- Count1: 5
- Count2: 10
EventB
- ReqID: 456
- Count1: 10
- Count2: 15
The output I'm trying to get is:
EvID | Count1 | Count2
1 | 15 | 25
Any help would be appreciated. I'm new to Splunk and this one is baffling me.
I'd like to see at least one more EvID to be sure I do the right thing, but this gives me the expected result on that minimalistic data set:
<your search base> EventB | stats sum(Count1) as Count1 sum(Count2) as Count2 by ReqID | appendcols [<your search base> EventA | table EvID, ReqID] | stats sum(Count1) as Count1 sum(Count2) as Count2 by EvID
Try it on a larger data set, if it gives the expected result. 🙂
Actually, I this should do the trick as well:
<your search base> EventA OR EventB | stats values(EvID) as EvID sum(Count1) as Count1 sum(Count2) as Count2 by ReqID | stats sum(Count1) as Count1 sum(Count2) as Count2 by EvID
This should give you enough to work out a final solution:
|makeresults| eval raw="EventA,1,123::EventA,1,456::EventB,123,5,10::EventB,456,10,15"
| makemv delim="::" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<which>\w+),(?<EvID>\d+),(?<ReqID>\d+)$"
| rex "^(?<which>\w+),(?<ReqID>\d+),(?<Count1>\d+),(?<Count2>\d+)$"
| fields - _time
| rename COMMENT AS "Everything above generates sample event data (not the same as yours); everything below is your solution (sort of)"
| stats values(ReqID) AS ReqID sum(Count*) AS Count* values(EvID) AS EvID BY which
| nomv ReqID
| stats values(*) AS * BY ReqID
Thanks for the reply, woodcock. This one took some finagling but also worked. I've already accepted knielsen's answer and I think it will fit a bit more with what I'm going for, but I appreciate you jumping in to help!
I'd like to see at least one more EvID to be sure I do the right thing, but this gives me the expected result on that minimalistic data set:
<your search base> EventB | stats sum(Count1) as Count1 sum(Count2) as Count2 by ReqID | appendcols [<your search base> EventA | table EvID, ReqID] | stats sum(Count1) as Count1 sum(Count2) as Count2 by EvID
Try it on a larger data set, if it gives the expected result. 🙂
Actually, I this should do the trick as well:
<your search base> EventA OR EventB | stats values(EvID) as EvID sum(Count1) as Count1 sum(Count2) as Count2 by ReqID | stats sum(Count1) as Count1 sum(Count2) as Count2 by EvID
Both of these worked perfectly! Thank you so much for your help. The solution looks so simple too, wish I hadn't spent so much time trying other routes 🙂