Sunday, November 16, 2008

Find Unused Mailbox Powershell Gui Exchange 2007


Finding unused mailboxes is one of those mind numbing tasks a Sys Admin must perform routinely in Large Exchange organizations. Many of the scripts on this blog help to show ways to tackle different issues around unused or disabled mailboxes such as removing disabled users accounts from groups or finding broken delegate forwarding rules etc. I've also posted a few scripts before that showed some methods to track down unused mailboxes by looking at the number of unread email and the last time a mailbox sent a message. Well this script puts some of these methods together in a powershell GUI script that uses Exchange Web Services and some Exchange Management Shell cmdlets to look at all mailboxes on a server and show us information about when a mailbox was logged into, how big it is, how many unread email there is and when the last sent and/or received email was. It presents all this information back to you as a GUI with data grid and also has a button that allows exporting the result to a CSV file.

How it works?

To get the information about when the mailbox was last logged onto the EMS cmdlet get-mailbox is used and also get-Mailboxstatistics is also used to get the mailbox size in MB. To get the number of unread email in a mailbox and to get the last time a mail was sent from the sent items folder Exchange Web Services is used to query the mailbox. To do this from Powershell and the EMS I’ve used my EWSUtil library which has some routine in there that will perform a finditem operation with the nessasary restrictions so it will only show Unread messages. The library will handle Autodiscover and allow for both delegate and impersonation access method. The Form allows for all of these combinations and also allows you to specify overrides for both authentication and the URL for the CAS server to use (you need to use the full url to the EWS if you going to use eg https://servername/ews/exchange.asmx). The rest of the script just builds the form elements for the GUI and adds some simple function that does a export of the data shown in the GRID.

To use this script you need to have the EwsUtil.dll in c:\temp you can download the library from https://github.com/gscales/Powershell-Scripts/raw/master/ScriptArchive/ewsutil.zip. You can download the actual script from here I've include a few other scripts in the download that show simpler examples of read the unread email from mailboxes as well (Just for fun). I've put the download of the script here the Code itself looks like


[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
$form = new-object System.Windows.Forms.form
[void][Reflection.Assembly]::LoadFile("C:\temp\EWSUtil.dll")
$mbcombCollection = @()
$Emailhash = @{ }

function ExportGrid{

$exFileName = new-object System.Windows.Forms.saveFileDialog
$exFileName.DefaultExt = "csv"
$exFileName.Filter = "csv files (*.csv)|*.csv"
$exFileName.InitialDirectory = "c:\temp"
$exFileName.Showhelp = $true
$exFileName.ShowDialog()
if ($exFileName.FileName -ne ""){
$logfile = new-object IO.StreamWriter($exFileName.FileName,$true)
$logfile.WriteLine("UserName,EmailAddress,Last_Logon,MailboxSize,
Inbox_Number_Unread,Inbox_Unread_LastRecieved,Sent_Items_LastSent")
foreach($row in $msTable.Rows){
$logfile.WriteLine("`"" + $row[0].ToString() + "`"," + $row[1].ToString() + "," + $row[2].ToString() + "," + $row[3].ToString() + "," + $row[4].ToString() + "," + $row[5].ToString() + "," + $row[6].ToString())
}
$logfile.Close()
}

}

function Getinfo(){

$mbcombCollection = @()
$Emailhash.clear()
$msTable.clear()

Get-Mailbox -server $snServerNameDrop.SelectedItem.ToString() -ResultSize Unlimited | foreach-object{
if ($Emailhash.containskey($_.ExchangeGuid) -eq $false){
$Emailhash.add($_.ExchangeGuid.ToString(),$_.windowsEmailAddress.ToString())
}
}

get-mailboxstatistics -server $snServerNameDrop.SelectedItem.ToString() | foreach-object{
$mbcomb = "" | select DisplayName,EmailAddress,Last_Logon,MailboxSize,
Inbox_Number_Unread,Inbox_Unread_LastRecieved,Sent_Items_LastSent
$mbcomb.DisplayName = $_.DisplayName.ToString()
if ($Emailhash.ContainsKey($_.MailboxGUID.ToString())){
$mbcomb.EmailAddress = $Emailhash[$_.MailboxGUID.ToString()]
}
if ($_.LastLogonTime -ne $null){
$mbcomb.Last_Logon = $_.LastLogonTime.ToString()
}
$mbcomb.MailboxSize = $_.TotalItemSize.Value.ToMB()

"Mailbox : " + $mbcomb.EmailAddress
if ($mbcomb.EmailAddress -ne $null){
$mbMailboxEmail = $mbcomb.EmailAddress
if ($unCASUrlTextBox.text -eq ""){ $casurl = $null}
else { $casurl= $unCASUrlTextBox.text}
$useImp = $false
if ($seImpersonationCheck.Checked -eq $true) {
$useImp = $true
}
if ($seAuthCheck.Checked -eq $true) {
$ewc = new-object EWSUtil.EWSConnection($mbMailboxEmail,$useImp, $unUserNameTextBox.Text, $unPasswordTextBox.Text, $unDomainTextBox.Text,$casUrl)
}
else{
$ewc = new-object EWSUtil.EWSConnection($mbMailboxEmail,$useImp, "", "", "",$casUrl)
}


$drDuration = new-object EWSUtil.EWS.Duration
$drDuration.StartTime = [DateTime]::UtcNow.AddDays(-365)
$drDuration.EndTime = [DateTime]::UtcNow

$dTypeFld = new-object EWSUtil.EWS.DistinguishedFolderIdType
$dTypeFld.Id = [EWSUtil.EWS.DistinguishedFolderIdNameType]::inbox
$dTypeFld2 = new-object EWSUtil.EWS.DistinguishedFolderIdType
$dTypeFld2.Id = [EWSUtil.EWS.DistinguishedFolderIdNameType]::sentitems

$mbMailbox = new-object EWSUtil.EWS.EmailAddressType
$mbMailbox.EmailAddress = $mbMailboxEmail
$dTypeFld.Mailbox = $mbMailbox
$dTypeFld2.Mailbox = $mbMailbox

$fldarry = new-object EWSUtil.EWS.BaseFolderIdType[] 1
$fldarry[0] = $dTypeFld
$msgList = $ewc.FindUnread($fldarry, $drDuration, $null, "")
$mbcomb.Inbox_Number_Unread = $msgList.Count
if ($msgList.Count -ne 0){
$mbcomb.Inbox_Unread_LastRecieved = $msgList[0].DateTimeSent.ToLocalTime().ToString()
}

$fldarry = new-object EWSUtil.EWS.BaseFolderIdType[] 1
$fldarry[0] = $dTypeFld2
$msgList = $ewc.FindItems($fldarry, $drDuration, $null, "")
if ($msgList.Count -ne 0){
$mbcomb.Sent_Items_LastSent = $msgList[0].DateTimeSent.ToLocalTime().ToString()
}
$msTable.Rows.add($mbcomb.DisplayName,$mbcomb.EmailAddress,$mbcomb.Last_Logon,
$mbcomb.MailboxSize,$mbcomb.Inbox_Number_Unread,
$mbcomb.Inbox_Unread_LastRecieved,$mbcomb.Sent_Items_LastSent)
$mbcombCollection += $mbcomb}
}
$dgDataGrid.DataSource = $msTable
}

$msTable = New-Object System.Data.DataTable
$msTable.TableName = "Mailbox Info"
$msTable.Columns.Add("UserName")
$msTable.Columns.Add("EmailAddress")
$msTable.Columns.Add("Last Logon Time",[DateTime])
$msTable.Columns.Add("Mailbox Size(MB)",[int64])
$msTable.Columns.Add("Inbox Number Unread",[int64])
$msTable.Columns.Add("Inbox unread Last Recieved",[DateTime])
$msTable.Columns.Add("Sent Item Last Sent",[DateTime])



# Add Server DropLable
$snServerNamelableBox = new-object System.Windows.Forms.Label
$snServerNamelableBox.Location = new-object System.Drawing.Size(10,20)
$snServerNamelableBox.size = new-object System.Drawing.Size(80,20)
$snServerNamelableBox.Text = "ServerName"
$form.Controls.Add($snServerNamelableBox)

# Add Server Drop Down
$snServerNameDrop = new-object System.Windows.Forms.ComboBox
$snServerNameDrop.Location = new-object System.Drawing.Size(90,20)
$snServerNameDrop.Size = new-object System.Drawing.Size(150,30)
get-mailboxserver | ForEach-Object{$snServerNameDrop.Items.Add($_.Name)}
$form.Controls.Add($snServerNameDrop)

# Add Export Grid Button

$exButton2 = new-object System.Windows.Forms.Button
$exButton2.Location = new-object System.Drawing.Size(250,20)
$exButton2.Size = new-object System.Drawing.Size(125,20)
$exButton2.Text = "Execute"
$exButton2.Add_Click({Getinfo})
$form.Controls.Add($exButton2)


# Add Export Grid Button

$exButton1 = new-object System.Windows.Forms.Button
$exButton1.Location = new-object System.Drawing.Size(10,610)
$exButton1.Size = new-object System.Drawing.Size(125,20)
$exButton1.Text = "Export Grid"
$exButton1.Add_Click({ExportGrid})
$form.Controls.Add($exButton1)

# Add Impersonation Clause

$esImpersonationlableBox = new-object System.Windows.Forms.Label
$esImpersonationlableBox.Location = new-object System.Drawing.Size(10,75)
$esImpersonationlableBox.Size = new-object System.Drawing.Size(130,20)
$esImpersonationlableBox.Text = "Use EWS Impersonation"
$form.controls.Add($esImpersonationlableBox)

$seImpersonationCheck = new-object System.Windows.Forms.CheckBox
$seImpersonationCheck.Location = new-object System.Drawing.Size(150,70)
$seImpersonationCheck.Size = new-object System.Drawing.Size(30,25)
$form.controls.Add($seImpersonationCheck)

# Add Auth Clause

$esAuthlableBox = new-object System.Windows.Forms.Label
$esAuthlableBox.Location = new-object System.Drawing.Size(10,105)
$esAuthlableBox.Size = new-object System.Drawing.Size(130,20)
$esAuthlableBox.Text = "Specify Credentials"
$form.controls.Add($esAuthlableBox)

$seAuthCheck = new-object System.Windows.Forms.CheckBox
$seAuthCheck.Location = new-object System.Drawing.Size(140,100)
$seAuthCheck.Size = new-object System.Drawing.Size(30,25)
$seAuthCheck.Add_Click({if ($seAuthCheck.Checked -eq $true){
$unUserNameTextBox.Enabled = $true
$unPasswordTextBox.Enabled = $true
$unDomainTextBox.Enabled = $true
}
else{
$unUserNameTextBox.Enabled = $false
$unPasswordTextBox.Enabled = $false
$unDomainTextBox.Enabled = $false}})
$form.controls.Add($seAuthCheck)

# Add UserName Box
$unUserNameTextBox = new-object System.Windows.Forms.TextBox
$unUserNameTextBox.Location = new-object System.Drawing.Size(230,100)
$unUserNameTextBox.size = new-object System.Drawing.Size(100,20)
$form.controls.Add($unUserNameTextBox)

# Add UserName Lable
$unUserNamelableBox = new-object System.Windows.Forms.Label
$unUserNamelableBox.Location = new-object System.Drawing.Size(170,105)
$unUserNamelableBox.size = new-object System.Drawing.Size(60,20)
$unUserNamelableBox.Text = "UserName"
$unUserNameTextBox.Enabled = $false
$form.controls.Add($unUserNamelableBox)

# Add Password Box
$unPasswordTextBox = new-object System.Windows.Forms.TextBox
$unPasswordTextBox.PasswordChar = "*"
$unPasswordTextBox.Location = new-object System.Drawing.Size(400,100)
$unPasswordTextBox.size = new-object System.Drawing.Size(100,20)
$form.controls.Add($unPasswordTextBox)

# Add Password Lable
$unPasswordlableBox = new-object System.Windows.Forms.Label
$unPasswordlableBox.Location = new-object System.Drawing.Size(340,105)
$unPasswordlableBox.size = new-object System.Drawing.Size(60,20)
$unPasswordlableBox.Text = "Password"
$unPasswordTextBox.Enabled = $false
$form.controls.Add($unPasswordlableBox)

# Add Domain Box
$unDomainTextBox = new-object System.Windows.Forms.TextBox
$unDomainTextBox.Location = new-object System.Drawing.Size(550,100)
$unDomainTextBox.size = new-object System.Drawing.Size(100,20)
$form.controls.Add($unDomainTextBox)

# Add Domain Lable
$unDomainlableBox = new-object System.Windows.Forms.Label
$unDomainlableBox.Location = new-object System.Drawing.Size(510,105)
$unDomainlableBox.size = new-object System.Drawing.Size(50,20)
$unDomainlableBox.Text = "Domain"
$unDomainTextBox.Enabled = $false
$form.controls.Add($unDomainlableBox)


# Add CASUrl Box
$unCASUrlTextBox = new-object System.Windows.Forms.TextBox
$unCASUrlTextBox.Location = new-object System.Drawing.Size(280,75)
$unCASUrlTextBox.size = new-object System.Drawing.Size(400,20)
$unCASUrlTextBox.text = $strRootURI
$form.Controls.Add($unCASUrlTextBox)

# Add CASUrl Lable
$unCASUrllableBox = new-object System.Windows.Forms.Label
$unCASUrllableBox.Location = new-object System.Drawing.Size(200,75)
$unCASUrllableBox.size = new-object System.Drawing.Size(50,20)
$unCASUrllableBox.Text = "CASUrl"
$form.Controls.Add($unCASUrllableBox)

# Add DataGrid View

$dgDataGrid = new-object System.windows.forms.DataGridView
$dgDataGrid.Location = new-object System.Drawing.Size(10,145)
$dgDataGrid.size = new-object System.Drawing.Size(1000,450)
$dgDataGrid.AutoSizeRowsMode = "AllHeaders"
$form.Controls.Add($dgDataGrid)



$form.Text = "Exchange 2007 Unused Mailbox Form"
$form.size = new-object System.Drawing.Size(1200,700)
$form.autoscroll = $true
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()

26 comments:

Neill T. said...

Hi Glen,
GUI looks nice as usual but when I run it I don't get the Unread, Received or Sent details.

Unread has 0 against all mailboxes, Sent and Received are blank.

Any ideas?

Thanks
Neill

Glen said...

All the information your not seeing should be comming from EWS. Have a look at the cmdbox to see if you getting any errors. Other things to check is if you have rights to the mailboxes your trying to access or if your trying to use EWS impersonation that you have impersonation configured.

Cheers
Glen

Anonymous said...

Glen,

I have the same rsult as Neill T. does. No visible errors. Account that I use for this script working fine with all other scrpts. So I am thinking it must be something else.

Thank you for your great work.
Happy Holidays!
Sharapov

Glen said...

You wont get visable errors but you maybe getting errors in the powershell cmdbox this is where you want to check.

Cheers
Glen

Anonymous said...

Glen,

I am getting the same result as other guys. No red errors in powershell cmdbox but I ge a lot of the following errors in grey:

EWSUtil.FindItemException: Exception of type 'EWSUtil.FindItemException' was thrown. at EWSUtil.EWSConnection.FindUnread(BaseFolderIdType[] biArray, Duration duDuration, BasePathToElementType[] exExtenededProperties, String mtMessageType)
Error During FindItem request : The specified object was not found in the store.

Thank you.

Glen said...

I would say there is a problem with the script but i cant fault it on the servers i have. I would try using different authentication (eg use Impersonation) and try specifying the URL to your CAS server.

The other thing is maybe its a language issue are the users your trying to report on using a different langauge then the account you trying to use to access them ?

Cheers
Glen

Anonymous said...

Glen,

I tried your suggestions but get the same result. The users I'm trying to report on using the same langauge then the account I'm trying to use to access them.

Thank you.

Glen said...

Sorry as I said its probably the script or the library but i cant reproduce the issue so its hard for me to debug. So i can only suggest that you try and debug why this isn't working. You could try a really simple script like this to see if you can get the unread email on a few mailboxes to test the library is working.

[void][Reflection.Assembly]::LoadFile("C:\temp\EWSUtil.dll")
$ewc = new-object EWSUtil.EWSConnection("user@domain.com",$false, "user", "password", "domain","https://servername/ews/exchange.asmx")

$drDuration = new-object EWSUtil.EWS.Duration
$drDuration.StartTime = [DateTime]::UtcNow.AddDays(-365)
$drDuration.EndTime = [DateTime]::UtcNow

$dTypeFld = new-object EWSUtil.EWS.DistinguishedFolderIdType
$dTypeFld.Id = [EWSUtil.EWS.DistinguishedFolderIdNameType]::inbox

$mbMailbox = new-object EWSUtil.EWS.EmailAddressType
$mbMailbox.EmailAddress = "user@domain.com"

$dTypeFld.Mailbox = $mbMailbox

$fldarry = new-object EWSUtil.EWS.BaseFolderIdType[] 1
$fldarry[0] = $dTypeFld
$msgList = $ewc.FindUnread($fldarry, $drDuration, $null, "")
$msgList.Count

Cheers
Glen

krishna said...

Hello Geln,
I am getting below mentioned error when in rrun unreadreport_Simple.ps1. Please let me know how to fix this. If this works then then it would really help me to clean up the unused mailbox.

New-Object : Exception calling ".ctor" with "6" argument(s): "Unable to connect
to the remote server"
At C:\Powershell\Mailcount.ps1:12 char:19
+ $ewc = new-object <<<< EWSUtil.EWSConnection($mbMailboxEmail,$false, $nu
ll,$null,$null,$null)

Glen said...

This sound like autodicover is failing try to put the name of a CAS server in eg

https://servername/ews/exchange.asmx

You will also need to be using an account that has been granted impersonation rights and click the impersonation button or has been give Delegate access to all the accounts you trying to report on.

Cheers
Glen

Anonymous said...

Hi Glen

I have the same problem as Neil, script run but I get 0 under unread, received or sent. This the information I urently need to report on. I am very new to Powershell so I just wondering if you know of any other one line commands I can run to just report on Unread messages?

Thank you so much

Nel

Glen said...

Check the permissions this script needs to be running under an account that has been delegated client rights to the mailboxes in question or using and account that has been given EWS impersonation rights (and then selecting the use impersonation option). Just delegate Exchange Admin rights wont give you access to the a mailbox.

Cheers
Glen

Anonymous said...

Hi Glen

Just wanted to say thank you got it working setup impersonation properly also had to add Default security certificate - Trusted Root Certification Authorities.

Many Thanks

Nel

Pérsio Hartmann said...

Glen!

Just a stupid question: How to I give impersonation rights to EWS?

Thanks in advance,

Pérsio

Alaa elmahdy said...

http://msdn.microsoft.com/en-us/library/bb204095.aspx

Anonymous said...

Hi,

I would like to use this script but when i execute it i get:

You cannot call a method on a null-valued expression.
At C:\temp\unreadgui.ps1:91 char:27
+ $msgList = $ewc.FindItems <<<< ($fldarry, $drDuration, $null, "")
+ CategoryInfo : InvalidOperation: (FindItems:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

Anonymous said...

Hi,

For some accounts i get all the info but for some other i get:

dedProperties, String mtMessageType)
Error During FindItem request : The specified object was not found in the store.
EWSUtil.FindItemException: Exception of type 'EWSUtil.FindItemException' was thrown.
at EWSUtil.EWSConnection.FindItems(BaseFolderIdType[] biArray, Duration duDuration, BasePathToElementType[] exExtened

How is this possible ?
thanks, Verus.

Glen said...

It sounds like a permissions issue are you running it using the impersonation option ?. To use this script you would need to configure EWS impersonation on the account you running the script from or you will need to assign the account access to all the mailboxes on the server eg equivalent to a blackberry. Note just using an account that has administrative rights wont work because these account are specifically denied access to every mailbox other then their own.

Cheers
Glen

Ben said...

This is probably a stupid question, but how can I determine the correct URL for EWS? I know the one we use for OWA - https://server/owa - but using this returns empty unread message counts. Is the EWS address different? The error is:

System.Net.WebException: The request failed with HTTP status 440: Login Timeout.

Glen said...

The EWS URL is https://servername/ews/exchange.asmx

Cheers
Glen

Anonymous said...

Also don't get Unread, Received or Sent detail...
I am logged on Exchange 2007 SP1 as Domain Administrator and tried to get results for Administrator's mailbox...
Tried nearly every kind of impersonation, mailbox and ad-permissions...

Rich said...

Hi!

This would be awesome if i could get it running.. I've filled out all boxes (not checking EWS) and using a blackberry account we use to logon to all maibloxes here. I get the following error

EWSUtil.FindItemException: Exception of type 'EWSUtil.FindItemException' was thr
own.
at EWSUtil.EWSConnection.FindItems(BaseFolderIdType[] biArray, Duration duDur
ation, BasePathToElementType[] exExtenededProperties, String mtMessageType)
Error During FindItem request : The specified object was not found in the store.

thoughts? Exchange 2007 SP1, ive put the .dll in c:\temp as instructed.

Khan Saheb said...

Thanks man. Really helpful. There is just no easy way to get unread count for any user in Exchange.

Anonymous said...

Legend!!! took a while to understand what to do to get it going. but beautiful once it does

Mark Cousens said...

Hi Glen,

This looks really useful, thanks!

I have a couple of questions if you don't mind..Is there a way to restrict the script to running on a particular set of mailboxes, maybe read from a CSV file?

We've been asked to provide the date the last read email was opened - can you help add this to the script?

Thanks again!
Mark

Roareth said...
This comment has been removed by the author.