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:
- Creating Graphical Reports for Exchange 2007 (Part 1)
- Creating Graphical Reports for Exchange 2007 (Part 2)
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?
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
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.
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!
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).
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):
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.
Figure 28: Mailbox Size Summary Reporting GUI
Figure 29: Mailbox Size GUI