none
Dsquery to show all Users and last login time and date

    Question

  • Hi , been trying to find the correct syntax to run 2 seperate dsquery's ,the first one to display all active AD accounts and when they last logged onto the system and the 2nd to show all disabled acccounts, been trying various combinations of the dsquery, dsget and both, however unable to get the commands to return the desired values. Can anyone share the correct context in which to use the commands for this purpose?
    Thursday, May 23, 2013 6:23 AM

Answers

  • Dsquery outputs are like that. I am explaining those.

     "sAMAccountName"= User login ID

     "lastlogontimestamp" as far as I remember that is 64 bit integer value & you can convert that with the help of the below link.

     Lastlogontimestamp conversion

    "useraccountcontol" value 514 means user is disabled.

    If you want to avoid the conversion you can use Quest powershell

    Get-QADUser -Name * -sizelimit 0 | select givenName,sn,name,lastlogontimestamp | Export-CSV c:\test.csv

    Quest Powershell for Active Directory

    Regards
    Biswajit Biswas

    My Blogs|TechnetWiki Ninja


      


    Best regards Biswajit Biswas Disclaimer: This posting is provided "AS IS" with no warranties or guarantees , and confers no rights. MCP 2003,MCSA 2003, MCSA:M 2003, CCNA, MCTS, Enterprise Admin

    • Marked as answer by SB3806 Monday, May 27, 2013 6:37 AM
    Thursday, May 23, 2013 7:25 AM

