Microsoft Exchange Server related News and Articles

EAS logs a lot of data in IIS about what’s going on, but parsing out this will be a huge headache. To analyze the data we can use the Log Parser tool from Microsoft.
Download Log Parser tool from below link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en
We can use SQL scripts to work with the Log parser tool to generate the hit ratio of EAS usage.
Note – We should enable the IIS W3C Extended Log File Format.

The output will also give you the details of user alias name of the users who are currently using the Exchange Active Sync feature. This way the EAS usage report can be generated.

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

INTO C:\Program Files\Logparser\EASUsage.csv /* The output report */
FROM C:\Program Files\Logparser\Logs\*.log /* Specify the path where the IIS logs are saved */
WHERE cs-uri-stem = '/Microsoft-Server-ActiveSync'
GROUP BY User, DeviceType

Follow the steps below to generate the reports:

1. Install the Log parser to the server where the Exchange FE server which acts as exchange active sync server (You can also install on the server where you can copy the IIS logs for analysis).
2. Save the above SQL script into a note pad file and save it as EASUsageReport.sql and place it into the logparser installation directory.
3. Open the command prompt and navigate the path where Log parser installed. For e.g C:\Program Files\Logparser>
4. Execute the command below to generate the report
   LogParser.exe -i:IISW3C file:(path)\EASUsageReport.sql
   E.g - LogParser.exe -i:IISW3C file:C:\Program Files\Logparser \Hits_By_DeviceType.sql
5. Copy the file EASUsage.csv and format it from MS Excel

Let me know if you face any issue during the report extraction. The report may not be so beatiful, but it will have the information that you are looking for. Rest is in your hand ....

-Praveen