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
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
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
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
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)")
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
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
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
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
no luck though...getting this
SOR_NAME SLA_THRESHOLD time_stamp time_diff
1cid 1548727200.000000
1lus 1548727200.000000 1548729169.000000
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
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
i had modified above query, please let me know.
perfect..this is working...many thanks
welcome 🙂
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
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
ok got you...do you have any idea how to convert time_diff value into minutes
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
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
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
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