• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

require a script for getting last password change and username

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
Shankar3003
Asked:
Shankar3003
  • 6
  • 4
  • 4
  • +2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Krzysztof PytkoSenior Active Directory EngineerCommented:
All you need is here http://www.scriptinganswers.com/vault/AD%20Administration/

Look for "password"
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Krzysztof PytkoSenior Active Directory EngineerCommented:
@teylyn: he wanted to have the last reset password stamp info. Which user and when change password last time.
0
 
Meir RivkinFull stack Software EngineerCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@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
 
Krzysztof PytkoSenior Active Directory EngineerCommented:
@teylyn: I don't feel offended :) and I appreciate this action preventing hacking technics.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
all good!
0
 
Shankar3003Author Commented:
Hi sedgwick: thank you for the script, will try it out, thank you.
0
 
Shankar3003Author Commented:
Hi iSiek:thank you for the link.
0
 
Shankar3003Author Commented:
hi sedgwick: i used your script, some of the fields are getting 149619, any reason why?
0
 
Shankar3003Author Commented:
Hi sedgwick, how can i display the displayname of the user as well using your vbs script, thank you.
0
 
Meir RivkinFull stack Software EngineerCommented:
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
 
Shankar3003Author Commented:
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
 
Meir RivkinFull stack Software EngineerCommented:
sorry i modified the LADP root seach.

replace line 39, with this one:

strQuery = "<LDAP://" & strDNSDomain & ">;" & strFilter & ";distinguishedName;subtree"
0
 
Meir RivkinFull stack Software EngineerCommented:
is this working for you?
0
 
Shankar3003Author Commented:
thank you sedqwick, it works.
0
 
gabrielazCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 6
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now