Solved

require a script for getting last password change and username

Posted on 2010-08-23
18
374 Views
Last Modified: 2012-05-10
Hi, i require a script urgently be it VBS or batch script. I am required to output all usernames from AD to an excel file with the last password change information of the user. It will be good if there is a script to display all usernames, all password last set into columns. As this requirement is urgent, any help with be much appreciated, thank you.
0
Comment
Question by:Shankar3003
  • 6
  • 4
  • 4
  • +2
18 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 33499348
Shankar, do you want to document the user's last password and current password?

I'm not sure this is a question that should be answered in EE. It sounds a bit like trying to circumvent security or try to gain passwords that you shouldn't be supposed to know.

0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 33499361
I've raised a request for attention and urge all experts to hold answering this questions until the moderators have made a decision whether or not it's legit.

cheers, teylyn
0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 33499398
All you need is here http://www.scriptinganswers.com/vault/AD%20Administration/

Look for "password"
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 33499405
@teylyn: he wanted to have the last reset password stamp info. Which user and when change password last time.
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 33499420
here's the vb script.
const OUTPUT_EXCEL_FILE = "c:\temp\output.xlsx"
const SHEET_HEADERS = "Username,PWD Last Change (Date),PWD Last Change (Days ago)"
Const xlExcel7 = 51

On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = false
 
If (Err.Number <> 0) Then
    On Error GoTo 0
    Wscript.Echo "Excel application not found."
    Wscript.Quit
End If
On Error GoTo 0

col=1
row=2

' Create a new workbook.
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

for each header in Split(SHEET_HEADERS,",")
	objSheet.Cells(1, col).Value = header
	col=col+1
next

Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
set objConn = CreateObject("ADODB.Connection")
set objCmd = CreateObject("ADODB.Command")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
Set objCmd.ActiveConnection = objConn
objCmd.Properties("Cache Results") = False
strFilter = "(&(objectclass=user)(objectcategory=person))"
strQuery = "<LDAP://" & strDNSDomain & ">;" & strFilter & ";distinguishedName;subtree"
objCmd.CommandText = strQuery
Set wshFSO=Createobject("Scripting.FileSystemObject")
 
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

Set objRecordSet = objCmd.Execute
 
' Obtain local Time Zone bias from machine registry.
Set objShell = CreateObject("Wscript.Shell")
lngBiasKey = objShell.RegRead("HKLM\System\CurrentControlSet\Control\" & "TimeZoneInformation\ActiveTimeBias")
 
If UCase(TypeName(lngBiasKey)) = "LONG" Then
	lngTZBias = lngBiasKey
ElseIf UCase(TypeName(lngBiasKey)) = "VARIANT()" Then
	lngTZBias = 0
	For k = 0 To UBound(lngBiasKey)
		lngTZBias = lngTZBias + (lngBiasKey(k) * 256^k)
	Next
End If
 
Do Until objRecordSet.EOF
	on error resume next
	strDN = objRecordSet.Fields("distinguishedName")
	Set objUser = GetObject("LDAP://" & strDN)
 
	str_sAMAccountName = objUser.sAMAccountName
	str_pwdLastSet = Integer8Date(objUser.pwdLastSet, lngTZBias)
 
	int_DateDiff = DateDiff("D", str_PWDLastSet, Date)

	objSheet.Cells(row, 1).Value = str_sAMAccountName
	objSheet.Cells(row, 2).Value = str_pwdLastSet
	objSheet.Cells(row, 3).Value = int_DateDiff

	row=row+1
	objRecordSet.MoveNext
Loop
 
 
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs OUTPUT_EXCEL_FILE, xlExcel7
objExcel.ActiveWorkbook.Close false

' Quit Excel.
objExcel.Application.Quit

Set objSheet = Nothing
Set objExcel = Nothing

Wscript.Echo "done."

Function Integer8Date(objDate, lngBias)
' Function to convert Integer8 (64-bit) value to a date, adjusted for
' local time zone bias.
  Dim lngAdjust, lngDate, lngHigh, lngLow
  lngAdjust = lngBias
  lngHigh = objDate.HighPart
  lngLow = objdate.LowPart
 
' Account for error in IADslargeInteger property methods.
  If lngLow < 0 Then
		lngHigh = lngHigh + 1
  End If
 
  If (lngHigh = 0) And (lngLow = 0) Then
		lngAdjust = 0
  End If
 
  lngDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) + lngLow) / 600000000 - lngAdjust) / 1440
 
' Trap error if lngDate is ridiculously huge.
  On Error Resume Next
  Integer8Date = CDate(lngDate)
 
  If Err.Number <> 0 Then
		On Error GoTo 0
		Integer8Date = #1/1/1601#
  End If
 
  On Error GoTo 0
End Function

Open in new window

0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 33499432
@iSiek,

thank you for the heads up. Sounds like you know what the asker is after and you seem confident that there's no security issue. This is not quite apparent to the plain Excel-only expert with little knowledge of AD. To me it sounds as if the actual password is wanted in the Excel file. But then, I'm no AD whiz and don't know what's doable.

Apologies, if I've stepped on toes, but maybe someone will appreciate that experts are alerting mods to questions that look suspect to circumvent security.

Will go and renege the request for attention now. No offense meant.

cheers, teylyn
0
 
LVL 39

Expert Comment

