en Passant not letting life just pass on by

I was recently tasked with the process of setting up a fairly automated report to display each time one of our Executive Management Team's mailboxes had been accessed by internal users.

We had already turned on the log generation on the server according to the Microsoft tech article 867640. This generates a 1016 event in the server's application log each time a mailbox is accessed.

According to MS this includes:

• When an account logs on to a mailbox successfully.
• When a user tries to access another user’s mailbox folders, such as the Inbox, Calendar, Contacts, Journal, Tasks, or Notes folders.
• When an account logs on to a mailbox, Mailbox Resources is updated to show that the Exchange Service Account has logged on to the System Attendant mailbox.

In addition to malicious intent, each time someone books a meeting with another person, a backup is ran that uses a MAPI connection, or services like Blackberry Enterprise Server, accesses a mailbox they will also be annotated in the app logs. Plus, this report is destined for Sr. Management, so sending a dump of the Application logs was out of the question.

LogParser should already be your best friend. You can use this versatile tool to query any ASCII log file or server Event logs to pull out information.

Setup
The attached batch file, runs a logparser query against a mailbox server and generates a SUMMARY.CSV file.

1. Decompress ZIP file into a folder C:\SUMMARY
2. Modify SQL file to reflect your environment.
   • Change SMTP Addresses and Display to match those you wish to query
   • Change FROM Server to match the mailbox server containing these users
3. Run the BATCH file.

Details
We run this batch 6 times a day (using a Windows Scheduled task), creating a 1kb file for the 20 something Executives we monitor. After a month, I have almost 1mb of log files. The script is designed to pull information only from the last time it ran, so no overlap. This batch creates a new file with updates since the last run, then rebuilds the SUMMARY.CSV file.

The summary contains the display name of the executive, date of mailbox access, the domain account that accessed the mailbox, and how many times on that day.

I've expanded this by exporting all active mailboxes in the domain (see HTA coming soon) and import that into an Access database. I then created a 'linked table' connection to the CSV. Using a simple query to correlate the domain account, to a display name from Active Directory. (I only need to update the AD Export, when a account does not resolve correctly in the query.) Then I use Crystal Reports, pulling from the Access Query, to filter the information, generate summaries, etc.

Future
To expand upon this, we've considered porting the data collected to a SQL server (which Logparser handles nicely). Until then I have a simple query I can run anytime, and get relatively up-to-date access reports for these users.