Page 1 of 2 12>
Topic Options
#65829 - 2002-05-30 06:29 PM Password expiration for a service account
Kdyer Offline
KiX Supporter
*****

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
We have a policy where all accounts (user, etc.) expire in X days..

I wanted to create a notification script to notify when the account is due to expire.

Comments, etc. are welcome.

code:
 
BREAK ON
CLS

; -- Ref. http://kixtart.org/board/Forum2/HTML/000133.html for date routine

IF Left(@kix,1) < 4
?"This script requires Kixtart 4.x"
SLEEP 2
EXIT
ELSE

$ldomain = @domain
$lstrusername = "servicesql"
$mailhost = "smtp.server.com"
$adminrecpt = "recipient@@server.com"

$userobj = GetObject("WinNT://$LDomain/$lstrUserName")

;$UserObj = GetObject("WinNT://" + $DomainString + "/" + $lstrUserName)

$pwexpire = $userobj.passwordexpirationdate
$dtcalc = $pwexpire = @date

?"UserAuthor: " + $userobj.name
?"UserPasswordExpires: " + $pwexpire

$tt = $pwexpire

$pwdate = substr($tt,1,10)

$pwyear = val(substr($tt,1,4))
$pwmonth = val(substr($tt,6,2))
$pwdate = val(substr($tt,9,2))

$nowyear = @year
$nowmonth = @monthno
$nowdate = @mdayno

IF $pwyear <> $nowyear
$nowmonth = $nowmonth + (12 * ($nowyear - $pwyear))
ENDIF

IF $pwmonth <> $nowmonth
$nowdate = $nowdate + 30
ENDIF

?$nowdate - $pwdate

IF ($nowdate - $pwdate) <= 2
? "Password Change required within 2 days, please change"
SHELL 'postie.exe -host:$MailHost -to:$AdminRecpt -from:"ServiceAccountChg" -s:"Password Change" -msg:"The service account: $lstrUserName needs to be changed."'
ELSE
? "Password older than 2 Days, no action is needed"
ENDIF
ENDIF

Cheers!

- Kent

[ 30 May 2002, 19:05: Message edited by: kdyer ]
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#65830 - 2002-05-30 11:52 PM Re: Password expiration for a service account
BrianTX Offline
Korg Regular

Registered: 2002-04-01
Posts: 895
Kent,

I'll test your script if you test mine! (lol)

Brian

Top
#65831 - 2002-05-31 05:07 AM Re: Password expiration for a service account
Kdyer Offline
KiX Supporter
*****

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
It needs some help..

I am running to some trouble with substr..

Sure.. I can look into your script.

- Kent
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#65832 - 2002-05-31 10:34 AM Re: Password expiration for a service account
Breaker Offline
Hey THIS is FUN
*****

Registered: 2001-06-15
Posts: 268
Loc: Yorkshire, England
Kent,

I've not used it myself (I leave tricky stuff like this to the pros!) but Jpols has a UDF DateCalc() which might save you the trouble of doing all the Substr'ing yourself. I assume it is the date calculation that's giving you grief?

Breaker
_________________________
================================================
Breaker


Top
#65833 - 2002-05-31 05:09 PM Re: Password expiration for a service account
BrianTX Offline
Korg Regular

Registered: 2002-04-01
Posts: 895
I just whipped up a little script.. I may post the UDF in the UDF forum (I thought it was handy anyways..)

code:
BREAK ON
CLS

IF Left(@kix,1) < 4
?"This script requires Kixtart 4.x"
SLEEP 2
EXIT
ELSE
$ldomain = @domain
$lstrusername = "servicesql"
$mailhost = "smtp.server.com"
$adminrecpt = "recipient@@server.com"
$userobj = GetObject("WinNT://$LDomain/$lstrUserName")
$pwexpire = $userobj.passwordexpirationdate

?"UserAuthor: " + $userobj.name
?"UserPasswordExpires: " + $pwexpire

