Splunk Search
Highlighted

How to write a search using eval to create a new field with values calculated from the difference between two time fields?

Path Finder

Hi,

We integrated Splunk to ServiceNow and looking to find a late closure incidents.

For this we have 2 fields Stopdate, closeddate... we need to evaluate a new field Late Closure using these 2 dates.

  1. we need to find the diff of Stopdate and closeddate
  2. We need to list if Late closure > 5 (excluding weekends)
  3. For few of them, we don't have closed date. We need to compare with current date and evaluate number of late closure for these?

Stopdate and closeddate is of this format: 08-01-2016 05:00:00 MST

base search...|table Stopdate closeddate

Can someone please help us with the search?

0 Karma
Highlighted

Re: How to write a search using eval to create a new field with values calculated from the difference between two time fields?

SplunkTrust
SplunkTrust

Comparing dates requires converting them to epoch (integer) form. Do that with strptime(field, "%m-%d-%Y %H:%M:%S %Z").
Allowing for a missing closed date is easy with isnull.
I'm at a loss for a way to exclude weekends, but better heads than mine may have ideas.

A sample query:

index= foo | eval stopTS = strptime(Stopdate, "%m-%d-%Y %H:%M:%S %Z") | eval closedTS=if(isnull(closedDate),now(), strptime(closedDate, "%m-%d-%Y %H:%M:%S %Z")) | eval diff = (closedTS - stopTS)/86400 | where diff > 5 | ...
---
If this reply helps you, an upvote would be appreciated.
Highlighted

Re: How to write a search using eval to create a new field with values calculated from the difference between two time fields?

Path Finder

Is diff results are in seconds ? need to do (diff/3600)?

0 Karma
Highlighted

Re: How to write a search using eval to create a new field with values calculated from the difference between two time fields?

SplunkTrust
SplunkTrust

Yes, diff is in seconds. Good catch. I'll update the answer.

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: How to write a search using eval to create a new field with values calculated from the difference between two time fields?

Legend

Try this

*UPDATED*

your base search | eval closedate=coalesce(strptime(closedate, "%m-%d-%Y %H:%M:%S") , now()) | stats latest(stopdate) as stopdate latest(closedate) as closedate by ticketid | eval stopdate=strptime(stopdate, "%m-%d-%Y %H:%M:%S") | eval lateclosure=closedate-stopdate | where lateclosure>=5*86400 | eval busdays=mvrange(stopdate, closedate, "1d") | eval busdays=strftime(busdays, "%a") | eval busdays=mvfilter(busdays!="Sat" AND busdays!="Sun") | where mvcount(busdays)>=5
0 Karma
Highlighted

Re: How to write a search using eval to create a new field with values calculated from the difference between two time fields?

Path Finder

| eval busdays=mvrange(stopdate, closeddate, 1d) - I am getting malfunction error in eval here?

0 Karma
Highlighted

Re: How to write a search using eval to create a new field with values calculated from the difference between two time fields?

SplunkTrust
SplunkTrust

I considered using coalesce, but was concerned about what strptime would return if closedate doesn't exist.

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: How to write a search using eval to create a new field with values calculated from the difference between two time fields?

Legend

@splunker9999 try the updated version. 1d should have been in quotes. Also removed the mvexpand command.

@richgalloway, if closedate doesn't exists, closedate will be populated with now()

0 Karma