Hello there:
I have the following two events:
Event #1
source=foo1
eventid=abc
message="some message dfsdfdfgfdggfg fgdfdgfdg "time":"2023-11-09T21:33:05.0738373837278Z, abcefg"
Event #2
source=foo2
eventid=abc
time: 2023-11-09T21:33:05Z
I need to related these two events based on their event_id and eventid values being the same.
I got help before to write that query:
index=foo (source=foo1 OR source=foo2) (eventid=* OR event_id=*) | eval eventID = coalesce(eventid, event_id) | stats values(*) as * by eventID
Now i need to expand the above query by extracting the timestamp from the message field from Event #1
and compare it against the time field from Event #2.
I basically will need to do the timestamp subtraction between the two fields to see if there time differences and by how (second, minutes,etc.)
Do u know how to do that?
Thanks!
Hi @bowesmana .. just thought to tell you, the rex was missing the closing parenthesis, and your rex and strptime works nicely.
Hi @djoobbani .. as said on the above reply, pls update us.. the time field is "_time" or you want to extract it from the msg.
if you want to extract from the msg, then, assuming the 2nd msg also same like 1st msg.. try something like this..
| makeresults
| eval msg1="some message dfsdfdfgfdggfg fgdfdgfdg \"time\":\"2023-11-09T21:33:05.0738373837278Z, abcefg"
| eval msg2="some message dfsdfdfgfdggfg fgdfdgfdg \"time\":\"2023-11-09T21:33:10.0738373837278Z, abcefg"
| rex field=msg1 "time.:.(?<event1_time>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2})"
| rex field=msg2 "time.:.(?<event2_time>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2})"
| eval e1_t=strptime(event1_time, "%FT%T")
| eval e2_t=strptime(event2_time, "%FT%T")
| eval diff=e1_t-e2_t
| table event1_time event2_time diff
this gives the result of:
event1_time event2_time diff
2023-11-09T21:33:05 2023-11-09T21:33:10 -5.000000
Does the Splunk default _time field represent the time in the data or is it a different time?
If it's a different time, then you need to extract those times (if not already extracted) and then just do the maths after the stats, e.g. something like
index=foo (source=foo1 OR source=foo2) (eventid=* OR event_id=*)
| eval eventID = coalesce(eventid, event_id)
| rex "time.:.(?<event1_time>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2})"
| rex "time:.(?<event2_time>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2})"
| stats values(*) as * by eventID
| eval e1_t=strptime(event1_time, "%FT%T")
| eval e2_t=strptime(event2_time, "%FT%T")
| eval diff=e1_t-e2_t
You may need to adjust the rex statements - this also ignores subsecond values - not sure what 13 decimal places is for in the first time - if you want to include those you'll have to change it a bit.
Hi @bowesmana .. just thought to tell you, the rex was missing the closing parenthesis, and your rex and strptime works nicely.
Hi @djoobbani .. as said on the above reply, pls update us.. the time field is "_time" or you want to extract it from the msg.
if you want to extract from the msg, then, assuming the 2nd msg also same like 1st msg.. try something like this..
| makeresults
| eval msg1="some message dfsdfdfgfdggfg fgdfdgfdg \"time\":\"2023-11-09T21:33:05.0738373837278Z, abcefg"
| eval msg2="some message dfsdfdfgfdggfg fgdfdgfdg \"time\":\"2023-11-09T21:33:10.0738373837278Z, abcefg"
| rex field=msg1 "time.:.(?<event1_time>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2})"
| rex field=msg2 "time.:.(?<event2_time>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2})"
| eval e1_t=strptime(event1_time, "%FT%T")
| eval e2_t=strptime(event2_time, "%FT%T")
| eval diff=e1_t-e2_t
| table event1_time event2_time diff
this gives the result of:
event1_time event2_time diff
2023-11-09T21:33:05 2023-11-09T21:33:10 -5.000000
Thanks @bowesmana
Ok let's make it simpler, i have this event:
Event
source=abc
time1=2023-11-10T00:33:53Z
time2=2023-11-11T12:33:53Z
How would you construct the query so that time2 is subtracted from time1 and display the time difference in the result using rex?
Thanks!
So a single event with two timestamps is easy
| rex "time1=(?<t1>[^Z]*Z)"
| rex "time2=(?<t2>[^Z]*Z)"
| eval t1_t=strptime(t1, "%FT%TZ")
| eval t2_t=strptime(t2, "%FT%TZ")
| eval diff=t2_t-t1_t
You can optimise that to make a single rex if you can guarantee the ordering of your _raw data