Communication & Collaboration

Reveal Deeper Meaning: Powerful Exchange Server Analysis with LogParser

Michael Higashi

At a Glance:

• Analyzing Exchange front end server log files
• Using Log Parser commands
• Generating reports via Office Web Components

Download the code for this article: HigashiExchangeLogs2006_10.exe (152KB)

As an IT professional, it's essential that you have access to information about how your users are impacting your servers. This is especially important when it comes to mission-critical

systems, such as e-mail servers. In this article, I will create a series of reports that help you see how users are impacting your Microsoft® Exchange 2003 front-end servers.

The Exchange front-end servers are built on top of IIS. If you look in IIS Manager on your Exchange Server 2003 front end machine, you will see a virtual directory hierarchy. When a client makes a request to one of the Exchange services that reside on an individual virtual directory, the service logs data about the command. These IIS logs contain some interesting information, but trying to read them can be frustrating. Fortunately, Log Parser lets you parse the IIS logs and dissect the info using a SQL-like query language.

The virtual directories that I will focus on in this article are Microsoft-Server-ActiveSync (for Mobile Devices), Exchange (for Outlook Web Access, or OWA), and RPC (for Outlook RPC over HTTP). I'll dive into each of these Exchange Server components and show how you can extract some interesting information to better understand what's going on across your entire Exchange Server 2003 front end. I will discuss five handy reports, showing you the SQL statement, the Log Parser command-line syntax, and the results you can produce for each. Jeffrey A. Johnson, a Senior Systems Engineer at Microsoft.com, helped me create some of the scripts I'll show.

Using Log Parser Commands

To help you make sense of the data that has been logged by your Exchange servers, I will show you how to create SQL queries and Log Parser commands that gather data and output it to a readable format. If you aren't already familiar with Log Parser, this is a powerful tool that offers query access to text-based data. It can be used with a number of data sources, including XML files, the Registry, and Active Directory®. You can download a copy at go.microsoft.com/fwlink/?LinkId=71149.

To format the data you are gathering, you pass in switches (or parameters) when running the Log Parser executable. Microsoft Office Web Components provides the mechanism for creating the actual charts. The Office Web Components are a collection of COM objects that allow you to create charts programmatically. Log Parser has built-in functionality to call the Office Web Component APIs so you do not need any special coding skills. All you need to know is what type of chart you want to create, specify it in your Log Parser command, and—BAM—you've got a pretty chart. Here's an overview of the switches I will use in my examples:

-o:<output-format> This is where you specify whether you want the output to be a CHART, CSV, or the default—NAT (which stands for native format and outputs to the console window).

-ChartType: You use this to specify what type of chart you want to display. To see a full list of available types, enter the following in the command line:

LogParser -h –o:Chart

-values: This lets you control whether the actual values are displayed on the chart.

-categories: Use this to specify whether you want the categories, usually the first column in the table, to be displayed on the chart.

-ChartTitle: This lets you give the chart a title.

-View: This setting lets you automatically see the chart once Log Parser completes processing.

Component Distribution Report

This report provides an overview of what Exchange services are being used the most. When tracked over time, this data can help with capacity planning or trend analysis. Based on these reports, you can put together an automated reporting solution that provides data you can use to examine usage patterns over a longer period of time.

SQL Statement and Log Parser Command

This SQL query deals with a single column in the IIS logs: cs-uri-stem. It uses Log Parser's Extract_Token function to parse the string and get the value after the first /. It also uses TO_UPPERCASE to format the data and force the values to be upper case. Since I know what virtual directories to look for—EXCHANGE, MICROSOFT-SERVER-ACTIVESYNC, and RPC—I can filter for those particular records. To do this, I use the CASE function and evaluate each incoming record to see if it matches any of these three Exchange services. If a record doesn't match, I drop it into the bucket called "other".

Next I need to gather the actual counts for each service. I use the COUNT(*) function, which simply counts all records that come into one of the three buckets.

Copy the SQL statement shown in Figure 1 into a text file and save it as TiSP2_Component_Breakdown.sql. You can use a different name for the file, but just be sure your Log Parser command calls that file name.

Figure 1 TiSP2_Component_Breakdown.sql

SELECT
    CASE TO_UPPERCASE(extract_token(cs-uri-stem,1,'/')) 
        when 'EXCHANGE' then 'Exchange' 
        when 'MICROSOFT-SERVER-ACTIVESYNC' then 'Exchange ActiveSync' 
        when 'RPC' then 'RPC' 
        else 'Other' 
    End as ExchangeComponent ,
    count(*) as Hits, 
    mul(100.0, propcount(*)) as Percentage 

