Exchange 2003 - Active Sync reporting

The_Exchange_Team · ‎Feb 14 2006

We have received a lot of feedback recently about your need to better understand the usage of your Exchange 2003 ActiveSync (EAS) deployment. With all of the great features built into EAS, it’s important for you as an IT Professional to know the volume and usage patterns of your deployment. This information can help you more effectively manage your EAS deployment, better understand user productivity, and help you calculate ROI.

Today, the EAS protocol, logs a lot of data in IIS about what’s going on, but parsing out that data can be a daunting task. We understand your pain, so to help you, we have created two sql scripts which will help you get started in better evaluating your EAS deployment. These scripts work in conjunction with the Microsoft Log Parser 2.2 tool to produce informative reports.

DISCLAIMER: Those scripts are samples only, not officially supported by Microsoft.

The first script produces a ‘Hits by User’ Report which will show you which users are hitting your EAS servers. This report provides many of the salient statistics that could help you better understand how your users are interacting with your EAS deployment. It returns information by individual user such as, devices type used, number of Syncs, Emails Sent, Attachments downloaded, Meeting Responses, etc.

The second script produces is a ‘Hits by DeviceType’ Report. This report gives you a breakdown of the DeviceTypes (SmartPhone, PocketPC, etc.) that are hitting your EAS Server. This report can give you a better idea of what types of devices your users prefer, which can help you make more informed planning decisions in the future. This script also can produce a very helpful chart, such as a 3D pie chart using Microsoft Office Web Components that can be used for reporting or presentations purposes to your management.

Here are the details for running Log Parser with these sql scripts…

NOTE: Your IIS machine should be setup to produce “W3C Extended Log File Format” logs. IIS set 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...

To install Microsoft Office Web Components go to… http://www.microsoft.com/downloads/details.aspx?FamilyID=982B0359-0A86-4FB2-A7EE-5F3A499515DD&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

You can either specify the UNC location of your Logs, or copy them locally to the machine running Log Parser. Mapped drives do not currently work in Log Parser 2.2.

SQL Queries (samples)

Hits by User Query

Create a file and name it Hits_by_User.sql and place this code block into the newly created file.

SELECT

TO_LOWERCASE (cs-username) AS User,

MyDeviceType AS DeviceType,

COUNT(*) AS Hits,

SUM (MySync) AS Syncs,

SUM (MyFolderSync) AS Folder_Syncs,

SUM (MyPing) AS Pings,

SUM (MySendMail) AS Emails_Sent,

SUM (MySmartReply) AS SmartReplies,

SUM (MyMeetingResponse) AS Meeting_Responses,

SUM (MyGetAttachment) AS Get_Attachments

USING

SUBSTR (cs-uri-query, ADD (INDEX_OF (cs-uri-query, 'DeviceType='), 11),

INDEX_OF (SUBSTR(cs-uri-query, ADD (INDEX_OF (cs-uri-query, 'DeviceType='), 11)), '&')) AS MyDeviceType,

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,

CASE MyCmd

WHEN 'Ping' THEN 1

ELSE 0

END AS MyPing,

CASE MyCmd

WHEN 'SendMail' THEN 1

ELSE 0

END AS MySendMail,

CASE MyCmd

WHEN 'SmartReply' THEN 1

ELSE 0

END AS MySmartReply,

CASE MyCmd

WHEN 'MeetingResponse' THEN 1

ELSE 0

END AS MyMeetingResponse,

CASE MyCmd

WHEN 'GetAttachment' THEN 1

ELSE 0

END AS MyGetAttachment,

CASE MyCmd

WHEN 'FolderSync' THEN 1

ELSE 0

END AS MyFolderSync

/* CSV

If you want to output to CSV, you will need to add the cmd-line parameter -o:CSV

EXAMPLE: LogParser.exe -i:IISW3C -o:CSV file:c:\Public\LogParser\SQL\User_Activity.sql

*/

INTO C:\Public\LogParserOutput\Hits_By_User.csv

FROM C:\Public\Logs\*.log

WHERE cs-uri-stem = '/Microsoft-Server-ActiveSync'

