topic Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days. in Splunk Search

i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Fri, 26 Sep 2014 20:09:28 GMT

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

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

somesoni2 — Fri, 26 Sep 2014 21:08:24 GMT

Try this (based on your sample value, if the format of date is changed, updated the strptime parameter accordingly)

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

Update

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=round((strptime(exp_date,"%Y-%m-%d %H:%M:%S")-strptime(lastPasswordChange,"%Y-%m-%d %H:%M:%S"))/86400)

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Mon, 29 Sep 2014 11:44:10 GMT

Hi ,

It is working good, Now i am trying to write the output for Expire day . I think it is (lastPasswordChange-exp_date) . exp day is the final output for this query .

Thank u , 🙂

Regards,
Siraj,Hi ,

Thank u , it is working good . Now i am trying to write the output for expire day . it is (lastPasswordChange-exp_date) expire day .

Thank a lot ,

Regards,
Siraj

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

somesoni2 — Mon, 29 Sep 2014 12:40:43 GMT

We are calculating exp_date as relative time of lastPasswordChange date, so its always going to be 90 days. My guess is that yiu want to calculate it based on now () so that you'll get how many days left for expiry. Correct me if I'm wrong.

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Mon, 28 Sep 2020 17:44:04 GMT

This query is working good ,

my output is ,

application =TFAYD
exp_date =2014-11-04 00:00:00 ( it is correct, calculating from lastPasswordChange)
lastpasswordchangedate =2014-08-06 11:11:43
owner =501936069
refreshedDate =2014-09-29 09:15:24
sourcetype =mysql

it is working good exp_date is working good , i need one more output ... exp_day format , for example

90days,89days,88days password can expire like that ....

So expiredays= 90days,89days,88days.

we have calculate same this also from lastpassword change date ...

please help me on this ....

Thank you ...

This query is working good ,

my output is ,

application =TFAYD
exp_date =2014-11-04 00:00:00
lastpasswordchangedate =2014-08-06 11:11:43
owner =501936069
refreshedDate =2014-09-29 09:15:24
sourcetype =mysql

it is working good exp_date is working good , i need one more output ... exp_day format , for example

90days,89days,88days password can expire like that ....

So expiredays= 90days,89days,88days.

we have calculate same this also from lastpassword change date ...

please help me on this ....

Thank you ...

Regards,
Siraj

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Mon, 28 Sep 2020 17:44:26 GMT

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") |eval exp_day=exp_date - lastPasswordChange | where exp_date < strftime(relative_time(strptime(lastPasswordChange,"%Y-%m-%d %H:%M:%S"),"+90d"),"%Y-%m-%d %H:%M:%S") |rename lastPasswordChange as lastpasswordchangedate ,

I am trying to get the exp_day ,

|eval exp_day=exp_date-lastPasswordChange | where exp_date < strftime(relative_time(strptime(lastPasswordChange,"%Y-%m-%d %H:%M:%S"),"+90d"),"%Y-%m-%d %H:%M:%S")

This exp_day query is correct ? correct me if i am wrong .... i am trying to get the expire day .... 🙂
any can help me on this ...

other output is good ,

other outputs are,

application =TFAYD
exp_date =2014-11-04 00:00:00 ( it is correct, calculating from
lastPasswordChange)
lastpasswordchangedate =2014-08-06 11:11:43
owner =501936069
refreshedDate =2014-09-29 09:15:24
sourcetype =mysql

Trying to add the field for exp_day ..... 🙂

Thank u ...

Regards,
Siraj
,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") |eval exp_day=exp_date-lastPasswordChange | where exp_date < strftime(relative_time(strptime(lastPasswordChange,"%Y-%m-%d %H:%M:%S"),"+90d"),"%Y-%m-%d %H:%M:%S") |rename lastPasswordChange as lastpasswordchangedate ,

I am trying to get the exp_day ,

|eval exp_day=exp_date-lastPasswordChange | where exp_date < strftime(relative_time(strptime(lastPasswordChange,"%Y-%m-%d %H:%M:%S"),"+90d"),"%Y-%m-%d %H:%M:%S")

This exp_day query is correct ? correct me if i am wrong .... i am trying to get the expire day .... 🙂
any can help me on this ...

other output is good ,

other outputs are,

application =TFAYD
exp_date =2014-11-04 00:00:00 ( it is correct, calculating from
lastPasswordChange)
lastpasswordchangedate =2014-08-06 11:11:43
owner =501936069
refreshedDate =2014-09-29 09:15:24
sourcetype =mysql

Trying to add the field for exp_day ..... 🙂

Thank u ...

Regards,
Siraj

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Tue, 30 Sep 2014 15:40:38 GMT

Hi ,

I want to minus this ,

lastpasswordchangedate =2014-08-06 11:11:43 and exp_date =2014-11-04 00:00:00 , and convert it as a number of days ,

any one can help me on this ...

Thanks u ..

Regards,
Siraj

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Mon, 28 Sep 2020 17:45:26 GMT

Hi,

actually we have a expire date , by using we have to found exp_day ,

it is expdate-currentdate(now()), it is should in the format of days ... 35days,34days,....

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

Exp Day query samples ,

| convert dur2sec(now()) as reqTimeConverted | convert dur2sec(expdate) as respTimeConverted | eval dur_in_sec=respTimeConverted-reqTimeConverteds |eval newout=tostring(dur_in_sec,"duration")

in this query , tostring is not accepting the field values .... it can accept only number in seconds .

any one can help me on this ...

Thanks u ,

Regards,
Siraj

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

somesoni2 — Mon, 28 Sep 2020 17:45:29 GMT

Try this then

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

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Wed, 01 Oct 2014 11:12:29 GMT

Hi,

I tried this, i am not getting output for DaysToExpire , Generally In the tostring command , if we are giving the input as a seconds, it is generating the output as or converting to days .... not getting the field of DaysToExpire ...

Thank u ,

Regards,
Siraj

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Wed, 01 Oct 2014 11:15:54 GMT

actually inside of the tostring command we are giving the field value , it can convert to days and hours,minute,seconds , but i think the tostring command need direct input of seconds , then only it can convert it in to days ...

exp_day output we have to convert as a seconds, that seconds output we have to submit in tostring command ,

example , expire_day=tostring(seconds,"duration")

seconds we have to calculate for expiry day ..

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Mon, 28 Sep 2020 17:45:59 GMT

i have one sample query ,

| gentimes start=now() end=exp_date | search starthuman!="Sun*" starthuman!="Sat*" | stats count

it can calculate the number of days between exp_date and current date ... but this query is not working ,,,,

Thank u ,

Regards,
Siraj

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Wed, 01 Oct 2014 19:34:17 GMT

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

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Wed, 01 Oct 2014 19:34:31 GMT

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

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Mon, 28 Sep 2020 17:46:28 GMT

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

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Wed, 01 Oct 2014 19:53:20 GMT

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

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Mon, 28 Sep 2020 17:46:31 GMT

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

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Wed, 01 Oct 2014 20:09:37 GMT

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

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

siraj198204 — Wed, 01 Oct 2014 20:33:43 GMT

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 ...

Re: i have one field like lastpasswordchangedate , by using this field i want to return value of password expiry date .... 90days.

somesoni2 — Wed, 01 Oct 2014 22:43:32 GMT

Value "now()" is already in epoch so you don't need to convert it to epoch. You can try following

| eval compare=strptime(exp_date,"%Y-%m-%d %H:%M:%S") | where compare>now()