How do you parse two string times and compare and get difference?

[imservicesbg]

Hello friends,

Say I have two index events that return string messages such as:

SCHEDULE - SUCCESS - Time: 05:12:02
AUTH - SUCCESS - Time: 05:14:01

Here are the example events:

EVENT 1 -

{  
   "cf_app_id":"345345345345",
   "cf_app_name":"my-app-services",
   "job_index":"b4853c36-6ecd-4902-9eb6xxxxxxxxx",
   "message_type":"OUT",
   "msg":{  
      "component":"MyApp",
      "device":"iPhone",
      "deviceID":"3534534534534",
      "level":"INFO",
      "levelindicator":"✏️",
      "message":"SCHEDULE - SUCCESS - Time: 05:12:02",
      "osversion":"12.0.1",
      "platform":"iOS",
      "reference":"Logger.swift|info|31",
      "sessionID":"234234234234",
      "username":"N/A",
      "version":"5.1.0"
   },
   "origin":"rep",
   "source_instance":"0",
   "source_type":"APP/PROC/WEB",
   "timestamp":1549302723595711849
}

EVENT 2 -

{  
   "cf_app_id":"34534534532",
   "cf_app_name":"my-app-services",
   "job_index":"b4853c36-6ecd-4902-9eb6zzzzzzz",
   "message_type":"OUT",
   "msg":{  
      "component":"MyApp",
      "device":"iPhone",
      "deviceID":"345435345345",
      "level":"INFO",
      "levelindicator":"✏️",
      "message":"AUTH - SUCCESS - Time: 05:14:01",
      "osversion":"12.0.1",
      "platform":"iOS",
      "reference":"Logger.swift|info|31",
      "sessionID":"6456464564",
      "username":"N/A",
      "version":"5.1.0"
   },
   "origin":"rep",
   "source_instance":"0",
   "source_type":"APP/PROC/WEB",
   "timestamp":567567567567
}

The logic here is, Splunk is reporting a successful SCHEDULE service call and adding a JS timestamp from Date() to "msg.message". Then comes an AUTHORIZATION success call a couple minutes later and does the same thing with a Date() timestamp to "msg.message".

What I want to do:

I want to take the timestamps out of each like so:

05:14:01 and 05:12:02 and then subtract those to find the difference of 2:01. Then create a panel that shows all comparisons like that which are over 1 minute in duration.

Is this even possible? What kind of search param would I use to find the substring with Splunk and find the time difference which creates my panel? I hope this is clear in what I am trying to do. Should I use compare?

Thank you!

[woodcock · Accepted solution]

Like this:

index=YouShouldAlwaysSpecifyAnIndex AND sourcetype=AndSourcetypeToo
| rex field=msg.message "\s+-\s+Time:\s+(?<Time>[^\"]+)"
| eval Time = strptime(Time, "%H:%M:%S")
| stats range(Time) AS duration list(*) AS * BY sessionID
| where duration > 60

This is assuming that the events share a common sessoinID value (which your events, as shown, do not). If this is a mistake, switch that out for the field that does correlate the 2 events.

[imservicesbg]

Great!

How does this look? You're correct. The sessionIDs would be the same!

cf_app_name="my-app-services" "msg.platform"=iOS "msg.level"=INFO
| rex field="msg.message" "\s+-\s+Time:\s+(?[^\"]+)"
| eval Time = strptime(Time, "%H:%M:%S")
| stats range(Time) AS duration list(*) AS * BY sessionID
| where duration > 60

Doesn't the rex field need to be "msg.message" according to my example above?

[woodcock]

I updated msg.messsage. Does it actually work for you? If so, click Accept to close the question.

[gjanders]

If you refer to https://answers.splunk.com/answers/613695/converting-hmmss-into-hour-minutes-and-seconds.html the dur2sec might be an option here...however that might fail when your close to midnight in which case strptime might work.

strptime to convert to epoch, minus the two epoch values, then convert back into a duration with an:
| eval duration=tostring(differencevalue,"duration")

Sorry don't have time to write a full answer this morning...