none
Using dsquery to list inactive users with last logon date.

    Question

  • I am trying to create a batch file that will list users who have been inactive for 6 months and also give their last logon date.  This batch file will be used by audit staff out in the field in different kinds of environments.  I know how to pull inactive users and I know how to pull the last logon time for all users, I just don't know how to make the last logon timestamp apply to only inactive users.  Any help would be much appreciated.

    Inactive Users dsquery

    dsquery user -o rdn -inactive 26 -limit 0 > Inactive_Users.txt

    Last logon timestamp dsquery

    dsquery * -filter "(&(objectCategory=user)(objectClass=user))" -limit 0 -attr displayname sAMAccountName lastLogonTimeStamp > Last_Logon_Date.txt

    Tuesday, March 05, 2013 1:29 PM

Answers

  • Look at oldCmp a freeware tool from MVP Joe Richards available at joeware.net.  This will allow you to manage users, computers or both with parameter input on length of time since last logged on.  This is a great tool, it can moved, disable, delete or just report via a whatif.
    http://www.joeware.net/freetools/tools/oldcmp/index.htm

    -- 
    Paul Bergson
    MVP - Directory Services
    MCITP: Enterprise Administrator
    MCTS, MCT, MCSE, MCSA, Security+, BS CSci
    2008, Vista, 2003, 2000 (Early Achiever), NT4
    http://www.pbbergs.com    Twitter @pbbergs
    http://blogs.dirteam.com/blogs/paulbergson

    Please no e-mails, any questions should be posted in the NewsGroup. This posting is provided "AS IS" with no warranties, and confers no rights.


    Wednesday, March 06, 2013 1:11 PM
  • I cannot see a way to do this in a batch file using the command line utilities dsquery and dsget. I think you need a script. The following VBScript is a modified version of one I have used, modified to the user can double click on the file and it will write the output to a specified text file:

    Option Explicit

    Dim adoCommand, adoConnection, strBase, strFilter, strAttributes
    Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName
    Dim intDays, dtmDate, objShell, lngBiasKey, lngBias, k
    Dim lngSeconds, str64Bit, strDisplay
    Dim objDate, dtmLastDate, lngHigh, lngLow
    Dim strFile, objFSO, objFile

    Const ForWriting = 2
    Const OpenAsASCII = 0
    Const CreateIfNotExist = True

    ' Specify number of days.
    intDays = 180

    ' Specify file for report.
    strFile = "c:\Scripts\StaleUsers.csv"

    ' Open text file for writting.
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    ' Open the file for write access.
    Set objFile = objFSO.OpenTextFile(strFile, _
        ForWriting, CreateIfNotExist, OpenAsASCII)

    ' Determine date the specified number of days in the past.
    dtmDate = DateAdd("d", - intDays, Now())

    ' 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

    ' Convert the datetime value to UTC.
    dtmDate = DateAdd("n", lngBias, dtmDate)

    ' Find number of seconds since 1/1/1601 for this date.
    lngSeconds = DateDiff("s", #1/1/1601#, dtmDate)

    ' Convert the number of seconds to a string
    ' and convert to 100-nanosecond intervals.
    str64Bit = CStr(lngSeconds) & "0000000"

    ' Setup ADO objects.
    Set adoCommand = CreateObject("ADODB.Command")
    Set adoConnection = CreateObject("ADODB.Connection")
    adoConnection.Provider = "ADsDSOObject"
    adoConnection.Open "Active Directory Provider"
    Set adoCommand.ActiveConnection = adoConnection

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

    ' Filter on disabled users that have not logged on in 6 months.
    strFilter = "(&(objectCategory=person)(objectClass=user)" _
        & "(userAccountControl=2)(lastLogonTimeStamp<=" & str64Bit & "))"

    ' Comma delimited list of attribute values to retrieve.
    strAttributes = "sAMAccountName,displayName,lastLogonTimeStamp"

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

    ' Run the query.
    Set adoRecordset = adoCommand.Execute

    ' Enumerate the resulting recordset.
    Do Until adoRecordset.EOF
        ' Retrieve values and display.
        strName = adoRecordset.Fields("sAMAccountName").Value
        strDisplay = adoRecordset.Fields("displayName").Value
        On Error Resume Next
        Set objDate = adoRecordset.Fields("lastLogonTimeStamp").Value
        If (Err.Number <> 0) Then
            On Error GoTo 0
            dtmLastDate = #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
                dtmLastDate = #1/1/1601#
            Else
                dtmLastDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _
                    + lngLow)/600000000 - lngBias)/1440
            End If
        End If
        ' Write to file, with comma delimited fields.
        objFile.WriteLine """" & strName & """,""" & strDisplay _
            & """,""" & CStr(dtmLastDate) & """"
        ' Move to the next record in the recordset.
        adoRecordset.MoveNext
    Loop

    ' Clean up.
    objFile.Close
    adoRecordset.Close
    adoConnection.Close

    -----

    Anyone should be able to run this and create the file specified. I made the file comma delimited. I hard coded the 180 days and the file name and path.


    Richard Mueller - MVP Directory Services

    Wednesday, March 06, 2013 5:44 PM

