Splunk Search

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

siraj198204
Explorer
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

Tags (1)
0 Karma

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

I am on leave for some three to four days ... I can check this on 8th of this month ...

Thanks for a help ...

0 Karma

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

siraj198204
Explorer

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

0 Karma

siraj198204
Explorer

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

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...