Visual Email Capacity Management and Monitoring (Part 1)

• Download Source file (VB,XML and EWS Library) - 418.8 Kb

Introduction

I had recently got a requirement in which the managers would want to see all the emails in several mailboxes, the agent that is responsible for each mailbox, emails that came in today, and everything that came in before today.

The main reason for this was to manage the capacity and resources working on those emails. This would include monitoring and escalation of non-worked emails if it sits in there for a long time. Well, that’s not part of the development requirement. For starting with the project, I had to come up with a solution that is fast and comes really close to counting the emails on real time. So there are two parts we need for this to work.

Using the code

1. A Windows based app that would pull/ count emails from the email servers and upload these numbers into an SQL database.
2. A webpage that would show these number in a graphical (or any other format) that is required.

There are 30 mailboxes that this app has to go through and get the count from All the email boxes are on an 2010 Exchange server. First, get a domain ID (username) that has access to all these email boxes.

Set up a SQL database with the below columns

Columns required: view_no (int), uname (varchar), co_code (varchar), pending (int), ageing (int).

• view_no is to split the display on the webpage later into 2 sets, so that the data is clearer on a webpage.
• uname would be the name of the user that handles that mailbox.
• co_code would be the abbreviated name of the email box( or you can keep the full name of the mailbox itself) this is used as a map from the Windows app to the SQL Server database.
• pending would be the number of emails that came in today.
• ageing would be the number of emails that are greater than 24 hours.

Fill the view_no, uname and co_code with the relevant data. I.e., if you have 40 mailboxes to retrieve data from then give the first 20 view_no as "1" and the rest 20 as "2". If you would want to show all 40 in the same graph or chart or table in the webpage, put "1" for all the 40 mailboxes. The co_code should be filled with the name that you would specify in the <DatabaseTable>...</DatabaseTable> tag in the mapping file which is EmailSettings.xml which is explained below. uname should be filled with the user's display name.

Creating the Windows app

You would need the Microsoft Exchange Web Services library (EWS) which can be downloaded from the Microsoft website. Start a new windows project from Visual Studio. From the Project menu --> Add Reference menu, browse and select the downloaded "Microsoft.Exchange.WebServices.dll" library.

Next add in the following Imports statements.

Imports System.Net.Security
Imports System.Security.Cryptography.X509Certificates
Imports Microsoft.Exchange.WebServices
Imports Microsoft.Exchange.WebServices.Data
Imports System Imports System.Data
Imports System.Data.SqlClient
Imports System.Linq
Imports System.Collections.Generic
Imports System.Xml.Linq
Imports System.IO

For a faster approach, go into you project folder, which is normally located in the "C:\Documents and Settings\USERNAME\My Documents\Visual Studio 2010\Projects\MYMAILPROJECT\MYMAILPROJECT".

Create a bin folder in here and then a debug folder inside the bin folder, these folders would be created by VS once you build the project as well. In the bin folder create two xml files as below:

Configuration.xml (with the below details inside the file):

<Configurations>
   <Configuration>
     <RefreshInterval>5</RefreshInterval>
   </Configuration>
</Configurations>

The number between <RefreshInterval>…</RefreshInterval> would indicate the interval in minutes that the tool should go through all the email IDs. It's preferable to put in a 5 minutes or so. You can try with a lower number (anything above 0) and see if the application breaks down if it does, then increase up the time here.

EmailSettings.xml (with the below details inside the file)

<EmailsMapping>            
    <EmailMap>    
        <Email>ADDRESS1@MAIL.COM</Email>    
        <DatabaseTable>Mail1</DatabaseTable>    
    </EmailMap>
    <EmailMap>
        <Email>ADDRESS2@MAIL.COM</Email>
        <DatabaseTable>MAIL2</DatabaseTable>
    </EmailMap>
    <EmailMap>    
        <Email>ADDRESS3@MAIL.COM</Email>
        <DatabaseTable>MAIL3</DatabaseTable>
    </EmailMap>
