Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

require a script for getting last password change and username

Posted on 2010-08-23
18
Medium Priority
?
388 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
[X]
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
  • 6
  • 4
  • 4
  • +2
18 Comments
 
LVL 50
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
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Independent Software Vendors: 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!

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

670 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