Output not populating to csv correctly, but shows up as write-host

I have actually 2 questions in one. First let me tell you what I am looking for.

I need to pull a list of all accepted domains in our system. (done), then I want to pull from each domain "mydomain.com", look for all email addresses that are over 2GB.(done) Set the result to /1000, so that my result will show example: 2.156 in the results. so the result will come up as mydomain.com 2.156 2.453 4.677 (one for each mailbox over 2 GB). Then list each of them when I use the following to the host (my PS screen).

$Domain=get-content c:\domains.csv
$Domains=$Domain -replace """"

#Goes through each domain in the $Domains list
foreach ($k in $Domains)
{
#Stores the overage mailbox values (in MB) in C:\temp.csv and re-imports to $Overage2 to remove non-integer values
$Overage=Get-Mailbox -Server 'Mail-ex01' -resultsize unlimited | Where {$_.PrimarySmtpAddress -like "*$k"} | Get-MailboxStatistics | Where {$_.TotalItemSize -gt 2000MB} | Select-Object @{label="Total Size (MB)";expression={$_.TotalItemSize.Value.ToMB()/1000}} | Export-Csv  c:\temp.csv -notype -Encoding ASCII
$Overage1=get-content c:\temp.csv
$Overage2=$Overage1 -replace """"
Write-Host $k "Overage values" $Overage2

However, I need this to to be placed in a CSV, in 2 different columns, 1 for "$k Overage Values" and the second column for the $overages2. the value. Each one will need to be in its own row, so that we can total each accepted domain GB values. UNless there is a better way.

Secondly,  I would like to round up if over 2 at all go to 3 example: if the result was 2.134, the will be a 3, if the result was 2.999, the result will also be 3, etc. I have tried to add the [Math]::round(), but cannot seem to get that to work in the main script at all, much less to populate a csv file.

Thank you for your help!!!

January 29th, 2015 11:36pm

1. You don't need to use get-content and then replace in strings if you use import-csv.

2. Do not use write-host as it cannot be redirected to a file.

3. To create an output CSV file, use a construct like this:

Free Windows Admin Tool Kit Click here and download it now
January 29th, 2015 11:56pm

Start like this so you can see how it works:

foreach ($k in $Domains){
    Get-Mailbox -Server 'Mail-ex01' -resultsize unlimited | 
        Where {$_.PrimarySmtpAddress -like "*$k"} | 
        Get-MailboxStatistics | Where {$_.TotalItemSize -gt 2000MB} | 
        Select-Object @{N='Total';E={$_.TotalItemSize.Value.ToMB()/1000}} |
        ForEach-Object{
            Write-Host '{0} Overage values: {1:D2}' -f $k,$Overage.Total
        }
 }

January 29th, 2015 11:59pm

Now look at how this one works:

Import-Csv c:\domains.csv -Header Domain|
    ForEach-Object{
        Write-Host $_ -ForegroundColor green
        Get-Mailbox -Server 'Mail-ex01' -resultsize unlimited | 
            Where {$_.PrimarySmtpAddress -like "*$k"} | 
            Get-MailboxStatistics | Where {$_.TotalItemSize -gt 2Gb} | 
            Select-Object @{N='Domain';E={$k}},
                @{N='Overage';E={$_.TotalItemSize.Value.ToMB()/1000}}
    } | Export-Csv Totals.csv -notype

Free Windows Admin Tool Kit Click here and download it now
January 30th, 2015 12:07am

Here is one that is tested:

# choose output
$server='Mail-ex01' 
$domains='c:\domains.csv'
$totals='c:\totals.csv'
$cutoff=2Gb
$selectProps=@(
    @{N='Domain';E={$maildomain}},
    'DisplayName',
    @{N='Overage';E={$_.TotalItemSize.Value.ToGb()}}
)

Import-Csv $domains |
    ForEach-Object{
        $maildomain=$_.Domain
        Write-Host $maildomain -ForegroundColor green
        Get-Mailbox -Server $server -resultsize unlimited | 
            Where {$_.PrimarySmtpAddress -match $maildomain} | 
            Get-MailboxStatistics | 
            Where{$_.TotalItemSize -gt $cutoff} | 
            Select-Object $selectProps
    } | Export-Csv Totals.csv -notype

You need to be sure your CSV file has a header with domain on the column.

This output can be easilly grouped and summed.

January 30th, 2015 12:43am

Thank you, I was not able to run the first script as I do not have a value set to the "$Overage" what should this be set to if I am replacing the Get-mailbox with your ForEach. Can you assist, again.
Free Windows Admin Tool Kit Click here and download it now
January 30th, 2015 1:20am

Okay, just saw the last one. I am looking at this one too.

The second script works, but the results are showing like this. ONly the second item in the list shows up, and repeats as shown below. 

