Hi,
I've written a query (see original query below) which joins 3 different event types to display A_events started during the selected date range. The A_events are selected using the timepicker and these are left joined via the event_id to return the B_events, returning the B_time, along with the packet_size. Finally, these are left joined using the device_id to the C_events to return the contextual data.
I'm having a number of issues here:
Using joins is too slow hence why I have played around with stats (see first stats query).
There is a limit on the number of events returned by a sub-search (50,000 events) so my C_events are being truncated.
My first attempt at writing a stats based query is faster than joins but because the event selection criteria has been moved to the top level of the query removing the need for the first join I don't seem to be able to have different time periods for each of the event types e.g. (event="A" timepicker based) OR (event="B" timepicker value + 2 days)
The other issue about having all the event selection at the top level is the C_events can occur anytime during the last 2 years and there are likely to be multiple events per device_id of which I need to return the most recent, hence the dedup on the second subsearch.
I have tried to use an append to apply different timescales to the different event types but this too runs very slowly and there is a limit of 50,000 events which are being truncated.
Finally, I am struggling with stats because the fields used to join the sub-queries are different. The A and B events have device_id and event_id as common fields where as the C events only have device_id
I did try a second stats query but I ended up with listing multiple event_ids within the same row as they had the same device_id.
Question
How would you advise I tackle this query based upon having to join 3 different event types which require 3 different time scales and are at different levels of granularity?
Any help would be greatly received as I have been trying to do this for several weeks now and am completed stumped!!
Thanks
Original Query
index=50 (event="A")
| fields device_id, event_id, _time
| eval A_time=strftime(_time,"%Y/%m/%d %H:%M:%S")
| join event_id type=left [search index=50 earliest=1 event="B"
| fields device_id, event_id, _time, packet_size
| eval B_time=strftime(_time,"%Y/%m/%d %H:%M:%S")]
| join device_id type=left [search index=50 earliest=1 event="C"
| dedup device_id sortby -_time
| fields device_id, contextual_data]
| eval status=case(
isnull(A_time),"Delete",
isnull(B_time),"Open",
1=1,"Closed"
)
| table device_id, event_id, A_time, B_time, packet_size, status, contextual_data
First Stats Query
index=50 (event="A") OR (event="B")
| dedup event, device_id, event_id sortby -_time
| eval A_time=if(event="A",_time,"")
| eval B_time=if(event="B",_time,"")
| eval packet_size=case(
event="A","",
event="B",packet_size
)
| stats values(A_time) AS A_time, values(B_time) as B_time, values(packet_size) AS packet_size by device_id, event_id
| convert num(A_time) AS A_time, num(B_time) AS B_time, num(packet_size) AS packet_size
| eval A_time=strftime(A_time,"%Y/%m/%d %H:%M:%S")
| eval B_time=strftime(B_time,"%Y/%m/%d %H:%M:%S")
| eval status=case(
isnull(A_time),"Delete",
isnull(B_time),"Open",
1=1,"Closed"
)
Desired Output
... View more