Log-File analysis with Excel
A few days ago Tom Chmielarski wrote on SearchSecurity about the options of using Excel for security-log analysis.
“Microsoft Excel, already installed on most corporate desktops, is commonly under-appreciated by IT security practitioners”, he wrote.
Excel may not be the best way to analyze log-files, but it’s often the quickest and simplest. Since the limitation of 65,000 rows of data is gone in Office 2007 it is also possible to manage large amounts of data such as logging-tools generate.
Using Active Directory logs as an example, we can use Auto Filter to show only the messages of a given Event ID, such as 540 (successful log on). An other way to analyze the data is to use PivotTable Reports: From an authentication log you could see which users logged on to each server. If you have generated the tables it is very easy just to drag and drop analysis of the data.
So far Tom Chmielarski has not mentioned something new to me. I use Excel for ad hoc log-file analysis since a few years. For the big hit you have to use the on board IDE for Visual Basic for Applications (VBA). With VBA you can manage all these steps just by starting a VBA-macro. So you’re able to program every tool you want.
