Format the script to export in csv or Excel.

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

  • Author
    Posts
  • #18552
    Profile photo of ahmed ali
    ahmed ali
    Participant

    Hi Team,

    As per my requirement,I managed to write a script. But the output is stored in a single cell in the output for the user. Can you help by advising me to finetune the script and export to a excel/csv with formatted output.

    Sno 521 Exchange version mailbox size No. of Items in MB Dumpster size No. of Items in Dumpster LH enabled Current Permissions

    The value of exchange version is in numbers so I have used if condition to store the Exchange value. The mailbox size and Dumpster size results are in bytes. I tried to divide them with /1MB but not getting the desired results. Also is it possible to add few color in the sheet if the Litigationhold enable is true = Green and if it is false then RED.

    $outputfile="c:\temp\file.csv"
    $users = GC c:\temp\alias.txt
    foreach ($user in $users)
    {
    $mailbox = ""
    $mailbox += (Get-mailbox -Identity $user).alias + ","
    $mailbox += (Get-mailbox -Identity $user).LitigationHoldEnabled
    $mailbox += (Get-mailbox -Identity $user).PrimarySMTPAddress
    $mailbox += (Get-mailbox -Identity $user).Displayname
    $mailbox += (Get-MailboxStatistics -Identity $user).ItemCount
    $mailbox += (Get-MailboxStatistics -Identity $user).TotalItemsize
    $mailbox += (Get-MailboxStatistics -Identity $user).DeletedItemCount
    $mailbox += (Get-MailboxStatistics -Identity $user).TotalDeletedItemsize
    $permission = (Get-MailboxPermission -Identity $user).user

    $ExchVersion = (Get-mailbox $user).Exchangeversion
    if($ExchVersion -like "0.20 (15.0.0.0)")
    {
    $version = "Exchange2013"
    }
    else
    {
    $version = "Exchange2010"
    }

    $mailbox = $mailbox.TrimEnd(",")
    $mailbox +";"+$version + ";" +$permission | out-file $outputfile -Append
    }

  • #18562
    Profile photo of Joshua Taylor
    Joshua Taylor
    Participant

    The reason you're seeing everything dumped into a single cell is because with the += you're effectively concatenating each output into a single string. A better way to handle this would be to create an object and add each of your queries as a property of that object.

    $outputfile="c:\temp\file.csv"
    $users = GC "c:\temp\alias.txt"
    foreach ($user in $users)
    {
        $Mailbox = Get-Mailbox $user
        $MailboxStat = Get-MailboxStatistics $user
        $MailboxPermission = Get-MailboxPermission $User
        ForEach ($Account in $MailboxPermission)
            {
                $Accounts += $Account.User + " " | ? { $_ -notmatch "S-1-5-21" }
            }
        $ExchangeVersion = $Mailbox.ExchangeVersion
        if($ExchangeVersion -like "0.20 (15.0.0.0)")
            {
                $version = "Exchange2013"
            }
                else
            {
                $version = "Exchange2010"
            }
    
        $obj = New-Object PSObject
        $obj | Add-Member -MemberType NoteProperty -Name Alias -Value $Mailbox.Alias
        $obj | Add-Member -MemberType NoteProperty -Name LitigationHoldEnabled -Value $Mailbox.LitigationHoldEnabled
        $obj | Add-Member -MemberType NoteProperty -Name PrimarySMTPAddress -Value $Mailbox.PrimarySMTPAddress
        $obj | Add-Member -MemberType NoteProperty -Name Displayname -Value $Mailbox.Displayname
        $obj | Add-Member -MemberType NoteProperty -Name ItemCount -Value $MailboxStat.ItemCount
        $obj | Add-Member -MemberType NoteProperty -Name TotalItemsize -Value $MailboxStat.TotalItemsize
        $obj | Add-Member -MemberType NoteProperty -Name DeletedItemCount -Value $MailboxStat.DeletedItemCount
        $obj | Add-Member -MemberType NoteProperty -Name TotalDeletedItemsize -Value $MailboxStat.TotalDeletedItemsize
        $obj | Add-Member -MemberType NoteProperty -Name MailboxPermission -Value $Accounts
    
        $obj | Export-CSV "c:\temp\file.csv" -Append -NoTypeInformation
    }
    

    As for the mailbox and dumpster sizes, the values returned by TotalItemSize and TotalDeletedItemSize are strings (I believe), not numbers, and therefor cannot be used in calculation. I'm sure if there is a way to convert a string to a number someone else here can answer that. I'm not sure how to color cells/rows in Excel either, but I imagine that wouldn't be too difficult.

    Hope that helps!

    – Joshua

  • #18613
    Profile photo of ahmed ali
    ahmed ali
    Participant

    Hi Joshua,

    Perfect script. Thank you so much.

    When we run the script for the second time the previous output is being stored. Is the any way to clear the output file before running the script.

    Thank you again.

    Ahmed.

  • #18680
    Profile photo of Joshua Taylor
    Joshua Taylor
    Participant

    What I typically do is check for the file and delete it if it exists, like this.

    If (Test-Path c:\filepath)
    {
        Remove-Item c:\filepath
    }
    

    Append that to the beginning of your script. That way the file is created new everytime the script is run.

    – Joshua

You must be logged in to reply to this topic.