Top Tips for SysAdmins No 1.
In this new column giving practical advice on all things Sys Admin related, Ben Lye takes on the often difficult task of keeping track of mobile device activity.
I was recently asked to generate reports on how many users are using mobile devices to access their Exchange mailbox, what kind of devices are being used, and how that use has changed over time. Fortunately, Exchange 2007 includes a PowerShell cmdlet which will parse the IIS log files on a client access server and produce CSV output files detailing the Exchange ActiveSync usage. So, with a small amount of effort it’s possible to extract the relevant data from the Exchange logs and produce some interesting reports.
The command for exporting ActiveSync logs is intuitively called Export-ActiveSyncLog. It takes an IIS log file as input, and generates six CSV files as output.
|
Output Filename |
Description |
|
Users.csv |
ActiveSync activity by user, including items sent and received, as well as device type and ID |
|
Servers.csv |
ActiveSync activity by client access server |
|
Hourly.csv |
Hour-by-hour report of ActiveSync activity |
|
StatusCodes.csv |
Summary of the HTTP response codes issued in response to ActiveSync requests |
|
PolicyCompliance.csv |
Report on device compliance with ActiveSync policy |
|
UserAgents.csv |
Summary of the different user agents used to access Exchange |
For my purposes Users.csv is the most interesting part of the output as it can be used to identify who the users are, which device types are the most popular, and how much use the service is getting. It’s worth noting that the data in the reports is taken from the server’s perspective, so “Total Emails Sent” refers to the number of emails that the server sent to the client device.
In an Exchange environment with multiple client access servers (such as an environment with servers in multiple Active Directory sites, or one using an internet-facing network-load-balancing array) you will need to export the logs from all client access servers which mobile devices connect to. If you have a single client access server exposed to the internet which all mobile devices connect to, you’ll only need to export the logs from that one.
To use Export-ActiveSyncLog you need:
- The Exchange Server Administrator role
- Read-only access to the directory that contains the IIS log files
This example will export the ActiveSync data from the IIS log file of September 1st 2008. It will use UTC times, and will put the output in C:\Temp\EASReports.
Export-ActiveSyncLog –FileName "C:\Windows\System32\LogFiles\W2SVC1\ex080901.log" –UseGMT:$true –OutputPath "C:\Temp\EASReports"
That will work fine for a single log file, but what if you need to export multiple log files? Well, you can list all the log files in a directory using Get-ChildItem, which you can in turn pipe to the Export-ActiveSync command:
Get-ChildItem "C:\Windows\System32\LogFiles\W3SVC1" | Export-ActiveSyncLog –UseGMT:$true –OutputPath "C:\Temp\EASReports"
This syntax will combine the data from each log file and give you produce a single set of CSV files covering the entire range of the input log files. Because I need to be able to report on usage over time this approach won’t give me what I need.
Another way to process multiple log files is to produce a set of CSV files for each log file. However because the CSV files would typically all use the same names I also need to specify a prefix for the name of the output CSV files, which will ensure I get all the output. For that I use the -OutputPrefix parameter of the Export-ActiveSyncLog cmdlet.
This command will create CSV files prefixed with the name of the log file they were generated from:
Get-ChildItem "C:\Windows\System32\LogFiles\W3SVC1" | ForEach { Export-ActiveSyncLog -FileName $_.FullName -OutputPath "C:\Temp\EASReports" -OutputPrefix $_.Name.Replace(".log","_") -UseGMT:$true}
Now that I have the CSV files for all my log files I can import the data into a database and run reports. For the database I have an SQL database which consists of a single table based on the Users.csv file, with the addition of an ID field as the primary key, and a date field to store the date of the log file.
Getting data from PowerShell into the database is a little bit more complicated. This PowerShell script will import all the Users.csv log files which were exported with the previous command into the SQL database.
# Script for importing Exchange ActiveSync Users.csv files into a SQL database
# Set up the parameters for connecting to the SQL database
$dbserver = "dbserver.company.com"
$dbname = "EASReports"
$dbuser = "dbusername"
$dbpass = "dbpassword"
# Create the ADO database object
$objConnection = New-Object -comobject ADODB.Connection
# Open the database connection
$objConnection.Open("PROVIDER=SQLOLEDB;DATA SOURCE=$dbserver;UID=$dbuser;PWD=$dbpass;DATABASE=$dbname")
# Find all the Users.csv files and import them
Get-ChildItem "C:\Temp\EASReports\*Users.csv" | ForEach {
# Get the date from the name of the file
$Date = ($_.Name).SubString(2,6)
$Year = "20" + $Date.SubString(0,2)
$Month = $Date.SubString(2,2)
$Day = $Date.SubString(4,2)
$Date = Get-Date -Year $Year -Month $Month -Day $Day -Hour 0 -Minute 0 -Second 0
# Import the CSV file
$CSVFile = Import-Csv $_
# Get the column names from the first line of the CSV file
$CSVFileProperties = Get-Content "$_" -totalcount 1 | % {$_.split(",")}
# Loop through each entry in the CSV file
ForEach ($Entry in $CSVFile) {
# Ignore lines with an empty Device ID
If ($Entry."Device ID" -ne "") {
# Construct the SQL insert statement
$SQLString = "INSERT INTO Users ("
Foreach ($Prop in $CSVFileProperties) {
$SQLString = $SQLString + "[$Prop],"
}
$SQLString = $SQLString + "[Date]) VALUES ("
Foreach ($Prop in $CSVFileProperties) {
$SQLString = $SQLString + "'" + $Entry."$Prop" + "',"
}
$SQLString = $SQLString + "'$Date')"
# Add the record to the database
$null = $objConnection.Execute($SQLString)
}
}
}
# Close the database connection
$objConnection.Close()
With the data in an SQL database I can then use Excel to connect to the database and analyze the data. The resulting output looks like this::
As I said at the beginning, it takes a small amount of effort to extract the data and get it into a format suitable for long-term reports, but once the pieces are in place it’s a relatively simple task.
More information on the Export-ActiveSyncLog cmdlet can be found on the Microsoft Exchange TechNet web site can be found on the Microsoft Exchange TechNet web site