Script Center >
Scripting Forums
>
The Official Scripting Guys Forum!
>
Last log on for a list of specific users in Active Directory
Last log on for a list of specific users in Active Directory
- I have a list of users that belong to different OUs in Active Directory. They are about 2500 in total. The list contains sAMAccountName in a notepad in a column, eg:
xx12301
zz45603
xyz1
zz4
There is a space after every username and then a new line follows. I would like to be able to generate the lastLogon time that corresponds to respective usernames to an excel sheet in two columns where one column has username and the other last log on time. Can any one please help me out here? Pretty please!
Answers
- If you have a list of 2500 users, and many more than that in total, then you have little choice. The script I post below is rather long and complicated, but works (for me). Again, I limited each query on each DC to 20 names. This could probably be increased, but certainly not to 2500. The output is echoed to the command prompt, so it should be redirected to a text file. Also, I removed the error trapping that handles any Domain Controllers that are not available. This script will raise an error and halt if any DC cannot be contacted.
Option Explicit Dim strFile, objFSO, objFile, objShell, lngBiasKey, lngBias, k Dim objRootDSE, strDNSDomain, adoCommand, adoConnection Dim strBase, strAttributes, strFilter, intCount, strName Dim strQuery, adoRecordset, strNTName, objDate, dtmDate Dim lngHigh, lngLow Dim strConfig, objDC, arrstrDCs(), objList, arrNames() Const ForReading = 1 ' Specify file of sAMAccountNames. strFile = "c:\Scripts\users.txt" ' Open the text file for reading. Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile(strFile, ForReading) ' Read names from file into array. k = 0 Do Until objFile.AtEndOfStream strName = Trim(objFile.ReadLine) ' Skip blank lines. If (strName <> "") Then ReDim Preserve arrNames(k) arrNames(k) = strName k = k + 1 End If Loop objFile.Close ' 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 lngBias = lngBiasKey ElseIf (UCase(TypeName(lngBiasKey)) = "VARIANT()") Then lngBias = 0 For k = 0 To UBound(lngBiasKey) lngBias = lngBias + (lngBiasKey(k) * 256^k) Next End If Set objShell = Nothing ' Use a dictionary object to track latest lastLogon for each user. Set objList = CreateObject("Scripting.Dictionary") objList.CompareMode = vbTextCompare ' Setup ADO objects. Set adoCommand = CreateObject("ADODB.Command") Set adoConnection = CreateObject("ADODB.Connection") adoConnection.Provider = "ADsDSOObject" adoConnection.Open "Active Directory Provider" adoCommand.ActiveConnection = adoConnection ' Search entire Active Directory domain. Set objRootDSE = GetObject("LDAP://RootDSE") strConfig = objRootDSE.Get("configurationNamingContext") strDNSDomain = objRootDSE.Get("defaultNamingContext") strBase = "<LDAP://" & strConfig & ">" strFilter = "(objectClass=nTDSDSA)" strAttributes = "AdsPath" strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree" adoCommand.CommandText = strQuery adoCommand.Properties("Page Size") = 100 adoCommand.Properties("Timeout") = 60 adoCommand.Properties("Cache Results") = False Set adoRecordset = adoCommand.Execute ' Enumerate parent objects of class nTDSDSA. Save Domain Controller ' AdsPaths in dynamic array arrstrDCs. k = 0 Do Until adoRecordset.EOF Set objDC = _ GetObject(GetObject(adoRecordset.Fields("AdsPath").Value).Parent) ReDim Preserve arrstrDCs(k) arrstrDCs(k) = objDC.DNSHostName k = k + 1 adoRecordset.MoveNext Loop adoRecordset.Close ' Comma delimited list of attribute values to retrieve. strAttributes = "sAMAccountName,lastLogon" ' Retrieve lastLogon attribute for each user on each Domain Controller. For k = 0 To Ubound(arrstrDCs) strBase = "<LDAP://" & arrstrDCs(k) & "/" & strDNSDomain & ">" ' Construct filter for user names using array of names. intCount = 0 strFilter = "(|" For Each strName in arrNames ' Deal with 20 names at a time. strFilter = strFilter & "(sAMAccountName=" & strName & ")" If (intCount = 19) Then strFilter = strFilter & ")" ' Construct the LDAP syntax query. strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree" adoCommand.CommandText = strQuery ' Run the query. Set adoRecordset = adoCommand.Execute ' Enumerate the resulting recordset. Do Until adoRecordset.EOF strNTName = adoRecordset.Fields("sAMAccountName").Value On Error Resume Next Set objDate = adoRecordset.Fields("lastLogon").Value If (Err.Number <> 0) Then On Error GoTo 0 dtmDate = #1/1/1601# Else On Error GoTo 0 lngHigh = objDate.HighPart lngLow = objDate.LowPart If (lngLow < 0) Then lngHigh = lngHigh + 1 End If If (lngHigh = 0) And (lngLow = 0) Then dtmDate = #1/1/1601# Else dtmDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _ + lngLow)/600000000 - lngBias)/1440 End If End If ' Retain largest (latest) value for each user. If (objList.Exists(strNTName) = True) Then If (dtmDate > objList(strNTName)) Then objList.Item(strNTName) = dtmDate End If Else objList.Add strNTName, dtmDate End If adoRecordset.MoveNext Loop adoRecordset.Close intCount = 0 strFilter = "(|" Else intCount = intCount + 1 End If Next ' Deal with any remaining names (less than 20). If (intCount <> 0) Then strFilter = strFilter & ")" ' Construct the LDAP syntax query. strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree" adoCommand.CommandText = strQuery ' Run the query. Set adoRecordset = adoCommand.Execute ' Enumerate the resulting recordset. Do Until adoRecordset.EOF strNTName = adoRecordset.Fields("sAMAccountName").Value On Error Resume Next Set objDate = adoRecordset.Fields("lastLogon").Value If (Err.Number <> 0) Then On Error GoTo 0 dtmDate = #1/1/1601# Else On Error GoTo 0 lngHigh = objDate.HighPart lngLow = objDate.LowPart If (lngLow < 0) Then lngHigh = lngHigh + 1 End If If (lngHigh = 0) And (lngLow = 0) Then dtmDate = #1/1/1601# Else dtmDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _ + lngLow)/600000000 - lngBias)/1440 End If End If ' Retain largest (latest) value for each user. If (objList.Exists(strNTName) = True) Then If (dtmDate > objList(strNTName)) Then objList.Item(strNTName) = dtmDate End If Else objList.Add strNTName, dtmDate End If adoRecordset.MoveNext Loop adoRecordset.Close End If Next ' Output latest lastLogon date for each user. For Each strNTName In objList.Keys Wscript.Echo strNTName & "," & objList.Item(strNTName) Next ' Clean up. adoConnection.CloseIf the script is saved in a file named LastLogon.vbs, then run it at a command prompt as follows:
cscript //nologo LastLogon.vbs > UsersLastLogon.csv
This assumes you are in the folder where LastLogon.vbs is saved. Start a command prompt and navigate to this folder. The file UsersLastLogon.csv will be created in the same folder. This program make take awhile to run, depending on the number of DC's and the speed of your links. Note, any last logon dates of 1/1/1601 means "never". That is the "zero" date.
Richard Mueller
MVP ADSI- Marked As Answer byDeepSpring Tuesday, November 10, 2009 9:47 PM
All Replies
If your domain is at Windows 2003 functional level or above, you can use the lastLogonTimeStamp attribute. This attribute is replicated, so you only need to contact one Domain Controller (DC). Otherwise, you must use the lastLogon attribute, which is not replicated so you must contact every DC in the domain. I have example VBScript programs to retrieve last logon dates at this link:
http://www.rlmueller.net/Last%20Logon.htm
The first program retrieves lastLogon, so it contacts every DC in the domain. The second program uses lastLogonTimeStamp, so it contacts just the nearest DC.
If you have a text list of sAMAccountNames, any program using such a list must use the NameTranslate object to convert these names into Distinguished Names. For more on using NameTranslate in VBScript program, see this link:
http://www.rlmueller.net/NameTranslateFAQ.htm
Most administrative VBScript programs are intended to be run at a command prompt using the cscript host program. You can redirect the output to a text file. If the values are delimited by commas, it is easy to read the file into a spreadsheet program. This is easier than modifying the program to write directly to a spreadsheet.
If you need (or want) to retrieve the value of the lastLogon attribute for your users, it would be much easier to use the first program in the first link above. It would require a lot of work to restrict the output to a list of names. This program outputs the Distinguished Name (DN) of each user and the last logon date. The values are delimited by semicolons because the DN values have embedded commas. However, a spreadsheet program can easily read this if you specify ";" as the delimiter.
Also, both programs in my first link above use ADO to retrieve the information from Active Directory. This avoids the need to bind to objects in AD, which can be slow. If you have a list of 2500 sAMAccountName values, and you use NameTranslate to convert these into DN's, I think that would be much slower than simply retrieving the information for all users in the domain.
Richard Mueller
MVP ADSI- Thinking about this some more, it would not make sense to use the NameTranslate object to convert the sAMAccountName values into DN's, since we still need to retrieve the value of the lastLogonTimeStamp attribute. We would either need to bind to the user objects (very slow) or use ADO to retrieve the value. We might as well use ADO and query based on sAMAccountName.
To make the program more efficient, we should query for a batch of user names at once. In the example below, I read the sAMAccountNames from a text file and query for 20 names at a time. This assumes the domain is at least at Windows 2003 functional level. Note if any names are not found in AD, there simply is no row in the resulting recordset for that user.
Option Explicit Dim strFile, objFSO, objFile, objShell, lngBiasKey, lngBias, k Dim objRootDSE, strDNSDomain, adoCommand, adoConnection Dim strBase, strAttributes, strFilter, intCount, strName Dim strQuery, adoRecordset, strNTName, objDate, dtmDate Dim lngHigh, lngLow Const ForReading = 1 ' Specify file of sAMAccountNames. strFile = "c:\Scripts\users.txt" ' Open the text file for reading. Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile(strFile, ForReading) ' 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 lngBias = lngBiasKey ElseIf (UCase(TypeName(lngBiasKey)) = "VARIANT()") Then lngBias = 0 For k = 0 To UBound(lngBiasKey) lngBias = lngBias + (lngBiasKey(k) * 256^k) Next End If Set objShell = Nothing ' Setup ADO objects. Set adoCommand = CreateObject("ADODB.Command") Set adoConnection = CreateObject("ADODB.Connection") adoConnection.Provider = "ADsDSOObject" adoConnection.Open "Active Directory Provider" adoCommand.ActiveConnection = adoConnection ' Search entire Active Directory domain. Set objRootDSE = GetObject("LDAP://RootDSE") strDNSDomain = objRootDSE.Get("defaultNamingContext") strBase = "<LDAP://" & strDNSDomain & ">" ' Comma delimited list of attribute values to retrieve. strAttributes = "sAMAccountName,lastLogonTimeStamp" ' Assign properties. adoCommand.Properties("Page Size") = 100 adoCommand.Properties("Timeout") = 30 adoCommand.Properties("Cache Results") = False ' Read names from the file. intCount = 0 strFilter = "(|" Do Until objFile.AtEndOfStream strName = Trim(objFile.ReadLine) ' Skip blank lines. If (strName <> "") Then ' Deal with 20 names at a time. strFilter = strFilter & "(sAMAccountName=" & strName & ")" If (intCount = 19) Then strFilter = strFilter & ")" ' Construct the LDAP syntax query. strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree" adoCommand.CommandText = strQuery ' Run the query. Set adoRecordset = adoCommand.Execute ' Enumerate the resulting recordset. Do Until adoRecordset.EOF strNTName = adoRecordset.Fields("sAMAccountName").Value On Error Resume Next Set objDate = adoRecordset.Fields("lastLogonTimeStamp").Value If (Err.Number <> 0) Then On Error GoTo 0 dtmDate = #1/1/1601# Else On Error GoTo 0 lngHigh = objDate.HighPart lngLow = objDate.LowPart If (lngLow < 0) Then lngHigh = lngHigh + 1 End If If (lngHigh = 0) And (lngLow = 0) Then dtmDate = #1/1/1601# Else dtmDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _ + lngLow)/600000000 - lngBias)/1440 End If End If ' Output values. Wscript.Echo strNTName & "," & CStr(dtmDate) adoRecordset.MoveNext Loop adoRecordset.Close intCount = 0 strFilter = "(|" Else intCount = intCount + 1 End If End If Loop ' Close the file. objFile.Close ' Deal with any remaining names (less than 20). If (intCount <> 0) Then strFilter = strFilter & ")" ' Construct the LDAP syntax query. strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree" adoCommand.CommandText = strQuery ' Run the query. Set adoRecordset = adoCommand.Execute ' Enumerate the resulting recordset. Do Until adoRecordset.EOF strNTName = adoRecordset.Fields("sAMAccountName").Value On Error Resume Next Set objDate = adoRecordset.Fields("lastLogonTimeStamp").Value If (Err.Number <> 0) Then On Error GoTo 0 dtmDate = #1/1/1601# Else On Error GoTo 0 lngHigh = objDate.HighPart lngLow = objDate.LowPart If (lngLow < 0) Then lngHigh = lngHigh + 1 End If If (lngHigh = 0) And (lngLow = 0) Then dtmDate = #1/1/1601# Else dtmDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _ + lngLow)/600000000 - lngBias)/1440 End If End If ' Output values. Wscript.Echo strNTName & "," & CStr(dtmDate) adoRecordset.MoveNext Loop adoRecordset.Close End IfRichard Mueller
MVP ADSI - Our domain functional level is windows 2000 mixed. I have not tried the script yet. Will it not work for the functional level my domain has?
No, the script will not work if your domain is Windows 2000. The new lastLogonTimeStamp attribute (which is replicated like most others) is not available. Instead, you must use the lastLogon attribute, which is not replicated so you must query every DC in the domain. You can use the script in the first link I supplied earlier, which documents all users in the domain. Or, give me a few minutes to revise the code I posted earlier so the same ADO filter is used to query all Domain Controllers.
Richard Mueller
MVP ADSI- Thanks. But the issue is not about documenting all users in the domain. I have a list of 2500 users who need to be queried for the last log on date. So, I believe something like what you you have written above to query for some specific list of users would be helpful. Can the output be set to a csv format so that it can be imported to excel? Thank you for your attention. Really appreciate it.
- If you have a list of 2500 users, and many more than that in total, then you have little choice. The script I post below is rather long and complicated, but works (for me). Again, I limited each query on each DC to 20 names. This could probably be increased, but certainly not to 2500. The output is echoed to the command prompt, so it should be redirected to a text file. Also, I removed the error trapping that handles any Domain Controllers that are not available. This script will raise an error and halt if any DC cannot be contacted.
Option Explicit Dim strFile, objFSO, objFile, objShell, lngBiasKey, lngBias, k Dim objRootDSE, strDNSDomain, adoCommand, adoConnection Dim strBase, strAttributes, strFilter, intCount, strName Dim strQuery, adoRecordset, strNTName, objDate, dtmDate Dim lngHigh, lngLow Dim strConfig, objDC, arrstrDCs(), objList, arrNames() Const ForReading = 1 ' Specify file of sAMAccountNames. strFile = "c:\Scripts\users.txt" ' Open the text file for reading. Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile(strFile, ForReading) ' Read names from file into array. k = 0 Do Until objFile.AtEndOfStream strName = Trim(objFile.ReadLine) ' Skip blank lines. If (strName <> "") Then ReDim Preserve arrNames(k) arrNames(k) = strName k = k + 1 End If Loop objFile.Close ' 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 lngBias = lngBiasKey ElseIf (UCase(TypeName(lngBiasKey)) = "VARIANT()") Then lngBias = 0 For k = 0 To UBound(lngBiasKey) lngBias = lngBias + (lngBiasKey(k) * 256^k) Next End If Set objShell = Nothing ' Use a dictionary object to track latest lastLogon for each user. Set objList = CreateObject("Scripting.Dictionary") objList.CompareMode = vbTextCompare ' Setup ADO objects. Set adoCommand = CreateObject("ADODB.Command") Set adoConnection = CreateObject("ADODB.Connection") adoConnection.Provider = "ADsDSOObject" adoConnection.Open "Active Directory Provider" adoCommand.ActiveConnection = adoConnection ' Search entire Active Directory domain. Set objRootDSE = GetObject("LDAP://RootDSE") strConfig = objRootDSE.Get("configurationNamingContext") strDNSDomain = objRootDSE.Get("defaultNamingContext") strBase = "<LDAP://" & strConfig & ">" strFilter = "(objectClass=nTDSDSA)" strAttributes = "AdsPath" strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree" adoCommand.CommandText = strQuery adoCommand.Properties("Page Size") = 100 adoCommand.Properties("Timeout") = 60 adoCommand.Properties("Cache Results") = False Set adoRecordset = adoCommand.Execute ' Enumerate parent objects of class nTDSDSA. Save Domain Controller ' AdsPaths in dynamic array arrstrDCs. k = 0 Do Until adoRecordset.EOF Set objDC = _ GetObject(GetObject(adoRecordset.Fields("AdsPath").Value).Parent) ReDim Preserve arrstrDCs(k) arrstrDCs(k) = objDC.DNSHostName k = k + 1 adoRecordset.MoveNext Loop adoRecordset.Close ' Comma delimited list of attribute values to retrieve. strAttributes = "sAMAccountName,lastLogon" ' Retrieve lastLogon attribute for each user on each Domain Controller. For k = 0 To Ubound(arrstrDCs) strBase = "<LDAP://" & arrstrDCs(k) & "/" & strDNSDomain & ">" ' Construct filter for user names using array of names. intCount = 0 strFilter = "(|" For Each strName in arrNames ' Deal with 20 names at a time. strFilter = strFilter & "(sAMAccountName=" & strName & ")" If (intCount = 19) Then strFilter = strFilter & ")" ' Construct the LDAP syntax query. strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree" adoCommand.CommandText = strQuery ' Run the query. Set adoRecordset = adoCommand.Execute ' Enumerate the resulting recordset. Do Until adoRecordset.EOF strNTName = adoRecordset.Fields("sAMAccountName").Value On Error Resume Next Set objDate = adoRecordset.Fields("lastLogon").Value If (Err.Number <> 0) Then On Error GoTo 0 dtmDate = #1/1/1601# Else On Error GoTo 0 lngHigh = objDate.HighPart lngLow = objDate.LowPart If (lngLow < 0) Then lngHigh = lngHigh + 1 End If If (lngHigh = 0) And (lngLow = 0) Then dtmDate = #1/1/1601# Else dtmDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _ + lngLow)/600000000 - lngBias)/1440 End If End If ' Retain largest (latest) value for each user. If (objList.Exists(strNTName) = True) Then If (dtmDate > objList(strNTName)) Then objList.Item(strNTName) = dtmDate End If Else objList.Add strNTName, dtmDate End If adoRecordset.MoveNext Loop adoRecordset.Close intCount = 0 strFilter = "(|" Else intCount = intCount + 1 End If Next ' Deal with any remaining names (less than 20). If (intCount <> 0) Then strFilter = strFilter & ")" ' Construct the LDAP syntax query. strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree" adoCommand.CommandText = strQuery ' Run the query. Set adoRecordset = adoCommand.Execute ' Enumerate the resulting recordset. Do Until adoRecordset.EOF strNTName = adoRecordset.Fields("sAMAccountName").Value On Error Resume Next Set objDate = adoRecordset.Fields("lastLogon").Value If (Err.Number <> 0) Then On Error GoTo 0 dtmDate = #1/1/1601# Else On Error GoTo 0 lngHigh = objDate.HighPart lngLow = objDate.LowPart If (lngLow < 0) Then lngHigh = lngHigh + 1 End If If (lngHigh = 0) And (lngLow = 0) Then dtmDate = #1/1/1601# Else dtmDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _ + lngLow)/600000000 - lngBias)/1440 End If End If ' Retain largest (latest) value for each user. If (objList.Exists(strNTName) = True) Then If (dtmDate > objList(strNTName)) Then objList.Item(strNTName) = dtmDate End If Else objList.Add strNTName, dtmDate End If adoRecordset.MoveNext Loop adoRecordset.Close End If Next ' Output latest lastLogon date for each user. For Each strNTName In objList.Keys Wscript.Echo strNTName & "," & objList.Item(strNTName) Next ' Clean up. adoConnection.CloseIf the script is saved in a file named LastLogon.vbs, then run it at a command prompt as follows:
cscript //nologo LastLogon.vbs > UsersLastLogon.csv
This assumes you are in the folder where LastLogon.vbs is saved. Start a command prompt and navigate to this folder. The file UsersLastLogon.csv will be created in the same folder. This program make take awhile to run, depending on the number of DC's and the speed of your links. Note, any last logon dates of 1/1/1601 means "never". That is the "zero" date.
Richard Mueller
MVP ADSI- Marked As Answer byDeepSpring Tuesday, November 10, 2009 9:47 PM
- Thanks. I will try to break the list into smaller ones and then get started that way.
- This works!! I broke the list down by alphabets and did it in about 10 shots. It could query as far as 500 users. I didn't go beyond 500. Upon running, I verified the times of the 20 random accounts and they were correct. Thanks a trillion!! By the way, is there anything I can modify to reflect just the date and not the time?
- I'm glad the script worked well with so many users. My guess is that a query for 2500 users would not take much longer than one for 500, unless the dictionary object exceeded memory or something similar.
You can use the FormatDateTime function with the dates. For example, to show just the date part (ignore the time), use:
Wscript.Echo strNTName & "," & FormatDateTime(objList.Item(strNTName), vbShortDate)
Richard Mueller
MVP ADSI I am sure you are not as glad as I am. I will try the formatdatetime function next time. If only I could script the way you do, I would transform the business process at my place. Did you drink some magic potion when you were a kid?
Thanks!
