how to reduce multiple results into one based on the earliest time

[djoobbani]

Hi there:

I have the following query:

source=accountCalc type=acct.change msg="consumed" event_id="*" process_id="*" posted_ timestamp =”*” msg_ timestamp =”*”
| eval e1_t=strptime(posted_ timestamp, "%FT%T")

| eval e2_t=strptime(msg_ timestamp, "%FT%T")

| eval lag_in_seconds=e1_t-e2_t

| eval r2_posted_timestamp=posted_time

| table event_id process_id msg_timestamp r2_posted_timestamp lag_in_seconds

The above query can return multiple events with the same event_id & process_id with different posted_ timestamp

I need to only return the one with the earliest/oldest posted_time(one of the fields in the event).
How can i change the above query to accomplish this?

Thanks!

[ITWhisperer · Accepted solution]

Try something like this

source=accountCalc type=acct.change msg="consumed" event_id="*" process_id="*" posted_ timestamp =”*” msg_ timestamp =”*”
| eval e1_t=strptime(posted_ timestamp, "%FT%T")
| eval e2_t=strptime(msg_ timestamp, "%FT%T")
| eval lag_in_seconds=e1_t-e2_t
| eval r2_posted_timestamp=posted_time
| table event_id process_id msg_timestamp r2_posted_timestamp lag_in_seconds e1_t
| sort 0 e1_t
| dedup event_id process_id

[inventsekar]

Hi @djoobbani .. please check this SPL.. thanks. 

source=accountCalc type=acct.change msg="consumed" event_id="*" process_id="*" posted_ timestamp =”*” msg_ timestamp =”*”
| eval e1_t=strptime(posted_ timestamp, "%FT%T")
| eval e2_t=strptime(msg_ timestamp, "%FT%T")
| eval lag_in_seconds=e1_t-e2_t
| eval r2_posted_timestamp=posted_time
| stats earliest(r2_posted_timestamp) AS Earliest_r2_posted_timestamp, latest(r2_posted_timestamp) AS Latest_r2_posted_timestamp  
| table event_id process_id msg_timestamp r2_posted_timestamp lag_in_seconds Earliest_r2_posted_timestamp Latest_r2_posted_timestamp 

[djoobbani]

This query does show the earliest posted_timestamp. However, all the other fields are blank for the table command for fields (event_id, process_id, msg_timestamp, lag_in_seconds)

Thanks!