Creating Graphical Reports for Exchange 2007 (Part 3)

If you would like to be notified of when Rui Silva releases the next part in this article series please sign up to our MSExchange.org Real-Time Article Update newsletter.

 

If you would like to read the other parts in this article series please go to:

 

 

 

 

Introduction

 

Having taken a look at Log Parser, we will now take a look at other tools freely available on the Internet that can produce graphical reports for Exchange.

 

But before that, there are a couple of points that I would like to discuss on the use of Log Parser:

 

 

  • How can I gather data from multiple servers?
  • How can I generate these reports automatically?

 

Multiple Servers

 

So far I always assumed that the logs consumed by Log Parser originated in only one server. Quite probably, in production environments, this is not the case as most companies have multiple servers due to high-availability and workload balancing.

 

So, the question is; how can I collect and aggregate information from multiple logs? There are a couple of different ways of doing it, such as:

 

 

  • You can copy all the logs to a central location beforehand and then run Log Parser against that repository (beware of duplicate file names!)
  • You can programmatically concatenate all the logs in a huge single file, using your favorite scripting language
  • You can run a multi-step Log Parser batch file, collecting information from the multiple servers

 

I will show you how to handle the job using the last option: a multi-step Log Parser batch file. Let us use the last chart we built from the IIS Logs, the one that showed OWA Activity along the hours of the day.

 

Suppose we have 2 Exchange CAS servers, SERVER01 and SERVER02. The Log Parser query to extract information from both servers would look something like this:

 

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT TO_STRING(time, 'HH') AS Hour, COUNT(*) AS Hits INTO AggregateOWALog.log FROM \\SERVER01\C$\Inetpub\logs\LogFiles\W3SVC1\u_ex*.log GROUP BY Hour ORDER BY Hour ASC" -i:IISW3C -o:TSV -headers:Auto -filemode:1

“C:\Program Files (x86)\Log Parser 2.2\logparser.exe” “SELECT TO_STRING(time, ‘HH’) AS Hour, COUNT(*) AS Hits INTO AggregateOWALog.log FROM \\SERVER02C$\Inetpub\logs\LogFiles\W3SVC1\u_ex*.log GROUP BY Hour ORDER BY Hour ASC” -i:IISW3C -o:TSV -headers:Auto -filemode:0

“C:\Program Files (x86)\Log Parser 2.2\logparser.exe” “SELECT Hour, Sum(Hits) AS TotalHits INTO OWAAggregate.gif FROM AggregateOWALog.log GROUP BY Hour ORDER BY Hour ASC” -i:TSV -o:CHART -chartType:AreaStacked3D -chartTitle:”Hourly Hits” -view:ON -legend:OFF -categories:ON

 

Note:

 

The first 2 Log Parser commands extract the data into a TSV (Tab Separated Values) file. We then use the output file, AggregateOWA.log, to generate the chart depicted in Figure 24 in the last step.

 

The first Log Parser command writes the AggregateOWA.log file in overwrite mode (-filemode:1), thus guarantying any previous aggregations are overwritten. The second command then uses append mode (-filemode:0), so that all the previous generated data is kept.

 

OWAAggregate.gif
Figure 25: Aggregate OWA Usage

 

As you see, is not difficult to collect and aggregate data from different servers. You may have to modify the previous Log Parser queries a little bit, but, by now, I’m sure you have all the reverse engineering skills to do the job!

 

Task Schedule

 

Depending of the quantity of logs that will be parsed and analyzed, the querying process can take quite a while. Furthermore, the list of reports to generate can also be long. So, how can one automate the processing of the Log Parser commands? Well, the answer is easy: Windows Task Scheduler.

 

Start by creating batch files with all the desired individual reports, and then create a new one that will orchestrate the launching of each report. This orchestrator batch file will look something like this:

 

START /WAIT AgentAcceptedRejected.bat

START /WAIT AgentReasonSpread.bat

START /WAIT Devices.bat

START /WAIT EAS-user-activity.bat

START /WAIT OWA-user-activity.bat

START /WAIT radar-send.bat

START /WAIT suspicious-senders.bat

START /WAIT top-users-internal-senders.bat

START /WAIT TopExternalReceiversCombined.bat

START /WAIT TopReceiversCombined.bat

START /WAIT TopRejectionErrors.bat

START /WAIT total-bytes-send-receive.bat

[…]

 

To generate the graphical reports every day, all it takes is to schedule the previous batch file to run every night (Figure 26).

 

21-task-scheduler.png
Figure 26: Task Scheduler

 

As a suggestion, you can even aggregate all the reports in an HTML page, published on your local intranet, using your preferred scripting language. I would not list the necessary code, because it is outside the scope of this article, but I think you will find it pretty easy to do.

 

Glen’s Cool Graphical Report Scripts

 

Glen Scales is an Exchange Server MVP that spends most of his free time writing code (mostly PowerShell) to be used with Microsoft Exchange. One of my favorites is this fantastic piece of code, the Exchange 2007 Content Agent Log Message Tracker Gui, which allows you to easily find any message that went through the Content Filtering Agent.

 

Although it is possible to manage the antispam agent logs using plain PowerShell, as we will see later on. For those of you who do not like the command line, the script from Glen Scales can really be a life safer.

 

Here is how the GUI looks like (Figure 27):

 

cagenttracker-big.jpg
Figure 27: Content Agent Log Message Tracker GUI

 