</EmailsMapping>

Fill the above with as many email address as you want with each email ID inside the <Email>...</Email> tag and their relevant name in the SQL database inside the <DatabaseTable>…</DatabaseTable> tag. That's it, you're done with letting the app know the email ids that are required.

Now back to creating the Windows app. There should be a subroutine that lets the app get the email list from the XML file that should be processed.

Private Sub LoadEmailList() 
    If File.Exists(Application.StartupPath + "\EmailSettings.xml") Then 
        'load from settings 
        Dim xdoc As XDocument = XDocument.Load(Application.StartupPath + "\EmailSettings.xml") 
        Dim mapping = From map In xdoc.Descendants("EmailMap") 
        Select New With { _ 
        .EmailAddress = map.Element("Email").Value, _ 
        .DatabaseTable = map.Element("DatabaseTable").Value _ 
        } 
        ReDim mailboxes(mapping.Count - 1, 2) 
        For m As Integer = 0 To mapping.Count() - 1 
            mailboxes(m, 0) = mapping(m).EmailAddress.Trim 
            mailboxes(m, 1) = mapping(m).DatabaseTable.Trim 
        Next 
   End If 
End Sub

The above code uses LINQ. Each mailbox is taken along with its mapping name in the sql and loaded into an array, i.e., in this case ReDim mailboxes. The next sub is to get the interval time for the timer to execute.

Private Sub LoadConfiguration()
    If File.Exists(Application.StartupPath + "\Configuration.xml") Then
     'load from settings 
     Dim xdoc As XDocument = XDocument.Load(Application.StartupPath + "\Configuration.xml") 
     Dim config = From con In xdoc.Descendants("Configuration") 
            Select New With { _ 
     .RefreshInterval = con.Element("RefreshInterval").Value} 
     For Each c As Object In config 
        Timer1.Interval = (c.RefreshInterval * 60 * 1000) 
     Next 
    End If 
End Sub

As you can see, the value mentioned in the <RefreshInterval> is taken and converted to milliseconds (c.RefreshInterval * 60 * 1000) as VS timer accepts interval as milliseconds.

In the page load section call these two subs.

Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load 
    LoadEmailList() 
    LoadConfiguration() 
End Sub

We are getting somewhere now.

For the UI of the Form itself, it would look something like the below, or you can throw in your creativity.

The controls and their names as used in the code behind file are mentioned in the image.

• UID, PWD, and Error List are just labels that would not be used in the code as such.
• lbl_status would show messages like “Process Busy”, “Completed” etc
• lbl_runtime would show the total times the app has executed and there by went through a full cycle of obtaining the info on the emails and their count and updating them in the sql database.
• txt_uid would be the user name of the Domain user that has access to all the other email boxes that would be searched through.
• txt_pwd, well the password of the above user.
• txt_details would show the name of the email box that is currently being read and the number of mails in it. This is just to cross check and see if the app is running.
• txt_err shows any error messages that might occur during runtime
• Add a Timer control and set Enabled to False
• Add a BackgroundWorker control and set the WorkerReportsProgress and WorkerSupportsCancellation to True.

Back to the code-behind file. Declare these variables:

Public Class Form1 
Dim sqlins As String 
Dim dbconn As New SqlConnection 
Dim dbcomm As SqlCommand 
Dim dbpath1 As String = "Server=SERVERNAME;Database=DB_NAME;Trusted_Connection=True;" 
Dim run_time As Integer 
Dim mailboxes(,) As String

If you are using SQLEXPRESS then the dbpath1 should be as below:

Dim dbpath1 As String = "Server=SERVERNAME\SQLEXPRESS;Database=DB_NAME;Trusted_Connection=True;"

We would be using the Backgroundworker control to run the main part of the code. The Backgroundworker cannot access the labels or textboxes of the Form directly, for this, you should use delegation as below.

 Delegate Sub SetLabelText_Delegate(ByVal [Label] As Label, ByVal [text] As String) 
