Exporting info from PS script to csv

Question

Hi,

This is a powershell/AD/Exchange question....

I'm running a script against a number of Users to check some of their attributes however I'm having trouble getting this to output to CSV. The script runs well and does exactly what I need it to do, the output on screen is fine, I'm just having trouble getting it to directly export to csv.

The input is a comma seperated txt file of usernames (eg "username1,username2,username3")

I've experimented with creating custom ps objects, adding to them and then exporting those but its not working....

Any suggestions gratefully received..

Thanks

George

$array = Get-Content $InputPath

#split the comma delimited string into an array
$arrayb = $array.Split(",");

foreach ($User in $arrayb)
{
    #find group memebership
    Write-Host "AD group membership for $User"
    Get-QADMemberOf $User

    #Get Mailbox Info
    Write-Host "Mailbox info for $User"
    Get-Mailbox $User | select ServerName, Database, EmailAddresses, PrimarySmtpAddress, WindowsEmailAddress

    #get profile details
    Write-Host "Home drive info for $User"
    Get-QADUser $User| select HomeDirectory,HomeDrive

    #add space between users
    Write-Host ""
    Write-Host "******************************************************"


}

Write-Host "End Script"

EDITED.... Methods I have tried for exproting (showing only the for loop/export code) Method1

$AllData = @()  

foreach ($User in $arrayb)
{
    #set title for this user
    #Write-host "Details for $User"

    #find out their group memebership
    Write-Host "AD group membership for $User"
    $AdMemberOf = Get-QADMemberOf $User 
    Write-Host "ad completed"
    Write-Host ""
    Write-Host ""


    #Get Mailbox Info
    Write-Host "Mailbox info for $User"
    $ExInfo = Get-Mailbox $User | select ServerName, Database, EmailAddresses, PrimarySmtpAddress, WindowsEmailAddress
    Write-Host "ex completed"
    Write-Host ""
    Write-Host ""


    #get profile details
    Write-Host "Home drive info for $User"
    $HomeInfo = Get-QADUser $User| select HomeDirectory,HomeDrive
    Write-Host "home drive completed"

    #add space between users
    Write-Host ""
    Write-Host "******************************************************"


    $ReturnedObj = New-Object PSObject
    $ReturnedObj | Add-Member NoteProperty -Name "AD Group Membership for $User" -Value $AdMemberOf
    $ReturnedObj | Add-Member NoteProperty -Name "Exchange details for $User" -Value $ExInfo
    $ReturnedObj | Add-Member NoteProperty -Name "Home drive info for $User" -Value $HomeInfo


    Write-Host $ReturnedObj

    $AllData += $ReturnedObj


}
Write-Host "starting csv export"
Write-Output $AllData |export-csv -Path $OutputPath -notype -force
Write-Host "End Script"

Method2

$ExportInfo = @()


foreach ($User in $arrayb)
{
    $UserInfo = New-Object System.Object

    #set title for this user
    $ExportInfo += "Details for $User"

    #Get Mailbox Info
    $ExportInfo += Get-Mailbox $User

    #find out their group memebership
    $ExportInfo += Get-QADMemberOf $User 

    #get profile details
    $ExportInfo += Get-QADUser $User| select HomeDirectory,HomeDrive

    #add space between users
    $ExportInfo += ""
    $ExportInfo += ""   


}

$ExportInfo | Export-Csv -Path $OutputPath ;

EDITED:

Thanks to suggestion from Stej I now have the code below... Still doesn't work correctly. I have added a check to confirm the user exists in AD as well. First problem is with getting a users AD group membership - if I put a break point in and look at the value of a a specific users AD membership, the value in teh varialbe is error "Cannot process argument because the value of argument "obj" is null. Change the value of argument "obj" to a non-null value." No idea what's causing this, so I've just ignored it for now and commented out those lines for AD membership to concentrate on gettng the rest working.

With the lines commented out, the script runs fine and again, with a breakpoint at the bottom I can look at the value of $ExportInfo and they all look fine, ie they have all been saved into the variable correctly. However, it won't output. As you can see, I put a command to get $ExportInfo written to screen but that doesn't show anything. When it attempts to execute the Export line (ie "Export-Csv $ExportInfo -Path $OutputPath") it errors with: "Cannot convert 'System.Object[]' to the type 'System.Char' required by parameter 'Delimiter'. Specified method is not supported. At :line:80 char:10 + Export-Csv <<<< $ExportInfo -Path $OutputPath"

I chnaged the line to "$ExportInfo | Export-Csv -Path $OutputPath" and it now exports to CSV... No idea why??? Two issues though... as noted above, AD groups aren't working and the field Email Addresses (which should return something like { SMTP:[email protected], smtp: [email protected], smtp: [email protected] and SIP:[email protected]}) just shows up in the CSV as "Microsoft.Exchange.Data.ProxyAddressCollection". Again, checking in $ExportInfo, the addresses are there....

Thanks

$ExportInfo = @()

