More on Exchange ActiveSync Reporting with Log Parser - COM object available

[The_Exchange_Team]

We recently posted details on how to use Log Parser to parse out Exchange ActiveSync (EAS) information to produce helpful reports that can give you a better idea of the usage load of your EAS deployment.  In that blog we relied solely on LogParser using the IISW3C input format and your IIS Logs.

In this blog post, we take the capabilities of Log Parser one step further by leveraging the pluggable COM object interface.  We have put together an EAS specific Log Parser COM object to help make building queries much easier.  You will find this Log Parser COM object very useful in further drilling into sync activity.  For example you can create a report that showed the number of actual Calendar items Added, Updated or Deleted from the device of a specific user.  The same information can be reported for both Email and Contacts as well.  While you could get this without the use of the EAS Log Parser COM Object, it is not a trivial task and involves string parsing contortions that would make your eyes blurry and also take a long time for results to be generated on a typical production IIS log dataset.  

For our examples we will show you how to write relatively straight-forward SQL statements that use the EAS Log Parser COM Object.  The first sample is a user report that will return a few of the fields exposed via the COM Object.  The second sample is a lot simpler, but still relatively important… we will produce a server bandwidth report which can show you how many bytes are being sent and received from your EAS Server.  For both of these examples we chose to output the results to an HTML formatted report.

NOTES:

-       The COM Object is compatible with all versions of Exchange with ActiveSync built-in... Exchange 2003 or higher (E12).

-       This reporting solution, SQL scripts and COM Object are not officially supported by Microsoft.

-       Your IIS machine should be setup to produce "W3C Extended Log File Format" logs.  IIS sets this format type by default format, but if you changed this format type please reset it in order for the SQL queries to find the correct field names.  You can change this on the properties of your website via IIS Mgr.

 

Getting Started

 

To install Log Parser go to: http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displa...

 

Log Parser Basics

 

By default, log parser will be installed in C:\Program Files\Log Parser 2.2.  Run the LogParser.exe from the Log Parser install directory. Examples of the full Log Parser syntax are included in the last section of this blog - "Running Log Parser with the Supplied SQL Scripts".

Log File Location

 

With the EAS Log Parser COM Object, you can specify the location of the logs via:

-       UNC

-       Mapped Drives

-       Or Locally

NOTE: Log Parser (without the help of the EAS Log Parser COM Object) sometimes has problems opening large amounts of log files through a mapped drive, so it is not a recommended approach.  

Installing the EAS Log Parser COM Object

Please copy the EAS Log Parser COM Object to your local machine and register the dll. Download the file from here:

EAS Log Parser COM Object - v2

Example on how to register it:

Regsvr32 c:\Program Files\Log Parser 2.2\lpeas.dll

 

SQL Queries

User Hits Report

Create a file and name it EAS_User_Statistics.SQL and place this code block into the newly created file.

SELECT  TOP 50

            eas-user-alias,

            eas-user-domain,

            eas-device-type,

            SUM (TotalClientEmailSync) AS TotalClientEmailSyncs,

            SUM (TotalServerEmailSync) AS TotalServerEmailSyncs,

            ADD (TotalClientEmailSyncs, TotalServerEmailSyncs) AS TotalEmailSyncs,

            SUM (TotalClientContactSync) AS TotalClientContactSyncs,

            SUM (TotalServerContactSync) AS TotalServerContactSyncs,

            ADD (TotalClientContactSyncs, TotalServerContactSyncs) AS TotalContactSyncs,

            SUM (TotalClientCalendarSync) AS TotalClientCalendarSyncs,

            SUM (TotalServerCalendarSync) AS TotalServerCalendarSyncs,

            ADD (TotalClientCalendarSyncs, TotalServerCalendarSyncs) AS TotalCalendarSyncs,

            ADD (TotalEmailSyncs, ADD (TotalContactSyncs, TotalCalendarSyncs)) AS TotalSyncs   

