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!