Private Sub SetLabelText_ThreadSafe(ByVal [Label] As Label, ByVal [text] As String) 
    If [Label].InvokeRequired Then 
        Dim MyDelegate As New SetLabelText_Delegate(AddressOf SetLabelText_ThreadSafe) 
        Me.Invoke(MyDelegate, New Object() {[Label], [text]}) 
    Else 
        [Label].Text = [text] 
    End If 
End Sub

The above code is to delegate text to the label controls. The below code is to delegate text to the textbox controls.

Delegate Sub SettextboxText_Delegate(ByVal [Textbox] As TextBox, ByVal [text] As String) 
Private Sub SettextboxText_ThreadSafe(ByVal [Textbox] As TextBox, ByVal [text] As String) 
    If [Textbox].InvokeRequired Then 
        Dim MyDelegate1 As New SettextboxText_Delegate(AddressOf SettextboxText_ThreadSafe) 
        Me.Invoke(MyDelegate1, New Object() {Textbox, [text]}) 
    Else 
        [Textbox].Text = [text] 
    End If 
End Sub

Next, add in the function for the Clear button.

Private Sub btn_clear_Click(sender As System.Object, e As System.EventArgs) Handles btn_clear.Click 
    txt_details.Clear() 
    txt_err.Clear() 
    lbl_status.Text = "" 
    lbl_runtime.Text = "" 
End Sub

Then codes for the Start and Stop buttons.

‘////STOP BUTTON
Private Sub btn_stop_Click(sender As System.Object, e As System.EventArgs) Handles btn_stop.Click
    'is the background worker doing some work?
    Timer1.Stop()
    If BackgroundWorker1.IsBusy Then
        'if it supports cancellation, cancel it
        If BackgroundWorker1.WorkerSupportsCancellation Then
            'tell the background worker to stop working
            BackgroundWorker1.CancelAsync()
            Timer1.Stop()
        End If
    End If

    'enable the start button
    Me.btn_start.Enabled = True
    'dissable the stop button
    Me.btn_stop.Enabled = False
End Sub

‘////START BUTTON
Private Sub btn_start_Click(sender As Object, e As System.EventArgs) Handles btn_start.Click
    'dissable the start button
    Me.btn_start.Enabled = False
    Me.lbl_runtime.Text = 0
    'enable the stop button
    Me.btn_stop.Enabled = True
    txt_err.Text = ""
    Timer1.Enabled = True

    Timer1.Start()

    'start the background worker working
    BackgroundWorker1.RunWorkerAsync()
End Sub

For the next part of the code to work make sure that WorkerReportsProgress and WorkerSupportsCancellation of the BackgroundWorker are set to True.

Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, _
            e As System.ComponentModel.RunWorkerCompletedEventArgs) _
        Handles BackgroundWorker1.RunWorkerCompleted
    If e.Cancelled Then
        Me.lbl_status.Text = "Cancelled"
    Else
        Me.lbl_status.Text = "Completed"
        If Not dbconn.State = ConnectionState.Closed Then
            dbconn.Close()
        End If
    End If
End Sub

For the above, you can build up something to update a progress bar as well. This is not covered in this article. Now for the main part of the code that would do all the work, the BackgroundWorker_DoWork.