foreach ($User in $arrayb)
{
    $CheckUser = Get-QADUser -Name $User

    if (!$CheckUser)
    {
        $CountUser++
        Write-Warning "############################################"
        Write-Warning "$user not found in AD"
        Write-Warning "############################################"
    }
    else
    {
        $CountUser++

        $UserInfo = New-Object System.Object



        #find out their group memebership
        Write-Host "AD group membership for $User"
        #$Temp = Get-QADMemberOf $User 
        #$UserInfo | Add-Member NoteProperty -Name "AD Group Membership" -Value $Temp.Name

        #set title for this user
        #Write-host "Details for $User"

        #Get Mailbox Info
        Write-Host "Mailbox info for $User"
        $Temp = Get-Mailbox $User #| select ServerName, Database, EmailAddresses, PrimarySmtpAddress, WindowsEmailAddress
        $UserInfo | Add-Member NoteProperty -Name "ServerName" -Value $Temp.ServerName
        $UserInfo | Add-Member NoteProperty -Name "Database" -Value $Temp.Database
        $UserInfo | Add-Member NoteProperty -Name "Email Addresses" -Value $Temp.EmailAddresses
        $UserInfo | Add-Member NoteProperty -Name "Primary SMTP" -Value $Temp.PrimarySmtpAddress
        $UserInfo | Add-Member NoteProperty -Name "Windows Email Address" -Value $Temp.WindowsEmailAddress
        #$ReturnedObj | Add-Member NoteProperty -Name





        #get profile details
        Write-Host "Home drive info for $User"
        $Temp = Get-QADUser $User #| select HomeDirectory,HomeDrive
        $UserInfo | Add-Member NoteProperty -Name "Home Directory Location" -Value $Temp.HomeDirectory
        $UserInfo | Add-Member NoteProperty -Name "Home Drive Mapped To" -Value $Temp.HomeDrive

        #add space between users
        Write-Host ""
        Write-Host "******************************************************"

        $ExportInfo += $UserInfo
    }#end else

}
Write-Host "blah"
Write-Host $ExportInfo
Export-Csv $ExportInfo -Path - $OutputPath

Write-Host "Number of Users processed: $CountUser"

Answer 1

Is there any error message? Is there something in $error[0]? What does it mean that it is not working?

---

Edited: In your second method you create $UserInfo object, but you don't use it. I guess it was intended to be added to $ExportInfo. Instead of this you add bare strings to $ExportInfo and that's why you gave such a strange content of your csv file.

How it should be done correctly:

$UserInfo = New-Object System.Object

$ReturnedObj | Add-Member NoteProperty -Name "User Name" -Value $user.Name
$ReturnedObj | Add-Member NoteProperty -Name "User otherValue" -Value $user.othValue

#Get Mailbox Info
$mailBox = Get-Mailbox $User
$ReturnedObj | Add-Member NoteProperty -Name "User Mail box size" -Value $mailBox.Size
$ReturnedObj | Add-Member NoteProperty -Name "User Mail box -count of messages" -Value $user.countOfMessages

... # and so on
$ExportInfo  += $ReturnedObj

Note that the objects that you want to export to csv have to have properties of type strings, integers, bools etc. Not composed objects like $User or the ones returned by Get-Mailbox $User. The values in csv have to be primitive. That's the reason.

That applies for your first example where you add NoteProperty with value $AdMemberOf. That is object itself that can not be exported to csv. You have to create bunch of properties for every interesting property of $AdMemberOf.

Answer 2

Turned out I needed to use some foreach loops to get the info I needed.... So:

foreach($user in $array)
{
$temp = Get-QADUser -Name $User

if (!$temp)
{
    $log += "????????????????" + "`n"
    $log += "$user not found in AD" + "`n"
    $log += "????????????????" + "`n"
}
else
{
    #find out their group memebership
    $log += "AD group membership for $User" + "`n"
    $temp = Get-QADMemberOf $User 

    foreach ($drive in $temp)
    {
        $temp2 = $drive
        $temp2 = $temp2.Substring(6)
        $log += "`t" + $drive + "`n"
}#end foreach for drive loop

$temp = Get-Mailbox $User

if ($temp.RecipientType -like "UserMailbox")
{
    $log += "Mailbox info for $User" + "`n"
    #$log += Get-Mailbox $User | select ServerName, Database, EmailAddresses, PrimarySmtpAddress, WindowsEmailAddress
    $log += "Email Server: " + $temp.ServerName + "`n"
    $log += "Email Database: " + $temp.Database + "`n"
    $log += "Primary SMTP: " + $temp.PrimarySmtpAddress + "`n"
    $log += "Windows Email Address: "+ $temp.WindowsEmailAddress + "`n"
    #$log += "`n"

    foreach ($e in $temp.EmailAddresses)
    {
        $log += "`t" + "Email Addresses: " + $e + "`n"
    }

}
else
{
    $log += "########" + "`n"
    $log += "$User is not a MailboxUser, IE no Exchange Mailbox" + "`n"
    $log += "########" + "`n"
}


$log += "Home drive info for $User" + "`n"
$temp = Get-QADUser $User| select HomeDirectory,HomeDrive
$log += "Home Directory: " + $temp.HomeDirectory + "`n"
$log += "Home Drive Letter: " + $temp.HomeDrive + "`n"

$tempvar = [string] $temp.HomeDirectory

if ($tempvar -eq "")
{
    $noHomedirectory += $User + "`n"
    $countNoHOmeDirectory ++
}
}#end of the main if/else to determine if the AD account exists

$OutputPath = "C:\SomeFolder\"+"User_Report_"+([datetime]::Now).tostring("yyyyMMddhhmmss")+".txt"

$log | Out-File -FilePath $OutputPath

I then dump all the specifc logs (eg $noHomeDirectory etc) in the body of an email, attach the complete log as exported above and send it to myself.

Thanks for everyone's suggestions above and sorry for the delay in posting back the answer...