If you download the script and save it at “%ProgramFiles%\Microsoft\Exchange Server\Scripts\”, you can create a shortcut to run it, using this command:

 

%windir%\system32\windowspowershell\v1.0\powershell.exe -PSConsoleFile “C:\Program Files\Microsoft\Exchange Server\bin\exshell.psc1” -command “. ‘C:\Program Files\Microsoft\Exchange Server\Scripts\cagentTracker.ps1′”

 

But Glen’s fantastic work does not end here. Check out these neat scripts (descriptions taken from Glen’s blog):

 

 

  • Mailbox Size Summary reporting Gui for Exchange 2007 (Figure 28) – Report on mailbox usage not just based on particular users, but usage by particular OU or other User property, e.g. Department, Office, etc.

     

  • Version 5 of the Mailbox Size Gui Script for Exchange 2007 (Figure 29) – When the script runs, now it will create a folder on the c drive called mbsizehistory, it will then record the results of each mailbox size query you make to a csv file in this directory with a serial date as the file name along with the servername. It will only create one file per day and per server you run it against. Basically, every time you run this script it will create this file, so if you run it once a week or one a day or every three days you will start collecting adhoc history data about your mailbox size growth.

     

  • WizBang Exchange 2007 Message Tracking Powershell Gui Version 1 (Figure 30) – Unlocking the secrets from the depths of the Message Tracking Logs is an ever recurring theme on this blog and, in general, an important area of Exchange Server Management. In this latest incarnation we boldly go where no Message Tracking application has gone before as well as the normal aggregation, graphing and exports bits and pieces.

     

  • Exchange Server Store Size Report (Figure 31) – A Report that will first go out and grab the mailbox/public folder store sizes, then grab the Whitespace, RetainedItems sizes and retained deleted Mailbox sizes and put them into a Datagrid and display them in a Winform. Also following on from last week we can then graph this data. The first graph is a pie graph that shows the percentage of space used across all stores on the server. Then the second graph is a stacked bar chart that compares all the parameters that where measured across all stores on a server.

 

15-mailbox-group-size.png
Figure 28: Mailbox Size Summary Reporting GUI

 

13-mailbox-size.png
Figure 29: Mailbox Size GUI

 

14-message-tracking-gui.png
Figure 30: Message Tracking GUI

 

16-server-store-size.png
Figure 31: Exchange Server Store Size Report

 

What about PowerShell?

 

What about good old plain PowerShell, is the question you may ask. To start with, Exchange Server includes some pre-built PowerShell scripts (located at %ProgramFiles%\Microsoft\Exchange Server\Scripts\ by default) that can generate human readable output from the Agent logs. Here is the list:

 

 

  1. Get-AntispamFilteringReport.ps1: Gets the top 10 (by default unless top parameter is specified) sources (i.e. agents) which are responsible for either rejecting connections, commands or for rejecting /deleting / quarantining a message.

     

  2. Get-AntispamSCLHistogram.ps1: Gets all entries for the Content Filter and groups by SCL values (Figure 32).

    19-get-antispamSCLhistogram.png
    Figure 32: Get-AntispamSCLHistogram

     

  3. Get-AntispamTopBlockedSenderDomains.ps1: Gets the top N sender domains that were blocked by antispam agents.

     

  4. Get-AntispamTopBlockedSenderIPs.ps1: Gets the top N sender IPs that were blocked by antispam agents.

     

  5. Get-AntispamTopBlockedSenders.ps1: Gets the top N senders that were blocked by antispam agents.

     

  6. Get-AntispamTopRBLProviders.ps1: Gets the top N reasons for rejection by blocklist providers.

     

  7. Get-AntispamTopRecipients.ps1: Gets the top N recipients that were rejected by antispam agents.

 

Since PowerShell does not natively support graphical outputs, there are some additions available freely on the Internet, such as PowerBoots, Visifire or ZedGraph. Using these add-ons one can build great charts and reports, but I decided to leave such examples for a next time 🙂

 

ExInsight

 

During my search on the Internet for some free Exchange reporting tools, I found ExInsight for Microsoft Exchange, a nice piece of software developed by Bitrunes.

 

ExInsight is a free monitoring and statistics tool that provides a real-time view of an Exchange environment and the inner workings of MAPI, OWA, POP3, IMAP4, NNTP and other protocols, allowing you to evaluate individual user’s usage of Exchange Server.

 

The program’s main console can show you a nice statistics dashboard, like the one depicted in Figure 33, but if you desire a report with cool charts (Figure 34), from the Statistics window select File and then Save As.

 

ExInsight_Stat.jpg
Figure 33: ExInsight Statistics

 

ExInsight_Report.jpg
Figure 34: ExInsight Statistics Report

 

Conclusion

 

Hopefully, by the end of this series, you will no longer say that the only way of generating appealing graphic reports from Exchange Server 2007 is buying a 3rd party product from some of the plenty Microsoft partners out there. Almost 20 Log Parser reports and some free tools on the Internet are an invaluable addition to the toolbox of every Messaging Administrator. Since there are no significant changes on the Exchange Server 2010 logging features, I would say that most (or all) of these reports will still work on the next version of the e-mail server from Microsoft.

 

Related Links

 

 

 

If you would like to be notified of when Rui Silva releases the next part in this article series please sign up to our MSExchange.org Real-Time Article Update newsletter.

 

If you would like to read the other parts in this article series please go to:

 

 

 

 

About The Author

Leave a Comment

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Scroll to Top