Splunk Search

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

tkwaller_2
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

yannK
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

yannK
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)

tkwaller_2
Communicator

yep, thats exactly what I did, thanks!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...