$tt = SPLIT($pwexpire," ")
$mdy = SPLIT($tt[0],"/")
$expiredatearray = $mdy[2],$mdy[0],$mdy[1]
$currentdatearray = "@YEAR","@MONTHNO","@MDAYNO"


Function CalcDayofYear($ymdarray) ; Only works on non-millenium years after 2000.
$Calendar = 31,28,31,30,31,30,31,31,30,31,30,31
If VAL($ymdarray[0]) & 1 $Ly = 0 ; leap year calculations
Else
If (Val($ymdarray[0])/2) & 1 $Ly = 0
Else $Ly = 1
Endif
Endif
$calendar[1] = $calendar[1] + $LY
$mdays = 0
For $m = 0 to (Val($ymdarray[1]) -2)
$mdays = $mdays + $calendar[$m]
Next
$CalcDayofYear = $mdays + $ymdarray[2]
EndFunction

$daystoexpire = CalcDayofYear($expiredatearray) - CalcDayofYear($currentDateArray) + ((VAL($expiredatearray[0]) - VAL(currentdataArray[0])) * 365)

If $daystoexpire <= 2
? "Password Change required within 2 days, please change"
SHELL 'postie.exe -host:$MailHost -to:$AdminRecpt -from:"ServiceAccountChg" -s:"Password Change" -msg:"The service account: $lstrUserName needs to be changed."'
ELSE
? "Password will not expire for more than 2 Days, no action is needed"
ENDIF

ENDIF

What do you think? It works great for me.

Brian

[ 04 June 2002, 17:15: Message edited by: BrianTX ]

Top
#65834 - 2002-05-31 05:53 PM Re: Password expiration for a service account
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11623
Loc: CA
FYI,

Active Directory needs:

$userobj = GetObject("LDAP://cn=??,ou=??,etc...")

Top
#65835 - 2002-05-31 06:14 PM Re: Password expiration for a service account
BrianTX Offline
Korg Regular

Registered: 2002-04-01
Posts: 895
I don't use Win2k Servers.. I don't know if Kent does or not. [Smile] I suppose this could be modified to check for active directory....?

Brian

Top
#65836 - 2002-05-31 09:53 PM Re: Password expiration for a service account
Kdyer Offline
KiX Supporter
*****

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
Brian,

Works a treat!

Doc - Sure you could use AD, but I think this is simpler. [Big Grin]

If you want the VBScript version..

code:
Dim DomainString, UserObj,PwExpire,TodayDt,DtCalc,FmtDate

DomainString = "YOURDOMAIN"

lstrUserName = "servicesql"

Set UserObj = GetObject("WinNT://" & DomainString & "/" & lstrUserName)

PwExpire = UserObj.PasswordExpirationDate
DtCalc = DateDiff("d",date,PwExpire)
'TodayDt = Date
'DtCalc = FmtDate - TodayDt

If DtCalc <= 2 Then
wscript.echo "Please Change your servicesql password"
end if

wscript.echo "UserAuthor: " & UserObj.Name
wscript.echo "UserPasswordExpires: " & PwExpire
'wscript.echo TodayDt
wscript.echo DtCalc

Pieces are found at - http://cwashington.netreach.net/depo/view.asp?Index=198&ScriptType=vbscript

Thanks,

- Kent

[ 31 May 2002, 21:55: Message edited by: kdyer ]
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#65837 - 2002-05-31 10:04 PM Re: Password expiration for a service account
BrianTX Offline
Korg Regular

Registered: 2002-04-01
Posts: 895
I had to edit script above to allow for the expiration date coming in a different year...

Lemme see if I can think of a scenario it wouldn't be accurate...

