How to Use Exchange 2007 ActiveSync logs to track mobile device activity

Microsoft Exchange Server 2007 doesn't offer a tool to specifically track the amount of data that users send and receive from their portable devices. However, it does log all ActiveSync transactions. Learn how you can export Exchange 2007 ActiveSync reports to comma-separated values (CSV) files via the Exchange Management Shell to analyze and determine mobile device activity, and estimate the costs associated with mobile messaging in your organization.

---

One problem with mobile messaging is that, unless your company has a wireless plan that offers unlimited data transmission, users can accumulate hefty wireless bills. As an Exchange Server administrator, you must track how much data each mobile device user sends and receives to budget for future growth. If you know, on average, how much data each mobile user requires, you can easily estimate the monthly cost of adding users.
Exchange Server 2007 logs all ActiveSync transactions that go through the server; and you can use various data-mining techniques to glean information from these logs. When looking at ActiveSync reports, it's important to understand information contained within them. Reports are created based on the Exchange server's perspective of data, rather than the users' perspective.
A new ActiveSync log is created on a daily basis; therefore, there is no master-log file that contains every transaction that has ever occurred. You need to analyze the data from day to day. As you extract the data, you can plug it into a SQL Server or Microsoft Access database to perform a more detailed analysis over time, rather than evaluating just a single day's data.
Unfortunately, there is no graphical user interface (GUI) mechanism that analyzes ActiveSync logs, so you will have to use the Exchange Management Shell to export the data. To do so, you will need to know the date that the desired data was collected, and the path to which you want to dump the extracted data. For example, suppose that you wanted to extract data that was collected on July 31, 2007, and move it to a folder called C:\Logs. In this situation, you would use the following command:
Export-ActiveSyncLog –FileName: 'C:\Windows\System32\LogFiles\W3SVC1\ex070731.log' –UseGMT:$true –OutputPath 'c: \Logs\'
This file name is based on the date chosen, which is entered in YYMMDD format. Exchange Server will then create six comma-separated values (CSV) files in the target directory. CSV files are text files that are designed to be imported into Microsoft Access or Excel.
The first CSV file that Exchange Server creates is Users.csv. This file logs user activity, and lets an administrator view these logs to pinpoint the heaviest users. The Users.csv file tracks items sent and items received for each user.
Keep in mind that ActiveSync reports are generated based on the server's perspective, so the Items sent column refers to the number of items that the server sent to the specified user; not the number of messages that the user sent. Likewise, the Items received column refers to the number of items that the server received from the user; not the number of messages that the user received.
User-Agents.csv is the second CSV file that Exchange Server 2007 creates. This file logs activity according to device type. The Users-Agents.csv file can be used to determine how many users employ each different type of wireless device, and how much traffic each device generates.
The next three CSV files that Exchange generates are Servers.csv, PolicyCompliance.csv and StatusCodes.csv.

• The Servers.csv file reports ActiveSync-related activity on a mailbox
  server-by-mailbox server basis.
• The PolicyCompliance.csv file reports on how well various wireless devices comply with established ActiveSync mailbox policies.
• The StatusCodes.csv file contains various undocumented codes designed for use by Microsoft technical support staff.

The fourth file, Hourly.csv, compresses wireless activity into hour-by-hour chunks, and lets you identify when mobile users send and receive the most data. Some wireless providers offer different rates for peak hours vs. off-peak hours, so this hour-by-hour analysis can help you better predict charges that users incur.