Getting Data In

How do you calculate SLA with unstructured date format?

Explorer

hi guys,

can you please help me in how we can we try to convert this value 2019-01-28-20-32-49 to 2019-01-28 20:00:00 format .

And calculate time difference between the two values with the date format 2019-01-28 20:00:00 . Thank you in advance.

@jkat54 @woodcock @vnravikumar

Tags (2)
1 Solution
Champion

Hi @pench2k19

Try this

``````| makeresults
| eval time1="2019-01-28-20-32-49",time2="2019-01-29-23-10-00"
| eval epochtime1=strptime(time1,"%Y-%m-%d-%H-%M-%S"), epochtime2=strptime(time2,"%Y-%m-%d-%H-%M-%S")
| eval newtime1=strftime(epochtime1,"%Y-%m-%d %H:00:00"),newtime2=strftime(epochtime2,"%Y-%m-%d %H:00:00")
| eval newepoch1=strptime(newtime1,"%Y-%m-%d %H:00:00"),newepoch2=strptime(newtime2,"%Y-%m-%d %H:00:00")
| eval diff = tostring((newepoch2-newepoch1), "duration") | table time1,time2,epochtime1,epochtime2,newtime1,newtime2,newepoch1,newepoch2,diff
``````
Explorer

let me explain the question more in detail

i have been joining two quries and calculate the time difference. In the main search i have got the time format as 2019-01-28 20:00:00 and in the subsearch i have got the time format as 2019-01-28-20-32-49

Now i want convert the 2019-01-28-20-32-49 into value like this 2019-01-28 20:32:49 and calculate the time difference.

following is the query i m using FYR

| inputlookup SLA.csv|table SOR_NAME SLA_THRESHOLD| join type=left SOR_NAME [ search index=xx source=xx|rex "info\s:\s+{4}\s(?\w+)\s+{4}\sJob run_ingest_(?\w+)(?\d+-\d+-\d+-\d+-\d+-\d+)" |where Datafeed_name!=""|rex field=Datafeed_name "^(?\w{2,5})_(?\w+)\$" |fields SOR_NAME time_stamp]|dedup SOR_NAME |eval time_diff = (SLA_THRESHOLD - time_stamp)|table SOR_NAME SLA_THRESHOLD time_stamp time_diff

Champion

Hi @pench2k19

Try this

``````| makeresults
| eval time1="2019-01-28-20-32-49",time2="2019-01-29-23-10-00"
| eval epochtime1=strptime(time1,"%Y-%m-%d-%H-%M-%S"), epochtime2=strptime(time2,"%Y-%m-%d-%H-%M-%S")
| eval newtime1=strftime(epochtime1,"%Y-%m-%d %H:00:00"),newtime2=strftime(epochtime2,"%Y-%m-%d %H:00:00")
| eval newepoch1=strptime(newtime1,"%Y-%m-%d %H:00:00"),newepoch2=strptime(newtime2,"%Y-%m-%d %H:00:00")
| eval diff = tostring((newepoch2-newepoch1), "duration") | table time1,time2,epochtime1,epochtime2,newtime1,newtime2,newepoch1,newepoch2,diff
``````
Champion

If you want to format `diff` use this `| eval duration2=replace(diff,"(\d*)\+*(\d+):(\d+):(\d+)","\1 day(s) \2 hour(s) \3 minute(s) \4 sec(s)")`

Explorer

i have been joining two quries and calculate the time difference. In the main search i have got the time format as 2019-01-28 20:00:00 and in the subsearch i have got the time format as 2019-01-28-20-32-49

Now i want convert the 2019-01-28-20-32-49 into value like this 2019-01-28 20:32:49 and calculate the time difference.

following is the query i m using FYR

| inputlookup SLA.csv|table SOR_NAME SLA_THRESHOLD| join type=left SOR_NAME [ search index=xx source=xx|rex "info\s:\s+{4}\s(?\w+)\s+{4}\sJob run_ingest_(?\w+)(?\d+-\d+-\d+-\d+-\d+-\d+)" |where Datafeed_name!=""|rex field=Datafeed_name "^(?\w{2,5})_(?\w+)\$" |fields SOR_NAME time_stamp]|dedup SOR_NAME |eval time_diff = (SLA_THRESHOLD - time_stamp)|table SOR_NAME SLA_THRESHOLD time_stamp time_diff

