Auditing Export To Excel transactions via IIS logs

Posted by Mike Snyder on March 5, 2007  |  Comments (7)

Whenever I demo Microsoft CRM to prospects, one feature that I make sure that I ALWAYS show is the Export to Excel. Microsoft CRM can of course simply dump data into Excel (like many other CRM applications), but Microsoft CRM allows you to export data into a Dynamic Worksheet or a Dynamic PivotTable. By doing a dynamic Export, the Excel file maintains a live link to the Microsoft CRM database so that you can refresh the data in Excel at any time.

When executives see me demo the export the Excel feature one of the first questions out of their mouths is always "What prevents an employee from exporting all of our customer records into a file and then leaving the company!?". The answer is simple. You can disable the Export to Excel feature for users within their security role settings. But let's assume for a second that your company allows users to Export to Excel and you were curious about what type of data exports your employees have been performing. How could find out this information?

Well Microsoft CRM is really just a big giant web application at its foundation, and therefore it runs within Internet Information Services ("IIS"). And of course IIS records rather detailed log information about web site usage including IP addresses, pages requested, dates, times, querystrings and so on. Therefore to find out what types of Excel exports your Microsoft CRM users are doing, you can just poke around a little in the IIS log files.

If you're familiar with IIS, you probably already know how to find the location of the log files, but I'll give a quick recap for those who are not. On the Microsoft CRM server, open the Internet Information Services Manager and find the Microsoft CRM v3.0 website. Right-click on the website and select Properties. The following dialog will open:

Then select the Properties button, and this window will open:

From here you can see that the log file directory is c:\WINDOWS\system32\LogFiles\ and the folder for this Microsoft CRM website is W3SVC1 (but yours might be different). Now launch Windows Explorer and browse to this directory. You will see a bunch of files in this folder (one for each day) named with the exyymmdd.log naming convention.

Open any of these files with the text editor of your choice. Then search in the file for the text "mode=export". If someone exported data to Excel during that day, you'll see a line that looks something like this:

2007-03-05 00:59:14 W3SVC1 192.168.252.144 GET /_grid/print/print_dlg.aspx mode=export&multipage=0&hasfetch=0&showlive=1&enablelive=1&viewid=%7BE35F1FC9-9450-455B-8F2A-5B59BA193706%7D&viewtype=1039&otc=10016 80 - 192.168.252.200 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+6.0;+SLCC1;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506;+.NET+CLR+1.1.4322) 401 2 2148074254

For detailed information about what all of this stuff means, go read this. For the purposes of this post, we just care about two parts of this:

1. The viewid of =%7BE35F1FC9-9450-455B-8F2A-5B59BA193706%7D
2. The IP address of 192.168.252.200

One way to determine which view of data that the viewid relates to is to browse to (entering in the name of your Microsoft CRM server and the viewid you retrieved from the IIS log file):

http://<yourcrmserver>/tools/vieweditor/viewManager.aspx?id=<enterviewidhere>

If you copied and pasted correctly, then you'll see the view editor for the view that a user exported! For example, if you see the view editor for the Active Leads view then you know that a user exported the data from the Active Leads view. Now that you know what data was exported, you can use the IP address to determine which computer/user did the export. The NBSTAT -A command in DOS typically does the trick for me to translate an IP address to a NETBIOS computer name.

If you have a large number of Microsoft CRM users, the IIS log files will get quite large. Working with very large log files can be slow, and it can be cumbersome to search for the export information one day at a time. Therefore if you wanted to audit this Excel export information on a regular basis I would strongly recommend that you import the IIS log files into a SQL Server database and then write the appropriate queries, reports, etc.