Strange result when piping script to Export-CSV


This topic contains 2 replies, has 2 voices, and was last updated by  Robert Prüst 3 years, 3 months ago.

  • Author
  • #14942

    Robert Prüst

    Hi all,

    I'm stuck on trying to fix a script when I try to output the content to CSV by using Export-CSV.
    This is the script in question:

    < # .SYNOPSIS Get-ADComputerQuery retrieves the computername and logged on user for all active AD Computers found in the requested OU. .DESCRIPTION Get-ADComputerQuery retrieves the computername and logged on user for all active AD Computers found in the requested OU. It uses the PowerShell Active Directory module to query Active Directory for all ADComputer objects and then uses WMI to query each machine for it's currently logged on User. .PARAMETER Searchbase This is the LDAP location of the OU you would like to query For example CN=Computers,DC=CONTOSO,DC=com ,PARAMETER ErrorLog Here you can define the location of error logfile created .PARAMETER LogErrors This switch parameter defines if you want to log errors or not. If you set this parameter, it will automatically log errors to the file located in the $ErrorLog parameter .EXAMPLE Get-ADComputerQuery -SearchBase "CN=Computers,DC=CONTOSO,DC=com" -LogErrors | Out-Gridview This will Query the computers in the CN=Computers,DC=CONTOSO,DC=com OU, logs all errors to the default location and displays the result in Gridview. .EXAMPLE Get-ADComputerQuery -SearchBase "CN=Computers,DC=CONTOSO,DC=com" | Export-CSV -NoTypeInformation .\Output.csv This will Query the computers in the the CN=Computers,DC=CONTOSO,DC=com OU and exports the information to a CSV file called Output.csv. #>
    Param (
    HelpMessage='Enter the LDAP OU location you want to have searched')]

    [string]$ErrorLog = ".\Get-ADComputerQuery_ErrorLog-$(((get-date).ToUniversalTime()).ToString('ddMMyyyyThhmm')).txt",

    BEGIN {
    Write-Verbose "Errors will be written to the logfile located at $ErrorLog"

    # Imports the required ActiveDirectory module. If this step fails, all other steps won't work and the script will stop running.
    Write-Verbose 'Adding the Active Directory PowerShell module required to query'
    Import-Module ActiveDirectory -ErrorAction Stop

    # Create an error log when LogErrors is enabled
    if ($LogErrors) {
    New-Item -Path $ErrorLog -Type File -Force
    Add-Content -Path $ErrorLog -Value "ComputerName,LastLogonDateDT `n"
    Write-Verbose "Log file created called $ErrorLog"
    Write-Verbose "Obtaining all AD Computers from provided OU: $SearchBase"
    $ADComputers = Get-ADComputer -Filter * -SearchBase $SearchBase -Properties Name,OperatingSystem,OperatingSystemServicePack,LastLogonTimestamp,DistinguishedName

    foreach ($ADComputer in $ADComputers){
    $ADComputerName = $

    # Convert LastLogonTimestamp to a readable format
    $Time = $ADComputer.LastLogonTimestamp
    $LastLogon = [DateTime]::FromFileTime($Time)

    Write-Verbose "Querying computer $ADComputerName"

    # Query Computer through WMI. If this fails, write computername to error log and disregard other queries.
    Try {
    $Everything_ok = $true
    $CS = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $ADComputerName -ErrorAction Stop

    Catch {
    $Everything_ok = $false
    Write-Warning "$ADComputerName failed"
    if ($LogErrors) {
    $ADComputerName + ',' + $LastLogon | Out-File -FilePath $ErrorLog -Append -Encoding ascii
    Write-Warning "Error logged to $ErrorLog"

    if ($Everything_ok) {
    $CSP = Get-WmiObject -Class Win32_ComputerSystemProduct -ComputerName $ADComputerName

    # Create new set of properties based on retrieved information
    $properties = @{'ComputerName'=$ADComputerName;

    Write-Verbose 'WMI Queries completed'

    # Create a new object containing set of properties
    $obj = New-Object -TypeName psobject -Property $properties

    # Outputting newly created object [this output can be piped to commands such as Export-CSV for example]
    Write-Output $obj

    # Outputting newly created object to graphical view
    # $obj | Out-GridView

    END {}

    When I use this as an example:
    Get-ADComputerQuery -SearchBase "CN=Computers,DC=CONTOSO,DC=com" | Export-CSV -NoTypeInformation .\Output.csv

    I get a .csv file, but this seems to contain the following "mess":

    "Microsoft.PowerShell.Core\FileSystem::C:\users\administrator.PICTORIGHT\documents\Get-ADComputerQuery_ErrorLog-02052014T1150.txt","Microsoft.PowerShell.Core\FileSystem::C:\users\administrator.PICTORIGHT\documents","Get-ADComputerQuery_ErrorLog-02052014T1150.txt","C","Microsoft.PowerShell.Core\FileSystem","False","File: C:\users\administrator.PICTORIGHT\documents\Get-ADComputerQuery_ErrorLog-02052014T1150.txt
    Debug: False
    Patched: False
    PreRelease: False
    PrivateBuild: False
    SpecialBuild: False
    ","Get-ADComputerQuery_ErrorLog-02052014T1150","-a---","Get-ADComputerQuery_ErrorLog-02052014T1150.txt","0","C:\users\administrator.CONTOSO\documents","C:\users\administrator.CONTOSO\documents","False","True","C:\users\administrator.CONTOSO\documents\Get-ADComputerQuery_ErrorLog-02052014T1150.txt",".txt","5/2/2014 1:50:45 PM","5/2/2014 11:50:45 AM","5/2/2014 1:50:45 PM","5/2/2014 11:50:45 AM","5/2/2014 1:50:45 PM","5/2/2014 11:50:45 AM","Archive"

    When I run the script without the Export-CSV everything works just fine...
    Any tips on what's causing this behaviour?

    And while I'm at it, any tips on how I can convert this to a WorkFlow so I can run this parallel [I understand that's a whole different question, but perhaps someone knows the answer to both 😉 ]

    Kind regards

  • #14944

    Dave Wyatt

    When you got that result, were you using the LogError switch? This jumps out at me:

    New-Item -Path $ErrorLog -Type File -Force

    New-Item returns the object that was created, and your CSV output looks a lot like what I'd expect to happen if a FileInfo object were the first thing in the output stream. You can suppress the output from New-Item by any of the usual methods:

    $null = New-Item -Path $ErrorLog -Type File -Force
    [void](New-Item -Path $ErrorLog -Type File -Force)
    New-Item -Path $ErrorLog -Type File -Force | Out-Null
  • #14958

    Robert Prüst

    Thanks a million Dave!
    Never would've looked there to find the solution...

    Tested it out and worked like a charm.
    Just for other people reading it, I've used the Out-Null solution and it works perfectly now with any parameter..
    Happy weekend ahead!

You must be logged in to reply to this topic.