topic Re: How to extract and find the difference in time between two fields using regex? in Splunk Search

How to extract and find the difference in time between two fields using regex?

rrkollip — Wed, 31 Jan 2018 23:30:45 GMT

Hi ,

I have 2 events like below and I need to find the difference in time between 2 events. There may be a lot of other events between them.
I'm trying to write a regular expression to extract the time in [] brackets and create as an EntryTIME and EXITTIME fields.

Event 1: Worker#108 [SWAPScheduler-INDIA] - [2018-01-31 04:30:04,340] - DEBUG - AppLogger: SWAPScheduler.executeInternal entry

Event 2: Worker#108 [SWAPScheduler-INDIA] - [2018-01-31 04:30:04,340] - DEBUG - AppLogger: SWAPScheduler.executeInternal exit

Please help me in creating regex to extract this in Splunk. I tried field extractor but it's allowing only 1 string to add as a required text. If I do this I can't have 2 fields to get entry time and exit time.

Re: How to extract and find the difference in time between two fields using regex?

nabeel652 — Thu, 01 Feb 2018 00:59:29 GMT

Try this - You can run this as is copying and pasting it in your search. - you can tweak it a little bit to fit your needs:

| stats count | eval data = "Event 1: Worker#108 [SWAPScheduler-INDIA] - [2018-01-31 04:30:04,340] - DEBUG - AppLogger: SWAPScheduler.executeInternal entry;Event 1: Worker#115 [SWAPScheduler-INDIA] - [2018-01-31 04:30:09,340] - DEBUG - AppLogger: SWAPScheduler.executeInternal entry;
  Event 2: Worker#115 [SWAPScheduler-INDIA] - [2018-01-31 04:30:29,340] - DEBUG - AppLogger: SWAPScheduler.executeInternal exit;
 Event 2: Worker#108 [SWAPScheduler-INDIA] - [2018-01-31 04:30:34,340] - DEBUG - AppLogger: SWAPScheduler.executeInternal exit" | makemv delim=";" data 
 | mvexpand data | rename data as _raw  | rex field=_raw ".*Worker\#(?<worker_id>[\d+]*).*\-\s(\[(?<entry_time>[^\]]*).*entry|\[(?<exit_time>[^\]]*).*exit)"

Re: How to extract and find the difference in time between two fields using regex?

493669 — Thu, 01 Feb 2018 04:40:22 GMT

are you expecting something like this:

... | rex field=_raw "(.*\]\s\-\s\[(?<entry_time>[^\,]*).*executeInternal\sentry)|(.*\]\s\-\s\[(?<exit_time>[^\,]*).*executeInternal\sexit)"

Re: How to extract and find the difference in time between two fields using regex?

mayurr98 — Thu, 01 Feb 2018 06:18:04 GMT

hey try this run anywhere search

| makeresults 
|  eval _raw="Worker#108 [SWAPScheduler-INDIA] - [2018-01-31 04:30:04,340] - DEBUG - AppLogger: SWAPScheduler.executeInternal entry"
 | append
    [ | makeresults 
    |  eval _raw="Worker#108 [SWAPScheduler-INDIA] - [2018-01-31 04:30:04,340] - DEBUG - AppLogger: SWAPScheduler.executeInternal exit"] 
| rex field=_raw "\s-\s\[((?<entry_time>[^\]]+).*entry|(?<exit_time>[^\]]+).*exit)"

in your environment, you should write

<your_base_search> | rex field=_raw "\s-\s\[((?<entry_time>[^\]]+).*entry|(?<exit_time>[^\]]+).*exit)"

let me know if this helps!

Re: How to extract and find the difference in time between two fields using regex?

nabeel652 — Thu, 01 Feb 2018 06:32:06 GMT

Please check the updated answer

Re: How to extract and find the difference in time between two fields using regex?

rrkollip — Tue, 29 Sep 2020 17:58:04 GMT

This worked to get the entry & exit time. When I run query like below to calculate the difference, none of the strptime and strftime functions works with fields entry_time and exit_time.

Does strptime and strftime functions works with the fields extracted from regex?

Re: How to extract and find the difference in time between two fields using regex?

nabeel652 — Tue, 29 Sep 2020 18:00:50 GMT

Well for strptime you'll have to provide the timeformat string that matches the date/time in entry_time and exit_time. So you need to do:

| eval exit = strptime(exit_time,"%Y-%m-%d %H:%M:%S")

However by doing |eval diff = exit - entry you'll not get any useful results because entry_time corresponding to every exit_time (and vise versa) are NULL.

In order to get time difference between entry and exit you'll have to use the transaction command that I worked out earlier.

Re: How to extract and find the difference in time between two fields using regex?

nabeel652 — Wed, 07 Feb 2018 01:37:45 GMT

If you still want duration through same query use this:

    | stats count | eval data = "Event 1: Worker#108 [SWAPScheduler-INDIA] - [2018-01-31 04:30:04,340] - DEBUG - AppLogger: SWAPScheduler.executeInternal entry;Event 1: Worker#115 [SWAPScheduler-INDIA] - [2018-01-31 04:30:09,340] - DEBUG - AppLogger: SWAPScheduler.executeInternal entry;
   Event 2: Worker#115 [SWAPScheduler-INDIA] - [2018-01-31 04:30:29,340] - DEBUG - AppLogger: SWAPScheduler.executeInternal exit;
  Event 2: Worker#108 [SWAPScheduler-INDIA] - [2018-01-31 04:30:34,340] - DEBUG - AppLogger: SWAPScheduler.executeInternal exit" | makemv delim=";" data 
  | mvexpand data | rename data as _raw | rex field=_raw ".*Worker\#(?[\d+]*).*\-\s(\[(?[^\]]*).*entry|\[(?[^\]]*).*exit)"  | stats first(entry_time) as entry last(exit_time) as exit by worker_id | eval entry = strptime(entry,"%Y-%m-%d %H:%M:%S") | eval exit = strptime(exit,"%Y-%m-%d %H:%M:%S") | eval diff = round(exit -entry,0)