Champion

Try this,

``````| inputlookup SLA.csv
| table SOR_NAME SLA_THRESHOLD
| join type=left SOR_NAME
[ search index=xx source=xx
| rex "info\s:\s+{4}\s(?\w+)\s+{4}\sJob run_ingest_(?\w+)(?\d+-\d+-\d+-\d+-\d+-\d+)"
| where Datafeed_name!=""
| rex field=Datafeed_name "^(?\w{2,5})_(?\w+)\$"
| eval time_stamp=strptime(strftime(strptime(time_stamp,"%Y-%m-%d-%H-%M-%S") ,"%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| fields SOR_NAME time_stamp]
| dedup SOR_NAME
| eval time_stamp = strptime(SLA_THRESHOLD,"%Y-%m-%d %H:%M:%S")
| eval time_diff = tostring((SLA_THRESHOLD-time_stamp), "duration")
| table SOR_NAME SLA_THRESHOLD time_stamp time_diff
``````
Explorer

no its not working ...getting the following result

SOR_NAME SLA_THRESHOLD time_stamp time_diff
1cid 2019-01-28 20:00:00 1548727200.000000

1lus 2019-01-28 20:00:00 1548727200.000000

Champion

try this previously wrongly assigned to this variable `| eval SLA_THRESHOLD = strptime(SLA_THRESHOLD,"%Y-%m-%d %H:%M:%S")`

``````| inputlookup SLA.csv
| table SOR_NAME SLA_THRESHOLD
| join type=left SOR_NAME
[ search index=xx source=xx
| rex "info\s:\s+{4}\s(?\w+)\s+{4}\sJob run_ingest_(?\w+)(?\d+-\d+-\d+-\d+-\d+-\d+)"
| where Datafeed_name!=""
| rex field=Datafeed_name "^(?\w{2,5})_(?\w+)\$"
| eval time_stamp=strptime(strftime(strptime(time_stamp,"%Y-%m-%d-%H-%M-%S") ,"%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| fields SOR_NAME time_stamp]
| dedup SOR_NAME
| eval SLA_THRESHOLD = strptime(SLA_THRESHOLD,"%Y-%m-%d %H:%M:%S")
| eval time_diff = tostring((SLA_THRESHOLD-time_stamp), "duration")
| table SOR_NAME SLA_THRESHOLD time_stamp time_diff
``````
Explorer

no luck though...getting this

SOR_NAME SLA_THRESHOLD time_stamp time_diff
1cid 1548727200.000000

1lus 1548727200.000000 1548729169.000000

Champion

try this

``````| inputlookup SLA.csv
| table SOR_NAME SLA_THRESHOLD
| join type=left SOR_NAME
[ search index=xx source=xx
| rex "info\s:\s+{4}\s(?\w+)\s+{4}\sJob run_ingest_(?\w+)(?\d+-\d+-\d+-\d+-\d+-\d+)"
| where Datafeed_name!=""
| rex field=Datafeed_name "^(?\w{2,5})_(?\w+)\$"
| eval time_stamp = strftime(strptime(time_stamp,"%Y-%m-%d-%H-%M-%S") ,"%Y-%m-%d %H:%M:%S")
| eval time_stamp_epoch=strptime(time_stamp,"%Y-%m-%d %H:%M:%S")
| fields SOR_NAME time_stamp,time_stamp_epoch]
| dedup SOR_NAME
| eval SLA_THRESHOLD_epoch = strptime(SLA_THRESHOLD,"%Y-%m-%d %H:%M:%S")
| eval time_diff = tostring((time_stamp_epoch-SLA_THRESHOLD_epoch), "duration")
| table SOR_NAME SLA_THRESHOLD time_stamp time_diff
``````
Explorer

the following is thw result ...not showing the difference time

SOR_NAME SLA_THRESHOLD time_stamp time_diff

1lus 2019-01-28 20:00:00 2019-01-28 20:32:49

Champion

Explorer

perfect..this is working...many thanks

Champion

welcome 🙂

Explorer

hi @vnravikumar ..sorry for the late question..for some reason the following query result showing some unwanted symbols in the out put

updatequery:
| inputlookup SLA.csv|rename SOR_TDQ_FAIL_SLA_THRESHOLD as SLA_THRESHOLD |eval SLA_THRESHOLD=if(SLA_THRESHOLD == "BUS_DT+0,21:00", "2019-01-28 20:00:00" ,SLA_THRESHOLD) |where SLA_THRESHOLD =="2019-01-28 20:00:00" |table SOR_NAME SLA_THRESHOLD| join type=left SOR_NAME [ search index=XXX source=XXX |rex "info\s:\s+{4}\s(?\w+)\s+{4}\sJob run_ingest_(?\w+)(?\d+-\d+-\d+-\d+-\d+-\d+)" |where Datafeed_name!=""|rex field=Datafeed_name "^(?\w{2,5})_(?\w+)\$" | eval time_stamp = strftime(strptime(time_stamp,"%Y-%m-%d-%H-%M-%S") ,"%Y-%m-%d %H:%M:%S")
| eval time_stamp_epoch=strptime(time_stamp,"%Y-%m-%d %H:%M:%S")
| fields SOR_NAME time_stamp,time_stamp_epoch]
| dedup SOR_NAME
| eval SLA_THRESHOLD_epoch = strptime(SLA_THRESHOLD,"%Y-%m-%d %H:%M:%S")
| eval time_diff = tostring((time_stamp_epoch-SLA_THRESHOLD_epoch), "duration")
| table SOR_NAME SLA_THRESHOLD time_stamp time_diff

result:

SOR_NAME SLA_THRESHOLD time_stamp time_diff

1lus 2019-01-28 20:00:00 2019-01-29 20:33:23 1+00:33:23.000000

Champion

Hi

1+00:33:23.000000 it showing difference i.e `1 day 33 mins and 23 seconds`. If you want it in a readable format then add the following statement before the table command `| eval time_diff=replace(time_diff,"(\d*)\+*(\d+):(\d+):(\d+)","\1 day(s) \2 hour(s) \3 minute(s) \4 sec(s)")`

``````| inputlookup SLA.csv|rename SOR_TDQ_FAIL_SLA_THRESHOLD as SLA_THRESHOLD |eval SLA_THRESHOLD=if(SLA_THRESHOLD == "BUS_DT+0,21:00", "2019-01-28 20:00:00" ,SLA_THRESHOLD) |where SLA_THRESHOLD =="2019-01-28 20:00:00" |table SOR_NAME SLA_THRESHOLD| join type=left SOR_NAME [ search index=XXX source=XXX |rex "info\s:\s+{4}\s(?\w+)\s+{4}\sJob run_ingest_(?\w+)(?\d+-\d+-\d+-\d+-\d+-\d+)" |where Datafeed_name!=""|rex field=Datafeed_name "^(?\w{2,5})_(?\w+)\$" | eval time_stamp = strftime(strptime(time_stamp,"%Y-%m-%d-%H-%M-%S") ,"%Y-%m-%d %H:%M:%S")
| eval time_stamp_epoch=strptime(time_stamp,"%Y-%m-%d %H:%M:%S")
| fields SOR_NAME time_stamp,time_stamp_epoch]
| dedup SOR_NAME
| eval SLA_THRESHOLD_epoch = strptime(SLA_THRESHOLD,"%Y-%m-%d %H:%M:%S")
| eval time_diff = tostring((time_stamp_epoch-SLA_THRESHOLD_epoch), "duration") | eval time_diff=replace(time_diff,"(\d*)\+*(\d+):(\d+):(\d+)\.(.*)","\1 day(s) \2 hour(s) \3 minute(s) \4 sec(s)")
| table SOR_NAME SLA_THRESHOLD time_stamp time_diff
``````
Explorer

ok got you...do you have any idea how to convert time_diff value into minutes

Explorer

i cant use makeresults command..let me explain the question more in detail.

I have mixing two queries to get the time difference. The main search has expected file arrival time in 2019-01-28 20:00:00 format and the sub search has the actual arrival time in date 2019-01-28-20-32-49 format .

I have to convert the sub search result format as like the main search result format and calculate the difference.

I am forwarding the query FYR

| inputlookup SLA.csv|rename SOR_TDQ_FAIL_SLA_THRESHOLD as SLA_THRESHOLD |eval SLA_THRESHOLD=if(SLA_THRESHOLD == "BUS_DT+0,21:00", "2019-01-28 20:00:00" ,SLA_THRESHOLD) |where SLA_THRESHOLD =="2019-01-28 20:00:00" |table SOR_NAME SLA_THRESHOLD| join type=left SOR_NAME [ search index=XX source=XX |rex "info\s:\s+{4}\s(?\w+)\s+{4}\sJob run_ingest_(?\w+)(?\d+-\d+-\d+-\d+-\d+-\d+)" |where Datafeed_name!=""|rex field=Datafeed_name "^(?\w{2,5})_(?\w+)\$" |fields SOR_NAME time_stamp]|dedup SOR_NAME |eval time_diff = (SLA_THRESHOLD - time_stamp)|table SOR_NAME SLA_THRESHOLD time_stamp time_diff

SplunkTrust

hi @pench2k19

Does this work for you

``````| makeresults
| eval orig1 = "2019-01-28-20-32-49"
| eval time1 = strptime(orig1, "%Y-%m-%d-%H-%M-%S")
| eval orig2 = "2019-01-27-10-11-23"
| eval time2 = strptime(orig2, "%Y-%m-%d-%H-%M-%S")
| eval diff = time1-time2
| eval diff_friendly = tostring(diff, "duration")
| table orig1 orig2 time1 time2 diff diff_friendly
``````

Hope it helps

Explorer

thanks for the quick response @chrisyoungerjds
let me give more details about my question

i have been joining two quries and calculate the time difference. In the main search i have got the time format as 2019-01-28 20:00:00 and in the subsearch i have got the time format as 2019-01-28-20-32-49

Now i want convert the 2019-01-28-20-32-49 into value like this 2019-01-28 20:32:49 and calculate the time difference.

following is the query i m using FYR

| inputlookup SLA.csv|table SOR_NAME SLA_THRESHOLD| join type=left SOR_NAME [ search index=xx source=xx|rex "info\s:\s+{4}\s(?\w+)\s+{4}\sJob run_ingest_(?\w+)(?\d+-\d+-\d+-\d+-\d+-\d+)" |where Datafeed_name!=""|rex field=Datafeed_name "^(?\w{2,5})_(?\w+)\$" |fields SOR_NAME time_stamp]|dedup SOR_NAME |eval time_diff = (SLA_THRESHOLD - time_stamp)|table SOR_NAME SLA_THRESHOLD time_stamp time_diff

Explorer

thansk for the prompt response @chrisyoungerjds ..let me give more details about the question...i m appending two queries where the first query has expected arival of file in 2019-01-28 20:00:00 and the subsearch has the arrival time of the file in 2019-01-28-20-32-49 .

I have to change the date format of the subsearch result as per the expected time format in the first query and find the difference between them.

following is the query i m using FYR

| inputlookup SLA.csv|rename SOR_TDQ_FAIL_SLA_THRESHOLD as SLA_THRESHOLD |eval SLA_THRESHOLD=if(SLA_THRESHOLD == "BUS_DT+0,21:00", "2019-01-28 20:00:00" ,SLA_THRESHOLD) |where SLA_THRESHOLD =="2019-01-28 20:00:00" |table SOR_NAME SLA_THRESHOLD| join type=left SOR_NAME [ search index=XXX source=XXXhost="wppra06a0337"|rex "info\s:\s+{4}\s(?\w+)\s+{4}\sJob run_ingest_(?\w+)(?\d+-\d+-\d+-\d+-\d+-\d+)" |where Datafeed_name!=""|rex field=Datafeed_name "^(?\w{2,5})_(?\w+)\$" |fields SOR_NAME time_stamp]|dedup SOR_NAME |eval time_diff = (SLA_THRESHOLD - time_stamp)|table SOR_NAME SLA_THRESHOLD time_stamp time_diff

Get Updates on the Splunk Community!

Starting With Observability: OpenTelemetry Best Practices

Tech Talk Starting With Observability: OpenTelemetry Best Practices Tuesday, October 17, 2023   |  11AM PST / ...

.conf23 | Get Your Cybersecurity Defense Analyst Certification in Vegas

We’re excited to announce a new Splunk certification exam being released at .conf23! If you’re going to Las ...

Streamline Data Ingestion With Deployment Server Essentials

REGISTER NOW! Every day the list of sources Admins are responsible for gets bigger and bigger, often making ...