locked
All in One report Mailbox RRS feed

  • Question

  • Hello Team

    in mt test lab, i would like to generate the report like below in single CSV file, it is running ex2007

    username, server name, Database Name, ItemCOunt, Mailboxasize, Database size, Database free size

    How do i get this

    example

    UN   SN      DBNamae  ItemCount MailboxSize Database size Free Size

    usr1 ex01, ex01\db01,        32,    400MB,        70GB,                20GB


    ramakrishnan
    Sunday, October 16, 2011 10:00 AM

Answers

  • Hi,

    Write the script like below:

    For(;;){

    Get-MailboxDatabase | Select Server, StorageGroupName, Name, @{Name="Size (GB)";Expression={$objitem = (Get-MailboxDatabase $_.Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1048576KB; [math]::round($size, 2)}}, @{Name="Size (MB)";Expression={$objitem = (Get-MailboxDatabase $_.Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1024KB; [math]::round($size, 2)}}, @{Name="No. Of Mbx";expression={(Get-Mailbox -Database $_.Identity | Measure-Object).Count}} | Export-CSV C:\ServerStat-2.csv

    //Send email with attachment

    send-mailmessage -from "User01 <user01@example.com>" -to "User02 <user02@example.com>" -subject "Sending the Attachment" -body "Forgot to send the attachment. Sending now." -Attachments "ServerStat-2.csv" -priority High -smtpServer smtp.domain.com

    //Sleep time (seconds)

    Start-sleep -s 3600

    }

    http://technet.microsoft.com/en-us/library/ee177002.aspx

    http://technet.microsoft.com/en-us/library/dd347693.aspx


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Edited by Jason LJS Tuesday, October 18, 2011 5:15 AM
    • Marked as answer by Jason LJS Monday, October 24, 2011 2:13 AM
    Tuesday, October 18, 2011 5:14 AM
  • This command does applies to "Windows PowerShell 2.0"
    Jasjit Singh Dhindsa | ITIL v3 | IASA Foundation Certified | MCITP:Exchange 2010 | Exchange 2007 | MCTS:OCS 2007 | Exchange 2010 | Exchange 2007 | MCSA:Messaging | Security | MCSE:Messaging | Security
    • Marked as answer by Jason LJS Monday, October 24, 2011 2:13 AM
    Wednesday, October 19, 2011 12:53 PM

All replies

  • Run the below command
    Get-MailboxStatistics -ResultSize Unlimited | ft DisplayName,ServerName,DatabaseName,ItemCount,@{label="TotalItemSize(MB)";expression={$_.TotalItemSize.Value.ToMB()}},@{Name="Size (GB)";Expression={$objitem = (Get-MailboxDatabase $_.Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1048576KB; [math]::round($size, 2)}} | Export-CSV C:\Mailbox.csv
    
    What do you mean by free space in Database ?
     Do you want to export the free space reported by event ID 1221 ? or is the free space of disk
    On this link there is some usefull information about database size http://exchangeshare.wordpress.com/2009/07/27/exchange-2007-database-statistics-in-powershell/ 

    Jasjit Singh Dhindsa | ITIL v3 | IASA Foundation Certified | MCITP:Exchange 2010 | Exchange 2007 | MCTS:OCS 2007 | Exchange 2010 | Exchange 2007 | MCSA:Messaging | Security | MCSE:Messaging | Security
    Sunday, October 16, 2011 11:11 AM
  •  hello jasjit

     

    here is the error am getting, can u help me

     

    Get-MailboxStatistics : A parameter cannot be found that matches parameter name
     'ResultSize'.
    At line:1 char:34
    + Get-MailboxStatistics -ResultSize  <<<< Unlimited | ft DisplayName,ServerName
    ,DatabaseName,ItemCount,@{label="TotalItemSize(MB)";expression={$_.TotalItemSiz
    e.Value.ToMB()}},@{Name="Size (GB)";Expression={$objitem = (Get-MailboxDatabase
     $_.Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.D
    riveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2);
     $size = ((Get-ChildItem $path).length)/1048576KB; [math]::round($size, 2)}} |
    Export-CSV C:\Mailbox.csv

     

    i tried to save the above command in TXT file and save as .PS1 file and ran it , again same above error

     


    ramakrishnan
    • Edited by rush2ramki Sunday, October 16, 2011 11:31 AM
    Sunday, October 16, 2011 11:30 AM
  • If you have less then 1000 users you can remove this "-ResultSize  Unlimited" part from the command and instead you can narow it to particular server by doing -Server "Servername"
    Jasjit Singh Dhindsa | ITIL v3 | IASA Foundation Certified | MCITP:Exchange 2010 | Exchange 2007 | MCTS:OCS 2007 | Exchange 2010 | Exchange 2007 | MCSA:Messaging | Security | MCSE:Messaging | Security
    Sunday, October 16, 2011 11:38 AM
  • On Sun, 16 Oct 2011 11:30:46 +0000, rush2ramki wrote:
     
    >here is the error am getting, can u help me
    >
    >
    >
    >Get-MailboxStatistics : A parameter cannot be found that matches parameter name 'ResultSize'. At line:1 char:34 + Get-MailboxStatistics -ResultSize <<<< Unlimited | ft DisplayName,ServerName ,DatabaseName,ItemCount,@{label="TotalItemSize(MB)";expression={$_.TotalItemSiz e.Value.ToMB()}},@{Name="Size (GB)";Expression={$objitem = (Get-MailboxDatabase $_.Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.D riveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1048576KB; [math]::round($size, 2)}} | Export-CSV C:\Mailbox.csv
    >
    >
    >
    >i tried to save the above command in TXT file and save as .PS1 file and ran it , again same above error
     
    The get-mailboxstatistics cmdlet doesn't have a "-resultsize"
    parameter. Remove it.
     
    ---
    Rich Matheisen
    MCSE+I, Exchange MVP
     

    --- Rich Matheisen MCSE+I, Exchange MVP
    Sunday, October 16, 2011 3:04 PM
  • Hi Jasit

    Another Error Below

    [PS] C:\>Get-MailboxStatistics -server "AUEXMBXCCR" | ft DisplayName,ServerName,
    DatabaseName,ItemCount,@{label="TotalItemSize(MB)";expression={$_.TotalItemSize.
    Value.ToMB()}},@{Name="Size (GB)";Expression={$objitem = (Get-MailboxDatabase $_
    .Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.Drive
    Name.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2); $siz
    e = ((Get-ChildItem $path).length)/1048576KB; [math]::round($size, 2)}} | Export
    -CSV C:\Mailbox.csv
    Format-Table : Illegal key Name
    At line:1 char:48
    + Get-MailboxStatistics -server "AUEXMBXCCR" | ft  <<<< DisplayName,ServerName,
    DatabaseName,ItemCount,@{label="TotalItemSize(MB)";expression={$_.TotalItemSize
    .Value.ToMB()}},@{Name="Size (GB)";Expression={$objitem = (Get-MailboxDatabase
    $_.Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.Dr
    iveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2);
    $size = ((Get-ChildItem $path).length)/1048576KB; [math]::round($size, 2)}} | E
    xport-CSV C:\Mailbox.csv

     

    BUT

     

    however i had complted the 50% with the below command

    get-mailboxstatistics -server "Servername" | FT Displayname, ServerName, DatabaseName, ItemCount, Totalitemsize

     

    But and am unbale to combine the command for dabatabase size and Free size for the current database

    also, if i have more than 1000 users across the different servers and database, should i need to use -resultsize Unlimited scwitch

     

    could you please tell me what coulbe be error while issuing the above command "Illegal key name"

     

     


    ramakrishnan
    Sunday, October 16, 2011 3:53 PM
  • I would suggest breaking your task into two iterations. First would be reated to getting the user infomration by running the below command

    Get-Mailbox -resultsize unlimited | get-mailboxstatistics | FT Displayname, ServerName, DatabaseName, ItemCount, Totalitemsize
    
    

    Second would be get the database size by running the below command

    Get-MailboxDatabase | Select Server, StorageGroupName, Name, @{Name="Size (GB)";Expression={$objitem = (Get-MailboxDatabase $_.Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1048576KB; [math]::round($size, 2)}}, @{Name="Size (MB)";Expression={$objitem = (Get-MailboxDatabase $_.Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1024KB; [math]::round($size, 2)}}, @{Name="No. Of Mbx";expression={(Get-Mailbox -Database $_.Identity | Measure-Object).Count}} | Export-CSV C:\ServerStat-2.csv
    

    Copied from here http://exchangeshare.wordpress.com/2009/07/27/exchange-2007-database-statistics-in-powershell/

     

     

     



    Jasjit Singh Dhindsa | ITIL v3 | IASA Foundation Certified | MCITP:Exchange 2010 | Exchange 2007 | MCTS:OCS 2007 | Exchange 2010 | Exchange 2007 | MCSA:Messaging | Security | MCSE:Messaging | Security
    Sunday, October 16, 2011 4:37 PM
  • Hi Jasjit

     

    Excellent , It's Working with two different commads,

    However can we run these above two commands in schedule files .PS1 and and result should be send it to be email with these two attachement?

     


    ramakrishnan
    Monday, October 17, 2011 1:13 PM
  • Hi,

    Write the script like below:

    For(;;){

    Get-MailboxDatabase | Select Server, StorageGroupName, Name, @{Name="Size (GB)";Expression={$objitem = (Get-MailboxDatabase $_.Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1048576KB; [math]::round($size, 2)}}, @{Name="Size (MB)";Expression={$objitem = (Get-MailboxDatabase $_.Identity); $path = "`\`\" + $objitem.server + "`\" + $objItem.EdbFilePath.DriveName.Remove(1).ToString() + "$"+ $objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem $path).length)/1024KB; [math]::round($size, 2)}}, @{Name="No. Of Mbx";expression={(Get-Mailbox -Database $_.Identity | Measure-Object).Count}} | Export-CSV C:\ServerStat-2.csv

    //Send email with attachment

    send-mailmessage -from "User01 <user01@example.com>" -to "User02 <user02@example.com>" -subject "Sending the Attachment" -body "Forgot to send the attachment. Sending now." -Attachments "ServerStat-2.csv" -priority High -smtpServer smtp.domain.com

    //Sleep time (seconds)

    Start-sleep -s 3600

    }

    http://technet.microsoft.com/en-us/library/ee177002.aspx

    http://technet.microsoft.com/en-us/library/dd347693.aspx


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Edited by Jason LJS Tuesday, October 18, 2011 5:15 AM
    • Marked as answer by Jason LJS Monday, October 24, 2011 2:13 AM
    Tuesday, October 18, 2011 5:14 AM
  • Hi jason

    normally, in my Powershell, Send-Message is not at all working.

    Get-MailboxDatabase | Select Server, StorageGroupName, Name, @{Name="Size (GB)";Expression={$objitem= (Get-MailboxDatabase$_.Identity); $path = "`\`\"+ $objitem.server+ "`\" + $objItem.EdbFilePath.DriveName.Remove(1).ToString()+ "$"+$objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem$path).length)/1048576KB;[math]::round($size, 2)}}, @{Name="Size (MB)";Expression={$objitem= (Get-MailboxDatabase$_.Identity); $path = "`\`\"+ $objitem.server+ "`\" + $objItem.EdbFilePath.DriveName.Remove(1).ToString()+ "$"+$objItem.EdbFilePath.PathName.Remove(0,2); $size = ((Get-ChildItem$path).length)/1024KB;[math]::round($size, 2)}}, @{Name="No. Of Mbx";expression={(Get-Mailbox-Database $_.Identity | Measure-Object).Count}} | Export-CSV C:\ServerStat-2.csv

    send-mailmessage -from "User01 <user01@example.com>" -to "User02 <user02@example.com>" -subject "Sending the Attachment" -body "Forgot to send the attachment. Sending now." -Attachments "ServerStat-2.csv" -priority High -smtpServer smtp.domain.com

    i mades in a Notepad and save as PS1 file

    When i ran like. .\filename.ps1

    it is throwing an error "The Term 'send-message' is not recoganized as cmdlet , function,operale program, or Script file. Verify the the term and try again

    Not sure Why


    ramakrishnan
    Tuesday, October 18, 2011 10:47 AM
  • Hi,

    it is throwing an error "The Term 'send-message' is not recoganized as cmdlet , function,operale program, or Script file. Verify the the term and try again

    Try just running Send-MailMessage command through EMS without any parameter. Make sure there is no typo.

    If it works there is no difference running this command in a script or a simple command.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Edited by Jason LJS Wednesday, October 19, 2011 1:02 AM
    Wednesday, October 19, 2011 1:00 AM
  • Hello Jason,

    From EMS, just typed Send-Mailmessage and hit enter

    it is throwing same error "The Term 'send-MailMessage' is not recoganized as cmdlet , function,operale program, or Script file. Verify the the term and try again

    is that due to PS version issue?

    Exchange server 2007.

    from the PS command, issued the command --------------> $psvertiontable

    PSversion is 2.0

     

     

     

     


    ramakrishnan
    Wednesday, October 19, 2011 12:47 PM
  • This command does applies to "Windows PowerShell 2.0"
    Jasjit Singh Dhindsa | ITIL v3 | IASA Foundation Certified | MCITP:Exchange 2010 | Exchange 2007 | MCTS:OCS 2007 | Exchange 2010 | Exchange 2007 | MCSA:Messaging | Security | MCSE:Messaging | Security
    • Marked as answer by Jason LJS Monday, October 24, 2011 2:13 AM
    Wednesday, October 19, 2011 12:53 PM
  • Meaning that this will not work on Exchange Server 2007, on Windows Server 2003....

    is that


    ramakrishnan
    Wednesday, October 19, 2011 2:16 PM
  • It works with Exchange 2007 SP2 and Exchange 2010 provided you have Windows PowerShell 2.0
    Jasjit Singh Dhindsa | ITIL v3 | IASA Foundation Certified | MCITP:Exchange 2010 | Exchange 2007 | MCTS:OCS 2007 | Exchange 2010 | Exchange 2007 | MCSA:Messaging | Security | MCSE:Messaging | Security
    Wednesday, October 19, 2011 3:56 PM