INTO c:\LogParser\Output\TiSP2_Component_BreakDown_Pie3d.gif

FROM c:\Logs\SP2\ex06051423.log 

GROUP BY ExchangeComponent 

ORDER BY hits desc

For this example, I am outputting the results into a three-dimensional pie chart using the Microsoft Office 2003 Web Components. With all the switches included, here's what my command line looks like:

LogParser.exe -i:IISW3C -o:CHART -ChartType:Pie3D -values:OFF  -
categories:On -ChartTitle:"Component Breakdown Report" -view:ON file:
C:\Technet\TiSP2_Component_Breakdown.sql

And the resulting chart will look something like Figure 2.

Figure 2** Charting Exchange Service Usage **(Click the image for a larger view)

OWA Activity Report

This report can help you drill further into OWA activity, letting you see which tasks users perform most frequently.

SQL Statement and Log Parser Command

This SQL statement has a similar construct to the one in the previous example, but this time we are looking for the value following the third "/". This will give us information about what data the user is trying to access from OWA, such as the Inbox, Calendar, Contracts, and so on. For this report, we use the WHERE clause to filter only OWA data. Figure 3 shows the complete SQL statement—place it in a new file named TiSP2_OWA_Activity.sql.

Figure 3 TiSP2_OWA_Activity.sql

SELECT 
    case extract_token(cs-uri-stem,3,'/') 
        when 'Inbox' then 'Inbox' 
        when 'Calendar' then 'Calendar' 
        when 'Contacts' then 'Contacts' 
        when 'Tasks' then 'Tasks' 
        else 'Other' end as Activity ,
    count(*) as Hits

INTO c:\LogParser\Output\TiSP2_OWA_Activity_Column.gif

FROM c:\Logs\SP2\ex06051423.log 

WHERE cs-uri-stem like '/exchange/%'

GROUP BY activity 

ORDER BY hits desc

Figure 4** Breakdown of OWA Tasks **(Click the image for a larger view)

You can include a configuration file to further customize the chart. The Log Parser output format includes a switch called -config, which lets you point to a jscript or vbscript file, like so:

LogParser -i:IISW3C -o:CHART -values:ON -chartType:Column3D -view:ON
file:c:\Technet\TiSP2_OWA_Activity.sql -config:c :\Technet\MyScript.js

Place the following in a text file and name it MyScript.js:

// Customize title
chartSpace.HasChartSpaceTitle = true;
chartSpace.ChartSpaceTitle.Caption = "Generated by Log Parser 2.2";
chartSpace.ChartSpaceTitle.Font.Size = 6;
chartSpace.ChartSpaceTitle.Position = chartSpace.Constants.chTitlePositionBottom;

// Change the background color
chart.PlotArea.Interior.Color = chartSpace.Constants.chColorNone;

The resulting chart will look something like Figure 4.

OWA Hourly Report

This report breaks activity down into one-hour segments, showing you the OWA activity for each hour. This is handy for determining peak usage times. You can use this data, in turn, to plan for upgrades and server outages.

SQL Statement and Log Parser Command

This report uses the function QUANTIZE to round to the nearest unit—in this case, I round to the nearest 3600 seconds (or hour). I then sum up each activity, such as _Inbox, in the remaining columns of the chart. Copy Figure 5 into a text file named TiSP2_OWA_Hourly_Activity.sql.

Figure 5 TiSP2_OWA_Hourly_Activity.sql

select 
          quantize(to_localtime(to_timestamp(date,time)), 3600) as Hour,
          sum(_Inbox) as Inbox,
          sum(_Calendar) as Calendar,
          sum(_Tasks) as Tasks,
          sum(_Contacts) as Contacts,
          sum(_Other) as Other
using 
          extract_token(cs-uri-stem,3,'/') as _activity,
          case _activity when 'Inbox' then 1 else 0 end as _Inbox,
          case _activity when 'Calendar' then 1 else 0 end as _Calendar,
          case _activity when 'Tasks' then 1 else 0 end as _Tasks,
          case _activity when 'Contacts' then 1 else 0 end as _Contacts,
          case add(add(add(_Inbox,_Calendar),_Tasks),_Contacts) when 0
              then 1 else 0 end as _Other

INTO
           C:\LogParser\Output\TiSP2_OWA_Hourly_Report.csv

FROM
           c:\Logs\SP2\ex06051423.log
