none
Is there a way to add the last login time/date in AD to an excel column? RRS feed

  • Question

  • I was able to get to this point by piecing together scripts, not by my own coding...

    I am not sure how to implement it in such a way as to run through and loop out the disabled accounts from the active accounts in two seperate files when ran. 

    I started learning some but I don't have a reason to use it beyond this project.  Although, I am interested, I just need more time before I can dedicate my resources.  I find it very interesting to code.  :)

     

    **************************************

    ' UsersGroups.vbs
    ' VBScript program to document security group membership of all users.
    ' Reveals nested group and primary group membership. Does not reveal
    ' distribution group membership or cross-domain group membership.
    '
    ' ----------------------------------------------------------------------
    ' Copyright (c) 2009 Richard L. Mueller
    ' Hilltop Lab web site - http://www.rlmueller.net
    ' Version 1.0 - May 8, 2009
    '
    ' You have a royalty-free right to use, modify, reproduce, and
    ' distribute this script file in any way you find useful, provided that
    ' you agree that the copyright owner above has no warranty, obligations,
    ' or liability for such use.

    Option Explicit

    Dim adoCommand, adoConnection, strBase, strFilter, strFilter2, strAttributes
    Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName, strDN, strBaseOU
    Dim strExcelpath, strExcelpath2, objExcel, objSheet, intRow, intCol, objUser
    Dim arrbytSIDs, objGroupList, j, arrstrGroupSIDs(), objGroup
    Dim strGroupName
    Dim oShell, oEnv, oFS, strDirectory, strIncrement, numIncrement


    'Create the folder to place the reports.
    Set oShell = CreateObject("wscript.Shell")
    set oFS = CreateObject("scripting.FileSystemObject")
    Set oEnv = oShell.Environment("Process")
    strDirectory = oEnv("USERPROFILE") & "\Desktop\Audit Reports"
    if not oFS.FolderExists(strDirectory) then oFS.CreateFolder(strDirectory & strIncrement)
    '--- this is new
    strDirectory=strDirectory & "\" & Month(Now) & "-" & Day(Now) & "-" & Year(Now)
    strIncrement = ""
    numIncrement = 0
    Do While True
     if not oFS.FolderExists(strDirectory & strIncrement) then
      oFS.CreateFolder(strDirectory & strIncrement)
      strDirectory=strDirectory & strIncrement
      Exit Do
     Else
      numIncrement=numIncrement+1
      strIncrement="_" & CStr(numIncrement)
     End If
    Loop
    ' Spreadsheet file name to be created.
    strExcelPath = strDirectory & "\WV1.xls"
    strExcelPath2 = strDirectory & "\WV1-Disabled.xls"

    ' Bind to Excel.
    Set objExcel = CreateObject("Excel.Application")

    ' Create new workbook.
    objExcel.Workbooks.Add

    ' Bind to worksheet.
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
    objSheet.Name = "Domain Users"

    ' Write column headings.
    objSheet.Cells(1, 1).Value = "sAMAccountName"
    objSheet.Cells(1, 2).value = "Distinguished Name"
    objSheet.Cells(1, 3).Value = "Group Memberships"

    ' Dictionary object to keep track of group SID values.
    Set objGroupList = CreateObject("Scripting.Dictionary")
    objGroupList.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

    ' Edit the next line to reflect your OU
    strBaseOU ="OU=WV1,OU=Accounts"

    ' Search entire Active Directory domain.
    Set objRootDSE = GetObject("LDAP://RootDSE")
    strDNSDomain = objRootDSE.Get("defaultNamingContext")
    strBase ="<LDAP://" & strBaseOU & "," & strDNSDomain & ">"

    strFilter = "(&(objectCategory=person)(objectClass=user)" _
        & "(!userAccountControl:1.2.840.113556.1.4.803:=2))"
    strFilter2 = "(&(objectCategory=person)(objectClass=user)" _
        & "(userAccountControl:1.2.840.113556.1.4.803:=2))"

    ' Comma delimited list of attribute values to retrieve.
    ' Cannot retrieve tokenGroups with ADO.
    strAttributes = "sAMAccountName,distinguishedName"

    ' Construct the LDAP syntax query.
    strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
    adoCommand.CommandText = strQuery
    adoCommand.Properties("Page Size") = 100
    adoCommand.Properties("Timeout") = 30
    adoCommand.Properties("Cache Results") = False

    ' Run the query.
    Set adoRecordset = adoCommand.Execute   ' Line 76

    ' Enumerate the resulting recordset.
    intRow = 2
    Do Until adoRecordset.EOF
        ' Retrieve values and display.
        strName = adoRecordset.Fields("sAMAccountName").Value
        objSheet.Cells(intRow, 1).Value = strName
        strDN = adoRecordset.Fields("distinguishedName").value
        strDN = Replace(strDN, "/", "\/")
        objSheet.Cells(intRow, 2).Value = Mid(Split(strDN,",")(0),4)
        ' Bind to the user object.
        Set objUser = GetObject("LDAP://" & strDN)
        ' Retrieve tokenGroups attribute.
        objUser.GetInfoEx Array("tokenGroups"), 0
        arrbytSIDs = objUser.Get("tokenGroups")
        If (UBound(arrbytSIDs) = -1) Then
            ' No group SID values, do nothing.
        ElseIf (TypeName(arrbytSIDs) = "Byte()") Then
            ' One group SID.
            ReDim arrstrGroupSIDs(0)
            arrstrGroupSIDs(0) = OctetToHexStr(arrbytSIDs)
            ' Check if this group encountered before.
            If (objGroupList.Exists(arrstrGroupSIDs(0)) = False) Then
                ' Save group SID and name in dictionary object.
                Set objGroup = GetObject("LDAP://<SID=" & arrstrGroupSIDs(0) & ">")
                strGroupName = objGroup.distinguishedName
                objGroupList.Add arrstrGroupSIDs, strGroupName
                  'objSheet.Cells(intRow, 3).Value = strGroupName
                  objSheet.Cells(intRow, 3).Value = Mid(Split(strGroupName,",")(0),4) &  Chr(10)  & objSheet.Cells(intRow, 3).Value
            Else
              
    ' Retrieve group name from dictionary object.
                strGroupName = objGroupList(arrstrGroupSIDs(0))
                'objSheet.Cells(intRow, 3).Value = strGroupName
                 objSheet.Cells(intRow, 3).Value = Mid(Split(strGroupName,",")(0),4) &  Chr(10)  & objSheet.Cells(intRow, 3).Value
            End If
        Else

            ' More than one SID value in the array.
            intCol = 3
            ReDim arrstrGroupSIDs(UBound(arrbytSIDs))
            For j = 0 To UBound(arrbytSIDs)
                arrstrGroupSIDs(j) = OctetToHexStr(arrbytSIDs(j))
                ' Check if this group encountered before.
                If (objGroupList.Exists(arrstrGroupSIDs(j)) = False) Then
                    ' Save group SID and name in dictionary object.
                    Set objGroup = GetObject("LDAP://<SID=" & arrstrGroupSIDs(j) & ">")
                    strGroupName = objGroup.distinguishedName
                    objGroupList.Add arrstrGroupSIDs(j), strGroupName
                    'objSheet.Cells(intRow, intCol).Value = strGroupName
                    objSheet.Cells(intRow, 3).Value = Mid(Split(strGroupName,",")(0),4) &  Chr(10)  & objSheet.Cells(intRow, 3).Value
                Else
                    ' Retrieve group name from dictionary object.
                    strGroupName = objGroupList(arrstrGroupSIDs(j))
                    'objSheet.Cells(intRow, 3).Value = strGroupName
                    objSheet.Cells(intRow, 3).Value = Mid(Split(strGroupName,",")(0),4) &  Chr(10)  & objSheet.Cells(intRow, 3).Value
                End If
            Next       
        End If
        ' Move to the next record in the recordset.
        intRow = intRow + 1
        adoRecordset.MoveNext
    Loop

     

    ' Save spreadsheet and close the workbook.
    objExcel.ActiveWorkbook.SaveAs strExcelPath
    objExcel.ActiveWorkbook.Close

    ' Quit Excel and clean up.
    objExcel.Application.Quit
    objExcel.Application.Quit
    'adoRecordset.Close
    'adoConnection.Close

    ' Bind to Excel.
    Set objExcel = CreateObject("Excel.Application")

    ' Create new workbook.
    objExcel.Workbooks.Add

    ' Bind to worksheet.
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
    objSheet.Name = "Domain Users"

    ' Write column headings.
    objSheet.Cells(1, 1).Value = "sAMAccountName"
    objSheet.Cells(1, 2).value = "Distinguished Name"
    objSheet.Cells(1, 3).Value = "Group Memberships"

    ' Dictionary object to keep track of group SID values.
    Set objGroupList = CreateObject("Scripting.Dictionary")
    objGroupList.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

    ' Edit the next line to reflect your OU
    strBaseOU ="OU=WV1,OU=Accounts"

    ' Search entire Active Directory domain.
    Set objRootDSE = GetObject("LDAP://RootDSE")
    strDNSDomain = objRootDSE.Get("defaultNamingContext")
    strBase ="<LDAP://" & strBaseOU & "," & strDNSDomain & ">"


    ' Construct the LDAP syntax query.
    strQuery = strBase & ";" & strFilter2 & ";" & strAttributes & ";subtree"
    adoCommand.CommandText = strQuery
    adoCommand.Properties("Page Size") = 100
    adoCommand.Properties("Timeout") = 30
    adoCommand.Properties("Cache Results") = False

    ' Run the query.
    Set adoRecordset = adoCommand.Execute


    ' Enumerate the resulting recordset for disabled accounts.
    intRow = 2
    Do Until adoRecordset.EOF
        ' Retrieve values and display.
        strName = adoRecordset.Fields("sAMAccountName").Value
        objSheet.Cells(intRow, 1).Value = strName
        strDN = adoRecordset.Fields("distinguishedName").value
        strDN = Replace(strDN, "/", "\/")
        objSheet.Cells(intRow, 2).Value = Mid(Split(strDN,",")(0),4)
        ' Bind to the user object.
        Set objUser = GetObject("LDAP://" & strDN)
        ' Retrieve tokenGroups attribute.
        objUser.GetInfoEx Array("tokenGroups"), 0
        arrbytSIDs = objUser.Get("tokenGroups")
        If (UBound(arrbytSIDs) = -1) Then
            ' No group SID values, do nothing.
        ElseIf (TypeName(arrbytSIDs) = "Byte()") Then
            ' One group SID.
            ReDim arrstrGroupSIDs(0)
            arrstrGroupSIDs(0) = OctetToHexStr(arrbytSIDs)
            ' Check if this group encountered before.
            If (objGroupList.Exists(arrstrGroupSIDs(0)) = False) Then
                ' Save group SID and name in dictionary object.
                Set objGroup = GetObject("LDAP://<SID=" & arrstrGroupSIDs(0) & ">")
                strGroupName = objGroup.distinguishedName
                objGroupList.Add arrstrGroupSIDs, strGroupName
                  'objSheet.Cells(intRow, 3).Value = strGroupName
                  objSheet.Cells(intRow, 3).Value = Mid(Split(strGroupName,",")(0),4) &  Chr(10)  & objSheet.Cells(intRow, 3).Value
            Else
              
    ' Retrieve group name from dictionary object.
                strGroupName = objGroupList(arrstrGroupSIDs(0))
                'objSheet.Cells(intRow, 3).Value = strGroupName
                 objSheet.Cells(intRow, 3).Value = Mid(Split(strGroupName,",")(0),4) &  Chr(10)  & objSheet.Cells(intRow, 3).Value
            End If
        Else

            ' More than one SID value in the array.
            intCol = 3
            ReDim arrstrGroupSIDs(UBound(arrbytSIDs))
            For j = 0 To UBound(arrbytSIDs)
                arrstrGroupSIDs(j) = OctetToHexStr(arrbytSIDs(j))
                ' Check if this group encountered before.
                If (objGroupList.Exists(arrstrGroupSIDs(j)) = False) Then
                    ' Save group SID and name in dictionary object.
                    Set objGroup = GetObject("LDAP://<SID=" & arrstrGroupSIDs(j) & ">")
                    strGroupName = objGroup.distinguishedName
                    objGroupList.Add arrstrGroupSIDs(j), strGroupName
                    'objSheet.Cells(intRow, intCol).Value = strGroupName
                    objSheet.Cells(intRow, 3).Value = Mid(Split(strGroupName,",")(0),4) &  Chr(10)  & objSheet.Cells(intRow, 3).Value
                Else
                    ' Retrieve group name from dictionary object.
                    strGroupName = objGroupList(arrstrGroupSIDs(j))
                    'objSheet.Cells(intRow, 3).Value = strGroupName
                    objSheet.Cells(intRow, 3).Value = Mid(Split(strGroupName,",")(0),4) &  Chr(10)  & objSheet.Cells(intRow, 3).Value
                End If
            Next       
        End If
        ' Move to the next record in the recordset.
        intRow = intRow + 1
        adoRecordset.MoveNext
    Loop


    ' Save spreadsheet and close the workbook.
    objExcel.ActiveWorkbook.SaveAs strExcelPath2
    objExcel.ActiveWorkbook.Close

    ' Quit Excel and clean up.
    objExcel.Application.Quit
    objExcel.Application.Quit
    adoRecordset.Close
    adoConnection.Close

    Function OctetToHexStr(ByVal arrbytOctet)
        ' Function to convert OctetString (byte array) to Hex string.
        Dim k
        OctetToHexStr = ""
        For k = 1 To Lenb(arrbytOctet)
            OctetToHexStr = OctetToHexStr _
                & Right("0" & Hex(Ascb(Midb(arrbytOctet, k, 1))), 2)
        Next
    End Function

    Wscript.Echo "Report Generated"

    ************************************************

    Monday, April 12, 2010 8:39 PM

