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


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&displaylang=en

 

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

 


Comments (14)
  1. Robert Spilman says:

    This is great stuff!  Can we get more?

  2. Joachim Farla says:

    What can i do when i get the following message in my system log:

    Generate Activation Context failed for c:Program FilesLog Parser 2.2lpeas.dll. Reference error message: The referenced assembly is not installed on your system.

  3. David Ruth says:

    I’ve been using the earlier Logparser scripts with great success.  Thanks…

    When I try and register lpeas.dll on my XPsp2 or W2K3sp1 systems I get "LoadLibrary(lpeas.dll)Failed-The application failed to start because the application configuration is incorrect.  Reinstalling the application may fix this problem."  

    What next?

  4. Erik Jans says:

    Thanks for great work!

    I unfortunately get the same error as David Ruth. "LoadLibrary(lpeas.dll) failed – This application has failed to start because the application configuration is incorrect. …"

    Running XP SP2 fully patched, and I installed LogParser 2.2 a few minutes ago. :)

  5. Simon says:

    Im getting the same error, im runing on Windows Server 2003 SP1, Exchange Server 2003 SP2.

  6. mhigashi says:

    The problem may be associated with your machine not having the correct version of the MSVCR80.dll (or not having it at all).

    We have update the EAS_Logparser_COM_Obj.zip file to include an executable which will install the MSVCR80.dll on your machine.

    Please try this out and let me know if that resolves your problem.

    –Mike

  7. Tony Eversole says:

    I would really like to see something like this for Exchange IMF data.  We have the ability to see the SCL totals, etc within perfmon, but how could we use something like this to give us data on the IMF counters?

  8. MaxDamage says:

    I’m a fan of your work guys.   Excelent Job.  I sort of missed the boat and started reading this blog a bit late.  I have been doing this in VBScript for a while.   So if its okay with you I’ll try to plagerise this bit too LOL.

    Cheers

    Max

  9. David Ruth says:

    Thanks, the MSVCR80.dll fixed my issue.

  10. Thomas says:

    I’m getting a very different error no matter where I run the command from or how I set up my SQL file:

    Error: Error calling Input Context plugin method "OpenInput": The filename, dire

    ctory name, or volume label syntax is incorrect.

    This is on a Win2k3SP1 server with the updated com object redist installed…thoughts?

  11. Chris W says:

    This is great, but what about using Log Parser on Message tracking to the same end?  I have been playing with it, and all thought I can read thru the log’s I’m having problems finding resources telling me what I’m looking for.  And once I find it, what doies it mean.

    Any suggestions?

  12. Simon says:

    That did the trick, now for some fun!

  13. mhigashi says:

    Thomas,

    Can you provide the following:

    – the complete syntax that you typed into the command-line?

    – the contents of the script that you were executing

    This should me understand where the problem may be coming from.

    Thanks!

    –Michael

  14. Anonymous says:

    The need for getting information on users using Activesync as well as devices connecting to the environment

Comments are closed.