GROUP BY User, DeviceType

ORDER BY DeviceType, Hits DESC

What’s going on in this sql statement?

With EAS, most of the salient data that will give you valuable reports are stored in the cs-uri-query field. The EAS protocol logs each HTTP Post request and response, such as sending email, downloading attachments, etc. Here in this example, we first parse the cs-uri-query field to look for the ‘DeviceType=’ section in this field (cs-uri-query) and retrieve the value that is between the ‘DeviceType=’ and the ‘&’ (single quotes excluded). Next we go after the different types of sync commands by using the same mechanism that we used to get the DeviceType. To do this we look for the ‘Cmd=’ section and find the value that follows. The parsed out commands are then stored into the MyCmd variable. In order to display the various sync command stored in the MyCmd variable into individual fields we need to place them into their own variable and SUM up the variable to get the total of each sync command. We use the CASE function to evaluate the incoming data of the MyCmd field. In our example, we returned some of the more interesting commands, but there are more commands to report on if you’re interested. See the below list of 'Sync Commands' which you could add to this sql statement...

Finishing up on the details of this first sql statement we select the output format. In this case we place the results into a CSV file which you can quickly view, or you could import the CSV into a SQL reporting solution. In the next sql query example, we’ll show you how to create a 3D pie chart of the results using Microsoft Office Web Components.

Modifying/Customizing this sql statement

We know that you may have unique reporting requirements and would like to have a report that only returned certain data points. You can use the above sample as a starting point to add or remove fields to have the most appropriate report for your needs. For example, if you wanted to have a report that just showed the top 10 users who have downloaded the most attachments you could modify the query to something like this…

SELECT

TO_LOWERCASE (cs-username) AS User,

SUM (MyGetAttachment) AS Get_Attachments

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 'GetAttachment' THEN 1

ELSE 0

END AS MyGetAttachment

/* CSV

If you want to output to CSV, you will need to add the cmd-line parameter -o:CSV

EXAMPLE: LogParser.exe -i:IISW3C -o:CSV file:c:\Public\LogParser\SQL\Get_Attachment_Hits_by_User.sql

*/

INTO C:\Public\LogParserOutput\Get_Attachment_Hits_By_User.csv

FROM C:\Public\Logs\*.log

WHERE cs-uri-stem = '/Microsoft-Server-ActiveSync'

GROUP BY User

ORDER BY Get_Attachments DESC

Sync Commands

This list is the comprehensive list of sync commands. Depending on your Exchange Server version and the devices that connect to your server, you may not have these commands return data. For example, the Ping command was recently introduced in the Exchange 2003 SP2 release, so if you have a longer version of Exchange you may not get this information returned back in your report.

Sync

FolderSync

GetItemEstimate

Ping

Search

GetAttachment

SmartReply

SendMail

MeetingResponse

ItemOperations

Notify

MoveItems

Settings

SmartForward

GetHierarchy

Provision

Hits by DeviceType Query

Create a file and name it Hits_by_DeviceType.sql and place this code block into the newly created file.

SELECT MyDeviceType AS DeviceType,

COUNT(*) AS Hits

USING SUBSTR (cs-uri-query, ADD (INDEX_OF (cs-uri-query, 'DeviceType='), 11),

INDEX_OF (SUBSTR(cs-uri-query, ADD (INDEX_OF (cs-uri-query, 'DeviceType='), 11)), '&')) AS MyDeviceType

/* Output Types: 1) CSV, 2) Chart */

/* CSV */

/* INTO C:\Public\LogParserOutput\Hits_By_DeviceType.csv */

/* Chart */

INTO C:\Public\LogParserOutput\Hits_By_DeviceType.gif

FROM C:\Public\Logs\*.log

WHERE cs-uri-stem LIKE '%ActiveSync'

GROUP BY MyDeviceType

What’s going on in this sql statement?

