Logparser Script: Executive Mailbox Report Access Report

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.

Decompress ZIP file into a folder C:\SUMMARY
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
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.

Using LogParser to query Exchange 5.5 SMTP traffic

We are working to decommission our existing Exchange 5.5 environment and looking to migrate all services to Exchange 2003. As part of the decom, we need to re-direct the Internal SMTP traffic off our Exchange 5.5 bridgeheads. This meant, determining which servers were connecting to the server. First we changed all MX records in our DNS to point to the new server. Then we watched the Application logs on the server for MSExchangeIMC - ID: 2000 events.
This provided some 2,000 hits on our server. Ouch! Using LogParser from Microsoft, I was able to generate a quick query to pull the information.

Server	Hits
citrixserver.example.local	373
othersmtpserver.example.local	214
appserver.example.local.local	150
192.168.0.28	25
webserver.example.local	1

With this data, I was able to contact the server owners and have them change their relay information.
logparser "select trim(substr(strings,0,index_of(strings,'|'))) as Server, count(*) as hits into LogFile.csv from \\ExchangeServer\application where Sourcename like 'MSExchangeIMC' and TimeGenerated >= SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-01 00:00:00', 'yyyy-MM-dd hh:mm:ss')) and eventid=2000 group by strings order by hits DESC"
Let me break this down a bit.

What fields I want
This section determines which fields are pulled out. Strings is a Application Log field. The query pulls out the server name from this field, then counts each entry in the value HITS.
trim(substr(strings,0,index_of(strings,'|'))) as Server, count(*) as hits
Where to/from
Put the results in the specified log file when pulling from this server's app log. Putting commas between the different servers, you can list several there. (\\server1\application, \\server2\application).
into LogFile.csv from \\ExchangeServer\application
Give me only what's relevant:
Give me all MSExchangeIMC events where the ID = 2000 and in the last 2 days. The long line for 'timegenerated' is the long date difference field.
Sourcename like 'MSExchangeIMC' and eventid=2000 and TimeGenerated >= SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00:00', 'yyyy-MM-dd hh:mm:ss'))
Make it pretty
This summarizes the data and combines on the SERVER field. Then it sorts the group by HITS in descending order.
group by server order by hits DESC

This query runs in about 20 seconds on 2 servers with 3 days of data. Putting this into a BATCH file, I was able to make the script rather user friendly.