use stats within join

[anjo5]

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!

[somesoni2 · Accepted solution]

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

[anjo5]

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

[chrisyounger]

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!

[Vijeta]

you ca use append before your third query