Splunk Search
Highlighted

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

Path Finder

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 FlightID, eg `search FlightID="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?

Tags (2)
0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

Hi mohitab,

did you try

... | stats first(_time) by Flight_ID

Alternative to cluster you could try the transaction command.

cheers, MuS

View solution in original post

Highlighted

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

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

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

Highlighted

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

Path Finder

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.

Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma