Calculate the number of events that occur between two other time values in each event.

[ALXWBR]

Really struggling with this one, so looking for a hero to come along with a solution!

I have an index of flight data. Each departing flight has a timestamp for when the pilot calls up to the control tower to request to push back, this field is called ASRT (Actual Start Request Time). Each flight also has a time that it uses the runway, this is called ATOT_ALDT (Actual Take Off Time/Actual Landing Time). What I really need to calculate, is for each departing flight, how many over flights used the runway (had an ATOT_ALDT) between when the flight calls up (ASRT) and then uses the runway itself (ATOT_ALDT). This is to work out what the runway queue was like for each departing aircraft.

I have tried using the concurrency command, however, this doesn't return the desired results as it only shows the number flights that started before and not the ones that started after. We may have a situation where an aircraft calls up after one before but then departs before. And this doesn't capture that.

So I've found an approach that in theory should work. I ran an eventstats that lists the take off/landing time of every flight, so then I can mvexpand that and run an eval across each line. However, multi-value fields have a limit of 100, and there can be up to 275 flights in the time period I need to check.

Can anyone else think of a way of achieving this? My code is below:

REC_UPD_TM = the time the record was updated (this index uses the flights scheduled departure time as _time, so we need to find the latest record for each flight)

displayed_flyt_no = The flight number e.g EZY1234

DepOrArr = Was the flight a departure or an arrival.

index=flights
| eval _time = strptime(REC_UPD_TM."Z","%Y-%m-%d %H:%M:%S%Z") 
| dedup AODBUniqueField sortby - _time 
| fields AODBUniqueField DepOrArr displayed_flyt_no ASRT ATOT_ALDT
| sort ATOT_ALDT 
| where isnotnull(ATOT_ALDT) 
| eval 
    asrt_epoch = strptime(ASRT,"%Y-%m-%d %H:%M:%S"),
    runway_epoch = strptime(ATOT_ALDT,"%Y-%m-%d %H:%M:%S")
| table DepOrArr displayed_flyt_no ASRT asrt_epoch ATOT_ALDT runway_epoch  
| eventstats list(runway_epoch) as runway_usage 
| search DepOrArr="D" 
| mvexpand runway_usage 
| eval queue = if(runway_usage>asrt_epoch AND runway_usage<runway_epoch,1,0) 
| stats sum(queue) as queue by displayed_flyt_no 

[ALXWBR]

Lets take flight16 in my example. It had an ASRT timestamp of 06:19 and an ATOT_ALDT of 06:32. Flight15 had an ATOT_ALDT of 06:19, flight14 06:29 and flight15 06:31, so that's 3 flights that used the runway between the ASRT and ATOT of flight16.

[PickleRick]

I think I'd approach it from a completely different side. I wouldn't try to track down single ATOT_ALDT occurrences. Just count how many ATOT_ALDT occured before "your" ASRT and then check how many were at the time of your ATOT_ALDT. Depending on whether you include your own ATOT_ALDT in that count or not you might need to correct the result by one but the answer to your question would be simply count@ATOT_ALDT-count@ASRT.

So use the streamstats to count ATOT_ALDTs, then use stats/eventsats or even transaction to match ASRT with ATOT_ALDT for a single flight and calculate the difference in counts.

[ALXWBR]

I'm not sure that approach would work? Are you able to share some SPL with me so I can fully understand what your approach is. Here's some sample data. I've worked out what the values should be using excel with a good ol' fashioned countif.

