Splunk Search

Joining two events by common field

jl19
Explorer

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.

Tags (1)
0 Karma
1 Solution

knielsen
Contributor

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

View solution in original post

woodcock
Esteemed Legend

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

jl19
Explorer

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!

0 Karma

knielsen
Contributor

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

jl19
Explorer

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 🙂

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...