USING

            CASE eas-folder-data-type

                        WHEN 'Em' THEN

                               ADD(eas-client-adds,

                               ADD (eas-client-changes,

                               ADD (eas-client-deletes, eas-client-fetches)))

                        ELSE 0

            END AS TotalClientEmailSync,

            CASE eas-folder-data-type

                        WHEN 'Em' THEN

                               ADD(eas-server-adds,

                               ADD (eas-server-changes, eas-server-deletes))

                        ELSE 0

            END AS TotalServerEmailSync,

            CASE eas-folder-data-type

                        WHEN 'Co' THEN

                               ADD(eas-client-adds,

                               ADD (eas-client-changes,

                               ADD (eas-client-deletes, eas-client-fetches)))

                        ELSE 0

            END AS TotalClientContactSync,

            CASE eas-folder-data-type

                        WHEN 'Co' THEN

                               ADD(eas-server-adds,

                               ADD (eas-server-changes, eas-server-deletes))

                        ELSE 0

            END AS TotalServerContactSync,

            CASE eas-folder-data-type

                        WHEN 'Ca' THEN

                               ADD(eas-client-adds,

                               ADD (eas-client-changes,

                               ADD (eas-client-deletes, eas-client-fetches)))

                        ELSE 0

            END AS TotalClientCalendarSync,

            CASE eas-folder-data-type

                        WHEN 'Ca' THEN

                               ADD(eas-server-adds,

                               ADD (eas-server-changes, eas-server-deletes))

                        ELSE 0

            END AS TotalServerCalendarSync

INTO C:\Public\LogParserOutput\EAS_User_Statistics.html

/* Substitute this directory location with the location of your logs.  Wildcards are allowed.

*/

FROM C:\Public\Logs\*.log

GROUP BY

            eas-user-alias,

            eas-user-domain,

            eas-device-type

ORDER BY

            TotalSyncs DESC

What's going on in this SQL statement?

