Archive

Archive for the ‘Scripting’ Category

PowerShell Application and System Event Log Matrix (HTML)

December 30, 2014 Leave a comment

As a fun scripting project, I thought it would be beneficial to create an Event Log Matrix of sorts that displayed Windows Server application and system errors and warnings by count and error message on a simple HTML page using PowerShell.   The below script dumps the top 5 errors per server, along with the error count, Event ID, Error Type and the event message for the last 2 hours.   This is a great tool for daily system checks.

Enjoy!

Ed McKinzie

image

#SCRIPT Purpose: This script enumerates and sorts the top 5 Application and System event errors 
# and warnings for each Exchange server in your environment and populates them
# into a neatly formated HTML page. The script also parses the first 100 characters
# of the event message for easy analysis. A progress bar was also added to show progress.
#Author: Ed McKinzie - edmckinzie@hotmail.com
#Script name: Get_Eventlog_Count.ps1
#PreReqs: Create a text file named C:\Scripts\Servers_list.txt and populate it with FQDN server names, 1 server name per line

cls;

$date = (Get-Date).Addhours(-2);

#Define the Error Handling
$erroractionpreference = "SilentlyContinue";
[Int] $intSucceeded = 0;

#remove-item C:\Exchange_Event_Log_Matrix.htm (This will error the first time it runs, as the file does not exist.
remove-item "C:\Exchange_Event_Log_Matrix.htm";

#Create a new HTML Page
$file = New-Item -type file "C:\Exchange_Event_Log_Matrix.htm";

Add-Content $file "<HTML><TITLE>Exchange Event Log Matrix</TITLE>";
Add-Content $file "<style>BODY{color:white;font-family:verdana;font-size:7pt;background-color:black}table{border-style:solid;border-width:thin;border-color:white;width:100%;}th{font-size:7pt;text-align:left;}td{font-size:7pt;background-color:#000000;}</style>";
Add-content $file "<font color=#00FF00 font size='3'><Center><b>Exchange Event Log Matrix</b></Center></font>";
Add-content $file "<br>";

Function Get_WinEVENT {
#$CAS_Servers = Get-ExchangeServer;
#@(foreach($comp in $CAS_Servers) {

@(foreach($comp in(Get-Content "C:\Scripts\Servers_list.txt")){ #Change it to this if you want the script to run against a list of servers

Write-Progress -Activity "Parsing Event Logs" -Status "Number of servers processed: $intSucceeded";

$App_Error_CNT = 0;
$App_Warning_CNT = 0;
$Sys_Error_CNT = 0;
$Sys_Warning_CNT = 0;

Write-Host "Working on $Comp";

#Enumerate the Event logs
#Application Logs
$appErrors = Get-WinEvent -ComputerName $comp -FilterHashTable @{LogName='Application'; Level=1,2,3; StartTime=$date} | Select-Object [string]$comp, @{Expression={$_.Id};Label="ID"},@{Expression={$_.LevelDisplayName};Label="ErrorType"}, @{Expression={$_.ProviderName};Label="Source"}, @{Label='Message';Expression={$_.Message.Substring(0,100)}} #| ConvertTo-Html;

#System Logs
$SysErrors = Get-WinEvent -ComputerName $comp -FilterHashTable @{LogName='System'; Level=1,2,3; StartTime=$date} -ErrorAction SilentlyContinue | Select [string]$comp, @{Expression={$_.Id};Label="ID"},@{Expression={$_.LevelDisplayName};Label="ErrorType"}, @{Expression={$_.ProviderName};Label="Source"}, @{Label='Message';Expression={$_.Message.Substring(0,100)}} #| ConvertTo-Html;

# Combine and sort the arrays
#Sort and Filter the Application Logs
$AppError_Count = $appErrors | Group-Object ID, ErrorType, Source, Message | Sort-Object Count -descending | Select @{Expression={$_.count};Label="App Errors"}, @{Expression={$_.Name};Label="Event ID, Error Type, and Message"} -First 5 | ConvertTo-Html;

#Sort and Filter the System Logs
$SysError_Count = $SysErrors | Group-Object ID, ErrorType, Source, Message | Sort-Object Count -descending | Select @{Expression={$_.count};Label="Sys Errors"}, @{Expression={$_.Name};Label="Event ID, Error Type, and Message"} -First 5 | ConvertTo-Html;

Write-Host "Finished $Comp";

#Add the content to the HTLM Page
Add-content $file "<TR><font color=#00FF00 font size='1'><B>$comp</Font></td>","$AppError_Count</td>","$SysError_Count</td>";
Add-content $file "<BR>";

#Increment Progress Bar
$intSucceeded++;

}
)

#Close the table
Add-Content $file "</Table>"

}

