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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...