1. one of the dates is in the year 2100.
2. Date last changed is after Feb 28 on a leap year and Date expired is the next year. (You'd get a day less than correct so a day early warning)

I could fix these things by making a leap year function.. or recording days from January 1, 2002. Is this necessary?

[edit: hmm. or making a loop step from expire year to changed year and count the days in the year (December 31)]

Brian

[ 31 May 2002, 22:08: Message edited by: BrianTX ]

Top
#65838 - 2002-05-31 10:29 PM Re: Password expiration for a service account
BrianTX Offline
Korg Regular

Registered: 2002-04-01
Posts: 895
OK. I'm reposting the corrected version which does not have the inconsistencies of the other version.

code:
BREAK ON
CLS

IF Left(@kix,1) < 4
?"This script requires Kixtart 4.x"
SLEEP 2
EXIT
ELSE
$ldomain = @domain
$lstrusername = "servicesql"
$mailhost = "smtp.server.com"
$adminrecpt = "recipient@@server.com"
$userobj = GetObject("WinNT://$LDomain/$lstrUserName")
$pwexpire = $userobj.passwordexpirationdate

?"UserAuthor: " + $userobj.name
?"UserPasswordExpires: " + $pwexpire

$tt = SPLIT($pwexpire," ")
$mdy = SPLIT($tt[0],"/")
$expiredatearray = $mdy[2],$mdy[0],$mdy[1]
$currentdatearray = "@YEAR","@MONTHNO","@MDAYNO"


Function CalcDayofYear($ymdarray) ; Only works on non-millenium years after 2000.
$Calendar = 31,28,31,30,31,30,31,31,30,31,30,31
If VAL($ymdarray[0]) & 1 $Ly = 0 ; leap year calculations
Else
If (Val($ymdarray[0])/2) & 1 $Ly = 0
Else $Ly = 1
Endif
Endif
$calendar[1] = $calendar[1] + $LY
$mdays = 0
For $m = 0 to (Val($ymdarray[1]) -2)
$mdays = $mdays + $calendar[$m]
Next
$CalcDayofYear = $mdays + $ymdarray[2]
EndFunction

$diffyears = VAL($expiredatearray[0]) - VAL($currentdatearray[0])
If $diffyears > 0
$YearstoDays = 0
For $countyear = VAL($currentdatearray[0]) to (VAL($expiredatearray[0])-1)
$acountyear = $countyear,"12","31"
$YearstoDays = $Yearstodays + CalcDayofYear($acountyear)
Next
Endif

$daystoexpire = CalcDayofYear($expiredatearray) - CalcDayofYear($currentDateArray) + $yearstodays
If $daystoexpire <= 2
? "Password Change required within 2 days, please change"
SHELL 'postie.exe -host:$MailHost -to:$AdminRecpt -from:"ServiceAccountChg" -s:"Password Change" -msg:"The service account: $lstrUserName needs to be changed."'
ELSE
? "Password will not expire for more than 2 Days, no action is needed"
ENDIF

ENDIF

I fixed the problem with the next year/leap year stuff. Still won't work with the year 2100 (who cares?)

Brian

[ 04 June 2002, 17:16: Message edited by: BrianTX ]

Top
#65839 - 2002-05-31 10:35 PM Re: Password expiration for a service account
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11164
Loc: Boston, MA, USA
Whyt don't you just use the following two UDFs instead?
DateCalc() - Calculates Days between or returns calculated Date (Days as modifier)
SerialDate() - Convert dates to numbers (and back) for the purpose of performing date
_________________________
There are two types of vessels, submarines and targets.

Top
#65840 - 2002-05-31 11:01 PM Re: Password expiration for a service account
BrianTX Offline
Korg Regular

Registered: 2002-04-01
Posts: 895
You're right.. their line:

$SerialDate=$d+(153*$m-457)/5+365*$y+$y/4-$y/100+$y/400-306

does everything in one line that i do in several lines. However, you have to go through a bunch of math theorems to come up with that formula... (I know.. someone worked it out a long time ago. I saw something like that in the 3rd grade.).. Anyway it's just a good exercise to do it yourself! (and my code is smaller than with the functions from scriptlogic)

Brian

Top
#65841 - 2002-05-31 11:13 PM Re: Password expiration for a service account
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11623
Loc: CA
Kent,

FYI
In general a WinNT call will work for a lot of things on AD, but not everything. WinNT does not understand the Schema of AD so it can not get "all" details. LDAP is designed to do this.

Top
#65842 - 2002-06-01 04:10 AM Re: Password expiration for a service account
Kdyer Offline
KiX Supporter
*****

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
Jens,

I have used the SerialDate and DateMath recently for a similar type of post, but I think BrianTX's code is much better at it (more compact). I did read the link that Brian Styles placed on the Serial Date with regard to the algorithms used and found it interesting.

- Kent
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#65843 - 2002-06-03 04:56 PM Re: Password expiration for a service account
BrianTX Offline
Korg Regular

Registered: 2002-04-01
Posts: 895
It has to do with the Julian date, correct? I thought it was interesting as well. Because it is an approximation, I was wondering what dates it has been tested for, and if it is 100% accurate.

Brian

Top
#65844 - 2002-06-03 05:27 PM Re: Password expiration for a service account
Kdyer Offline
KiX Supporter
*****

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
Doc,

Question for you..

Trying to use the LDAP Method and I keep getting:
quote:

Script error : unknown command !
$pwexpire = $userobj.passwordexpirationdate

If we look at Active Directory Users and computers and have "Advanced Features" turned on.. We look at the object, we see:
quote:

DOMAIN.com/Accounts/Users/Service Account Users/SQL Service Accounts/ServiceSql

So, we make the changes per your suggestion, and refer to - http://cwashington.netreach.net/depo/default.asp?topic=adsifaq

quote:

$userobj = GetObject("LDAP://PDC.DOMAIN.com,ou=Accounts,ou=Users,ou=Service Account Users,ou=SQL Service Accounts,cn=ServiceSql")

Any ideas?

Thanks,

- Kent
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#65845 - 2002-06-04 05:04 PM Re: Password expiration for a service account
Kdyer Offline
KiX Supporter
*****

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
Made some changes to the script to add in a simple array (split) for checking other accounts that fall into this realm.

Doc - You out there? [Smile]

Here is the modified code:
code:
 BREAK ON
CLS

; -- Author - Kent Dyer
; -- Original Date - 30 May 2002
; -- Change Date - 4 June 2002
; -- Initially used Date Routine - http://kixtart.org/board/Forum2/HTML/000133.html
; -- Date Routine built/changes by BrianTX
; -- Optionally, you could use DateMath, SerialDate from
; -- http://scriptlogic.com/Kixtart/ViewFunction.asp?FN=SerialDate
; -- http://scriptlogic.com/Kixtart/ViewFunction.asp?FN=DateMath
; -- Added a simple array (using split) from Kix 4.x 6/4/02
; -- pieces taken from
; -- http://cwashington.netreach.net/depo/view.asp?Index=198&ScriptType=vbscript
; -- http://cwashington.netreach.net/depo/view.asp?Index=323&ScriptType=vbscript
; -- http://kixtart.org/board/ultimatebb.php?ubb=get_topic;f=2;t=003347

IF Left(@kix,1) < 4
?"This script requires Kixtart 4.x"
SLEEP 2
EXIT
ELSE
$ldomain = @domain
;$lstrusername = "servicesql" ; -- uncomment out if you don't want to use split
$lstrusername = Split("servicesql~~cbcwindu~~servicemmc", "~~") ; -- comment out if you don't want to use split
$mailhost = "smtphost.domain.com" ; -- fill in with your smtp server
$adminrecpt = "user@@domain.com" ; -- add in your e-mail address

FOR EACH $element IN $lstrusername ; -- comment out if you don't want to use split

;$userobj = GetObject("LDAP://PDC.DOMAIN.com,ou=Accounts,ou=Users,ou=Service Account Users,ou=SQL Service Accounts,cn=ServiceSql")
;$userobj = GetObject("WinNT://$LDomain/$lstrUserName") ; -- uncomment out if you don't want to use split
$userobj = GetObject("WinNT://$LDomain/$Element") ; -- comment out if you don't want to use split
$pwexpire = $userobj.passwordexpirationdate

?"UserName: " + $userobj.name
?"UserPasswordExpires: " + $pwexpire

$tt = SPLIT($pwexpire," ")
$mdy = SPLIT($tt[0],"/")
$expiredatearray = $mdy[2],$mdy[0],$mdy[1]
$currentdatearray = "@YEAR","@MONTHNO","@MDAYNO"


FUNCTION CalcDayofYear($ymdarray) ; Only works on non-millenium years after 2000.
$calendar = 31,28,31,30,31,30,31,31,30,31,30,31
IF VAL($ymdarray[0]) & 1 $ly = 0 ; leap year calculations
ELSE
IF (Val($ymdarray[0])/2) & 1 $ly = 0
ELSE
$ly = 1
ENDIF
ENDIF
$calendar[2] = $calendar[2] + $ly
$mdays = 0
FOR $m = 0 TO (Val($ymdarray[1]) -2)
$mdays = $mdays + $calendar[$m]
NEXT
$calcdayofyear = $mdays + $ymdarray[2]
ENDFUNCTION

$daystoexpire = CalcDayofYear($expiredatearray) - CalcDayofYear($currentdatearray)
?$daystoexpire

IF $daystoexpire <= 2
? "Password Change required within 2 days, please change"
;SHELL 'postie.exe -host:$MailHost -to:$AdminRecpt -from:"ServiceAccountChg" -s:"Password Change" -msg:"The service account: $lstrUserName needs to be changed."'
SHELL 'postie.exe -host:$MailHost -to:$AdminRecpt -from:"ServiceAccountChg" -s:"Password Change" -msg:"The service account: $Element needs to be changed."'
ELSE
? "Password will not expire for more than 2 Days, no action is needed"
ENDIF
NEXT ; -- comment out if you don't want to use split

ENDIF

Thanks!

- Kent
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#65846 - 2002-06-04 05:15 PM Re: Password expiration for a service account
BrianTX Offline
Korg Regular

Registered: 2002-04-01
Posts: 895
Kent.. I keep finding bugs in the script! lol... (geez... can't seem to get everything to work 100% correctly)

It should be:

$calendar[1] = $calendar[1] + $ly

(i forgot the 0 element of the array was january)

Also, please see the second script I posted that accounts for year rollover...

Brian

Top
#65847 - 2002-06-04 07:16 PM Re: Password expiration for a service account
Kdyer Offline
KiX Supporter
*****

Registered: 2001-01-03
Posts: 6241
Loc: Tigard, OR
Brian,

Works great..
quote:

UserName: ServiceSql
UserPasswordExpires: 7/9/2002 6:18:13 AM
Number of Days till password expires: 35
Password will not expire for more than 2 Days, no action is needed
UserName: cbcwindu
UserPasswordExpires: 8/28/2002 10:29:29 AM
Number of Days till password expires: 85
Password will not expire for more than 2 Days, no action is needed
UserName: servicemmc
UserPasswordExpires: 7/17/2002 7:26:16 AM
Number of Days till password expires: 43
Password will not expire for more than 2 Days, no action is needed

Added in your fix..

code:
 BREAK ON
CLS

; -- Author - Kent Dyer
; -- Original Date - 30 May 2002
; -- Change Date - 4 June 2002
; -- Initially used Date Routine - http://kixtart.org/board/Forum2/HTML/000133.html
; -- Date Routine built/changes by BrianTX
; -- Optionally, you could use DateMath, SerialDate from
; -- http://scriptlogic.com/Kixtart/ViewFunction.asp?FN=SerialDate
; -- http://scriptlogic.com/Kixtart/ViewFunction.asp?FN=DateMath
; -- Added a simple array (using split) from Kix 4.x 6/4/02
; -- pieces taken from
; -- http://cwashington.netreach.net/depo/view.asp?Index=198&ScriptType=vbscript
; -- http://cwashington.netreach.net/depo/view.asp?Index=323&ScriptType=vbscript
; -- http://kixtart.org/board/ultimatebb.php?ubb=get_topic;f=2;t=003347

IF Left(@kix,1) < 4
?"This script requires Kixtart 4.x"
SLEEP 2
EXIT
ELSE
$ldomain = @domain
;$lstrusername = "servicesql" ; -- uncomment out if you don't want to use split
$lstrusername = Split("servicesql~~cbcwindu~~servicemmc", "~~") ; -- comment out if you don't want to use split
$mailhost = "smtphost.domain.com" ; -- fill in with your smtp server
$adminrecpt = "user@@domain.com" ; -- add in your e-mail address

FOR EACH $element IN $lstrusername ; -- comment out if you don't want to use split

;$userobj = GetObject("LDAP://PDC.DOMAIN.com,ou=Accounts,ou=Users,ou=Service Account Users,ou=SQL Service Accounts,cn=ServiceSql")
;$userobj = GetObject("WinNT://$LDomain/$lstrUserName") ; -- uncomment out if you don't want to use split
$userobj = GetObject("WinNT://$LDomain/$Element") ; -- comment out if you don't want to use split
$pwexpire = $userobj.passwordexpirationdate

?"UserName: " + $userobj.name
?"UserPasswordExpires: " + $pwexpire

$tt = SPLIT($pwexpire," ")
$mdy = SPLIT($tt[0],"/")
$expiredatearray = $mdy[2],$mdy[0],$mdy[1]
$currentdatearray = "@YEAR","@MONTHNO","@MDAYNO"


FUNCTION CalcDayofYear($ymdarray) ; Only works on non-millenium years after 2000.
$calendar = 31,28,31,30,31,30,31,31,30,31,30,31
IF VAL($ymdarray[0]) & 1 $ly = 0 ; leap year calculations
ELSE
IF (Val($ymdarray[0])/2) & 1 $ly = 0
ELSE
$ly = 1
ENDIF
ENDIF
$calendar[1] = $calendar[1] + $ly
$mdays = 0
FOR $m = 0 TO (Val($ymdarray[1]) -2)
$mdays = $mdays + $calendar[$m]
NEXT
$calcdayofyear = $mdays + $ymdarray[2]
ENDFUNCTION

$diffyears = VAL($expiredatearray[0]) - VAL($currentdatearray[0])
IF $diffyears > 0
$yearstodays = 0
FOR $countyear = VAL($currentdatearray[0]) TO (VAL($expiredatearray[0])-1)
$acountyear = $countyear,"12","31"
$yearstodays = $yearstodays + CalcDayofYear($acountyear)
NEXT
ENDIF

$daystoexpire = CalcDayofYear($expiredatearray) - CalcDayofYear($currentdatearray) + $yearstodays
?"Number of Days till password expires: " + $daystoexpire
IF $daystoexpire <= 2
? "Password Change required within 2 days, please change"
;SHELL 'postie.exe -host:$MailHost -to:$AdminRecpt -from:"ServiceAccountChg" -s:"Password Change" -msg:"The service account: $lstrUserName needs to be changed."'
SHELL 'postie.exe -host:$MailHost -to:$AdminRecpt -from:"ServiceAccountChg" -s:"Password Change" -msg:"The service account: $element needs to be changed."'
ELSE
? "Password will not expire for more than 2 Days, no action is needed"
ENDIF

NEXT ; -- comment out if you don't want to use split

ENDIF

Added in a small piece to tell you visually how many days it will be when the password expires..

Thanks!

- Kent
_________________________
Utilize these resources:
UDFs (Full List)
KiXtart FAQ & How to's

Top
#65848 - 2002-06-04 07:47 PM Re: Password expiration for a service account
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11623
Loc: CA
I'm checking on it... I'll get back with you soon.
Top
Page 1 of 2 12>


Moderator:  Glenn Barnas, NTDOC, Arend_, Jochen, Radimus, Allen, ShaneEP, Ruud van Velsen, Mart 
Hop to:
Shout Box

Who's Online
1 registered (Allen) and 382 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.078 seconds in which 0.032 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org