Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Awesome, Richard! Thanks for sharing. I've been fairly evasive of PowerShell, but these look like really useful scripts and a great use of the facilities provided by Microsoft. I'll give them a shot! –  Evan Anderson Jul 3 '09 at 11:34
    
Fortunatly for me I am a programmer (at heart) employed as a Sys. Admin. so I enjoyed putting these scripts together, I have been shying away from PowerShell for a long time, this is the first project that it has really made sense for. –  Richard Slater Jul 3 '09 at 13:38
add comment

2 Answers

up vote 1 down vote accepted

I'm not aware of any off-the-shelf program that does what you're talking about. You could script various data gathering mechaisms and report on that data however you see fit, but you're talking about a fairly "custom" solution.

  1. You could get this from "Message Tracking" logs. The logfiles are ASCII text, and the various event ids are listed here: http://support.microsoft.com/kb/821905 I generally run w/ "Message Tracking" enabled in all my production installs, anyway, just because it's too handy not to have enabled. You do take a slight performance hit with it enabled, but I think it's more than worth it.

  2. This could be scripted. You'd need to run the script as a user that has rights to open every user mailbox. (You could remove the annoying "Deny - Receive As" ACEs placed at the root of the organization, but be aware that service packs and updates could restore them. I always remove these annoying ACEs anyway-- an "Administrator" should be able to open any mailbox.) This would kinda be a fun script to write, but I don't have time today. Users could create server-side rules that would divert unread messages into other folders, so this might not give you an accurate metric.

  3. You're going to have to parse the security log on the Exchange Server computer(s) for that. If you want to ignore the "logons" from Backup Exec you'll need to do that there, too. (Why is Backup Exec "logging-on", anyway? Are you doing a "brick level" backup? Ick... I avoid those at all costs. If I need to restore an item in E2K8, I just restore a database page-level backup to an RSG.) The "last logon" attribute that the Information Store maintains is single-valued, so the only other way to get this, aside from parsing the security log, would be to "poll" that value. That would be highly inefficient.

If you haven't thought about it, I'd track mailbox size and number of items (to compute average size per item). I've caught "abuse" of "precious" Exchange IS space this way in the past. Now that E2K3 Standard has a 72GB store limit that's not such a big issue. Even so, it can tell you things about your users usage patterns.

Sounds like this would be a fun system to put together!

share|improve this answer
    
Right, I have managed to pull total sent and total received per user from the Message Tracking logs using Log Parser 2.2, working on sending it to SQL 2005 then probably going to export it to Excel whenever someone asks for the stats. BackupExec logs to do a brick-level backup yes so that we can use the Granular Recovery method. BackupExec 12 doesn't support restore to RSG for Exchange 2003 (go figure!). –  Richard Slater Jul 1 '09 at 17:16
    
I'll have to check that our re: Backup Exec 12 and the lack of RSG support. I use RSGs whenever I need to restore individual items (which is fairly infrequently, thankfully) and I'd miss that feature! –  Evan Anderson Jul 1 '09 at 23:13
    
I was pretty annoyed that it wasn't possible too, but on Symantecs side the Check box does say (Requires Exchange 2007 or above). The media server is going to be de-comissioned and replaced with a new server with BackupExec 12.5 for SQL 2008 support. –  Richard Slater Jul 2 '09 at 14:30
add comment

I don't know if you can do all you want but there are a variety of was to create scripts to extract data from Exchange. In my case I'm only interested in the number of messages and the total size of each mailbox. A Perl script that runs each night gathers that information and logs it into a MySQL database. It then uses the data in the database to generate an Excel spreadsheet with graphs for each mailbox, plus the total. All that was done from examples I found on the Internet. There are no doubt commercial offerings to do similar but an hour or two scripting is more cost effective (for me) and gives me an open ended solution I can modify or add to as required.

share|improve this answer
    
Thanks John, Could you give me any more details of how your Perl script pulls information from Exchange? WebDAV or is there another way? –  Richard Slater Jul 1 '09 at 17:12
1  
I've sanitized one script for you to have, which you can build on as required. The code is not pretty, being cobbled together as it was. The Excel stuff actually started life as a VBS script. gardeniers.com/files/ChartMailboxes.zip –  John Gardeniers Jul 2 '09 at 5:06
    
Thanks that is helpful, I am making headway in getting it working in PowerShell 1.0. Will post up more when I am done. –  Richard Slater Jul 2 '09 at 14:33
    
I wish I could accept two answers for this one, both answers have been very helpful in finding a solution for me but Evan's response was the most complete. –  Richard Slater Jul 3 '09 at 9:30
    
Thanks for the sentiment Richard. Just happy I had something to offer. I'll also be taking a good look at Evan's suggestions and will no doubt use at least some of it. –  John Gardeniers Jul 3 '09 at 11:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.