topic Re: How do you calculate SLA with unstructured date format? in Getting Data In

How do you calculate SLA with unstructured date format?

pench2k19 — Tue, 29 Jan 2019 10:40:05 GMT

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

Re: How do you calculate SLA with unstructured date format?

chrisyounger — Tue, 29 Jan 2019 10:50:13 GMT

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

Re: How do you calculate SLA with unstructured date format?

pench2k19 — Tue, 29 Sep 2020 23:00:06 GMT

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

Re: How do you calculate SLA with unstructured date format?

vnravikumar — Tue, 29 Jan 2019 11:33:39 GMT

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

Re: How do you calculate SLA with unstructured date format?

pench2k19 — Tue, 29 Sep 2020 22:58:25 GMT

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

Re: How do you calculate SLA with unstructured date format?

vnravikumar — Tue, 29 Jan 2019 11:55:40 GMT

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

Re: How do you calculate SLA with unstructured date format?

pench2k19 — Tue, 29 Sep 2020 22:58:28 GMT

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

Re: How do you calculate SLA with unstructured date format?

pench2k19 — Tue, 29 Sep 2020 22:58:31 GMT

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

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

Re: How do you calculate SLA with unstructured date format?

pench2k19 — Tue, 29 Sep 2020 22:58:34 GMT

let me explain the question more in detail

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

Re: How do you calculate SLA with unstructured date format?

vnravikumar — Tue, 29 Jan 2019 12:43:46 GMT

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

Re: How do you calculate SLA with unstructured date format?

pench2k19 — Tue, 29 Sep 2020 23:00:08 GMT

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

Re: How do you calculate SLA with unstructured date format?

vnravikumar — Tue, 29 Jan 2019 13:42:49 GMT

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

Re: How do you calculate SLA with unstructured date format?

pench2k19 — Tue, 29 Sep 2020 23:00:11 GMT

no luck though...getting this

SOR_NAME SLA_THRESHOLD time_stamp time_diff
1cid 1548727200.000000

1lus 1548727200.000000 1548729169.000000

Re: How do you calculate SLA with unstructured date format?

vnravikumar — Tue, 29 Jan 2019 14:46:47 GMT

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

Re: How do you calculate SLA with unstructured date format?

pench2k19 — Tue, 29 Sep 2020 23:00:13 GMT

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

Re: How do you calculate SLA with unstructured date format?

vnravikumar — Tue, 29 Jan 2019 15:01:30 GMT

i had modified above query, please let me know.

Re: How do you calculate SLA with unstructured date format?

pench2k19 — Tue, 29 Jan 2019 15:07:27 GMT

perfect..this is working...many thanks

Re: How do you calculate SLA with unstructured date format?

vnravikumar — Tue, 29 Jan 2019 15:30:14 GMT

welcome 🙂

Re: How do you calculate SLA with unstructured date format?

pench2k19 — Tue, 29 Sep 2020 23:01:07 GMT

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

the highlighted text is unexpected...can you please help me to resolve this

Re: How do you calculate SLA with unstructured date format?

vnravikumar — Wed, 30 Jan 2019 14:30:26 GMT

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