Can't export Get-ADUser results using Export-CSV

This topic contains 6 replies, has 3 voices, and was last updated by Profile photo of thickgit thickgit 2 years, 4 months ago.

  • Author
    Posts
  • #17684
    Profile photo of thickgit
    thickgit
    Participant

    Morning Folks!

    I'm doing a Get-ADUser search for inactive/dormant users, including the 'lastlogon' attribute for each user (and querying all four domain controllers as I know this attribute doesn't replicate). This is working fine. (Obviously I have up to four entries for each user, but Security are happy with that – they'll deal with it).

    However, I can't get the overall output on to a single spreadsheet. I essentially want the output of each DC to 'append' to the csv file, rather than overwrite it (tried -NoClobber to prevent any overwrites but I obviously only end up with the output of a single DC).

    On my first attempt, I had to use Out-File, write to a text file using the -Append switch. I then pasted that into Excel and messed with it a little to get what I wanted. OK as a one-off, but it looks like I'm going to be asked for this on a regular basis. Also, more properties are now required per user and it's defaulting to a list-view. I've tried format-table before exporting to my text file (which I know isn't great), but I think it's time I just found a way of natively exporting everything to a single csv file. I've shifted curly braces around and still get errors. I'm sure I'll kick myself when I find out what's wrong.

    My input file is simply a single-column csv containing the first name and last name of each user. Column header is 'name'.

    Could somebody please look at the attached code and suggest how I might use Export-Csv to a single csv file? (I've left the current Out-Default cmdlet at the end for now).

    Cheers-much-in-advance.

    TG

  • #17686
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    You cannot do a Format-Table (ft) prior to outputting it to a file. The Format-Table or Format-List to do not pass through. That's the end of the line. Remove the | ft try your command.

  • #17687
    Profile photo of thickgit
    thickgit
    Participant

    Thanks Rob,

    Sorry – I should have made it clearer. I only did that so I could get the information into Out-File, which was a very last resort when Export-Csv wasn't working the way I wanted it to. I didn't originally have ft in there. Even without the | ft, it still doesn't output properly using Export-Csv. It just overwrites the csv file it creates.

  • #17690
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    So, you can use that logic and append to a CSV, but that very inefficient. Writing a single line as the script is runs is vbScript logic, but it opens a file, writes each line and then closes the file. In Powershell you should leverage a PSObject, which is comparable in that you create a new object, add a object (line) to it and then you have an object that you can sort, filter and export. :

    #Generate a blank object
    $users = @()
    $dcs = Get-ADComputer -f * -SearchBase 'ou=domain controllers,dc=company,dc=pri'
    #Assign a variable to your for loop and as Get-ADUser is run, it will append to the @users object
    $users = foreach ($dc in $dcs) {
        Import-Csv 'E:\list_of_displaynames.csv' | foreach {
            $f = $_.name;
            Get-ADUser -f {displayname -eq $f} -Server $dc.name -properties DisplayName,whencreated,description,lastlogon} |
            select Name,DisplayName,Description,WhenCreated,Enabled,@{l='LastLoggedIn';e={w32tm /ntte $_.lastlogon}
        }
    }
    
    # Now you have an object, you can leverage the power of PowerShell
    # Group all of the Enabled\Disabled users and Sort by the count
    $users | Group-Object -Property Enabled | | Sort-Object -Property Count -Descending
    #Get all users that have marketing in the Description
    $users | Where{$_.Description -like "*Marketing*"}
    # Export the results to a CSV
    $users | Export-CSV C:\users.csv
  • #17692
    Profile photo of thickgit
    thickgit
    Participant

    Thanks Rob – that has worked perfectly! Just what I needed.

    I thought I was a lot closer than I obviously was, but I've learnt plenty in this one example. Many thanks for your help with this. 🙂

  • #17742
    Profile photo of Robert McDonnell
    Robert McDonnell
    Participant

    Two things to add here. Rob Simmers nailed the 'format right rule' on this one, once you pipe to a format command, you have effectively converted your very useful object to a less useful Microsoft.PowerShell.Commands.Internal.Format object. The easy way to see this is to take a command like Get-WMIObject and pipe it to Get-Member, do this again with a format command inserted like this:

    PS C:\Scripting> Get-WmiObject Win32_BIOS | Get-Member
    
    
       TypeName: System.Management.ManagementObject#root\cimv2\Win32_BIOS
    
    Name                  MemberType    Definition
    
    PSComputerName        AliasProperty PSComputerName = __SERVER
    BiosCharacteristics   Property      uint16[] BiosCharacteristics {get;set;}
    
    # Stuff Deleted
    
    PS C:\Scripting> Get-WmiObject Win32_BIOS | Format-List | Get-Member
    
       TypeName: Microsoft.PowerShell.Commands.Internal.Format.FormatStartData
    
    # Stuff Deleted
    
       TypeName: Microsoft.PowerShell.Commands.Internal.Format.GroupStartData
    
    # Lots of Stuff Deleted
    
    

    On a separate AD related note, is there any reason why you are trying to use lastLogon as opposed to lastLogonTimestamp. The lastLogonTimeStamp, by default is replicated to all Domain Controllers in the domain.

    These attributes are designed to help with inactive users as they are typically about 9-14 days earlier than the last true logon, real time data will need to come from log collection.

  • #17757
    Profile photo of thickgit
    thickgit
    Participant

    Thanks Robert,

    Yes, I did know about the format-right rule. I was just trying anything to get all of the data into one sheet so I could manipulate it in Excel for a one-off report which was needed in a rush. I know it was slapdash (now you know how I got my name), but it worked on this one occasion.

    I may have originally misunderstood the way that the lastlogondate,lastlogon and lastlogontimestamp attributes worked. I had read that they were low-priority replication, but I was worried that if someone hadn't logged in for a number of months, and then for example returned from maternity leave yesterday and logged in, it might not have registered/replicated.

    We're disabling accounts that haven't been used for 60 days, so I want to be absolutely sure before I send the results on to Management. Apologies if I've got the wrong end of the stick. I'll read up some more on the differences between the various attributes and if the lastlogontimestamp is the one to use, I'll amend the script to query that attribute and just the one DC.

    Appreciate your input. Many thanks.

    TG

You must be logged in to reply to this topic.