More fun with Logparser and Exchange logs
Published Sep 12 2007 03:12 PM 29K Views

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 ... Statistics: ----------- 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): http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displa... Here are some advanced posts on Logparser if you want to build on what I've put together here: Exchange 2003 - Active Sync reporting http://msexchangeteam.com/archive/2006/02/14/419562.aspx More on Exchange ActiveSync Reporting with Log Parser - COM object available http://msexchangeteam.com/archive/2006/03/03/421149.aspx I would like to thank Shane Zwerman and John Krick for the idea and input for this post. Enjoy! - Jim Westmoreland

3 Comments
Not applicable
Very interesting. I started doing something similar to this approach with PowerShell a while ago: http://www.muscetta.com/2007/08/09/powershell-and-regexp-a-match-made-my-day/
but this is much more complete, of course. Nice!
Not applicable
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'"
Not applicable
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:

http://www.microsoft.com/downloads/details.aspx?familyid=7287252C-402E-4F72-97A5-E0FD290D4B76&displaylang=en

Version history
Last update:
‎Sep 12 2007 03:12 PM
Updated by: