Solved

require a script for getting last password change and username

Posted on 2010-08-23
18
344 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:teylyn
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:teylyn
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
 
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:teylyn
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:teylyn
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now