Private Sub BackgroundWorker1_DoWork(sender As Object, _
          e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
    'has the backgroundworker been asked to stop?
    If BackgroundWorker1.CancellationPending Then
        'set cancel to true
        e.Cancel = True
        Exit Sub
    End If
    SetLabelText_ThreadSafe(Me.lbl_status, "...Started..")
    Try '//A
        dbconn = New SqlConnection(dbpath1)
        dbconn.Open()
    Catch ex As Exception
        SettextboxText_ThreadSafe(Me.txt_err, ex.Message.ToString & " A")
    End Try
    Dim myexchange As New ExchangeService(ExchangeVersion.Exchange2010)
    Dim myversion As New ExchangeVersion
    myexchange.Credentials = New NetworkCredential(txt_uid.Text, txt_pwd.Text)
    myexchange.Url = New Uri("https://SERVER.DOMAIN.COM/EWS/Exchange.asmx")
    Dim inboxfolder As New Folder(myexchange)

    Dim mb As Mailbox
    Dim fid1 As FolderId
    SettextboxText_ThreadSafe(txt_details, "")
    Dim bound0 As Integer = mailboxes.GetUpperBound(0)
    Dim bound1 As Integer = mailboxes.GetUpperBound(1)
    Dim emailid As String
    Dim cntryname As String

    For i As Integer = 0 To bound0
        emailid = mailboxes(i, 0)

        cntryname = mailboxes(i, 1)
        mb = New Mailbox(emailid)


        Dim iv_old As ItemView = New ItemView(999)
        Dim oldmail As Integer
        Dim newmail As Integer

        Dim filters_old As SearchFilter = New SearchFilter.IsLessThan(_
              ItemSchema.DateTimeReceived, DateTime.Today.AddHours(-24))
        Dim olditems As FindItemsResults(Of Item) = Nothing
        Try
            fid1 = New FolderId(WellKnownFolderName.Inbox, mb.Address)
            inboxfolder = folder.Bind(myexchange, fid1)

            iv_old.Traversal = ItemTraversal.Shallow

            olditems = myexchange.FindItems(inboxfolder.Id, filters_old, iv_old)
            oldmail = olditems.Items.Count
            iv_old = Nothing

            sqlins = "UPDATE tbl_TABLENAME SET ageing = " & oldmail & " WHERE co_code = '" & cntryname & "'"
            dbcomm = New SqlCommand(sqlins, dbconn)
            dbcomm.ExecuteNonQuery()
        Catch ex As Exception
            SettextboxText_ThreadSafe(Me.txt_err, Me.txt_err.Text & _
                 ex.Message.ToString & cntryname & " : C " & "| TRYING AGAIN" & vbNewLine)

        Finally

        End Try

        Dim iv_new As ItemView = New ItemView(999)
        iv_new.Traversal = ItemTraversal.Shallow
        Dim filters_new As SearchFilter = _
            New SearchFilter.IsGreaterThanOrEqualTo(ItemSchema.DateTimeReceived, DateTime.Today.AddHours(-24))
        Dim newitems As FindItemsResults(Of Item) = Nothing
        newitems = myexchange.FindItems(inboxfolder.Id, filters_new, iv_new)
        newmail = newitems.Items.Count
        iv_new = Nothing

        Try
            sqlins = "UPDATE tbl_TABLENAME SET pending = " & newmail & " WHERE co_code = '" & cntryname & "'"
            dbcomm = New SqlCommand(sqlins, dbconn)
            dbcomm.ExecuteNonQuery()

        Catch ex As Exception
            SettextboxText_ThreadSafe(Me.txt_err, Me.txt_err.Text & _
              ex.Message.ToString & cntryname & " : C " & "| TRYING AGAIN" & vbNewLine)
        Finally

        End Try
        SettextboxText_ThreadSafe(Me.txt_details, txt_details.Text + emailid + " : " + _
          cntryname + " : " + newmail.ToString + " : " + oldmail.ToString + vbNewLine)

    Next
    mb = vbNull
    inboxfolder = Nothing
    fid1 = Nothing
    If dbconn.State = ConnectionState.Open Then
        dbconn.Close()
    End If
    SetLabelText_ThreadSafe(Me.lbl_runtime, lbl_runtime.Text + 1)
End Sub

In the SearchFilter mentioned above you can put in your own search criteria.

The code is very basic so please modify it to suit your needs. Displaying it on a webpage is fairly simple, however, if any one would require it please let me know, that can be the Part 2 of this article.

Points of interest

During the initial stages, I did get into trouble trying to get EWS to work properly. There is always a missing factor, either Permissions or some wrong usage of code or in one case the email server was down and I was breaking my head thinking it was the problem with the coding. So have a coffee while any of these things happen, sit back and relax.