All replies

  • Dsquery * -limit 0 -filter "&(objectClass=User)(objectCategory=Person)" -attr sAMAccountName lastlogontimestamp useraccountcontol >>output123.txt

    Useraccountcontrol attribute provide the info about the user is disabled or not. see the below link. Useraccountcontrol value for disabled users "514" 

    http://support.microsoft.com/kb/305144

    Also find my blog below for various dsquery.

    Active Directory: DSQUERY Commands

    Regards
    Biswajit Biswas

    My Blogs|TechnetWiki Ninja


    Best regards Biswajit Biswas Disclaimer: This posting is provided "AS IS" with no warranties or guarantees , and confers no rights. MCP 2003,MCSA 2003, MCSA:M 2003, CCNA, MCTS, Enterprise Admin



    Thursday, May 23, 2013 6:43 AM
  • Hi Biswajit,

    Thanks but we already tried that one, we're looking for a way to present this information to a non-technical User, whilst that's a great query, they won't understand the output but thx anyway ...

    Thursday, May 23, 2013 6:51 AM
  • Dsquery outputs are like that. I am explaining those.

     "sAMAccountName"= User login ID

     "lastlogontimestamp" as far as I remember that is 64 bit integer value & you can convert that with the help of the below link.

     Lastlogontimestamp conversion

    "useraccountcontol" value 514 means user is disabled.

    If you want to avoid the conversion you can use Quest powershell

    Get-QADUser -Name * -sizelimit 0 | select givenName,sn,name,lastlogontimestamp | Export-CSV c:\test.csv

    Quest Powershell for Active Directory

    Regards
    Biswajit Biswas

    My Blogs|TechnetWiki Ninja


      


    Best regards Biswajit Biswas Disclaimer: This posting is provided "AS IS" with no warranties or guarantees , and confers no rights. MCP 2003,MCSA 2003, MCSA:M 2003, CCNA, MCTS, Enterprise Admin

    • Marked as answer by SB3806 Monday, May 27, 2013 6:37 AM
    Thursday, May 23, 2013 7:25 AM
  • I have tested the below output for you. Try IT. :). You no need to convert anything.

    get-aduser -f * -pr lastlogondate | ft samaccountname,LastLogonDate,Enabled -auto

    See the output.

    PS C:\> get-aduser -f * -pr lastlogondate | ft samaccountname,LastLogonDate,Enabled -auto
    
    samaccountname LastLogonDate        Enabled
    -------------- -------------        -------
    Administrator  5/23/2013 4:43:26 PM    True
    Guest                                 False
    krbtgt                                False
    AMather                                True
    ASeth                                  True

    Regards
    Biswajit Biswas

    My Blogs|TechnetWiki Ninja



    Best regards Biswajit Biswas Disclaimer: This posting is provided "AS IS" with no warranties or guarantees , and confers no rights. MCP 2003,MCSA 2003, MCSA:M 2003, CCNA, MCTS, Enterprise Admin


    Thursday, May 23, 2013 1:42 PM
  • --------------AD Powershell--------------

    All properties for all users.

    Get-ADUser -F * -property *

    All properties list of an users.

    AccountExpirationDate                :
    accountExpires                       : 0
    AccountLockoutTime                   :
    AccountNotDelegated                  : False
    adminCount                           : 1
    AllowReversiblePasswordEncryption    : False
    BadLogonCount                        : 0
    badPasswordTime                      : 0
    badPwdCount                          : 0
    CannotChangePassword                 : False
    CanonicalName                        : contoso.com/Users/Administrator
    Certificates                         : {}
    City                                 :
    CN                                   : Administrator
    codePage                             : 0
    Company                              :
    CompoundIdentitySupported            : {}
    Country                              :
    countryCode                          : 0
    Created                              : 5/23/2013 4:35:19 PM
    createTimeStamp                      : 5/23/2013 4:35:19 PM
    Deleted                              :
    Department                           :
    Description                          : Built-in account for administering the computer/domain
    DisplayName                          :
    DistinguishedName                    : CN=Administrator,CN=Users,DC=contoso,DC=com
    Division                             :
    DoesNotRequirePreAuth                : False
    dSCorePropagationData                : {5/23/2013 5:05:41 PM, 5/23/2013 5:05:41 PM, 5/23/2013 4:40:35 PM, 1/1/1601 10:12:16 AM}
    EmailAddress                         :
    EmployeeID                           :
    EmployeeNumber                       :
    Enabled                              : True
    Fax                                  :
    GivenName                            :
    HomeDirectory                        :
    HomedirRequired                      : False
    HomeDrive                            :
    HomePage                             :
    HomePhone                            :
    Initials                             :
    instanceType                         : 4
    isCriticalSystemObject               : True
    isDeleted                            :
    KerberosEncryptionType               : {}
    LastBadPasswordAttempt               :
    LastKnownParent                      :
    lastLogoff                           : 0
    lastLogon                            : 130139373813245584
    LastLogonDate                        : 5/23/2013 4:43:26 PM
    lastLogonTimestamp                   : 130138262065991851
    LockedOut                            : False
    logonCount                           : 7
    logonHours                           : {255, 255, 255, 255...}
    LogonWorkstations                    :
    Manager                              :
    MemberOf                             : {CN=Group Policy Creator Owners,CN=Users,DC=contoso,DC=com, CN=Domain Admins,CN=Users,DC=contoso,DC=com, CN=Enterprise Admins,CN=Users,DC=contoso,DC=com, CN=Schema Admins,CN=Users,DC=contoso,DC=com...}
    MNSLogonAccount                      : False
    MobilePhone                          :
    Modified                             : 5/23/2013 5:05:41 PM
    modifyTimeStamp                      : 5/23/2013 5:05:41 PM
    msDS-User-Account-Control-Computed   : 0
    Name                                 : Administrator
    nTSecurityDescriptor                 : System.DirectoryServices.ActiveDirectorySecurity
    ObjectCategory                       : CN=Person,CN=Schema,CN=Configuration,DC=contoso,DC=com
    ObjectClass                          : user
    ObjectGUID                           : 04a12180-5c90-4cbe-b99f-a94241d8f268
    objectSid                            : S-1-5-21-3894563728-440662116-3668880775-500
    Office                               :
    OfficePhone                          :
    Organization                         :
    OtherName                            :
    PasswordExpired                      : False
    PasswordLastSet                      : 5/23/2013 4:01:32 PM
    PasswordNeverExpires                 : False
    PasswordNotRequired                  : False
    POBox                                :
    PostalCode                           :
    PrimaryGroup                         : CN=Domain Users,CN=Users,DC=contoso,DC=com
    primaryGroupID                       : 513
    PrincipalsAllowedToDelegateToAccount : {}
    ProfilePath                          :
    ProtectedFromAccidentalDeletion      : False
    pwdLastSet                           : 130138236928869453
    SamAccountName                       : Administrator
    sAMAccountType                       : 805306368
    ScriptPath                           :
    sDRightsEffective                    : 15
    ServicePrincipalNames                : {}
    SID                                  : S-1-5-21-3894563728-440662116-3668880775-500
    SIDHistory                           : {}
    SmartcardLogonRequired               : False
    State                                :
    StreetAddress                        :
    Surname                              :
    Title                                :
    TrustedForDelegation                 : False
    TrustedToAuthForDelegation           : False
    UseDESKeyOnly                        : False
    userAccountControl                   : 512
    userCertificate                      : {}
    UserPrincipalName                    :
    uSNChanged                           : 16433
    uSNCreated                           : 8196
    whenChanged                          : 5/23/2013 5:05:41 PM
    whenCreated                          : 5/23/2013 4:35:19 PM

    Regards
    Biswajit Biswas

    My Blogs|TechnetWiki Ninja



    Best regards Biswajit Biswas Disclaimer: This posting is provided "AS IS" with no warranties or guarantees , and confers no rights. MCP 2003,MCSA 2003, MCSA:M 2003, CCNA, MCTS, Enterprise Admin



    Saturday, May 25, 2013 7:17 AM
  • Hi Biswajit,

    I tried what you said above and kept on getting the following error:


    PS C:\Windows\system32> get-aduser -f * -pr lastlogondate | ft samaccountname,La
    stLogonDate,Enabled -auto
    The term 'get-aduser' is not recognized as the name of a cmdlet, function, scri
    pt file, or operable program. Check the spelling of the name, or if a path was
    included, verify that the path is correct and try again.
    At line:1 char:11
    + get-aduser <<<<  -f * -pr lastlogondate | ft samaccountname,LastLogonDate,Ena
    bled -auto
        + CategoryInfo          : ObjectNotFound: (get-aduser:String) [], CommandN
       otFoundException
        + FullyQualifiedErrorId : CommandNotFoundException

    PS C:\Windows\system32> Set-ExecutionPolicy AllSigned

    I admit I am not a PS guru so thought you could run it from the command line but is it mean't to go into a script?

    I noticed our execution policy was set to restricted, I have since made it 'all signed' & this has seemed to have had no affect.

    Monday, May 27, 2013 12:29 AM
  • Which OS you are using? See the link for PS prerequisites on 2003 and 2008. You need to import PS AD module with the below command.

    Use Active Directory PowerShell to manage win 2003-2008 DCs

    Step-by-Step: How to use Active Directory PowerShell cmdlets against 2003 domain controllers

    Regards
    Biswajit Biswas

    My Blogs|TechnetWiki Ninja


    Best regards Biswajit Biswas Disclaimer: This posting is provided "AS IS" with no warranties or guarantees , and confers no rights. MCP 2003,MCSA 2003, MCSA:M 2003, CCNA, MCTS, Enterprise Admin


    Monday, May 27, 2013 4:34 AM
  • Oh, I tested it on my laptop which is Windows 7 Enterprise (x64) with SP1 and on Windows 2008 Server R2 (also X64) and ran an import-module activedirectory on the 2008 box, still couldn't get it to work though.

    Haven't finished looking at your links yet, I will finish that a bit later..

    Monday, May 27, 2013 6:01 AM
  • Good news, couldn't get the windows PS working but the quest one worked a treat!

    Thanks for your help, awesome result! :)

    Monday, May 27, 2013 6:37 AM
  • Glad to here that is working.

    Regards
    Biswajit Biswas

    My Blogs|TechnetWiki Ninja


    Best regards Biswajit Biswas Disclaimer: This posting is provided "AS IS" with no warranties or guarantees , and confers no rights. MCP 2003,MCSA 2003, MCSA:M 2003, CCNA, MCTS, Enterprise Admin

    Monday, May 27, 2013 7:44 AM