Getting Data In

How do you calculate SLA with unstructured date format?

pench2k19
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

0 Karma
1 Solution

vnravikumar
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

View solution in original post

0 Karma

pench2k19
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

0 Karma

vnravikumar
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

View solution in original post

0 Karma

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

pench2k19
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

0 Karma

vnravikumar
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
0 Karma

pench2k19
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

0 Karma

vnravikumar
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
0 Karma

pench2k19
Explorer

no luck though...getting this

SOR_NAME SLA_THRESHOLD time_stamp time_diff
1cid 1548727200.000000

1lus 1548727200.000000 1548729169.000000

0 Karma

vnravikumar
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
0 Karma

pench2k19
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

0 Karma

vnravikumar
Champion

i had modified above query, please let me know.

0 Karma

pench2k19
Explorer

perfect..this is working...many thanks

0 Karma

vnravikumar
Champion

welcome 🙂

0 Karma

pench2k19
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

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

0 Karma

vnravikumar
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
0 Karma

pench2k19
Explorer

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

0 Karma

pench2k19
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

0 Karma

chrisyounger
SplunkTrust
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

0 Karma

pench2k19
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

0 Karma

pench2k19
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

0 Karma