locked
export the ad users list with Username , First and Last name, and last login date in an Excel format RRS feed

  • Question

  • Hi  folks.

    I'm trying to make  a script  for  query all  the users  from specific   OU  in my  domain and  get the Username , First and Last name, and last login date in an Excel format  from each  user , and  export the  result  to  a csv  file .

    Could anyone help?

    Thanks in Advance,

    Kousic

    Friday, February 15, 2019 8:44 AM

All replies

  • Start by learning these CmdLets.

    help Get-AdUser -online
    help export-Csv -online

    You can also find scripts in the Gallery that do this.

    We do not write scripts on request in this forum.


    \_(ツ)_/

    • Proposed as answer by BOfH-666 Friday, February 15, 2019 11:46 AM
    Friday, February 15, 2019 9:19 AM
  • As noted, you can use the Export-Csv cmdlet. A properly formatted CSV file can always be imported into Excel. And the help for Get-ADUser documents the parameters you can use to retrieve first name, etc.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    • Proposed as answer by BOfH-666 Friday, February 15, 2019 11:46 AM
    Friday, February 15, 2019 11:41 AM
  • Hi Kousic

    Update the $BaseOU with the OU you need. it has to be in that format. 

    $BaseOU = "OU=Your,OU=OU,DC=domain,DC=local"
    $Property = @(
        @{n='Username';e={$_.Name}},
        @{n='FirstName';e={$_.GivenName}},
        @{n='LastName';e={$_.SurName}}
        @{n='LastLogin';e={[DateTime]::FromFileTime($_.lastLogonTimestamp)}}    
    )
    
    Get-AdUser -Filter * -SearchBase $BaseOU -Properties lastlogonTimestamp | 
    Select -Property $Property |
    Export-CSV -Path C:\temp\output.csv -NoTypeInformation
    
    

    This will generate csv file which you can open in Excel and save it as a workbook. The alternative is to use 'ImportExcel' Module. you will need to change the last bit of the code accordingly.

    Good luck

    Friday, February 15, 2019 1:56 PM
  • Instead of retrieving the lastLogonTimestamp attribute, it would make sense to retrieve the LastLogonDate PowerShell property exposed by Get_ADUser, which does the conversion into a datetime value in the local time zone for you.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Friday, February 15, 2019 2:08 PM
  • Instead of retrieving the lastLogonTimestamp attribute, it would make sense to retrieve the LastLogonDate PowerShell property exposed by Get_ADUser, which does the conversion into a datetime value in the local time zone for you.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    I have been using LastLogonTimeStamp due to this post...

    lastlogon vs. lastlogontimestamp

    Friday, February 15, 2019 2:18 PM
  • Yes, but the LastLogonDate property is the same as the lastLogonTimestamp attribute, but with extra code that converts the LargeInteger (64-bit) value into a datetime in the local time zone automatically for you. It is true that the lastLogon attribute is not replicated, so you would need to query every DC to get the largest (latest) value. The lastLogonTimestamp attribute and the LastLogonDate property are only updated if the old value is more than 15 days (by default) in the past, but the value is replicated to all DCs. This makes it useful for identifying stale accounts.

    In my terminology (based on Microsoft documentation), an attribute is actually saved in the AD database, while a property is short for property method. It has code that can convert an attribute value into a friendly format.



    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Friday, February 15, 2019 3:00 PM
  • Both examples are wrong.

    First one must be this:

    $params = @{
        Filter = '*'
        SearchBase = 'OU=Users,DC=fabrikam,DC=com'
    Properties = 'Surname', 'GivenName' } Get-ADUser @params | Select-Object Surname, GivenName, SamAccountName | Export-Csv MyOutputFile.csv –NoTypeInformation

    Second must be this:

    Get-ADUser -Filter * -SearchBase 'ou=users,dc=contoso,dc=local' -Properties lastLogonDate |
        Select-Object Name,LastLogonDate | 
        Export-CSV -NoTypeInformation last.csv

    The links posted are for very old and very bad code examples.  The user posting the blog was copying code from PS v1.  PowerShell is at WMF 5.1 and ADWS has evolved since then.

    Also, jamming everything on one line is a very bad way to write code.


    \_(ツ)_/


    • Edited by jrv Monday, February 18, 2019 12:24 PM
    Monday, February 18, 2019 12:23 PM
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Thursday, February 28, 2019 9:55 AM