In this example we use the same mechanism to parse into the cs-uri-query field to get the DeviceType value. We then group and count the different Device Types. The interesting thing about this example is that the results fit nicely into a chart, such as a pie chart. Log Parser has hooks built into the Microsoft Office Web Component API which will allow you to produce relatively sophisticated charts through the Log Parser tool. We’ve provided you with the option to produce either a CSV or a Chart. Just comment out (using /* COMMENT */) the output type that you do not want.

Running Log Parser with the Supplied SQL Scripts

To generate reports using the sample sql files that you created above open, type in (or copy & paste) this command into the command prompt that you opened up above.

LogParser.exe -i:IISW3C file:<<LOCATION TO SQL FILE>>\Hits_By_User.sql

Or

LogParser.exe -i:IISW3C file:<<LOCATION TO SQL FILE>>\Hits_By_DeviceType.sql

EXAMPLE:

LogParser.exe -i:IISW3C file:c:\Public\LogParser\SQL\Hits_By_DeviceType.sql

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.

- Michael Higashi and Patrick Tousignant

Report Inappropriate Content · ‎Feb 15 2006

One of the big requests we've had from large deployments of Exchange Activesync is the ability to generate...

Report Inappropriate Content · ‎Feb 15 2006

Good stuff!! Works like a charm

Report Inappropriate Content · ‎Feb 16 2006

I just tried&nbsp;the&nbsp;SQL script for getting a useful view of the activesync logs from here:
http://blogs.technet.com/exchange/archive/2006/02/14/419562.aspx...

Report Inappropriate Content · ‎Feb 19 2006

I almost missed the weekend, but gladly I made it on time&nbsp;;-)

Rivals Take Aim at RIM
Microsoft...

Report Inappropriate Content · ‎Feb 23 2006

Great code, thanks for sharing. You can clean the code up by replacing the substring commands you are using to pull data out of the cs-uri-query with EXTRACT_VALUE. For example, this:

SUBSTR (cs-uri-query, ADD (INDEX_OF (cs-uri-query, 'DeviceType='), 11),
INDEX_OF (SUBSTR(cs-uri-query, ADD (INDEX_OF (cs-uri-query, 'DeviceType='), 11)), '&')) AS MyDeviceType

Can be simplified to:

EXTRACT_VALUE(cs-uri-query,'DeviceType') AS MyDeviceType

Much cleaner and my tests showed that it ran the analysis nearly 50% faster on large logs.

Report Inappropriate Content · ‎Mar 02 2006

This is exactly what I've been looking for. Although there is one aspect I would love to add if possible which is the time of last sync. Since many of the users in this list could be gone from the company now, it would be good to know the time of the last good sync of each user. Is this easily added to the .sql file?

Thanks!
Alan Sebastian
Alan.Sebastian@hansonamerica.com

Report Inappropriate Content · ‎Mar 03 2006

We recently posted details on how to use Log Parser to parse out Exchange ActiveSync (EAS) information...

Report Inappropriate Content · ‎Mar 10 2006

Dam

I just spent two days writing this exact report in VBscript. That will teach me to pay more attention :)

If you are interested I can share the code ?

Regards Max

Report Inappropriate Content · ‎Mar 21 2006

To answer Alan's question above, you can add:
MAX(date)
to the list of columns returned. That will give you the date of the last request sent, which you could use to figure out who left the company (or threw their phones in a lake). It gets a little trickier to do for the last "good" sync. I would probably revert to some perl script to do that, although I've seen some pretty clever uses of SQL statements, so maybe it would be possible to do it all in one statement.

Report Inappropriate Content · ‎Jun 22 2007

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

Report Inappropriate Content · ‎Aug 29 2008

I have tested the first sample,
There is an error in request,
you must change
WHERE cs-uri-stem = '/Microsoft-Server-ActiveSync'
with
WHERE cs-uri-stem like '%/Microsoft-Server-ActiveSync%'

For me the difference is, I appear in result with the second request.
and the right number of user :-)

Products (50)

Special Topics (27)

Video Hub (462)

Most Active Hubs

Most Active Hubs

Video Hub

Exchange 2003 - Active Sync reporting

Log File Location

SQL Queries (samples)

Running Log Parser with the Supplied SQL Scripts