All replies

  • I just don't know how to make the last logon timestamp apply to only inactive users.  Any help would be much appreciated.

    What do you mean by that? LastLogontimestamp is an AD attribute managed by AD system itself (Even administrators cannot update it). This attribute is used by all user accounts and is not only used by inactive ones. Due to that, there is no way to make it used only by inactive users.

    Unfortunately, this attribute will be delayed with 9-14 days behind the current date. That should not be a problem for inactive accounts. Details here: http://blogs.technet.com/b/askds/archive/2009/04/15/the-lastlogontimestamp-attribute-what-it-was-designed-for-and-how-it-works.aspx


    This posting is provided "AS IS" with no warranties or guarantees , and confers no rights.

    Tuesday, March 05, 2013 1:36 PM
  • Mr X, thank you for your reply.

    I understand this attribute is an attribute in all user accounts.  What I'm trying to accomplish is getting a list together of inactive users in an AD environment and it would be nice to have the last logon date to accompany those users for reporting purposes.  Being 9 to 14 days off isn't to much of a concern as we can get a general idea of when that user logged in last.

    Tuesday, March 05, 2013 1:45 PM
  • Hi,

    So what you wanted is to list all the inactive users for 6 months and meanwhile get their last logon date, correct?

    The task is not simple. Actually the lastLogonTimestamp attribute also doesn't reflect the accurate last logon time of a user. It is not updated with all logon types or at every logon, it only reflects simple bind operations and NTLM network-based logons. To determine the real-time of the user last logon date, you need to extract logon events from various DCs to get the information. Please go through the article Mr X posted above.

    Regards,
    Cicely


    Wednesday, March 06, 2013 7:22 AM
  • Look at oldCmp a freeware tool from MVP Joe Richards available at joeware.net.  This will allow you to manage users, computers or both with parameter input on length of time since last logged on.  This is a great tool, it can moved, disable, delete or just report via a whatif.
    http://www.joeware.net/freetools/tools/oldcmp/index.htm

    -- 
    Paul Bergson
    MVP - Directory Services
    MCITP: Enterprise Administrator
    MCTS, MCT, MCSE, MCSA, Security+, BS CSci
    2008, Vista, 2003, 2000 (Early Achiever), NT4
    http://www.pbbergs.com    Twitter @pbbergs
    http://blogs.dirteam.com/blogs/paulbergson

    Please no e-mails, any questions should be posted in the NewsGroup. This posting is provided "AS IS" with no warranties, and confers no rights.


    Wednesday, March 06, 2013 1:11 PM
  • Hi jwcblc

    Here`s the powershell command to do what you require.

    Search-ADAccount -AccountInactive -UsersOnly -TimeSpan 180.00:00:00.

    You`ll need to load the Active directory module first. This command will search all of AD for inactive user accounts, more than 180 days.

    Hope this helps

    Ernie


    Ernie Prescott

    Search-ADAccount -accountinactive -usersonly -timespan 180.00:00:00 | format-table -property UserPrincipalName,LastLogonDate >text.txt

    This will format and filter the results and dump the data into a text file.

    Wednesday, March 06, 2013 2:16 PM
  • Cicely,

    That is exactly what I'm trying to accomplish.  I understand the lastLogonTimestamp attribute is not accurate to the day as it was only designed to show inactive user and computer accounts but it is close enough for what I need.  Basically what I need to put in an audit report is here is a list of inactive users and then say each inactive user hasn't logged in in x number of months.




    • Edited by jwcblc Wednesday, March 06, 2013 4:42 PM
    Wednesday, March 06, 2013 4:41 PM
  • Pbbergs and Ernie,

    I would love to use a tool such as oldCmp or even powershell, problem being is these audits are being done in environments where we can not install any additional software or tools to perform our tasks.  This is why I need to use a universal batch file that can run in different environments without having to install any additional tools.

    I might just have to run the two dsquerys separately and then cross reference them for the information I need.

    Wednesday, March 06, 2013 4:42 PM
  • I cannot see a way to do this in a batch file using the command line utilities dsquery and dsget. I think you need a script. The following VBScript is a modified version of one I have used, modified to the user can double click on the file and it will write the output to a specified text file:

    Option Explicit

    Dim adoCommand, adoConnection, strBase, strFilter, strAttributes
    Dim objRootDSE, strDNSDomain, strQuery, adoRecordset, strName
    Dim intDays, dtmDate, objShell, lngBiasKey, lngBias, k
    Dim lngSeconds, str64Bit, strDisplay
    Dim objDate, dtmLastDate, lngHigh, lngLow
    Dim strFile, objFSO, objFile

    Const ForWriting = 2
    Const OpenAsASCII = 0
    Const CreateIfNotExist = True

    ' Specify number of days.
    intDays = 180

    ' Specify file for report.
    strFile = "c:\Scripts\StaleUsers.csv"

    ' Open text file for writting.
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    ' Open the file for write access.
    Set objFile = objFSO.OpenTextFile(strFile, _
        ForWriting, CreateIfNotExist, OpenAsASCII)

    ' Determine date the specified number of days in the past.
    dtmDate = DateAdd("d", - intDays, Now())

    ' 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

    ' Convert the datetime value to UTC.
    dtmDate = DateAdd("n", lngBias, dtmDate)

    ' Find number of seconds since 1/1/1601 for this date.
    lngSeconds = DateDiff("s", #1/1/1601#, dtmDate)

    ' Convert the number of seconds to a string
    ' and convert to 100-nanosecond intervals.
    str64Bit = CStr(lngSeconds) & "0000000"

    ' Setup ADO objects.
    Set adoCommand = CreateObject("ADODB.Command")
    Set adoConnection = CreateObject("ADODB.Connection")
    adoConnection.Provider = "ADsDSOObject"
    adoConnection.Open "Active Directory Provider"
    Set adoCommand.ActiveConnection = adoConnection

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

    ' Filter on disabled users that have not logged on in 6 months.
    strFilter = "(&(objectCategory=person)(objectClass=user)" _
        & "(userAccountControl=2)(lastLogonTimeStamp<=" & str64Bit & "))"

    ' Comma delimited list of attribute values to retrieve.
    strAttributes = "sAMAccountName,displayName,lastLogonTimeStamp"

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

    ' Run the query.
    Set adoRecordset = adoCommand.Execute

    ' Enumerate the resulting recordset.
    Do Until adoRecordset.EOF
        ' Retrieve values and display.
        strName = adoRecordset.Fields("sAMAccountName").Value
        strDisplay = adoRecordset.Fields("displayName").Value
        On Error Resume Next
        Set objDate = adoRecordset.Fields("lastLogonTimeStamp").Value
        If (Err.Number <> 0) Then
            On Error GoTo 0
            dtmLastDate = #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
                dtmLastDate = #1/1/1601#
            Else
                dtmLastDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _
                    + lngLow)/600000000 - lngBias)/1440
            End If
        End If
        ' Write to file, with comma delimited fields.
        objFile.WriteLine """" & strName & """,""" & strDisplay _
            & """,""" & CStr(dtmLastDate) & """"
        ' Move to the next record in the recordset.
        adoRecordset.MoveNext
    Loop

    ' Clean up.
    objFile.Close
    adoRecordset.Close
    adoConnection.Close

    -----

    Anyone should be able to run this and create the file specified. I made the file comma delimited. I hard coded the 180 days and the file name and path.


    Richard Mueller - MVP Directory Services

    Wednesday, March 06, 2013 5:44 PM
  • Pbbergs and Ernie,

    I would love to use a tool such as oldCmp or even powershell, problem being is these audits are being done in environments where we can not install any additional software or tools to perform our tasks.  This is why I need to use a universal batch file that can run in different environments without having to install any additional tools.

    I might just have to run the two dsquerys separately and then cross reference them for the information I need.

    There is nothing to install it is a self contained object, unless that violates your process.  Otherwise it can sit in a share and you could run it from your workstation that resides in the domain.

    Paul

    Thursday, March 07, 2013 10:21 PM