Answers

  • If your domain is at Windows Server 2003 functional level or above, you can retrieve the lastLogonTimeStamp attribute for each user. However, this attribute is Integer8, meaning it is a 64-bit integer that represents a date as the number of 100-nanosecond intervals since 12:00 AM January 1, 1601 (a large number to be sure). Also, the date is in UTC (Coordinated Universal Time) so it must be converted into your local time zone. You an easily add lastLogonTimeStamp to the comma delimited list of attribute values to retrieve. For example:

    strAttributes = "sAMAccountName,distinguishedName,lastLogonTimeStamp"

     

    Then in the loop where you enumerate the ADO recordset you need to use a function to convert the 64-bit integer into a date/time in your time zone. Also, you need some code to determine the time zone offset. I add the following code somewhere at the beginning of any program that retrieves Integer8 values:

    ' Obtain local Time Zone bias from machine registry.
    ' This bias changes with Daylight Savings Time.
    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

     

    Then I add the following function at the end of the script:

    Function Integer8Date(ByVal objDate, ByVal 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

     

    Finally, you need to use this function in the loop where the ADO recordset is enumerated so you can retrieve the last logon value as a date in your time zone. For example:

    Do Until adoRecordset.EOF
        ' Retrieve values and display.
        strName = adoRecordset.Fields("sAMAccountName").Value
        objSheet.Cells(intRow, 1).Value = strName
        strDN = adoRecordset.Fields("distinguishedName").value
        strDN = Replace(strDN, "/", "\/")
        objSheet.Cells(intRow, 2).Value = Mid(Split(strDN,",")(0),4)
        ' Retrieve lastLogonTimeStamp using Set statement.
        Set objDate = adoRecordset.Fields("lastLogonTimeStamp").Value
        ' Convert Integer8 value to date in local time zone.
        dtmLastLogon = Integer8Date(objDate, lngBias)
        objSheet.Cells(intRow, 3).Value = dtmLastLogon
        ' .... other statements. Be sure to adjust the column numbers,
        ' as I have inserted a column and those to the right of this one
        ' must be incremented accordingly.
    Loop

     

    In my code above, the date #1/1/1601# is the "zero" date and really means never. If your domain is not at Windows Server 2003 functional level, then you really cannot add the last logon value to your program. If you retrieve the value of the lastLogon attribute, the value will not be correct unless you have only one Domain Controller (DC) in your domain. That's because the lastLogon attribute is not replicated, so a different value is saved for each user on every DC. Any program that retrieves the lastLogon value for a user must query every DC in the domain to find the largest value.

    I have example VBScript programs that just retrieve last logon dates linked here:

    http://www.rlmueller.net/Last%20Logon.htm

    The first program queries every DC in the domain to determine the largest lastLogon value for each user in the domain. For more on Integer8 attributes, see this link:

    http://www.rlmueller.net/Integer8Attributes.htm

    Richard Mueller


    MVP ADSI
    • Marked as answer by IamMred Wednesday, May 5, 2010 6:16 PM
    Tuesday, April 13, 2010 3:21 PM
    Moderator
  • Hi,

    I have a short snippet of a code in my blog on how to query for the lastLogonTimeStamp attribute for user accounts:

    http://sgwindowsgroup.org/blogs/badz/archive/2010/03/01/querying-for-the-lastlogontimestamp-attribute-of-all-users-in-an-ou.aspx

    It should be quite straightforward to add or incorporate this piece of code into your script.

    I'd also like to share this article which describes the lastLogonTimeStamp attribute in detail:

    “The LastLogonTimeStamp Attribute” – “What it was designed for and how it works”
    http://blogs.technet.com/askds/archive/2009/04/15/the-lastlogontimestamp-attribute-what-it-was-designed-for-and-how-it-works.aspx

    Regards,

    Salvador Manaois III
    MCSE MCSA MCTS MCITP C|EH CIWA
    ----------------------------------------------------------------------------
    Bytes & Badz: http://badzmanaois.blogspot.com
    My Passion: http://flickr.com/photos/badzmanaois
    My Scripting Blog: http://sgwindowsgroup.org/blogs/badz

    • Marked as answer by IamMred Wednesday, May 5, 2010 6:17 PM
    Tuesday, April 13, 2010 3:41 AM
    Moderator
  • hi

    this script will create a CV file with one column for the user display name , and another one for the last logon time

    so this is why powershell is cool :)

    # ==============================================================================================
    #
    # Microsoft PowerShell Source File -- Created with SAPIEN Technologies PrimalScript 2009
    #
    # NAME:
    #
    # AUTHOR: mohamed Garrana ,
    # DATE  : 4/13/2010
    #
    # COMMENT:
    #
    # ==============================================================================================

    function connect{

    $ADpath = "LDAP://OU=Users,OU=Amrya,OU=IT Department,DC=domain,DC=win2k,DC=dom" #set the ldap path to your domain or OU
    $searcher = New-Object DirectoryServices.DirectorySearcher
    $RootSearch = New-Object directoryservices.directoryentry $ADpath
    $searcher.searchroot = $RootSearch
    $searcher.filter = "(objectClass=user)"
    $allusers = $searcher.findall()
    foreach ($user in $allusers) { get-lastlogontime } 
    }
    function get-lastlogontime {
     BEGIN { }
     PROCESS  {
     #Write-Host $user.Properties.Displayname[0]
     try {
     $name = $user.Properties.displayname[0]
     $adlastlogon=$user.Properties.lastlogon[0]
     }
     Catch {
     Write-Host -ForegroundColor Red "   <<< WHoops ... >>>  $name : Error reading a required property from the AD User object, execution will continue anyway ;)"
      continue
      }
     finally {
     [datetime]$initialdate="01/01/1601" #microsoft date used to calculate lastlogon
     $lastlogon = $initialdate.Addseconds(($adlastlogon*1e-7))
     $AdUser = New-Object psobject
     $AdUser | Add-Member NoteProperty DisplayName ($name)
     $AdUser | Add-Member NoteProperty LastLogon ($lastlogon)
     Write-Output $AdUser
     }
     }
     END{}
    }
    $csvfile="C:\test\userlastlogon.csv" #set you output CSV here
    connect |  Export-Csv $csvfile

     


    G
    • Proposed as answer by Mohamed Garrana Tuesday, April 13, 2010 12:42 PM
    • Marked as answer by IamMred Wednesday, May 5, 2010 6:17 PM
    Tuesday, April 13, 2010 12:42 PM

