Home > Scripting > Exchange 2003 – Active Sync reporting?

Exchange 2003 – Active Sync reporting?

Taken from Microsoft’s Exchange Team Blog and modified, we have been able to take two SQL scripts, run them against our Front-end Protocol servers, and retrieve usage data pertaining to Active Sync users and clients.  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 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.)

You need several things setup in order for the scripts to work:

  • Install Microsoft’s LogParser 2.2 or later on your Front-End Exchange server(Get it here)
  • Copy both SQL scripts to the "C:\Program Files\Log Parser 2.2" directory on your Front-End Exchange server.  If you have more than one server, this will need to be ran on every Front-end server n your organization to get accurate results.  (IIS logs are being parsed)
  • Get the scripts.  Script 1 (Save as Hits_By_User.Sql) and Script 2 (Save as Hits_by_DeviceType.Sql). (Email Me for the scripts)

Once saved, open a command prompt to "C:\Program Files\Log Parser 2.2" from your Front-End Exchange server. 

Issue this command:  LogParser.exe -i:IISW3C file:"C:\Program Files\Log Parser 2.2\Hits_By_User.sql"

This is going to create a file named Hits_By_User.csv in the root of C:\.  Depending on your usage the script may take 15-30 minutes to run.

Once complete, open a command prompt to "C:\Program Files\Log Parser 2.2" from your Front-End Exchange server. 

Issue this command:  LogParser.exe -i:IISW3C file:"C:\Program Files\Log Parser 2.2\Hits_By_DeviceType.sql"

This is going to create a file named Hits_By_DeviceType.csv in the root of C:\.  Depending on your usage the script may take 15-30 minutes to run.

Results should look similar to this: (By User)

User,DeviceType,Hits,Syncs,Folder_Syncs,Pings,Emails_Sent,SmartReplies,Meeting_Responses,Get_Attachments
Domain\User1,SmartPhone,2258,1224,16,971,1,0,2,0
Domain\User2,SmartPhone,1824,867,149,630,0,1,0,5
,SmartPhone,1723,123,14,1028,1,1,0,0
Domain\User3,SmartPhone,1654,105,10,1325,0,0,0,0
Domain\User4,SmartPhone,1545,1008,5,487,4,3,3,9
Domain\User5,SmartPhone,1514,207,157,86,2,10,1,13
Domain\User6,SmartPhone,1207,184,73,31,1,6,1,16
Domain\User7,SmartPhone,1124,188,9,788,1,1,0,0
Domain\User8,SmartPhone,1123,474,44,416,1,22,0,21
Domain\User9,SmartPhone,946,284,1,644,0,0,0,0
Domain\User10,SmartPhone,940,324,198,172,0,3,0,0
Domain\User12,SmartPhone,896,348,13,311,0,1,0,25

(By Device)

DeviceType,             Hits
PocketPC,                103759
SmartPhone,             48890
PalmOneTreoAce,      16716
NokiaE62,                  2295
,                                60 *Other devices

Ed M.

Advertisements
Categories: Scripting
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: