none
User extraction script with creation and disabled date

    Question

  • Hi,

    I was able to extract few information with the below command-line.

    C:\>dsquery user | dsget user -dn -desc -samid -disabled > userinfo.txt

    Now I would like to extract the user creation and disabled date with the above information; i.e,  a single text/csv/excel file with the following details.
    Distribution Name, sAMID, Description, Disabled (Yes/No), Creation Date, Disabled Date.

    Please any one could help me with the script?

    Thanks and Regards,

    Mahesh B


    Regards, Mahesh B

    Monday, June 18, 2012 7:38 AM

Answers

  • Hi,

    Here's a Powershell script.

    Import-Module ActiveDirectory
    
    Get-ADUser -Filter * -Properties * | Select DistinguishedName, SAMAccountName, Description, Enabled, WhenCreated | Export-CSV C:\Temp\users.csv

    Disabled Date isn't store.

    Regards,

    Monday, June 18, 2012 8:06 AM
  • You cannot get date/time when user account has been disabled, however you can check when user last time logged on. For that you can simly use DSQUERY in LDAP query. Try this

    dsquery * -filter "(&(objectClass=User)(objectCategory=Person))" -attr distinguishedName sAMAccountName Description whenCreated whenChanged lastLogonTimestamp >>c:\users.txt

    and you cannot get simply disabled/enabled user account as you need to use another attribute and its property (userAccountControll). So, to get enabled users with all above information, use:

    dsquery * -filter "(&(objectClass=User)(objectCategory=Person)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))" -attr distinguishedName sAMAccountName Description whenCreated whenChanged lastLogonTimestamp >>c:\enabled_users.txt

    to get disabled users:

    dsquery * -filter "(&(objectClass=User)(objectCategory=Person)(userAccountControl:1.2.840.113556.1.4.803:=2))" -attr distinguishedName sAMAccountName Description whenCreated whenChanged lastLogonTimestamp >>c:\enabled_users.txt

    as lastLogonTimestamp attribute is stored as int64 value, you need to convert it to human readable format. For that you can simply use w32tm command. Import users.txt file into Excel and copy only column with lastLogonTimestamp values into notepad and save it as time.txt on C-Drive. Now, run this syntax to get human readable time:

    for /f %i in (c:\time.txt) do w32tm /ntte %i >>c:\fixed_time.txt

    import fixed_time.txt into Excel and put values into previous sheet in new column to get last logon time information.

    Much more simply is using PowerShell module for AD instead of MS DS Tools. If you have Windows Server 2008 R2 Domain Controller, you can try this syntax

    Get-ADUser -Filter * -Properties * | Select DistinguishedName,SamAccountName,Description,whenCreated,whenChanged,Enabled,LastLogonDate | Export-CSV c:\users.csv


    Regards, Krzysztof ---- Visit my blog at http://kpytko.wordpress.com

    Monday, June 18, 2012 8:18 AM
  • If you like GUI tool to fetch the information, below it is. The lastlogontimestamp attribute is not accurate, it is only proper when the logon is 9-14 days behind.

    http://www.joeware.net/freetools/tools/oldcmp/

    http://www.joeware.net/freetools/tools/adfind/index.htm

    http://www.cjwdev.co.uk/Software/ADTidy/Info.html


    Awinish Vishwakarma - MVP - Directory Services

    My Blog: awinish.wordpress.com

    Disclaimer This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Monday, June 18, 2012 8:39 AM
    Moderator

All replies

  • Hi,

    Here's a Powershell script.

    Import-Module ActiveDirectory
    
    Get-ADUser -Filter * -Properties * | Select DistinguishedName, SAMAccountName, Description, Enabled, WhenCreated | Export-CSV C:\Temp\users.csv

    Disabled Date isn't store.

    Regards,

    Monday, June 18, 2012 8:06 AM
  • You cannot get date/time when user account has been disabled, however you can check when user last time logged on. For that you can simly use DSQUERY in LDAP query. Try this

    dsquery * -filter "(&(objectClass=User)(objectCategory=Person))" -attr distinguishedName sAMAccountName Description whenCreated whenChanged lastLogonTimestamp >>c:\users.txt

    and you cannot get simply disabled/enabled user account as you need to use another attribute and its property (userAccountControll). So, to get enabled users with all above information, use:

    dsquery * -filter "(&(objectClass=User)(objectCategory=Person)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))" -attr distinguishedName sAMAccountName Description whenCreated whenChanged lastLogonTimestamp >>c:\enabled_users.txt

    to get disabled users:

    dsquery * -filter "(&(objectClass=User)(objectCategory=Person)(userAccountControl:1.2.840.113556.1.4.803:=2))" -attr distinguishedName sAMAccountName Description whenCreated whenChanged lastLogonTimestamp >>c:\enabled_users.txt

    as lastLogonTimestamp attribute is stored as int64 value, you need to convert it to human readable format. For that you can simply use w32tm command. Import users.txt file into Excel and copy only column with lastLogonTimestamp values into notepad and save it as time.txt on C-Drive. Now, run this syntax to get human readable time:

    for /f %i in (c:\time.txt) do w32tm /ntte %i >>c:\fixed_time.txt

    import fixed_time.txt into Excel and put values into previous sheet in new column to get last logon time information.

    Much more simply is using PowerShell module for AD instead of MS DS Tools. If you have Windows Server 2008 R2 Domain Controller, you can try this syntax

    Get-ADUser -Filter * -Properties * | Select DistinguishedName,SamAccountName,Description,whenCreated,whenChanged,Enabled,LastLogonDate | Export-CSV c:\users.csv


    Regards, Krzysztof ---- Visit my blog at http://kpytko.wordpress.com

    Monday, June 18, 2012 8:18 AM
  • Thank You Gregory. I shall try the script.  So you mean AD do not store the disabled date and to extract the disabled date we need to use the Security log?

    Regards, Mahesh B

    Monday, June 18, 2012 8:26 AM
  • If you like GUI tool to fetch the information, below it is. The lastlogontimestamp attribute is not accurate, it is only proper when the logon is 9-14 days behind.

    http://www.joeware.net/freetools/tools/oldcmp/

    http://www.joeware.net/freetools/tools/adfind/index.htm

    http://www.cjwdev.co.uk/Software/ADTidy/Info.html


    Awinish Vishwakarma - MVP - Directory Services

    My Blog: awinish.wordpress.com

    Disclaimer This posting is provided AS-IS with no warranties/guarantees and confers no rights.

    Monday, June 18, 2012 8:39 AM
    Moderator