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.
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!
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
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
Awesome!
Works indeed exactly as expected, and I like that this solution is indeed so much more simple than what I was trying. Thanks.
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!
you ca use append before your third query