where cs-uri-stem like '/exchange/%'
group by Hour
order by Hour

The Log Parser command for this example is quite simple, using the -o:csv switch to output the results into a CSV file, as shown here:

LogParser.exe -i:IISW3C –o:csv file:c:\TechNet\
    TiSP2_OWA_Hourly_Activity.sql

The result is a table similar to the one shown in Figure 6.

Figure 6 An Hourly Report of Usage

Exchange RPC Hourly Activity

This report lets you analyze remote procedure call (RPC) activity by hour. For most Exchange customers, this tends to be the most commonly used component on the Exchange front-end servers. As in the previous example, this breakdown of RPC activity by hour is useful for determining peak usage times and scheduling planned outages.

The SQL statement here is very similar to the one used to break down OWA activity by hour. The only difference is that I have elected to report on the total hits between the client and Exchange front-end server. (Note that you could use this same SQL syntax and tweak it slightly to get reports for Exchange ActiveSync, OWA, or the entire Exchange front-end server.) This time around, I'll output the results into a chart to make the data more digestible and visually interesting.

SQL Statement and Log Parser Command

Again, start by copying the contents of the SQL block shown in Figure 7 into a text file and save the file as TiSP2_RPC_Hourly_Activity.sql.

Figure 7 TiSP2_RPC_Hourly_Activity.sql

SELECT
         quantize(to_localtime(to_timestamp(date,time)), 3600) as Hour,
             Count(*) as [RPC Hourly Hits Report]

INTO
    C:\LogParser\Output\TiSP2_RPC_Hourly_Report.gif

FROM
    c:\Logs\SP2\ex06051423.log

WHERE cs-uri-stem like '/rpc%'
GROUP BY Hour
ORDER BY Hour

The Log Parser Command is as follows:

LogParser -i:IISW3C -o:CHART -values:OFF -chartType:Columnclustered -
view:ON file:c:\Technet\TiSP2_RPC_Hourly_Activity.sql -config:c:\Technet\
MyScript.js

And the resulting chart is shown in Figure 8. As you'll see, the data is much more readable when shown as a bar chart, as opposed to a table of numbers like in Figure 6.

Figure 8** Remote Procedure Calls by Hour **(Click the image for a larger view)

Exchange ActiveSync User Activity Report

This report allows you to view the top 25 Exchange ActiveSync users based on number of hits. I'll use three columns to build a bar chart that represents the data. Of all of the Exchange services on the front-end machine, the Exchange ActiveSync service logs provide the most detail in the cs-uri-query field. For more info on Exchange ActiveSync logging, visit the Exchange team blog at msexchangeteam.com/archive/2005/03/28/403047.aspx.

SQL Statement and Log Parser Command

I first need to parse the cs-uri-query field to look for the sync command. To do this, I use Log Parser's INDEX_OF function and retrieve the value that sits between the Cmd= and the &. The parsed-out commands are then stored into the MyCmd variable where I can evaluate the value. I keep only the Sync command and discard the others. To create this report, copy the text in Figure 9 to a file and save it as TiSP2_EAS_User_Activity.sql. Then run the following Log Parser command:

LogParser -i:IISW3C -o:CHART -values:OFF -chartType:Barclustered -view:ON
file:c:\Technet\TiSP2_EAS_User_Activity.sql

The results will look like the table shown in Figure 10.

Figure 9 TiSP2_EAS_User_Activity.sql

SELECT TOP 25
         TO_LOWERCASE (cs-username) AS User,
         COUNT(*)                   AS Hits,
         SUM (MySync)               AS Syncs
USING 
         SUBSTR (cs-uri-query, ADD (INDEX_OF (cs-uri-query, 'Cmd='), 4), 
         INDEX_OF (SUBSTR(cs-uri-query, ADD (INDEX_OF (cs-uri-query, 
              'Cmd='), 4)), '&')) AS MyCmd,
 
         CASE MyCmd
                     WHEN 'Sync' THEN 1
                     ELSE 0
         END AS MySync
            
INTO 
  C:\LogParser\Output\TiSP2_EAS_User_Activity.gif
FROM 
  c:\Logs\SP2\ex06051423.log 
 
WHERE cs-uri-stem = '/Microsoft-Server-ActiveSync'
 
GROUP BY User
 
ORDER BY Hits DESC

Figure 10** Top 25 Exchange ActiveSync Users **(Click the image for a larger view)

Michael Higashi is a Program Manager on the Exchange Server team at Microsoft and is currently working on Exchange Server 2007.

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.