Hi ,
source="dbmon-tail://idwarehouse/idw_account" application=TFAYD [|inputlookup execSSO.csv |rename sso as owner] |eval exp_date=strftime(relative_time(strptime(lastPasswordChange,"%Y-%m-%d %H:%M:%S"),"+90d"), "%Y-%m-%d %H:%M:%S") |eval CurrentTime=strftime(now(), "%Y-%m-%d %H:%M:%S") |rex field=exp_date "(?\d+) (?\d+):(?\d+)" | rex field=CurrentTime "(?\d+) (?\d+):(?\d+)" | eval in_s = (in_d *86400)+(in_h * 3600) + (in_m * 60) | eval out_s = (out_d * 86400)+(out_h * 3600) + (out_m * 60) | eval diff = in_s - out_s |eval tot_d = floor(diff/86400) |eval diff = diff - (tot_d * 86400) | eval tot_h = floor(diff/3600) | eval diff = diff - (tot_h * 3600) |eval tot_m = (diff/60) | eval duration = tot_d . ":" . tot_h . ":" . tot_m
I tried the above query , it is working good , but month wise result is wrong .
i want to minux two field , it is exp_date - CurrentTime
my output ,
CurrentTime =2014-10-09 13:19:55
application =TFAYD
duration =15:5:43
exp_date =2014-12-24 19:02:04
in_d =24
in_h =19
in_m =02
in_s =2142120
lastPasswordChange =2014-09-25 19:02:04
out_d =09
out_h =13
out_m =19
out_s =825540
owner =206046611
refreshedDate =2014-10-09 12:26:05
sourcetype =mysql
tot_d =15
tot_h =5
tot_m =43
in_d - out_d , 24 -9 = 15 (tot_d) .... but it is wrong ...
2014-12-24 19:02:04 (exp_date) - (CurrentTime ) 2014-10-09 13:19:55 .... it is only doing minus ... 24-9 ... not month wise ...
Thank u ...
Regards,
Siraj
... View more