#Call the WINEVENT Function
Get_WinEVENT

Advertisements

Exchange 2010 MASS Migration Move Queue Status–In an Email

May 11, 2012 1 comment

While migrating mailboxes from Exchange 2003 to Exchange 2010, I wanted to get periodic email updates on the mailbox move status.  Below is a quick Exchange PowerShell script to do this work.   (I am sure it can be improved greatly, but it served as a quick fix to my need)

The script: Save this file as Get-MOVE-STATUS.ps1 in your C:\Scripts folder.  (CAS server works best)

$Datevar = (get-date).tostring(“h:mm tt on M-dd-yy “)

$a = “<style>”
$a = $a + “BODY{background-color:white;}”
$a = $a + “BODY{font-size: 9pt; font-family: arial;font-color:#041F92}”
$a = $a + “TABLE{border-width: 0px;border-style: solid;border-color: black;border-collapse: collapse;}”
$a = $a + “TH{border-width: 0px;padding: 1px;border-style: solid;border-color: black;Font-color: white;background-color: Black;}”
$a = $a + “TD{border-width: 0px;padding: 1px;border-style: solid;border-color: black;background-color: DarkKhaki}”
$a = $a + “</style>”

$b = “<H4><b><u>VLAB Mailbox Migration Status</u></b></H4>”

$c = “<H4><b><u>VLAB Mailbox Mailboxes In Progress</u></b></H4>”

$d = “<H4><b><u>VLAB Mailbox Completing Mailboxes (Not quite complete – Waiting on AD Replication)</u></b></H4>”

$GetMoveQueue = Get-MoveRequest –resultsize unlimited | group status | select Name, Count | ConvertTo-Html -Head $a -Body $b
$GetMoveQueueInProgress = Get-MoveRequest -MoveStatus InProgress | Get-moverequeststatistics |select DisplayName,PercentComplete,TotalMailboxSize | ConvertTo-Html -Head $a -Body $c
$GetMoveQueueCompleting = Get-MoveRequest -MoveStatus CompletionInProgress | Get-moverequeststatistics |select DisplayName,PercentComplete,TotalMailboxSize | ConvertTo-Html -Head $a -Body $d

Send-MailMessage -To “admin1@vlab.com”, “admin2@vlab.com” -From “MailboxAdmin@vlab.com” `
-Subject “VLAB Mailbox Migration status as of $Datevar” -SmtpServer “smtp.vlab.com” -Body `
($GetMoveQueue, $GetMoveQueueInProgress,$GetMoveQueueCompleting | Out-String) -BodyAsHtml

 

The Output:

image

 

Note: If you want to automate this, save the above PowerShell Script as Get-MOVE-STATUS.ps1 and then create a batch file with the below text.  You can then create a scheduled task calling the batch file with whatever time iterations you need.  (Be sure to run it as “elevated” or highest privileges and as an account that has access to Exchange servers)

cd \
cd c:\scripts
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -command ". 'C:\Program Files\Microsoft\Exchange Server\V14\bin\RemoteExchange.ps1'; Connect-ExchangeServer -auto;C:\Scripts\Get-MOVE-STATUS.ps1"

 

Best of luck!

Ed McKinzie

Categories: Exchange 2010, Scripting

Exchange 2010 PowerShell:List Junk E-mail, Delete Items Folder

April 25, 2012 10 comments

How much overhead is there in Exchange 2010 with regard to mailbox content and storage consumption in the Junk Email and Deleted Items folders?  This topic often comes up as expensive SANs and Storage arrays fill up, especially with thin-provisioned storage arrays, such as Equallogic designs for Exchange.

There are 2 main topics that I wanted to check on each Exchange 2007 Mailbox Server:

  1. How much data is stored in the Deleted Items Folder
  2. How much data is stored in the Junk E-Mail Folder

My experience has shown that 10-15% of your overall Exchange data is located in one of these two folders if your organization does not institute some type managed folder or retention policy in your Exchange organization.

I have written a script to pull the Mailbox size, Deleted Items, and Junk Email Size out of Exchange.  For large Exchange organizations, this script could take some time to run.  The script dumps all of the data to an Excel spreadsheet that is then emailed to the email administrator.   At the end of the script you will have to define the Recipient, Sender and SMTP server.


$date = get-date
#Author: Ed McKinzie

$erroractionpreference = "SilentlyContinue"
$warningactionpreference = "SilentlyContinue"
#View the Entire Forest
Set-ADServerSettings -ViewEntireForest:$True
[Array] $MailboxVariablesCollection = @() 

# Hit all Mailboxes
(Get-Mailbox -ResultSize Unlimited) | ForEach { 

$erroractionpreference = "SilentlyContinue"
$warningactionpreference = "SilentlyContinue"
  # Get the DisplayName, LastLogonTime, DatabaseName and TotalItemSize statistics 
  $MailboxStats = Get-MailboxStatistics $_.identity | Select DisplayName, LastLogonTime, DatabaseName, TotalItemSize 

  # Enumerate Sent, Junk-Email and Deleted Items 
  $mbSentStats = Get-MailboxFolderStatistics $_.identity | Where {$_.FolderPath -eq "/Sent Items"} | Select ItemsInFolderAndSubfolders, @{name="SentItemsSize";expression={$_.FolderAndSubfolderSize.ToMB()}} 
  $mbDeletedStats = Get-MailboxFolderStatistics $_.identity | Where {$_.FolderPath -eq "/Deleted Items"} | Select ItemsInFolderAndSubfolders, @{name="DeletedItemsSize";expression={$_.FolderAndSubfolderSize.ToMB()}} 
  #$mbJunkEmailStats = Get-MailboxFolderStatistics $_.identity –FolderScope JunkEmail | Select ItemsInFolderAndSubfolders,@{name="JunkEmailItemCount";expression={$_.FolderAndSubfolderSize.ToMB()}} 
  $mbJunkEmailStats = Get-MailboxFolderStatistics $_.identity | Where {$_.FolderPath -eq "/Junk E-mail"} | Select ItemsInFolderAndSubfolders,@{name="JunkEmailItemCount";expression={$_.FolderAndSubfolderSize.ToMB()}} 
  $mbManagedFolderStats = Get-MailboxFolderStatistics $_.identity | Where {$_.FolderPath -eq "/Managed Folders"} | Select ItemsInFolderAndSubfolders,@{name="ManagedFolderItemCount";expression={$_.FolderAndSubfolderSize.ToMB()}}

  $MailboxVariables = "" | Select "Display Name", "Last Logon Time", Database, "Mailbox Size (MB)", "Total Sent Items", "Sent Items Size (MB)", "Total Deleted Items", "Deleted Items Size (MB)", "JunkEmailItemCount", "TotalJunkEmailSizeMB","ManagedFolderItemCount","ManagedFolderEmailSizeMB","E-mail Address" 

  $MailboxVariables."Display Name" = $MailboxStats.DisplayName 
  $MailboxVariables."Last Logon Time" = $MailboxStats.LastLogonTime 
  $MailboxVariables.Database = $MailboxStats.DatabaseName 
  $MailboxVariables."Mailbox Size (MB)" = "{0:n2}" -f ($MailboxStats.TotalItemSize.Value.ToMB()) 
  $MailboxVariables."Total Sent Items" = "{0:n1}" -f ($mbSentStats.ItemsInFolderAndSubfolders)
  $MailboxVariables."Sent Items Size (MB)" = "{0:n2}" -f ($mbSentStats.SentItemsSize) 
  $MailboxVariables."Total Deleted Items" = "{0:n1}" -f ($mbDeletedStats.ItemsInFolderAndSubfolders)
  $MailboxVariables."Deleted Items Size (MB)" = "{0:n2}" -f ($mbDeletedStats.DeletedItemsSize)

  $MailboxVariables."TotalJunkEmailSizeMB" = "{0:n2}" -f ($mbJunkEmailStats.JunkEmailItemCount)
  $MailboxVariables."JunkEmailItemCount" = "{0:n1}" -f ($mbJunkEmailStats.ItemsInFolderAndSubfolders)

  $MailboxVariables."ManagedFolderEmailSizeMB" = "{0:n2}" -f ($mbManagedFolderStats.ManagedFolderItemCount)
  $MailboxVariables."ManagedFolderItemCount" = "{0:n1}" -f ($mbManagedFolderStats.ItemsInFolderAndSubfolders)

  $MailboxVariables."E-mail Address" = $_.PrimarySmtpAddress 

  $MailboxVariablesCollection += $MailboxVariables 
} 

$MailboxVariablesCollection | Export-Csv C:Scripts"MailboxStats_All_Mailboxes_$(Get-Date -f 'yyyyMMdd').csv" -NoType

Send-MailMessage -To "oneyw@vlab.com" -From "Oneyw@vlab.com" `
        -Subject "Exchange 2010 Mailbox Junk Email and Deleted Items Folders - $Date" -SmtpServer "ex-cas1.vlab.com" -BodyAsHtml -Attachments C:Scripts"MailboxStats_All_Mailboxes_$(Get-Date -f 'yyyyMMdd').csv"

Invoke-Expression  C:Scripts"MailboxStats_All_Mailboxes_$(Get-Date -f 'yyyyMMdd').csv"

Out-Put is dumped to Excel:

image


Remedial Actions on cleaning up this data within Exchange:

  • We can create Exchange 2007 Managed Folder Policies or Exchange 2010 Retention Policy Tags, meaning we auto-purge items in specific folders.
  • We can adjust how long the Deleted Items or Dumpster is preserved
  • We can adjust the DIRT (Deleted Item Retention Time) and DMRT (Deleted Mailbox Retention Time)
  • We can adjust quotas and nag limits on usersdatabases

Update: I just recently added a “Managed Folder” query as well, which is defined as part of your Managed Folder or Item Retention Policy.

Links:

 
Best of Luck!
 
Ed McKinzie
 

List Exchange 2010 Mailbox and AD Information and Send via Email

April 17, 2012 2 comments

This is one of the more handy scripts I have put together.  It enumerates both the Active Directory and Exchange mailbox data\statistics and sends them to an administrator’s email account, in a nicely formatted message.

It lists:

Active Directory Information

  • Display Name
  • Simple Display Name
  • UserPrincipleName
  • Identity
  • Distinguished Name
  • GUID
    Exchange Mailbox Info

  • Display Name
  • Primary Email address
  • Exchange Server
  • Exchange Database
  • Recipient Type
  • Exchange Version
  • When Created
  • Client Throttling Policy
  • RoleAssignedPolicy
  • GAL Hidden status

Exchange Mailbox Statistics

  • Item Count
  • Size
  • Last Logon Time
  • Quota status

Safe Senders List

  • Status
  • Trusted Senders
  • Blocked Senders

Exchange Logon Statistics

  • Protocol
  • Client Name
  • Client Mode
  • Open Message Count
  • Open Folder Count

Exchange ActiveSync\Mobile Device Info

  • DeviceId
  • DeviceModel
  • First Sync Time
  • Last Sync Attempt
  • Last Ping time
  • Sync State
  • Number of Sync’d folders

The Output:

image

I am still working on the syntax for the Safe Senders list…..

The script:

#Author: Ed McKinzie edward@i-tekpro.com
#www.i-tekpro.com

#Load the Exchange snapin for 2010
$mailbox=Read-Host "Enter a mailbox you want to dump"
$mailbox_report=Read-Host "Enter a mailbox\Email Address you want to Send this report"
#Add-PSSnapin Microsoft.Exchange.Management.PowerShell.E2010
Set-ADServerSettings -ViewEntireForest:$True
$FormatEnumerationLimit =-1

$a = "<style>"
$a = $a + "BODY{background-color:white;}"
$a = $a + "BODY{font-size: 9pt; font-family: arial;font-color:#041F92}"
$a = $a + "TABLE{border-width: 0px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 0px;padding: 1px;border-style: solid;border-color: black;Font-color: white;background-color: Black; font-decoration: bold;}"
$a = $a + "TD{border-width: 0px;padding: 1px;border-style: solid;border-color: black;background-color: DarkKhaki}"
$a = $a + "</style>"

$b = "<H2><b><u>Active Directory Information</u></b></H2>"
$c = "<H2><b><u>Exchange 2010 Mailbox Info</u></b></H2>"
$d = "<H2><b><u>Mailbox Statistics</u></b></H2>"
$e = "<H2><b><u>Mailbox Mobile Device Info</u></b></H2>"
$f = "<H2><b><u>Mailbox Safe and Blocked Sender List</u></b></H2>"
$logonstatistics = "<H2><b><u>Exchange 2010 Mailbox Logon Statistics</u></b></H2>"


$bodyb = get-mailbox -identity $mailbox | Select DisplayName,SimpleDisplayName,UserPrincipalName,Identity,DistinguishedName,Guid | ConvertTo-Html -Head $a -Body $b
$bodyc = get-mailbox -identity $mailbox | Select DisplayName,PrimarySmtpAddress,ServerName,Database,RecipientType,ExchangeVersion,WhenCreated,ThrottlingPolicy,RoleAssignmentPolicy,HiddenFromAddressListsEnabled | ConvertTo-Html -Head $a -Body $c
$bodyd = get-mailboxstatistics -identity $mailbox | Select LastLoggedOnUserAccount, ItemCount, TotalItemSize, DeletedItemCount, TotalDeletedItemSize, LastLogonTime, StorageLimitStatus | ConvertTo-Html -Head $a -Body $d
$bodye = Get-ActiveSyncDeviceStatistics -mailbox $mailbox | Select DeviceID, DeviceModel, FirstSyncTime,LastSyncAttemptTime,LastPingHeartbeat,SyncStateUpgradeTime,NumberOfFoldersSynced | ConvertTo-Html -Head $a -Body $e
$bodyf = Get-MailboxJunkEmailConfiguration -identity $mailbox | Select Enabled,TrustedSendersAndDomains,BlockedSendersAndDomains | ConvertTo-Html -Head $a -Body $f


$bodylogonstatistics = get-mailbox -identity $mailbox | Get-LogonStatistics | select UserName,ApplicationId,ClientName,ClientMode,LogonTime,CurrentOpenMessages,CurrentOpenFolders | ConvertTo-Html -Head $a -Body $logonstatistics

 

 

Send-MailMessage -To $mailbox_report -From "Administrator@vlab.com" `
-Subject "Exchange Mailbox Dump $mailbox" -SmtpServer "SMTPSERVER.DOMAIN.COM" -Body `
($bodyb, $bodyc, $bodyd, $bodyf, $bodylogonstatistics, $bodye | Out-String) -BodyAsHtml

Usage: Run the script and pass it two variables at the prompts:

  1. The alias of the mailbox you want to dump
  2. The Email address of the person you want to send the report

Best of luck,

Ed McKinzie

www.i-tekpro.com

Exchange 2010 Mailbox Count, Database Size, and Average to HTML

April 16, 2012 8 comments

I decided to work on an Exchange 2010 PowerShell, HTML Status page script that would dump the following Exchange 2010 Mailbox information statistics:

  1. Exchange Mailbox and Distribution List counts per domain
  2. Total Exchange Mailbox Count, with averages and totals of Mailbox size and content
  3. Total number of Databases with raw storage used, averages, and max and min DB sizes
      The script:  Save the script below as

c:\scripts\Mailbox-Count-HTML-New.ps1.

#Author: Ed McKinzie edward@i-tekpro.com
# Site: www.i-tekpro.com
#Exchange 2010 Microsoft Engineer

#Delete any previous copies of the HTML page
del C:\Scripts\Exchange-Mailbox-Stats\Exchange-MailboxStats-$filedate.html

#Date the file
$filedate = (get-date).tostring("MM-dd-yyyy")

#Create the file

$file = New-Item -type file "C:\Scripts\Exchange-Mailbox-Stats\Exchange-MailboxStats-$filedate.html"

#File creation status
Write-Output $file

#Ignore any errors
$erroractionpreference = "SilentlyContinue"
$warningactionpreference = "SilentlyContinue"

#Add content to the HTML Page, including Body and Headers
Add-Content $file "<HTML><TITLE>Exchange Mailbox Information</TITLE>"
Add-Content $file "<HEAD><H1 class='auto-style1'><CENTER>Exchange Mailbox Information</Head></CENTER></H1>"
#Add-Content $file "<BR><HR class='auto-style1'>"
Add-Content $file "<Body bgcolor='#000000'>"
#####################################################################################################################
#####################################################################################################################
#####################################################################################################################

#Set the Domain
Set-AdServerSettings -RecipientViewRoot "vlab.com"
write-host "Counting Vlab Domain Mailboxes....."
$MailboxCount1 = (Get-Recipient -resultsize unlimited -RecipientType 'UserMailbox') | Measure-Object | Select Count

#####################################################################################################################
#####################################################################################################################
#####################################################################################################################
$DLCount1 = (Get-Recipient -ResultSize Unlimited -RecipientType 'DynamicDistributionGroup','MailNonUniversalGroup','MailUniversalDistributionGroup','MailUniversalSecurityGroup')| Measure-Object | Select Count

#####################################################################################################################
###DOMAIN #2 Uncomment this section and change the domain if you have a multi-Domain AD Forest
#Set-AdServerSettings -RecipientViewRoot "Contoso.com"
#write-host "Counting Contoso.com mailboxes Mailboxes....."
#$MailboxCount2 = (Get-Recipient -resultsize unlimited -RecipientType 'UserMailbox') | Measure-Object | Select Count
#$DLCount2 = (Get-Recipient -ResultSize Unlimited -RecipientType 'DynamicDistributionGroup','MailNonUniversalGroup','MailUniversalDistributionGroup','MailUniversalSecurityGroup')| Measure-Object | Select Count
#####################################################################################################################

#####################################################################################################################
#$MailboxSizeDump = Get-Mailbox -Resultsize unlimited | Get-MailboxStatistics | %{$_.TotalItemSize.Value.ToMB()} | Measure-Object -sum -average -max -min
# Retrieve the list of mailboxes from all mailbox databases on all servers

$listofmailboxdatabases = get-mailboxdatabase
$totalmailboxcount = 0
$totalmailboxtotalitemcount = 0
$totalmailboxtotalsize = 0

# start a loop that will check each mailbox database
foreach ($individualdatabase in $listofmailboxdatabases)
{

$listOfMailboxes = Get-MailboxDatabase $individualdatabase | Get-Mailbox

# Initialize the counter variables that we'll use
# Initialize the counter variables that we'll use
$mailboxCount = 0
$mailboxTotalItemCount = 0
$mailboxTotalSize = 0
$mailboxAverageSize = 0
$mailboxAverageItemCount = 0

# Start a loop that will count stats from individual mailboxes
foreach ($individualMailbox in $listOfMailboxes)
{
# increment the mailbox count by 1
$mailboxCount++

# Get the name of the current mailbox so that we can...
$individualMailboxName = $individualMailbox.Identity.DistinguishedName

#... quickly and easily get stats from that mailbox
$individualMailboxStats = Get-MailboxStatistics -Identity $individualMailbox

# Get the size of the mailbox in MB and save it in a variable
$individualMailboxSize = $individualMailboxStats.TotalItemSize.value.toMB()

# Get the number of items in the mailbox and save it in a variable
$individualMailboxItemCount = $individualMailboxStats.ItemCount

# Add the size of this mailbox to a running total
$mailboxTotalSize = $mailboxTotalSize + $individualMailboxSize

# Add the number of items in this mailbox to a running total
$mailboxTotalItemCount = $mailboxTotalItemCount + $individualMailboxItemCount
}

# Calculate the average mailbox size
$mailboxAverageSize = $mailboxTotalSize / $mailboxCount

# Calculate the average number of items per mailbox
$mailboxAverageItemCount = $mailboxTotalItemCount / $mailboxCount

$totalmailboxcount = $totalmailboxcount + $mailboxCount
$totalmailboxtotalsize = $totalmailboxtotalsize + $mailboxTotalSize
$totalmailboxtotalitemcount = $totalmailboxtotalitemcount + $mailboxTotalItemCount
}

# Calculate the average mailbox size
$totalmailboxAverageSize = $totalmailboxtotalsize / $totalmailboxcount

# Calculate the average number of items per mailbox
$totalmailboxAverageItemCount = $totalmailboxtotalitemcount / $totalmailboxcount

####################################################################################################################

write-host "Measuring Database statistics....."
$MailboxStorageStats = get-mailboxdatabase | foreach-object{select-object -inputobject $_ -property *,@{name="MailboxDBSizeinGB";expression={[math]::Round(((get-item ("\\" + $_.servername + "\" + $_.edbfilepath.pathname.replace(":","$"))).length / 1GB),2)}}} | Sort-Object mailboxdbsizeinGB -Descending | measure-object -Property mailboxdbsizeinGB -Sum -Maximum -Minimum -Average

#####################################################################################################################

$MBXCount = "
{0:n0}" -f ($mailboxcount1.Count)
$DLCount = "
{0:n0}" -f ($DLCount1.Count)

#If you have multiple domains, enable this to coincide with Domain#2
#$MBXCount2 = "
{0:n0}" -f ($mailboxcount2.Count)
#$DLCount2 = "
{0:n0}" -f ($DLCount2.Count)


$MBXDatabaseCount = "
{0:n0}" -f ($MailboxStorageStats.Count)
$MBXDatabaseSizeTotal = "
{0:n2}" -f ($MailboxStorageStats.Sum)
$MBXDatabaseAverage = "
{0:n2}" -f ($MailboxStorageStats.Average)
$MBXDatabaseLargest = "
{0:n2}" -f ($MailboxStorageStats.Maximum)
$MBXDatabaseSmallest = "
{0:n2}" -f ($MailboxStorageStats.Minimum)


$MBXCount_Total = "
{0:n0}" -f ($totalmailboxcount)
$MBXTotalItemSize = "
{0:n0}" -f ($totalmailboxtotalitemcount)

$MBXSizeTotal = "
{0:n2}" -f ($totalmailboxtotalsize/1000)
$MBXAverage = "
{0:n2}" -f ($totalmailboxAverageSize)
$MBXItemAverage = "
{0:n2}" -f ($totalmailboxAverageItemCount)


Add-content $file "
<h2 class='auto-style1'>Exchange Recipient Count</h2>"
Add-Content $file "
<style>table{border-style:solid;border-width:.5px;font-size:8pt;background-color:#ccc;width:80%;}th{text-align:left;}td{background-color:#000000;border-style:solid;border-width:.5px;}body{font-family:verdana;font-size:8pt;}h1{font-size:12pt;}h2{font-size:10pt;}.auto-style1 {color: #FFFFFF;}.auto-style2 { color: #000000;}</style>"
Add-content $file "<body bgcolor='#000000'><table class='auto-style1' BORDER='1'><TR class='auto-style2'><th>Domain</th><th>Mailbox\Recipient Count</th><th>Distribution List Count</th></tr>"
Add-content $file "<TR><TD>VLAB Domain","<TD>$MBXCount","<TD>$DLCount"

#Enable this for Domain #2....be sure to change the Domain#2 Name to the Domain Name Netbios name will suffice
#Add-content $file "<TR><TD>Contoso","<TD>$MBXCount2","<TD>$DLCount2"

Add-content $file "</Table>"
Add-Content $file "<BR>"

Add-content $file "<h1 class='auto-style1'>Exchange Total Mailbox Count</h1>"
Add-content $file "<table class='auto-style1' BORDER='1'><tr class='auto-style2'><th># Mailboxes</th><th>Total Mailbox Size</th><th>Total Items in Mailboxes</th><th>Average Mailbox Size</th><th>Average Items Per Mailbox</th></tr>"
Add-content $file "<TR><TD>$MBXCount_Total","<TD>$MBXSizeTotal GB","<TD>$MBXTotalItemSize</td>","<TD>$MBXAverage MB</td>","<TD>$MBXItemAverage</td>"
Add-content $file "</Table>"
Add-Content $file "<BR>"


Add-content $file "<h1 class='auto-style1'>Exchange Database Statistics</h1>"
Add-content $file "<table class='auto-style1' BORDER='1'><tr class='auto-style2'><th>Number of Databases</th><th>Total DB Size</th><th>Average DB Size</th></th><th>Largest DB Size</th><th>Smallest DB Size</th></tr>"
Add-content $file "<TD>$MBXDatabaseCount</TD>","<TD>$MBXDatabaseSizeTotal GB</TD>","<TD>$MBXDatabaseAverage GB","<TD>$MBXDatabaseLargest GB","<TD>$MBXDatabaseSmallest GB"
Add-content $file "</Table>"

#Add-Content $file "<BR><HR class='auto-style1'>"

$Datevar = (get-date).tostring("h:mm tt on M-dd-yy ")

Add-Content $file "<font color=#00FF00 font size='1'>This report starts Daily at 5 AM and finished at $Datevar</font>"

type $file

####################################################################################################################
#Change the TO and FROM variables and the SMTP server. Unless you allow anonymous SMPT sending, it is best run this script on your HUB server.
Send-MailMessage -To "oneyw@vlab.com" -From "Oneyw@vlab.com" `
-Subject "Exchange 2010 Mailbox Statistics - $Date" -SmtpServer "ex-cas1.vlab.com" -BodyAsHtml -Attachments C:\Scripts\Exchange-Mailbox-Stats\Exchange-MailboxStats-$filedate.html

##Invoke-Expression C:\Scripts\Exchange-Mailbox-Stats\Exchange-MailboxStats-$filedate.html

The Output:  Nice and organized HTML Page

image

If you want to automate this and archive the HTML file each day, you can create a batch file and call the PowerShell script using a scheduled task.

cd \ 

cd c:\scripts\

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -command ". 'C:\Program Files\Microsoft\Exchange Server\V14\bin\RemoteExchange.ps1'; Connect-ExchangeServer -auto;C:\Scripts\Mailbox-Count-HTML-New.ps1"

Del "C:\inetpub\wwwroot\WebServer\Exchange-MBX-Size\Exchange-MailboxStats.html"

Copy "C:\Scripts\MailboxStats\*.html" "C:\inetpub\wwwroot\WebServer\Exchange-MBX-Size"

Rename "C:\inetpub\wwwroot\WEBSERVER\Exchange-MBX-Size\*.html" "Exchange-MailboxStats.html"

Move "C:\Scripts\MailboxStats\*.html" "C:\Scripts\MailboxStats\MailboxStats-Archive"

Best of luck!

Ed McKinzie

Power of WMI and PowerShell (Exchange Little Brother Page)

March 12, 2008 Leave a comment
I have began developing a WMI\PowerShell Information site that dumps all of the pertinent information I need regarding the servers we manage.  On 15 minute increments, I have numerous scripts that fetch real-time data.  This data includes everything to Disk, FreeSpace, CPU load, to OWA, SMTP Queues and Server connections.  I have also wrote ASP pages that cater to other data that I may need in real-time.  I am using a framed console to display multiple HTML pages.  I am also leveraging Big Brother…..See below for a screen shot.
Each page is dynamically updating and allows for real-time reporting….
  Console Page                                                       ASP Query Page 
 Littlebro   query
Event Log Monitoring                                             Active Connection Count
logs     image
 

 

Categories: Scripting

Exchange 2003 – Active Sync reporting?

November 7, 2007 Leave a comment

Taken from Microsoft’s Exchange Team Blog and modified, we have been able to take two SQL scripts, run them against our Front-end Protocol servers, and retrieve usage data pertaining to Active Sync users and clients.  The first script produces a ‘Hits by User’ Report which will show you which users are hitting your EAS servers. This report provides many of the statistics that could help you better understand how your users are interacting with your EAS deployment. It returns information by individual user such as, devices type used, number of Syncs, Emails Sent, Attachments downloaded, Meeting Responses, etc.

The second script produces is a ‘Hits by DeviceType’ Report. This report gives you a breakdown of the DeviceTypes (SmartPhone, PocketPC, etc.)

You need several things setup in order for the scripts to work:

  • Install Microsoft’s LogParser 2.2 or later on your Front-End Exchange server(Get it here)
  • Copy both SQL scripts to the "C:\Program Files\Log Parser 2.2" directory on your Front-End Exchange server.  If you have more than one server, this will need to be ran on every Front-end server n your organization to get accurate results.  (IIS logs are being parsed)
  • Get the scripts.  Script 1 (Save as Hits_By_User.Sql) and Script 2 (Save as Hits_by_DeviceType.Sql). (Email Me for the scripts)

Once saved, open a command prompt to "C:\Program Files\Log Parser 2.2" from your Front-End Exchange server. 

Issue this command:  LogParser.exe -i:IISW3C file:"C:\Program Files\Log Parser 2.2\Hits_By_User.sql"

This is going to create a file named Hits_By_User.csv in the root of C:\.  Depending on your usage the script may take 15-30 minutes to run.

Once complete, open a command prompt to "C:\Program Files\Log Parser 2.2" from your Front-End Exchange server. 

Issue this command:  LogParser.exe -i:IISW3C file:"C:\Program Files\Log Parser 2.2\Hits_By_DeviceType.sql"

This is going to create a file named Hits_By_DeviceType.csv in the root of C:\.  Depending on your usage the script may take 15-30 minutes to run.

Results should look similar to this: (By User)

User,DeviceType,Hits,Syncs,Folder_Syncs,Pings,Emails_Sent,SmartReplies,Meeting_Responses,Get_Attachments
Domain\User1,SmartPhone,2258,1224,16,971,1,0,2,0
Domain\User2,SmartPhone,1824,867,149,630,0,1,0,5
,SmartPhone,1723,123,14,1028,1,1,0,0
Domain\User3,SmartPhone,1654,105,10,1325,0,0,0,0
Domain\User4,SmartPhone,1545,1008,5,487,4,3,3,9
Domain\User5,SmartPhone,1514,207,157,86,2,10,1,13
Domain\User6,SmartPhone,1207,184,73,31,1,6,1,16
Domain\User7,SmartPhone,1124,188,9,788,1,1,0,0
Domain\User8,SmartPhone,1123,474,44,416,1,22,0,21
Domain\User9,SmartPhone,946,284,1,644,0,0,0,0
Domain\User10,SmartPhone,940,324,198,172,0,3,0,0
Domain\User12,SmartPhone,896,348,13,311,0,1,0,25

(By Device)

DeviceType,             Hits
PocketPC,                103759
SmartPhone,             48890
PalmOneTreoAce,      16716
NokiaE62,                  2295
,                                60 *Other devices

Ed M.

Categories: Scripting