Splunk Search

What is the best way to calculate date and time span?

Communicator

Hello
What I am trying to do is calulate dates and span.
So I have a date called "Date Due" and a field "SLA". What I am trying to do is take "Date Due" and add "SLA" compare to the current date and give how many days past it currently is.

Currently Im using this but the results arent spot on

index=fp_dev_tsv "BO Type"="assessments"  | rename "BO ID" as id| convert timeformat="%Y-%m-%d %H:%M:%S.%6N"  mktime("Step Date Started") AS starttime  mktime("Step Date Completed") AS endtime mktime("Step Due Date") AS cumulativeDueDate mktime("Step Actual Due Date") AS actualDueDate

|eval dueDateRange=mvrange(actualDueDate,now(),86400)
|convert ctime(dueDateRange) timeformat="%+" 
| eval pastDueDays =mvcount(mvfilter(NOT match(dueDateRange,"(Sun|Sat).*")))

this doesnt take into account the SLA field just counts the days from the actualDueDate until now in days. Its correct BUT I need to add in SLA and then compare
Heres a sample table:

Name         Past Due Step Name       Past Due Step Due Date     SLA for Past Due Step

General Name    Info 1  2018-02-01 20:38:10.154000  3

Genberal Name   Info 2  2018-02-10 20:38:10.154000  10

General Name    Info 3  2018-03-08 20:38:10.154000  5

General Name    Info 4  2018-03-15 20:38:10.154000  5

General Name    Info 5  2018-03-22 20:38:10.154000  5

So what I need to do is check each steps due date, add SLA and then compare to todays date.
Any idea how I can achieve this?

Thanks for the help!

0 Karma
1 Solution

Splunk Employee
Splunk Employee

Why not convert your dates in epoch time (using eval or convert and mktime)
then convert your SLA in seconds, and add/subtract if from your dates
and at the end, convert back to a date (using eval/convert and ctime)

View solution in original post

Splunk Employee
Splunk Employee

Why not convert your dates in epoch time (using eval or convert and mktime)
then convert your SLA in seconds, and add/subtract if from your dates
and at the end, convert back to a date (using eval/convert and ctime)

View solution in original post

Communicator

yep, thats exactly what I did, thanks!

0 Karma