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!
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
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
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
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!