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

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

splunker9999 — Mon, 01 Aug 2016 18:09:33 GMT

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.

we need to find the diff of Stopdate and closeddate
We need to list if Late closure > 5 (excluding weekends)
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?

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

richgalloway — Mon, 01 Aug 2016 18:34:14 GMT

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 | ...

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

splunker9999 — Mon, 01 Aug 2016 18:39:44 GMT

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

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

sundareshr — Mon, 01 Aug 2016 18:41:43 GMT

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

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

splunker9999 — Mon, 01 Aug 2016 18:52:47 GMT

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

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

richgalloway — Mon, 01 Aug 2016 19:56:15 GMT

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

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

richgalloway — Mon, 01 Aug 2016 19:58:40 GMT

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

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

sundareshr — Mon, 01 Aug 2016 20:31:52 GMT

@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()