by:Krzysztof Pytko
ID: 33499538
@teylyn: I don't feel offended :) and I appreciate this action preventing hacking technics.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 33499585
all good!
0
 

Author Comment

by:Shankar3003
ID: 33502455
Hi sedgwick: thank you for the script, will try it out, thank you.
0
 

Author Comment

by:Shankar3003
ID: 33502476
Hi iSiek:thank you for the link.
0
 

Author Comment

by:Shankar3003
ID: 33507552
hi sedgwick: i used your script, some of the fields are getting 149619, any reason why?
0
 

Author Comment

by:Shankar3003
ID: 33507624
Hi sedgwick, how can i display the displayname of the user as well using your vbs script, thank you.
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 33508021
check this:
const OUTPUT_EXCEL_FILE = "c:\temp\output.xlsx"
const SHEET_HEADERS = "Username, Display Name, PWD Last Change (Date),PWD Last Change (Days ago)"
Const xlExcel7 = 51

On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = false
 
If (Err.Number <> 0) Then
    On Error GoTo 0
    Wscript.Echo "Excel application not found."
    Wscript.Quit
End If
On Error GoTo 0

col=1
row=2

' Create a new workbook.
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

for each header in Split(SHEET_HEADERS,",")
	objSheet.Cells(1, col).Value = header
	col=col+1
next

Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
set objConn = CreateObject("ADODB.Connection")
set objCmd = CreateObject("ADODB.Command")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
Set objCmd.ActiveConnection = objConn
objCmd.Properties("Cache Results") = False
strFilter = "(&(objectclass=user)(objectcategory=person))"
strQuery = "<LDAP://cn=users," & strDNSDomain & ">;" & strFilter & ";distinguishedName;subtree"
objCmd.CommandText = strQuery
Set wshFSO=Createobject("Scripting.FileSystemObject")
 
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

Set objRecordSet = objCmd.Execute
 
' Obtain local Time Zone bias from machine registry.
Set objShell = CreateObject("Wscript.Shell")
lngBiasKey = objShell.RegRead("HKLM\System\CurrentControlSet\Control\" & "TimeZoneInformation\ActiveTimeBias")
 
If UCase(TypeName(lngBiasKey)) = "LONG" Then
	lngTZBias = lngBiasKey
ElseIf UCase(TypeName(lngBiasKey)) = "VARIANT()" Then
	lngTZBias = 0
	For k = 0 To UBound(lngBiasKey)
		lngTZBias = lngTZBias + (lngBiasKey(k) * 256^k)
	Next
End If
 
Do Until objRecordSet.EOF
	on error resume next
	strDN = objRecordSet.Fields("distinguishedName")
	Set objUser = GetObject("LDAP://" & strDN)
 
	str_sAMAccountName = objUser.sAMAccountName
	str_displayName = objUser.DisplayName
	
	str_pwdLastSet = Integer8Date(objUser.pwdLastSet, lngTZBias)
	
	if str_pwdLastSet <> "1/1/1601" then
		int_DateDiff = DateDiff("D", str_PWDLastSet, Date)
	else
		str_pwdLastSet = ""
		int_DateDiff = ""
	end if
	
	objSheet.Cells(row, 1).Value = str_sAMAccountName
	objSheet.Cells(row, 2).Value = str_displayName
	objSheet.Cells(row, 3).Value = str_pwdLastSet
	objSheet.Cells(row, 4).Value = int_DateDiff

	row=row+1
	objRecordSet.MoveNext
Loop
 
 
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs OUTPUT_EXCEL_FILE, xlExcel7
objExcel.ActiveWorkbook.Close false

' Quit Excel.
objExcel.Application.Quit

Set objSheet = Nothing
Set objExcel = Nothing

Wscript.Echo "done."

Function Integer8Date(objDate, lngBias)
' Function to convert Integer8 (64-bit) value to a date, adjusted for
' local time zone bias.
  Dim lngAdjust, lngDate, lngHigh, lngLow
  lngAdjust = lngBias
  lngHigh = objDate.HighPart
  lngLow = objdate.LowPart
 
' Account for error in IADslargeInteger property methods.
  If lngLow < 0 Then
		lngHigh = lngHigh + 1
  End If
 
  If (lngHigh = 0) And (lngLow = 0) Then
		lngAdjust = 0
  End If
 
  lngDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) + lngLow) / 600000000 - lngAdjust) / 1440
 
' Trap error if lngDate is ridiculously huge.
  On Error Resume Next
  Integer8Date = CDate(lngDate)
 
  If Err.Number <> 0 Then
		On Error GoTo 0
		Integer8Date = #1/1/1601#
  End If
 
  On Error GoTo 0
End Function

Open in new window

0
 

Author Comment

by:Shankar3003
ID: 33508082
Hi, thank you, that worked but i can only see 15 names only. It does not give me a full list. is there are limit of user name output entry?
0
 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
ID: 33508116
sorry i modified the LADP root seach.

replace line 39, with this one:

strQuery = "<LDAP://" & strDNSDomain & ">;" & strFilter & ";distinguishedName;subtree"
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 33509536
is this working for you?
0
 

Author Comment

by:Shankar3003
ID: 33564114
thank you sedqwick, it works.
0
 
LVL 2

Expert Comment

by:gabrielaz
ID: 36385023
i tried using the script but it dosent output.. how do i get it to output to a CSV file.. im guessing it is because i dont have XLSX installed on my server...
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question