Creating Manual Reports for Exchange (Per Customer Basis)
As an example, I've used Phil Hoffman Travel to run the report.
This gives your mailbox sizes of all users under the company
get-mailbox -OrganizationalUnit "bluefire.bfms.local/Customers/Adam Internet(AI1)/Phil Hoffmann Travel(190102)" | Get-MailboxStatistics | select DisplayName,@{name="TotalItemSize(MB)";expression={$.totalitemsize.value.ToMB()}} | Export-Csv c:\tempreportpht.csv_ -notype
This tells you how much they have used of their Mailbox
get-mailbox -OrganizationalUnit "bluefire.bfms.local/Customers/Adam Internet(AI1)/Phil Hoffmann Travel(190102)" | select DisplayName,prohibitsendquota | Export-Csv c:\tempreportpht2.csv –notype
aaaaaaaaa - The text which is highlighted in Yellow, is the OU as to where the company resides. Go into AD and expand the tree down until you see the customer name. Right-click on the customer and select the Object tab. The Canonical name of object is the text that you want to copy and paste in the script where it's highlighted.
Aaaaaaaaa - the text highlighted in Green, is the name you'll be giving the report that extracts the data.
(1GB = 1024MB) To calculate GB to MB, 4GB x 1024 = 5120MB
Calculate the rest for the 10GB mailboxes and the 20GB Mailboxes and use the above screenshot to find and replace all.
/ 
X 100 = 54.5% used.
To automatically have the formula reciprocate over the mailboxes, select the cell where the first number is and hover over the tiny box in the right hand corner and drag all the way down until you reach the last mailbox (See image above where the red circle is).
Your column should now look like this
As you can see, once you've dragged the little box it auto completes the other cells. What we need to do now is set the decimal place to .1.
Select the category 'Number' and set the Decimal place to 1. Click Ok.
- Log onto an Exchange server (bf-nrd-exd2-11 or -12).
- Open up Exchange Power Shell as you'll be running the below PowerShell scripts to reveal the following:
As an example, I've used Phil Hoffman Travel to run the report.
This gives your mailbox sizes of all users under the company
get-mailbox -OrganizationalUnit "bluefire.bfms.local/Customers/Adam Internet(AI1)/Phil Hoffmann Travel(190102)" | Get-MailboxStatistics | select DisplayName,@{name="TotalItemSize(MB)";expression={$.totalitemsize.value.ToMB()}} | Export-Csv c:\tempreportpht.csv_ -notype
This tells you how much they have used of their Mailbox
get-mailbox -OrganizationalUnit "bluefire.bfms.local/Customers/Adam Internet(AI1)/Phil Hoffmann Travel(190102)" | select DisplayName,prohibitsendquota | Export-Csv c:\tempreportpht2.csv –notype
aaaaaaaaa - The text which is highlighted in Yellow, is the OU as to where the company resides. Go into AD and expand the tree down until you see the customer name. Right-click on the customer and select the Object tab. The Canonical name of object is the text that you want to copy and paste in the script where it's highlighted.
Aaaaaaaaa - the text highlighted in Green, is the name you'll be giving the report that extracts the data.
- Open up the CSV files that the PowerShell script created. Take one of the columns out of one of the Excel spreadsheet and paste it into the other as calculations will be done with both sets of data. Make sure that the columns are aligned i.e. the names are matching in each column. Essentially your Excel document should look similar to this.
- As you can see the in the "ProhibitSendQuota" column, the data that is spat out, shows 5GB mailboxes as 4.883GB, 10GB as 9.766GB and so on. These numbers need to be rounded off to MB rather than GB so that we're able to calculate the percentage at the end. Click on CTRL+F to bring up the find window and select the replace tab. Follow each diagram below.
(1GB = 1024MB) To calculate GB to MB, 4GB x 1024 = 5120MB
Calculate the rest for the 10GB mailboxes and the 20GB Mailboxes and use the above screenshot to find and replace all.
- You should now have a new column which looks like this which represents the sizes in MB
- Now that we have the Mailbox Size in MB we can move on to finding out how much percentage of their mailbox they have used. Create a new Column with the heading 'Percentage of Space Used'. What you need to do is take the number out of the TotalItemSize (MB) column and divide it by the number in the Mailbox Size (MB) then times it by 100 to get the percentage used of the mailbox.
/ X 100 = 54.5% used.
- In the column 'Percentage of Space Used', select the first column underneath the header and create the formula above. As you can see below, the 'TotalItemSize(MB)' header is under column 'H' and the 'MailboxSize (MB)' header is in column 'D'. So the formula for calculation in your 'Percentage of Space Used' column will be '=H2/D2*100'. This will give you the percentage of space that has been used within the mailbox.
- You should now have a number in the first column of where you put the formula
To automatically have the formula reciprocate over the mailboxes, select the cell where the first number is and hover over the tiny box in the right hand corner and drag all the way down until you reach the last mailbox (See image above where the red circle is).
Your column should now look like this
As you can see, once you've dragged the little box it auto completes the other cells. What we need to do now is set the decimal place to .1.
- Select the whole column from the very top, right click and select "Format Cells:" A screen like below should appear
Select the category 'Number' and set the Decimal place to 1. Click Ok.
- Your Column should now look like this
- Once the decimal places have been set, you've completed creating the report. The last thing to do is to make it look presentable by selecting the all columns (with data in them), Select Insert from the top and selecting table and your document should look similar to this.
