How to search for the first record for each group of data for a field?

[mohitab]

Data:
I have CSV data indexed containing sensory information. The structure is timestamp, Flight_ID, lon, lat, alt.

When I run query to find records against a particular Flight_ID, eg search Flight_ID="KLM281" , I get a set of records. The records represent flight information for more than one instance of the flight, i.e., it contains data of the flights on different days, eg one instance can be a flight from A to B on 8:12 AM to 9:45 AM on 12/09/2014 and other instance can be on 8:10 AM to 9:40 AM on 02/11/2014. The consecutive records corresponding to one instance are temporally near to each other (1-5 mins gap between consecutive records) but separated by next/previous instance by substantial amount of time(few hours or days).

The objective it to find first record for each instance. I am trying to workout a query and explored cluster & kmeans but could not figure this out. I tried Flight_ID="AAL287" | table _time alt | kmeans _time but kmeans seldom gives out expected clusters. Any suggestions? Any different approach other than clustering?

[MuS · Accepted solution]

Hi mohitab,

did you try

... | stats first(_time) by Flight_ID

Alternative to cluster you could try the transaction command.

cheers, MuS

[mohitab]

I tried transaction as : search Flight_ID="KLM281" | transaction maxpause=5m | table _time eventcount.

This got me the all the instances of the Flight. Thanks for the suggestions @MuS.

[MuS]

you're welcome 😉 please mark this as answered - thx

[mohitab]

 Flight_ID="AAL285" | table _time altitude Flight_ID| stats first(_time) by Flight_ID

This gave me earliest record for the Flight_ID. But that is not my objective. I want the first record for each cluster/group of flight data for Flight_ID="AAL285".

[MuS]

well, you did not build any of your needed clusters you just used a table. Build your clusters/groups first and use stats on them