Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

require a script for getting last password change and username

Posted on 2010-08-23
18
368 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Suggested Solutions

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 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