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.
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?
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
| eval busdays=mvrange(stopdate, closeddate, 1d) - I am getting malfunction error in eval here?
I considered using coalesce, but was concerned about what strptime would return if closedate doesn't exist.
@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()
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 | ...
Is diff results are in seconds ? need to do (diff/3600)?
Yes, diff is in seconds. Good catch. I'll update the answer.