How to monitor usage in Exchange 2003?

We have been tasked to monitor the usage of Exchange 2003, there doesn't seem to be a reporting component built into Exchange 2003 Standard. Does this mean using third party reporting services or can I use event sinks or logs to spit out usage data to SQL Server for delayed processing?

The areas I would like to know about most are:

1. Number of messages sent/received by user and as a whole.
2. How many unread messages in users inbox.
3. Logon times (note: BackupExec "logs on" to mailboxes)

I am also open to suggestions of good metrics to measure end-user uptake of features, so perhaps number of Contacts, Calender Items, Meeting Requests, Notes, etc in the store.

Solution

I elected to use PowerShell to collect statistics from Exchange as moving forwards to Exchange 2007 and PowerShell 2.0 there are more options for collecting data, and I can build upon the existing foundations.

The script runs at 0400 every day and relies on a SQL 2005/2008 server and LogParser being installed on the a server with access to the Exchange Message Tracking Logs.

Number of Messages Sent/Received

I built the command line using LogParser.exe then transposed it into the COM object that I use within the powershell script in the following function:

function Execute-LogParserQueryToSQL([string] $Query)
{
    Write-Host $Query

    $LogParser = New-Object -com MSUtil.LogQuery

    $Input = New-Object -comObject MSUtil.LogQuery.W3CInputFormat

    $Output = New-Object -comObject MSUtil.LogQuery.SQLOutputFormat
    $Output.server = "<your server>"
    $Output.database = "<your database>"
    $Output.username = "<your username>"
    $Output.Password = "<your password>"

    $Result = $LogParser.ExecuteBatch($Query, $Input, $Output)

    return $Result
}

The function to loop through any logs created yesterday or before (can do multiple in case it fails to run one day for some reason) then deletes the log file. If you are using message tracking for any other purpose don't delete the log file, use some other mechanism for "marking it as used".

function Execute-SentReceivedSummary()
{
    $TodaysLog = ("{0}.log" -f,(Get-Date -f yyyyMMdd))
    $MessageTrackingDir = "D:\Exchange\Logs\PORSCHE.log"
    $LogsToParse = Get-ChildItem -Path $MessageTrackingDir
    $SentEmailQuery = "SELECT Date,Sender-Address AS Account,Count(*) AS Count INTO DailySentEmailByUser FROM '{0}' WHERE Event-ID=1027 GROUP BY Sender-Address,Date"
    $ReceivedEmailQuery = "SELECT Date,Recipient-Address AS Account,Count(*) AS Count INTO DailyReceivedEmailByUser FROM '{0}' WHERE Event-ID=1028 GROUP BY Recipient-Address,Date"

    foreach ($Log in $LogsToParse)
    {
        if ($Log.ToString() -ne $TodaysLog)
        {
            $Query = ($SentEmailQuery -f,$Log.FullName)
            Execute-LogParserQueryToSQL $Query

            $Query = ($ReceivedEmailQuery -f,$Log.FullName)
            Execute-LogParserQueryToSQL $Query

            Remove-Item $Log.FullName
        }
    }

    return $true
}

How how many unread messages in user inbox

In the end, we decided that total size and count of items in the mailbox was a more useful metric. Some staff had huge numbers of unread messages but checked their e-mail every day (usualy because they were FYI type e-mails and the subject told them everything they needed to know).

As we only wanted live (albeit up to 24 hours old) I needed to truncate the table before inserting new data:

function Truncate-TotalsTable()
{
    $SqlConnection = new-object system.data.oledb.oledbconnection
    $SqlConnection.connectionstring = "<your connect string>"
    $SqlConnection.open()
    $Query = "TRUNCATE TABLE TotalsTable"
    $SqlCommand = New-Object system.data.oledb.oledbcommand
    $SqlCommand.connection = $SqlConnection
    $SqlCommand.commandtext = $Query
    $SqlCommand.executenonquery()
    $SqlConnection.close()

    return $true;
}

Then we use WMI to pull out the data from the Exchange Server and push them into SQL:

function Execute-MailboxTotalsQuery()
{
    $Result = Truncate-TotalsTable

    $Count = 0;

    $SqlConnection = new-object system.data.oledb.oledbconnection
    $SqlConnection.connectionstring = "<your connect string>"
    $SqlConnection.open()

    $MailboxReport = Get-Wmiobject -class Exchange_Mailbox -Namespace ROOT\MicrosoftExchangev2 -ComputerName <your exchange server>

    foreach ($Mailbox in $MailboxReport)
    {
        $MailboxDN = $Mailbox.MailboxDisplayName
        $TotalItems = [int]$Mailbox.TotalItems
        $TotalSize = [int]$Mailbox.Size

        $MailboxDN = $MailboxDN -replace "'","''"

        $Query = [String]::Format("INSERT TotalsTable Values ('{0}',{1},{2})",$MailboxDN, $TotalItems, $TotalSize)

        $SqlCommand = New-Object system.data.oledb.oledbcommand
        $SqlCommand.connection = $SqlConnection
        $SqlCommand.commandtext = $Query
        $Result = $SqlCommand.executenonquery()
    $Count = $Count + $Result
    }

    $SqlConnection.close()

    return $Count;
}

Logon Times

After using LogParser to look at Security event log, the results we got out of it were not that useful. The Event ID we were looking at was 540 which covered both Outlook Logins and OWA logins (and other logins), we decided the amout of work required to implement this was not worth the return. This is partially because you would need to parse and filter by message body to isolate the different types of login beyond event 540.

I welcome suggestions and submissions of other useful PowerShell scripts.