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 contr...
See more...
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