DepOrArr	displayed_flyt_no	ASRT	asrt_epoch	ATOT_ALDT	runway_epoch	queue
A	flight1			29/02/2024 00:52	1709167953
A	flight2			29/02/2024 01:41	1709170889
A	flight3			29/02/2024 05:08	1709183310
A	flight4			29/02/2024 05:33	1709184834
A	flight5			29/02/2024 05:36	1709185003
A	flight6			29/02/2024 05:40	1709185247
A	flight7			29/02/2024 05:51	1709185889
A	flight8			29/02/2024 06:01	1709186519
A	flight9			29/02/2024 06:04	1709186679
D	flight10	29/02/2024 05:49	1709185740	29/02/2024 06:08	1709186895	3
D	flight11	29/02/2024 05:57	1709186220	29/02/2024 06:10	1709187000	3
D	flight12	29/02/2024 06:03	1709186580	29/02/2024 06:14	1709187280	3
A	flight13			29/02/2024 06:19	1709187540
D	flight14	29/02/2024 06:15	1709187300	29/02/2024 06:29	1709188186	1
D	flight15	29/02/2024 06:16	1709187360	29/02/2024 06:31	1709188261	2
D	flight16	29/02/2024 06:19	1709187540	29/02/2024 06:32	1709188338	3
D	flight17	29/02/2024 06:22	1709187720	29/02/2024 06:34	1709188485	3
D	flight18	29/02/2024 06:31	1709188260	29/02/2024 06:42	1709188973	3
A	flight19			29/02/2024 06:44	1709189074
D	flight20	29/02/2024 06:32	1709188320	29/02/2024 06:46	1709189180	4
D	flight21	29/02/2024 06:38	1709188680	29/02/2024 06:47	1709189278	3
A	flight22			29/02/2024 06:49	1709189378
D	flight23	29/02/2024 06:27	1709188020	29/02/2024 06:51	1709189475	9
A	flight24			29/02/2024 06:52	1709189531
D	flight25	29/02/2024 06:36	1709188560	29/02/2024 06:54	1709189648	7
A	flight26			29/02/2024 06:55	1709189707
D	flight27	29/02/2024 06:43	1709188980	29/02/2024 06:56	1709189807	8
A	flight28			29/02/2024 06:57	1709189868
D	flight29	29/02/2024 06:45	1709189100	29/02/2024 06:59	1709189970	9
A	flight30			29/02/2024 07:01	1709190080
D	flight31	29/02/2024 06:46	1709189160	29/02/2024 07:03	1709190229	11
D	flight32	29/02/2024 06:49	1709189340	29/02/2024 07:04	1709190292	10
D	flight33	29/02/2024 06:47	1709189220	29/02/2024 07:06	1709190373	12
D	flight34	29/02/2024 06:53	1709189580	29/02/2024 07:07	1709190447	9
D	flight35	29/02/2024 06:50	1709189400	29/02/2024 07:09	1709190577	12
A	flight36			29/02/2024 07:10	1709190630
D	flight37	29/02/2024 06:56	1709189760	29/02/2024 07:12	1709190720	10
A	flight38			29/02/2024 07:13	1709190798
D	flight39	29/02/2024 06:55	1709189700	29/02/2024 07:14	1709190892	13
A	flight40			29/02/2024 07:15	1709190939
D	flight41	29/02/2024 06:57	1709189820	29/02/2024 07:16	1709191019	13
D	flight42	29/02/2024 06:45	1709189100	29/02/2024 07:18	1709191096	22
A	flight43			29/02/2024 07:18	1709191123
D	flight44	29/02/2024 07:04	1709190240	29/02/2024 07:20	1709191225	12
D	flight45	29/02/2024 07:06	1709190360	29/02/2024 07:21	1709191299	12
A	flight46			29/02/2024 07:22	1709191364
D	flight47	29/02/2024 07:07	1709190420	29/02/2024 07:24	1709191474	13
A	flight48			29/02/2024 07:25	1709191548
D	flight49	29/02/2024 06:59	1709189940	29/02/2024 07:27	1709191640	20
A	flight50			29/02/2024 07:28	1709191701
D	flight51	29/02/2024 06:58	1709189880	29/02/2024 07:29	1709191786	22
A	flight52			29/02/2024 07:31	1709191881
D	flight53	29/02/2024 07:10	1709190600	29/02/2024 07:34	1709192073	17
D	flight54	29/02/2024 07:11	1709190660	29/02/2024 07:35	1709192137	17
A	flight55			29/02/2024 07:36	1709192194
D	flight56	29/02/2024 07:12	1709190720	29/02/2024 07:38	1709192299	19
A	flight57			29/02/2024 07:38	1709192339
D	flight58	29/02/2024 07:16	1709190960	29/02/2024 07:40	1709192441	17
A	flight59			29/02/2024 07:41	1709192511
D	flight60	29/02/2024 07:04	1709190240	29/02/2024 07:43	1709192613	28
A	flight61			29/02/2024 07:44	1709192692
D	flight62	29/02/2024 07:17	1709191020	29/02/2024 07:46	1709192788	20
D	flight63	29/02/2024 07:19	1709191140	29/02/2024 07:47	1709192845	19
A	flight64			29/02/2024 07:48	1709192888
D	flight65	29/02/2024 07:23	1709191380	29/02/2024 07:49	1709192986	18
D	flight66	29/02/2024 07:27	1709191620	29/02/2024 07:50	1709193048	17
A	flight67			29/02/2024 07:51	1709193089

[PickleRick]

OK. Assuming you have _time and "event" (being either ASRT or ATOT_ALDT).

