source="dbmon-tail://idwarehouse/idw_account" application=TFAYD [|inputlookup execSSO.csv |rename sso as owner] |eval exp_date=strftime(relative_time(now(),"+90d@d"), "%Y/%m/%d %H:%M") |rename lastPasswordChange as lastpasswordchangedate
This is search is calculating the exp date is 90 days from today date . I am trying to get lastPasswordChange date to 90days exp date.
my output,
application =TFAYD
exp_date =2014/12/25 00:00
lastpasswordchangedate =2014-08-06 11:11:43
owner =501936069
refreshedDate =2014-09-26 12:16:36
sourcetype =mysql
The now() command is returning the current date , so this query is returning the exp_date =2014/12/25 00:00
, i want to return lastpasswordchangedate, so that i will get exp date 90days calculating from lastpasswordchangedate .
lastpasswordchangedate is a one of the field in splunk , i want to return this field value in splunk (lastpasswordchangedate =2014-08-06 11:11:43
) , so that i can get the exp_date value 90days from the lastpasswordchange date ,
in the same place , instead of now()
, if i place lastpasswordchangedate it is not returning the lastpasswordchangedate value ...
any one can help on this .....
Thanks and Regards,
Siraj
Hi ,
I have found an answer ,
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") | eval DateofExpire=strptime(exp_date, "%Y-%m-%d %H:%M:%S") |eval CurrentDay= now() |eval diff = DateofExpire - CurrentDay |eval DaystoExpire = tostring(diff,"duration")
my output,
CurrentDay =1412958153
CurrentTime =2014-10-10 12:22:33
DateofExpire =1419465724.000000
DaystoExpire =75+07:39:31.000000
application =TFAYD
exp_date =2014-12-24 19:02:04
lastPasswordChange =2014-09-25 19:02:04
owner =206046611
refreshedDate =2014-10-10 09:15:03
sourcetype =mysql
It is working good . i exactly found an answer is DaystoExpire =75+07:39:31.000000. Number of days to expire . It is correct .
Thanks for all
Thank you .
Regards,
Siraj
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
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
This query is working good ,
my output ,
CurrentTime =2014-10-09 10:56:25
application =TFAYD
duration =6:17:39
exp_date =2014-12-24 19:02:04
in_d =19
in_h =02
in_m =04
in_s =1649040
lastPasswordChange =2014-09-25 19:02:04
out_d =10
out_h =56
out_m =25
out_s =1067100
owner =206046611
refreshedDate =2014-10-09 09:14:14
sourcetype =mysql
tot_d =6
tot_h =17
tot_m =39
but it is considering the the hours As Number of day ... and Minutes as a Number of hours and seconds as a minutes ...
examples ,
exp_date =2014-12-24 19:02:04 , in this ... 19 is hours but it taking as 19days like that ...
02 is a minutes but it is taking as hours like that ...
04 is a seconds ... but it is calculating a minutes like that ....
in_d =19
in_h =02
in_m =04 .... see here ... 19 is hours ... but it is calculating as day ....
Thanks in advance ....
Regards,
Siraj
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") |eval CurrentTime=strftime(now(), "%Y-%m-%d %H:%M:%S") |rex field=exp_date "(?\d+):(?\d+)" | rex field=CurrentTime "(?\d+):(?\d+)" | eval in_s = (in_h * 3600) + (in_m * 60) | eval out_s = (out_h * 3600) + (out_m * 60) | eval diff = in_s - out_s | eval tot_h = floor(diff/3600) | eval diff = diff - (tot_h * 3600) | eval tot_m = diff/60 | eval duration = tot_h . ":" . tot_m
This is Query ...we have to do small modifications .....
Thank u ...
Regards,
Siraj
Hi ,
In this above query , we are able to calculate the current date and exp_date hours,minutes,seconds value , Now we have to calculate the number days , days value we have to convert in to seconds , have to minus the seconds ,(exp_day-currentday) then we can get seconds value , that we can convert to days ..... so we can get the result ..... ????
Thank u ....
Regards,
Siraj
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") |eval CurrentTime=strftime(now(), "%Y-%m-%d %H:%M:%S") |rex field=exp_date "(?\d+):(?\d+)" | rex field=CurrentTime "(?\d+):(?\d+)" | eval in_s = (in_h * 3600) + (in_m * 60) | eval out_s = (out_h * 3600) + (out_m * 60) | eval diff = out_s - in_s | eval tot_h = floor(diff/3600) | eval diff = diff - (tot_h * 3600) | eval tot_m = diff/60 | eval duration = tot_h . ":" . tot_m
My output,
CurrentTime =2014-10-08 13:48:51
application =TFAYD
duration =2:42
exp_date =2014-11-04 11:06
in_h =11
in_m =06
in_s =39960
lastPasswordChange =2014-08-06 11:06:44
out_h =13
out_m =48
out_s =49680
owner =206047117
refreshedDate =2014-10-08 12:19:26
sourcetype =mysql
i am able to calculate the hours,minute,seconds , but i want to calculate the days .... in_d is exp_day , out_d is Current Day ... i want to minus Exp_day - Current Day ..????? in_d - out_d
how can we calculate the day calculations ??? we are able to get the current day field and exp_day field .... we have to minus exp_day - Current Day ...
Thank u ,
Regards,
Siraj
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") | eval compare=strptime(exp_date,"%Y-%m-%d %H:%M:%S") | where compare>now()
I tried this , but iam getting error ,
No results found like that , if we are trying only exp_date , we are getting the result .
| eval compare=strptime(exp_date,"%Y-%m-%d %H:%M:%S") | where compare>now() this is not working ....
Thank u 🙂
Regards,
Siraj
Hi ,
|eval compare=strptime(exp_date,"%Y-%m-%d %H:%M:%S") | where compare>now()
I tried this , i am getting no results found error ...
Thank u ,
Regards,
Siraj
Hi ,
|eval compare=strptime(exp_date,"%Y-%m-%d %H:%M:%S") | where compare>now()
I tried this , i am getting no results found error ...
Thank u ..
Regards,
Siraj,Hi ,
|eval compare=strptime(exp_date,"%Y-%m-%d %H:%M:%S") | where compare>now()
I tried this , i am getting no results found error ...
I am on leave for some three to four days ... I can check this on 8th of this month ...
because I am not having server access from here ... form the outside network 🙂
Thanks for a help ...
Regards,
Siraj
I am on leave for some three to four days ... I can check this on 8th of this month ...
Because I am not having the server access from outside network ... 🙂
Thanks for a help ...
I am on leave for some three to four days ... I can check this on 8th of this month ...
Thanks for a help ...
http://docs.splunk.com/Documentation/Splunk/5.0.4/SearchReference/convert#Examples
dur2sec()
Syntax: dur2sec(
Description: Convert a duration format "D+HH:MM:SS" to seconds. ,
but here ... we are able to see , it is converting the fields values in to seconds ...
Hi,
dur2sec()
Syntax: dur2sec(
Description: Convert a duration format "D+HH:MM:SS" to seconds.
we have to convert this two fields in to seconds , that output have to apply the seconds value in tostring command .... , then we can get the output ... ..... trying to get the output .... as of now i am not getting the results ...
Thank u ,
Regards,
Siraj
Hi ,
host="sample" | convert dur2sec(Out_Time) dur2sec(In_Time) | eval diff=(Out_Time - In_Time) | eval Newfield=tostring(diff, "duration") | table Newfield
this is sample command ,
|convert dur2sec(exp_date) dur2sec(now()) |eval diff=(exp-date-now()) |eval exp_day=tostring(diff,"duration")
i tried this but not getting field output exp_day in output ...
is this correct ...?
Thank u
Regards,
Siraj
Hi,
1.we have a current date , now()
2.we have a expire date .
1. command tostring only gives seconds as output ...
example , |eval exp_day=(86400,"duration") it is outputs,
exp_day=90+ (90days)
exp_day-now() ..... output ... 36+,35+,34+
trying to get the answer ...
Thank u ,
Regards,
Siraj
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@d"), "%Y/%m/%d %H:%M") |rename lastPasswordChange as lastpasswordchangedate | eval DaysToExpire=tostring(strptime(exp_date,"%Y-%m-%d %H:%M:%S")-now(),"duration")
in this query , splunk is not accepting the - symbol with command .... example -now() ...
Thank u ,
Regards,
Siraj
try this
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@d"), "%Y/%m/%d %H:%M") |rename lastPasswordChange as lastpasswordchangedate | eval DaysToExpire=tostring((strptime(exp_date,"%Y-%m-%d %H:%M:%S")-now()),"duration")
Hi somesoni,
eval compare=strptime(exp_date,"%Y-%m-%d %H:%M:%S") | where compare>strptime(now(),"%Y-%m-%d %H:%M:%S")
I tried this to compare two fields , to get number of day of password expire ....
but not getting the result ....
Thank u ,
Regards,
Siraj
Hi somesoni,
eval compare=strptime(exp_date,"%Y-%m-%d %H:%M:%S") | where compare>strptime(now(),"%Y-%m-%d %H:%M:%S")
I tried this to compare two fields , to get number of day of password expire ....
but not getting the result ....
Thank u ,
Regards,
Siraj