All replies

  • hi razor

    Waw ... this is a very long script , what do you need to do exactly ?


    G

    Monday, April 12, 2010 11:12 PM
  • Hi,

    I have a short snippet of a code in my blog on how to query for the lastLogonTimeStamp attribute for user accounts:

    http://sgwindowsgroup.org/blogs/badz/archive/2010/03/01/querying-for-the-lastlogontimestamp-attribute-of-all-users-in-an-ou.aspx

    It should be quite straightforward to add or incorporate this piece of code into your script.

    I'd also like to share this article which describes the lastLogonTimeStamp attribute in detail:

    “The LastLogonTimeStamp Attribute” – “What it was designed for and how it works”
    http://blogs.technet.com/askds/archive/2009/04/15/the-lastlogontimestamp-attribute-what-it-was-designed-for-and-how-it-works.aspx

    Regards,

    Salvador Manaois III
    MCSE MCSA MCTS MCITP C|EH CIWA
    ----------------------------------------------------------------------------
    Bytes & Badz: http://badzmanaois.blogspot.com
    My Passion: http://flickr.com/photos/badzmanaois
    My Scripting Blog: http://sgwindowsgroup.org/blogs/badz

    • Marked as answer by IamMred Wednesday, May 5, 2010 6:17 PM
    Tuesday, April 13, 2010 3:41 AM
    Moderator
  • hi

    this script will create a CV file with one column for the user display name , and another one for the last logon time

    so this is why powershell is cool :)

    # ==============================================================================================
    #
    # Microsoft PowerShell Source File -- Created with SAPIEN Technologies PrimalScript 2009
    #
    # NAME:
    #
    # AUTHOR: mohamed Garrana ,
    # DATE  : 4/13/2010
    #
    # COMMENT:
    #
    # ==============================================================================================

    function connect{

    $ADpath = "LDAP://OU=Users,OU=Amrya,OU=IT Department,DC=domain,DC=win2k,DC=dom" #set the ldap path to your domain or OU
    $searcher = New-Object DirectoryServices.DirectorySearcher
    $RootSearch = New-Object directoryservices.directoryentry $ADpath
    $searcher.searchroot = $RootSearch
    $searcher.filter = "(objectClass=user)"
    $allusers = $searcher.findall()
    foreach ($user in $allusers) { get-lastlogontime } 
    }
    function get-lastlogontime {
     BEGIN { }
     PROCESS  {
     #Write-Host $user.Properties.Displayname[0]
     try {
     $name = $user.Properties.displayname[0]
     $adlastlogon=$user.Properties.lastlogon[0]
     }
     Catch {
     Write-Host -ForegroundColor Red "   <<< WHoops ... >>>  $name : Error reading a required property from the AD User object, execution will continue anyway ;)"
      continue
      }
     finally {
     [datetime]$initialdate="01/01/1601" #microsoft date used to calculate lastlogon
     $lastlogon = $initialdate.Addseconds(($adlastlogon*1e-7))
     $AdUser = New-Object psobject
     $AdUser | Add-Member NoteProperty DisplayName ($name)
     $AdUser | Add-Member NoteProperty LastLogon ($lastlogon)
     Write-Output $AdUser
     }
     }
     END{}
    }
    $csvfile="C:\test\userlastlogon.csv" #set you output CSV here
    connect |  Export-Csv $csvfile

     


    G
    • Proposed as answer by Mohamed Garrana Tuesday, April 13, 2010 12:42 PM
    • Marked as answer by IamMred Wednesday, May 5, 2010 6:17 PM
    Tuesday, April 13, 2010 12:42 PM
  • That article looks like it fits, however, I have virtually 0 experience with VBS.  At least it will help point me in the direction, I just need it implemented.  I tried and it was ulgy, lol
    Tuesday, April 13, 2010 1:45 PM
  • I have heard good things about PowerShell.  I was hoping to look into it one day.

    Thank you very much.

    Tuesday, April 13, 2010 1:46 PM
  • It is long since two parts of it are seperate loops.  The first loop creates an XLS file for all active members, the second loop creates a seperate file if there are any disabled accounts.  The keeps the active reports clean.

    What I am trying to do is have the Active report(the first loop) show me the last login time/date in the active report that is created.

    Tuesday, April 13, 2010 3:10 PM
  • If your domain is at Windows Server 2003 functional level or above, you can retrieve the lastLogonTimeStamp attribute for each user. However, this attribute is Integer8, meaning it is a 64-bit integer that represents a date as the number of 100-nanosecond intervals since 12:00 AM January 1, 1601 (a large number to be sure). Also, the date is in UTC (Coordinated Universal Time) so it must be converted into your local time zone. You an easily add lastLogonTimeStamp to the comma delimited list of attribute values to retrieve. For example:

    strAttributes = "sAMAccountName,distinguishedName,lastLogonTimeStamp"

     

    Then in the loop where you enumerate the ADO recordset you need to use a function to convert the 64-bit integer into a date/time in your time zone. Also, you need some code to determine the time zone offset. I add the following code somewhere at the beginning of any program that retrieves Integer8 values:

    ' Obtain local Time Zone bias from machine registry.
    ' This bias changes with Daylight Savings Time.
    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

     

    Then I add the following function at the end of the script:

    Function Integer8Date(ByVal objDate, ByVal 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

     

    Finally, you need to use this function in the loop where the ADO recordset is enumerated so you can retrieve the last logon value as a date in your time zone. For example:

    Do Until adoRecordset.EOF
        ' Retrieve values and display.
        strName = adoRecordset.Fields("sAMAccountName").Value
        objSheet.Cells(intRow, 1).Value = strName
        strDN = adoRecordset.Fields("distinguishedName").value
        strDN = Replace(strDN, "/", "\/")
        objSheet.Cells(intRow, 2).Value = Mid(Split(strDN,",")(0),4)
        ' Retrieve lastLogonTimeStamp using Set statement.
        Set objDate = adoRecordset.Fields("lastLogonTimeStamp").Value
        ' Convert Integer8 value to date in local time zone.
        dtmLastLogon = Integer8Date(objDate, lngBias)
        objSheet.Cells(intRow, 3).Value = dtmLastLogon
        ' .... other statements. Be sure to adjust the column numbers,
        ' as I have inserted a column and those to the right of this one
        ' must be incremented accordingly.
    Loop

     

    In my code above, the date #1/1/1601# is the "zero" date and really means never. If your domain is not at Windows Server 2003 functional level, then you really cannot add the last logon value to your program. If you retrieve the value of the lastLogon attribute, the value will not be correct unless you have only one Domain Controller (DC) in your domain. That's because the lastLogon attribute is not replicated, so a different value is saved for each user on every DC. Any program that retrieves the lastLogon value for a user must query every DC in the domain to find the largest value.

    I have example VBScript programs that just retrieve last logon dates linked here:

    http://www.rlmueller.net/Last%20Logon.htm

    The first program queries every DC in the domain to determine the largest lastLogon value for each user in the domain. For more on Integer8 attributes, see this link:

    http://www.rlmueller.net/Integer8Attributes.htm

    Richard Mueller


    MVP ADSI
    • Marked as answer by IamMred Wednesday, May 5, 2010 6:16 PM
    Tuesday, April 13, 2010 3:21 PM
    Moderator
  • I get erros when I try to implement it.  I don't know about increasing the cells, etc..  I am sure the code works, I am just not sure how to put it all together.  My expertise is in other areas, obviously! lol

    I vote helpful everytime :)

    Tuesday, April 13, 2010 8:57 PM