<your base search>
| sort _time
| streamstats count(eval(if(event="ATOT_ALDT",1,0)) as times_busy

This will give you additional column called "times_busy" saying how many times up to this  point in time you had an event of the runway being busy due to ATOT_ALDT.

So now you can do (assuming you're interested only in that parameter and we can just use stats and forget about everything else)

| stats values(state) as states values(times_busy) as busy by displayed_flyt_no

Now assuming you're interested only in those for which you had both states reported

| where states="ASRT" AND states="ATOT_ALDT"

And you can calculate your queue with

| eval queue=max(busy)-min(busy)-1

[ALXWBR]

Thanks @PickleRick, however, that search doesn't work for me. 

Not sure if I've made it clear enough. 

So for every single departing flight in the table (DepOrArr=D), I need to count the total of other flights who's ATOT_ALDT time was between the ASRT timestamp and ATOT_ALDT timestamp of that flight.

So if Flight1234 has and ASRT of 09:00 and an ATOT_ALDT of 09:15, how many other flights in the list had an ATOT_ALDT timestamp between those 2 times. And then so on for the next flight...etc etc.

What makes this more complicated is a flight can have an ASRT timestamp after another flight but still have an ATOT_ALDT timestamp before.

[PickleRick · Accepted solution]

I made some assumptions about the format of your data because I don't know your raw data but the mechanism is relatively sound.

My search should count precisely what you're saying.

Let's take an excerpt from your table and translate it into single events in the format I talked about(if you don't have them as separate ASRT and ATOT_ALDT, you'd have to split them in your base search). This is already sorted by time

displayed_flyt_no	_time	event
flight10	29/02/2024 05:49	ASRT
flight7	29/02/2024 05:51	ATOT_ALDT
flight11	29/02/2024 05:57	ASRT
flight8	29/02/2024 06:01	ATOT_ALDT
flight12	29/02/2024 06:03	ASRT
flight9	29/02/2024 06:04	ATOT_ALDT
flight10	29/02/2024 06:08	ATOT_ALDT
flight11	29/02/2024 06:10	ATOT_ALDT
flight12	29/02/2024 06:14	ATOT_ALDT
flight13	29/02/2024 06:19	ATOT_ALDT

So if you now add that streamstatsed count, you'd get this:

displayed_flyt_no	_time	event	times_busy
flight10	29/02/2024 05:49	ASRT	0
flight7	29/02/2024 05:51	ATOT_ALDT	1
flight11	29/02/2024 05:57	ASRT	1
flight8	29/02/2024 06:01	ATOT_ALDT	2
flight12	29/02/2024 06:03	ASRT	2
flight9	29/02/2024 06:04	ATOT_ALDT	3
flight10	29/02/2024 06:08	ATOT_ALDT	4
flight11	29/02/2024 06:10	ATOT_ALDT	5
flight12	29/02/2024 06:14	ATOT_ALDT	6
flight13	29/02/2024 06:19	ATOT_ALDT	7

So now you group the flights with the stats and get

displayed_flyt_no	states	busy
flight7	ATOT_ALDT	1
flight8	ATOT_ALDT	2
flight9	ATOT_ALDT	3
flight10	ASRT,ATOT_ALDT	0,4
flight11	ASRT,ATOT_ALDT	1,5
flight12	ASRT,ATOT_ALDT	2,6
flight13	ATOT_ALDT	7

So we now know that flights 7,8,9 and 13 only landed (I assume - they didn't ASRT so they only occupied the runway becaues of ATOT_ALDT). And we're not interested in those because they didn't wait in queue.

So we're filtering them out by our "where" command and we're left with just

displayed_flyt_no	states	busy
flight10	ASRT,ATOT_ALDT	0,4
flight11	ASRT,ATOT_ALDT	1,5
flight12	ASRT,ATOT_ALDT	2,6

Now if we calculate max(busy)-min(busy)-1, we'll see that all those flights waited in a queue of length 3.

And it's the same values as you have in your table.

[ALXWBR]

Ahhh ok, so the reason it wasn't working is because the ASRT and ATOT_ALDT were part of the same event, the example was effectively the events tabled. So now I have split the two timestamps into two separate events, your code works (couple of typos in the streamstats line, but sorted those). This is what I did. Thanks for all your help!

| eval 
    asrt_epoch = strptime(ASRT,"%Y-%m-%d %H:%M:%S"),
    runway_epoch = strptime(ATOT_ALDT,"%Y-%m-%d %H:%M:%S"),
    event="ASRT_".asrt_epoch.","."ATOT_ALDT_".runway_epoch,
    event=if(isnull(event),"ATOT_ALDT_".runway_epoch,event)
| makemv event delim=","
| mvexpand event
| rex field=event "^(?P<event>(ATOT_ALDT|ASRT))_(?P<_time>.+)$"
| sort _time
| streamstats count(eval(if(event="ATOT_ALDT",1,null()))) as times_busy
| stats values(event) as states values(times_busy) as busy values(ATOT_ALDT) as ATOT_ALDT by displayed_flyt_no
| sort ATOT_ALDT
| where states="ASRT" AND states="ATOT_ALDT"
| eval queue=(max(busy)-1)-(min(busy))