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

[splunker9999]

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?

[sundareshr]

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

[splunker9999]

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

[richgalloway]

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

[sundareshr]

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

[richgalloway]

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

[splunker9999]

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

[richgalloway]

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