Using Logparser with IIS logs to filter connection statistics for Exchange Server

Often we need to get statistics of connection types of users connecting to Exchange.

Logparser provides a very easy way to get a detailed statistics of connection types based on IIS logs.

This is where you can download Logparser (for free):

http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

we can use following queries to get a detailed list of users & theri connections types, number of connection attempts Device Type

To list all ActiveSync connections that are using the un-patched version of the iphone:
======================================================
logparser “SELECT cs-username AS UserID, cs(User-Agent) AS DeviceType, time as Time FROM c:\log\u_ex101210.log WHERE cs-uri-stem LIKE ‘%Microsoft-Server-ActiveSync%’ AND cs(User-Agent) LIKE ‘%801.293%’ GROUP BY Time, UserID, DeviceType” -rtp:-1 > c:\log\easdevice.txt

To list all Free busy lookups/OOF connections:
=============================
logparser “SELECT cs-username AS UserID, cs(User-Agent) AS DeviceType, time as Time FROM c:\log\u_ex101210.log WHERE cs-uri-stem LIKE ‘%/EWS/Exchange.asmx%’ AND cs-username IS NOT NULL GROUP BY Time, UserID, DeviceType” -rtp:-1 > c:\log\ems.txt

To list all OAB connections:
=================
logparser “SELECT cs-username AS UserID, cs(User-Agent) AS DeviceType, time as Time FROM c:\log\u_ex101210.log WHERE cs-uri-stem LIKE ‘%/OAB/%’ AND cs-username IS NOT NULL GROUP BY Time, UserID, DeviceType” -rtp:-1 > c:\log\oab.txt

To list all owa connections:
=================
logparser “SELECT cs-username AS UserID, cs(User-Agent) AS DeviceType, time as Time FROM c:\log\u_ex101210.log WHERE cs-uri-stem LIKE ‘%/owa/%’ AND cs-username IS NOT NULL GROUP BY Time, UserID, DeviceType” -rtp:-1 > c:\log\owa.txt

To list all RPC over HTTPS connections:
========================
logparser “SELECT cs-username AS UserID, cs(User-Agent) AS DeviceType, time as Time FROM c:\log\u_ex101210.log WHERE cs-uri-stem LIKE ‘%/rpc/%’ AND cs-username IS NOT NULL GROUP BY Time, UserID, DeviceType” -rtp:-1 > c:\log\rpc.txt

To list all Autodiscover connections:
=======================
logparser “SELECT cs-username AS UserID, cs(User-Agent) AS DeviceType, time as Time FROM c:\log\u_ex101210.log WHERE cs-uri-stem LIKE ‘%/Autodiscover/%’ AND cs-username IS NOT NULL GROUP BY Time, UserID, DeviceType” -rtp:-1 > c:\log\auto.txt

Exchange Server 2007, Exchange Server 2010