extracting texts from a field

[djoobbani]

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!

[bowesmana]

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.

[inventsekar · Accepted solution]

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

[djoobbani]

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!

[bowesmana]

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