This SQL statement calculates totals from several salient EAS activities (Emails, Calendar, Contact Sync'd, etc) for a specific user.  It is similar to the example that we provided in our external blog for EAS Reporting, but this time we show you how much cleaner it is without having to parse the cs-uri-query field.  As you can see in the sample above each data point (eas-cmd, eas-device-type, eas-client-adds, etc.) is now available as its own field, for example if you wanted to see the items created and sent up to the EAS Server you could query the 'eas-client-adds' field.  Taking it a step further, you can get the number of emails created from the device by first looking in the 'eas-folder-data-type' field for the value 'Em' which means email.  Once you find the record with 'Em' from the 'eas-folder-data-type' field, you then can SUM the number of 'eas-client-adds'.  For our sample, we took it one step further and calculated all client activity for each eas-folder-data-type (we did the same for server activity).  We calculated the total by using the ADD function and added each client related sync field together.  Since the ADD function only supports adding two arguments together we needed to string together several ADD functions to get the total.

Another interesting feature that this script leverages in Log Parser is the ability to output to an HTML file and being able to customize the HTML output.  To customize the HTML, you create a template file that has the extension '.tpl'.  In the 'Running Log Parser with the Supplied SQL Scripts' section below, we will show you what cmd-line parameters you need to pass in to get this to work.  

Dynamically Created HTML output

To create the template file create another file and name it EAS_User_Statistics.tpl and copy the contents into the file.  This file will be used by Log Parser to dynamically create an HTML formatted file output.

<LPHEADER>

<HTML>

<HEAD><TITLE>Summary of Exchange ActiveSync User Statistics</TITLE></HEAD>

<BODY BGCOLOR="#FFFFFF">

<TABLE BORDER="0" CELLPADDING="2" style="font-family: Tahoma; font-size: 10pt">

<TR>

 <TH COLSPAN="13" ALIGN="CENTER"><font size="4">Exchange ActiveSync User Statistics

            </font> </TH>

</TR>

<TR>

 <TH>UserName</TH>

 <TH>Domain</TH>

 <TH>Device Type</TH>

 <TH>Total Syncs</TH>

 <TH>Total Client Email Syncs</TH>

 <TH>Total Server Email Syncs</TH>

 <TH>Total Email Syncs</TH>

 <TH>Total Client Contact Syncs</TH>

 <TH>Total Server Contact Syncs</TH>

 <TH>Total Contact Syncs</TH>

 <TH>Total Client Calendar Syncs</TH>

 <TH>Total Server Calendar Syncs</TH>

 <TH>Total Calendar Syncs</TH>

</TR>

</LPHEADER>

<LPBODY>

<TR>

 <TD>%eas-user-alias%</TD>

 <TD>%eas-user-domain%</TD>

 <TD>%eas-device-type%</TD>

 <TD align="center"><font color="#FF0000">%TotalSyncs%</font></TD>

 <TD align="center">%TotalClientEmailSyncs%</TD>

 <TD align="center">%TotalServerEmailSyncs%</TD>

 <TD align="center">%TotalEmailSyncs%</TD>

 <TD align="center">%TotalClientContactSyncs%</TD>

 <TD align="center">%TotalServerContactSyncs%</TD>

 <TD align="center">%TotalContactSyncs%</TD>

 <TD align="center">%TotalClientCalendarSyncs%</TD>

 <TD align="center">%TotalServerCalendarSyncs%</TD>

 <TD align="center">%TotalCalendarSyncs%</TD>

</TR>

</LPBODY>

<LPFOOTER>

</TABLE>

</BODY>

</HTML>

</LPFOOTER>

EAS Server Bandwidth Report

For the next report, create a file and name it EAS_Server_Bandwidth.SQL and place this code block into the newly created file.

SELECT 

            s-ip AS ServerIP,

            COUNT (eas-cmd) AS TotalHits,

            SUM (sc-bytes) AS TotalBytesSent,

            SUM (cs-bytes) AS TotalBytesReceived    

INTO C:\Public\LogParserOutput\E12_Server_Usage.html

FROM C:\Public\Logs\ex05120714.01.log

GROUP BY

            s-ip

ORDER BY

            TotalHits DESC

What's going on in this SQL statement?

This SQL statement doesn't quite show the real benefit of the EAS Log Parser COM Object, because this same query could be easily created by simply getting the COUNT(*) instead of using COUNT(eas-cmd), but we wanted to share this report because we've heard from you that you want visibility into the amount of bandwidth flowing into and out of your EAS Servers.  To accomplish this, we are getting the bandwidth data directly from the sc-bytes and cs-bytes fields, which we have confirmed through analysis that this information also factors in GZip compression. Similar to the first sample, this sample also creates an HTML formatted output file.

Dynamically Created HTML output

To create the template file create another file and name it EAS_Server_Usage.tpl and copy the contents into the file.  This file will be used by Log Parser to dynamically create an HTML formatted file output.

<LPHEADER>

<HTML>

<HEAD><TITLE>Summary of Exchange ActiveSync Server Usage</TITLE></HEAD>

<BODY BGCOLOR="#FFFFFF">

<TABLE BORDER="0" CELLPADDING="2" CELLSPACING="2" style="font-family: Tahoma; font-size: 10pt">

<TR>

 <TH COLSPAN="4" ALIGN="CENTER"><font size="2">Summary of Exchange ActiveSync Server Usage</font></TH>

</TR>

<TR>

 <TH>Server IP</TH>

 <TH>Total Hits</TH>

 <TH>Total Bytes Received</TH>

 <TH>Total Bytes Sent</TH>

</TR>

</LPHEADER>

<LPBODY>

<TR>

 <TD>%ServerIP%</TD>

 <TD align="center"><font color="#FF0000">%TotalHits%</font></TD>

 <TD align="center">%TotalBytesReceived%</TD>

 <TD align="center">%TotalBytesSent%</TD>

</TR>

</LPBODY>

<LPFOOTER>

</TABLE>

</BODY>

</HTML>

</LPFOOTER>

Running Log Parser with the Supplied SQL Scripts

There are several input parameters that will allow you to make log parser aware of this COM Object and to output to html. They include:

COM Object

-          -i:COM

-          -iProgID:MSUtil.LogQuery.EASInputFormat. 

HTML Output

-          -o:TPL

-          -tpl:<<LOCATION TO TPL FILE>>

Here is the command syntax...

LogParser.exe -i:COM -iProgID:MSUtil.LogQuery.EASInputFormat file:<<LOCATION TO SQL FILE>>\EAS_Server_Bandwidth.sql -o:TPL -tpl:<<LOCATION TO TPL FILE>>\EAS_Server_Usage.tpl

EAS User Statistics EXAMPLE:

LogParser.exe -i:COM -iProgID:MSUtil.LogQuery.EASInputFormat file:c:\Public\LogParser\SQL\EAS_User_Statistics.sql -o:TPL -tpl:c:\Public\LogParser\SQL\EAS_User_Statistics.tpl

EAS User Statistics EXAMPLE:

LogParser.exe -i:COM -iProgID:MSUtil.LogQuery.EASInputFormat file:c:\Public\LogParser\SQL\EAS_Server_Bandwidth.sql -o:TPL -tpl:c:\Public\LogParser\SQL\EAS_Server_Usage.tpl 

Log Parser Help

The Log Parser Help is included as part of the Log Parser 2.2 install.  We have found this Help file to be very helpful in getting started.  The Reference section is especially informative. 

Here are some sections that were helpful...

Reference\

-       Command-Line Operation\Query Execution Mode

-       Query Syntax\*

-       Input Formats\*

-       Output Formats\*

We hope you find these two scripts helpful in getting started with your EAS reporting with the help of the EAS Log Parser COM Object. 

- Michael Higashi and Patrick Tousignant