"Domain","Overage"
"2nd_in_list.com","2.537"
"2nd_in_list.com","2.666"
"2nd_in_list.com","2.537"
"2nd_in_list.com","2.666"
"2nd_in_list.com","2.537"
"2nd_in_list.com","2.666"

The first one was skipped and the 3rd one was never addressed.

You guys are amazing!!

January 30th, 2015 1:25am

Okay, this is awesome... No really.  Just want to know, where does this round up to next GB? What is the command. It is rounding up, correct?
Free Windows Admin Tool Kit Click here and download it now
January 30th, 2015 1:32am

Nothing is rounding up.  You you can change the precision by not convertime the numer.  Get it as Bytes then format and convert in the output stream.

$selectProps=@(
   
@{N='Domain';E={$maildomain}},
   
'DisplayName',
   
@{N='Overage';E={$_.TotalItemSize.Value.ToBytes()}}
)

January 30th, 2015 1:54am

Okay, just saw the last one. I am looking at this one too.

The second script works, but the results are showing like this. ONly the second item in the list shows up, and repeats as shown below. 

"Domain","Overage"
"2nd_in_list.com","2.537"
"2nd_in_list.com","2.666"
"2nd_in_list.com","2.537"
"2nd_in_list.com","2.666"
"2nd_in_list.com","2.537"
"2nd_in_list.com","2.666"

The first one was skipped and the 3rd one was never addressed.

You guys are ama

Free Windows Admin Tool Kit Click here and download it now
January 30th, 2015 1:57am

You can easily insert Bills suggest:

$selectProps=@(
   
@{N='Domain';E={$maildomain}},
   
'DisplayName',
   
@{N='Overage';E={[math]::ceiling($_.TotalItemSize.Value.ToGb())}}
)


January 30th, 2015 1:59am

You guys are awesome. I changed the script to account for MB and then divide as well and that did it.

$selectProps=@(
    @{N='Domain';E={$maildomain}},
    'DisplayName',
    @{N='Overage';E={[math]::ceiling($_.TotalItemSize.Value.ToMB()/1000)}}
)

Thanks again

Paul

Free Windows Admin Tool Kit Click here and download it now
January 30th, 2015 2:21am

But that doesn't give you Gb.  Gb is $num/1Gb  Why use Mb.  Gb is Mb/1024 0r Mb/1Kb  Using 100 give you mathemtically incorrect results.
January 30th, 2015 2:24am

This will show the mamgnitude of error introduced by dividing by 1000

PS C:\scripts> 1Gb/1000
1073741.824
PS C:\scripts> 1Gb/1Kb
1048576

# next line measures the error difference
PS C:\scripts> (1Gb/1000)-(1Gb/1Kb)
25165.824  <<===== over 25Kb 0f difference.

This is what you want:

@{N='Overage';E={[math]::ceiling($_.TotalItemSize.Value.ToGb())}}

Free Windows Admin Tool Kit Click here and download it now
January 30th, 2015 2:37am

Here is the readout:
PS C:\scripts> 1Kb
1024
PS C:\scripts> 1Mb
1048576
PS C:\scripts> 1Gb
1073741824
PS C:\scripts> 1Tb
1099511627776

This iss because computer use binary and a Kb or any other measue is a technical term.

8Bits = 256 (0-256)  0xFF  [byte]
10bits=1024(0-1023) 0x400 (binary bits 10011111111)
etc

A Mb of memory is 1024 X 1024
A Gb of memory is 1024 X 1024 X 1024
etc.

If you use 1000 you will be overestimating by"

PS C:\scripts> '{0:P}' -f (24/1024)
2.34 %

See how easy it is to throw awat gigabytes:

PS C:\scripts> 1Tb * (24/1024)
25769803776
PS C:\scripts> (1Tb * (24/1024))/1gb
24

January 30th, 2015 2:47am

Ah, okay... Thank you. You are such a blessing donating your intel. :-)

I have another question about the sorting of this output. Is there a way to have each of the domains results on its own sheet in excel. This is probably too much to ask for, but accounting would love it.

Paul

Free Windows Admin Tool Kit Click here and download it now
January 30th, 2015 6:22pm

Sorry but we don't work for accounting.  We work for IT.  Tell accounting to post their new question in a new thread.

January 30th, 2015 8:16pm

I am referring to my job as IT. I have to give them the results and wanted to make my job easier, be segregating the CSV output to separate "Sheets" for each domain. They would be happier, because I would be able to automate it from my end and they would not have to wait on me to get it to them.

Thank you for everything anyways!!

Paul

Free Windows Admin Tool Kit Click here and download it now
January 30th, 2015 10:53pm

Like I said.  It is a new question.
January 30th, 2015 11:06pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics