More fun with Logparser and Exchange logs

Right now there is no easy way to tell who is using Entourage, RPC/HTTP (Outlook Anywhere), Exchange ActiveSync, or OWA with what frequency.  I have found Logparser to be very helpful in answering a lot of these questions.  The tool is a bit intimidating to get started but once you get the hang of modifying some of my sample scripts you can accomplish a lot of detailed reporting.  The following examples rely on the default IIS log settings.  The most useful non-default column to enable is cs-bytes because with that you will be able to query on the amount of data as well.  There are excellent built in examples and syntax help to modify the following to suit your own particular needs. Note that this should work the same on both Exchange 2003 and Exchange Server 2007.

Please note: the following scripts are samples and are not officially supported by Microsoft.

The following counts how many messages have been submitted by Entourage users and ranks them in descending order by domain/username:

logparser "select cs-username, Count(*) as DavMailSubmitted FROM c:\windows\system32\logfiles\w3svc1\ex*.log WHERE cs-uri-stem LIKE '%davmailsubmissionURI%' AND cs-username IS NOT NULL GROUP BY cs-username ORDER BY DavMailSubmitted desc" -rtp:-1

Output looks like this:

cs-username                 DavMailSubmitted
------------------          ----------------
DOMAIN\User1                153
DOMAIN\User2                148
DOMAIN\User3                143
DOMAIN\User4                141
DOMAIN\User5                138
DOMAIN\User6                130
DOMAIN\User7                124
DOMAIN\User8                124
DOMAIN\User9                121
Elements processed: 2010774
Elements output: 411
Execution time: 8.69 seconds

The following ranks Entourage users by activity as opposed to just mail submission:

logparser "SELECT cs-username, Count(*) AS Hits from c:\windows\system32\logfiles\w3svc1\ex*.log WHERE TO_LOWERCASE (cs(user-agent)) LIKE '%Entourage%' AND cs-username IS NOT NULL GROUP BY cs-username ORDER BY Hits Desc" -rtp:-1

Sample output:

cs-username                 Hits
---------------------       -----
DOMAIN\User1                18230
DOMAIN\User2                15342
DOMAIN\User3                14563
DOMAIN\User4                12774
DOMAIN\User5                12082
DOMAIN\User6                10895
DOMAIN\User7                10412
DOMAIN\User8                10369

The following ranks RPC/HTTP (Outlook Anywhere) users by activity:

logparser "SELECT cs-username, Count(*) AS RPCProxyHits from c:\windows\system32\logfiles\w3svc1\ex*.log WHERE cs-uri-stem LIKE '%rpcproxy.dll%' AND cs-username IS NOT NULL GROUP BY cs-username ORDER BY RpcProxyHits Desc" -rtp:-1

Sample output:

cs-username                 RPCProxyHits
------------------          ------------
DOMAIN\User1                3331
DOMAIN\User2                2183
DOMAIN\User3                2066
DOMAIN\User4                1745
DOMAIN\User5                1483
DOMAIN\User6                1136
DOMAIN\User7                1055
DOMAIN\User8                959
DOMAIN\User9                890

The following ranks EAS users by activity:

logparser "SELECT cs-username, Count(*) AS EASHits from c:\windows\system32\logfiles\w3svc1\ex*.log WHERE cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%' AND cs-username IS NOT NULL GROUP BY cs-username ORDER BY EASHits Desc" -rtp:-1

Sample output:

cs-username        EASHits
---------------   -------
DOMAIN\User1       1251
DOMAIN\User2       1152
DOMAIN\User3       971
DOMAIN\User4       774
DOMAIN\User5       756
DOMAIN\User6       737
DOMAIN\User7       676
DOMAIN\User8       634
DOMAIN\User9       613

This one finds ActiveSync users and sorts them by name and included the device type(s), and activity for each:

logparser "SELECT cs-username AS UserID, cs(User-Agent) AS DeviceType, count (*) FROM c:\windows\system32\logfiles\w3svc1\ex*.log WHERE cs-uri-stem LIKE '%Microsoft-Server-ActiveSync%' AND cs-username IS NOT NULL GROUP BY UserID, DeviceType ORDER BY UserID" -rtp:-1

Sample output:

UserID                DeviceType                              COUNT(ALL *)

-----------------     --------------------------------        -----------

DOMAIN\user1          Microsoft-Server-ActiveSync/6.5.7638.1    756
DOMAIN\user2          Microsoft-Server-ActiveSync/6.5.7638.1    350
DOMAIN\user3          Microsoft-Server-ActiveSync/6.5.7638.1    46
DOMAIN\user4          Microsoft-Server-ActiveSync/6.5.7638.1    387
DOMAIN\user5          PalmOne-TreoAce/1.02                      362
DOMAIN\user6          PalmOne-TreoAce/1.01                      25
DOMAIN\user7          MSFT-PPC/5.1.2201                         676
DOMAIN\user8          MSFT-PPC/5.1.2301                         238
DOMAIN\user9          MSFT-SPhone/4.0                           185
DOMAIN\user10         MSFT-SPhone/5.1.2300                      403
DOMAIN\user11         PalmOne-TreoAce/1.00g5                    14
DOMAIN\user12         MSFT-PPC/5.1.2301                         268
DOMAIN\user13         PalmOne-TreoAce/1.01                      109
DOMAIN\user14         PalmOne-TreoAce/1.00g6                    15
DOMAIN\user15         PalmOne-TreoAce/1.00                      10
DOMAIN\user16         MSFT-SPhone/4.0                           354
DOMAIN\user17         PalmOne-TreoAce/1.01                      17
DOMAIN\user18         MSFT-PPC/5.1.2201                         613

This last one creates a pie chart showing the distribution of device types being used:

logparser "SELECT cs(user-agent), count(*) as Devices into chart.gif from c:\windows\system32\logfiles\w3svc1\ex*.log WHERE cs-uri-stem LIKE '%microsoft-server-activesync%' and cs-username is NOT NULL GROUP BY cs(User-Agent) ORDER BY Devices desc" -charttype:pieexploded3d -ChartTitle:"Device Activity by Type" -categories:OFF

This is the chart generated with my test data running the above command:

Some things you might see:

  1. Duplicate entries.  By default Logparser is case sensitive and treats entries that are identical other than case as separate.  Also, you may see a client failing because a legitimate user entered their name incorrectly.  They will be included but you can ignore them.
  2. Dates.  The queries as I've written them will include every log file in the directory so the entire history of the server will be represented.  If you want to have different reports for different time frames you'll need to modify the query to only include specific log files.  You can use wild card characters like '?' or '*' to narrow or expand the search.  You can also copy the logs into separate folders and just change the path to execute on that specific folder.

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

Here are some advanced posts on Logparser if you want to build on what I've put together here:

Exchange 2003 - Active Sync reporting

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

I would like to thank Shane Zwerman and John Krick for the idea and input for this post.


- Jim Westmoreland

Comments (3)
  1. Very interesting. I started doing something similar to this approach with PowerShell a while ago:

    but this is much more complete, of course. Nice!

  2. alex says:

    A while ago, logparser was very handy in helping identify our POP and IMAP users. First, you’ll need to turn on POP and IMAP logging in Exchange System Manager (this is for 2003 btw, I have no idea about 2007) The logparser command I used (for POP) was as follows:

    Logparser.exe -i:evt -o:CSV "select distinct message into pop_user.csv from \yourexchangeserverApplication where EventID = 1017 AND SourceName = ‘POP3SVC’"

  3. alex says:

    On a system without Office 2003 installed, in order to produce charts Office Web Components add-in should be installed (otherwise you get the error: Error: invalid parameter "chartType").

    Get it here:

Comments are closed.

Skip to main content