topic Re: How to create a new date field with a value that is 90 days after the value in another date field? in Splunk Search

How to create a new date field with a value that is 90 days after the value in another date field?

siraj198204 — Wed, 24 Sep 2014 19:58:26 GMT

Hi ,

Similarly ,

source="dbmo-tail://idware/id_account" application=TFD [|inputlookup execSSO.csv |rename sso as owner] |fields source,Lastpasswordchangedate

The above query is working good.

I have the last passwordchangedate field , it is showing the date for lastpasswordchange date. by using this field i want to determine new field for password expire date . expire date time 90 days .

any one can help me on this .

Re: How to create a new date field with a value that is 90 days after the value in another date field?

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

What is the format of date in the field Lastpasswordchangedate?

If its a epoch time, then try this

source="dbmo-tail://idware/id_account" application=TFD [|inputlookup execSSO.csv |rename sso as owner] |fields source,Lastpasswordchangedate | eval Nextpasswordchangedate=relative_time(Lastpasswordchangedate,"+90d")
If its a string format date with fixed format, e.g. %Y/%m/%d %H:%M, then try this

source="dbmo-tail://idware/id_account" application=TFD [|inputlookup execSSO.csv |rename sso as owner] |fields source,Lastpasswordchangedate | eval Nextpasswordchangedate=strftime(relative_time(strptime(Lastpasswordchangedate,"%Y/%m/%d %H:%M"),"+90d"),"%Y/%m/%d %H:%M")

Re: How to create a new date field with a value that is 90 days after the value in another date field?

icyfeverr — Mon, 28 Sep 2020 17:41:37 GMT

I was just about to post the same thing, I am pretty sure he isn't using epoch time, so your second option is what he will want to use, but I think you can remove the strptime and just have the below.

eval exp_date=strftime(relative_time(Lastpasswordchangedate, "+90d@d"), "%m/%d/%Y")

Re: How to create a new date field with a value that is 90 days after the value in another date field?

siraj198204 — Thu, 25 Sep 2014 12:19:36 GMT

I am not getting any errors, while running this query , but i am not able to see field like Nextpasswordchangedate in the output . now trying to show is Nextpasswordchangedate field as visible .

The below fields are visible .
application =TFD
lastPasswordChange =2014-08-06 11:06:44
owner =206047890

Re: How to create a new date field with a value that is 90 days after the value in another date field?

siraj198204 — Thu, 25 Sep 2014 12:46:47 GMT

Thank for your help ,

i have ran this query , but it is not showing the nextpasswordchangedate field in output . i have set as a nextpassword change date is visible , it is has to show number of days for password expiry date ,

example , password expire date today is 90 , tomorrow is 89, 88,87. we need to get the Number of days till password expire ... day by it can reduce ... 86,85. that output and password expire date it should be visible...

once again thanks for a help ... 🙂

Regards,
Siraj

Re: How to create a new date field with a value that is 90 days after the value in another date field?

icyfeverr — Mon, 28 Sep 2020 17:42:04 GMT

Try the below at the end of your original search:

| eval exp_date=relative_time(Lastpasswordchangedate, "+90d@d") | eval exp_days=floor((exp_date-Lastpasswordchangedate)/86400)

If the above does not work, please copy and paste your entire search and provide a screenshot,

Re: How to create a new date field with a value that is 90 days after the value in another date field?

siraj198204 — Mon, 28 Sep 2020 17:42:20 GMT

source="dbmon-tail://idwarehouse/idw_account" application=TFAYD [|inputlookup execSSO.csv |rename sso as owner] |eval exp_date=strftime(relative_time(lastPasswordChange, "+90d@d"), "%y-%m-%d") | eval exp_days=floor((exp_date-lastpasswordchange)/86400)

my output ,
application =TFAYD
lastPasswordChange =2014-09-21 13:43:32
owner =206047117
refreshedDate =2014-09-25 09:14:52
sourcetype =mysql

if suppose i change the value to lastPasswordChangedate (dupilcate) i am getting exp date output is

application =TFAYD
exp_date =04-10-14 => actually it has to come => 2014-12-21 13:43:52
lastPasswordChange =2014-09-21 13:43:32
owner =206047117
refreshedDate =2014-09-25 09:14:52
sourcetype =mysql

Thanks for a help , actually my field is lastPasswordChange(original) , if i change this to lastPasswordChangedate like this i am getting the expire date output but it wrong ..

exp_date =04-10-14 ("%y-%m-%d") output is wrong ...

Re: How to create a new date field with a value that is 90 days after the value in another date field?

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

query 1
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"),"+90d"),"%Y/%m/%d %H:%M")

query 2
source="dbmon-tail://idwarehouse/idw_account" application=TFAYD [|inputlookup execSSO.csv |rename sso as owner] | eval exp_date=relative_time(lastPasswordChange, "+90d@d") | eval exp_days=floor((exp_date-lastPasswordChange)/86400)

my output ,
application =TFAYD
lastPasswordChange =2014-09-21 13:43:32
owner =206047117
refreshedDate =2014-09-25 09:14:52
sourcetype =mysql

I am not getting the output for exp_date and exp_day . any one can help me on this .

Thanks in advance .... 🙂
The original field is lastPasswordChange .
i modified the lastPasswordChange to lastPasswordChangedate , then i am getting the wrong output ,

exp_date =2004-10-14 it is wrong , correct output is 2014-12-21 13:43:32 ( 90 days)

Thanks for a help ... 🙂

Re: How to create a new date field with a value that is 90 days after the value in another date field?

siraj198204 — Mon, 28 Sep 2020 17:42:53 GMT

Hi ,

source="dbmon-tail://idwarehouse/idw_account" application=TFAYD [|inputlookup execSSO.csv |rename sso as owner] | eval exp_date=strftime(relative_time(Lastpasswordchangedate,"+90d@d"), "%Y-%m-%d %H:%M") |rename lastPasswordChange AS Lastpasswordchangedate

this query is working good,
my output ,

Lastpasswordchangedate =2014-08-06 11:11:43
application =TFAYD
exp_date =2004-09-28 00:00
owner =501936069
refreshedDate =2014-09-26 06:15:18
sourcetype =mysql

but i need the exp_date output is=2014-11-06 11:11:43 (90 days from the lastpasswordchangedate)

any one can help me on this ... ,source="dbmon-tail://idwarehouse/idw_account" application=TFAYD [|inputlookup execSSO.csv |rename sso as owner] | eval exp_date=strftime(relative_time(Lastpasswordchangedate,"+90d@d"), "%Y-%m-%d %H:%M") |rename lastPasswordChange AS Lastpasswordchangedate

The above query is working good,

my output ,

Lastpasswordchangedate =2014-08-06 11:11:43
application =TFAYD
exp_date =2004-09-28 00:00
owner =501936069
refreshedDate =2014-09-26 06:15:18
sourcetype =mysql

But i need the exp_date output is 2014-11-06 11:11:43 (90 days from the lastpassword change date )

any one can help me on this 🙂

Re: How to create a new date field with a value that is 90 days after the value in another date field?

siraj198204 — Mon, 28 Sep 2020 17:43:09 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