Splunk Search

use stats within join

New Member

I have been struggling with creating a proper query for the last hour, but I fail to understand how to achieve what I need, so hopefully you can help me out.

I want to make a combination from 3 different source types, all having '*.OrderId' as field on which they should be joined.

  • From sourcetype A, I want to obtain MessageTimeStamp as start time.
  • From sourcetype B, I want to obtain MessageTimeStamp as end time.
  • From sourcetype C, I want to count the number of messages which occurred having a given OrderId.

I want to report this in a table like this:

OrderId | start time | end time | count(sourcetype C)

To join start and endtime, I already have the following

index=* sourcetype=A | `Renaming`  | join type=outer OrderId 
[ search index=* sourcetype=B
| `Renaming` 
| eval "B.MessageTimeStamp"=MessageTimeStamp] | join type=outer OrderId 
[ search index=* sourcetype=A
| `Renaming` 
| eval "A.MessageTimeStamp"=MessageTimeStamp] | rename A.MessageTimeStamp as Started B.MessageTimeStamp as Finished | table OrderId  Started Finished

And for the count, i have this:

index=* sourcetype=C | stats count by OrderId

So in both separate queries, the OrderId is present. So how can I combine these 2 separate queries into a single one?

Thanks a lot in advance!

Tags (2)
0 Karma
1 Solution

Revered Legend

I believe you don't need the join at all. Give this query a try

index=* (sourcetype=A OR sourcetype=B OR sourcetype=C)
| `Renaming`
| eval Start=if(sourcetype="A",MessageTimeStamp,null())
| eval Finish=if(sourcetype="B",MessageTimeStamp,null())
| stats values(Start) as Start values(Finish) as Finish count(eval(sourcetype="C")) as count by OrderId

View solution in original post

0 Karma

Revered Legend

I believe you don't need the join at all. Give this query a try

index=* (sourcetype=A OR sourcetype=B OR sourcetype=C)
| `Renaming`
| eval Start=if(sourcetype="A",MessageTimeStamp,null())
| eval Finish=if(sourcetype="B",MessageTimeStamp,null())
| stats values(Start) as Start values(Finish) as Finish count(eval(sourcetype="C")) as count by OrderId

View solution in original post

0 Karma

New Member

Awesome!
Works indeed exactly as expected, and I like that this solution is indeed so much more simple than what I was trying. Thanks.

0 Karma

SplunkTrust
SplunkTrust

Hi @anjo5

Try this:

index=* sourcetype=B 
| `Renaming` 
| eval Finished = MessageTimeStamp 
| fields Finished OrderId 
| append 
    [ search index=* sourcetype=A 
    | `Renaming` 
    | eval Started = MessageTimeStamp 
    | fields Started OrderId] 
| append 
    [ search index=* sourcetype=C 
    | stats count as count by OrderId 
    | fields OrderId count] 
| stats values(Started) as Started values(Finished) as Finished by OrderId

Hope it helps!

0 Karma

Influencer

you ca use append before your third query

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!