toolsmith: Log Parser Lizard

Prerequisites

Windows

Microsoft Log Parser 2.2

Microsoft.Net 3.5

Introduction

At RSA Conference 2012 I gave a presentation called Evil Through The Lens of Web Logs. This presentation is built on research I’m conducting for a SANS Gold paper for graduate school and pays particular attention to SQL injection and Remote File Include attacks. One of the tools discussed as very useful for analysis tactics is Log Parser Lizard. You’re probably familiar with Log Parser, but I’ll bet you didn’t there was a great GUI-based tool with which to leverage its raw power with ease. Log Parser Lizard (LPL) is the brainchild of Dimce Kuzmanov, a Macedonian software engineer, who started Lizard Labs in 1998. In 2006 while also working as a part time sysadmin on financial systems, Dimce recognized that he was using Logparser on a daily basis for creating reports, analyzing logs, automatic error reporting, transferring data with txt files, etc. Over time his collection of queries became unmanageable and difficult to maintain so he created LPL for his personal use and because, having benefited from free software himself, wanted to release a useful freeware product to give back to the community. While LPL very successfully harnesses Log Parser’s capabilities Dimce firmly believes that as a great UI it help users learn and organize their queries with less effort. When he added log4net and regex input support the Logparser community really began to embrace LPL. LPL releases are a bit sporadic, usually based on a few new features, bug or code fixes and future releases are planned but not with a known frequency. Today LPL has a user base of about 2000 installations each month based on trend analysis for the last three years and approximately 80000 users worldwide.

The current production release of LPL is 2.1 and features include:

·         Ability to organize queries along with an improved source code editor that includes enhanced source navigation and analysis capability, syntax-highlighting, automatic source code completion, method insight, undo/redo, bookmarks, and more

·         Support for Facebook Query Language (FQL). This feature was introduced to help Facebook developers organize their queries

·         Code snippets (code templates) and constants. Log Parser Lizard also supports “constants” binding to static/shared properties from Microsoft .Net

·         Numerous other user-interface features including advanced grid with filtering and grouping as well as support for charts without requiring a Microsoft Office installation as is a dependcy for  a standalone instance of Logparser

·         Support for printing and exporting results to Excel and PDF documents

o   For registered users ($26.51 USD)

·         Support for inline VB.Net code to create LogParser SQL queries

Inline VB.net support allows you to drop your code between <% and %> marks; it will then be executed and the resulting string will be replaced in the query. Lizard Labs believes this feature will be very useful for LPL users. Before parsing logs you can move-copy-rename files, download via FTP, shutdown IIS, etc. You can also use .Net data types like DateTime for arithmetic operations and/or System.Environment settings in query parameters.

As I write this I’m testing the beta for LPL 2.5 and the new feature set includes:

·         Conditional field formatting (color, font, size, image) to identify required information. As an example, you can set the conditions to change error colors to red, warnings to yellow, etc. or highlight a specific field if it contains a string value of interest

·         Store and organize queries in SQL Server database for ease of use among multiple users and computers in an organization as well as backups, auditing and all other benefits that database storage allows

·         Excel-style row filtering

·         Ability to add columns with Excel style formulas (with most Excel functions) and support for exporting in Excel 2007 format (more than 65365 rows)

What would a toolsmith article be without a tool roadmap so let’s not break a good habit, eh? LPL 3.0 will likely include out of the box queries for IIS web reports (as in other commercial log analysis products), support for query execution scheduling, reports sent via e-mail from LPL, command line support, a query builder tool, text file input format (where a single file is one record and fields can be extracted with RegEx or with Logparser functions), and improved log4net input format. As with most of the tools we discuss, Dimce is certainly open to good ideas for the product and welcomes feedback and ideas from the user community. In total fantasy land the future of LPL may even include queries “in the cloud”, an LPL ASP.net web app that can be installed right on the server, a web service supporting LPL, mobile apps that can use this service, and a global query dictionary that users can submit, comment and rate the queries. “The future’s so bright, I gotta wear shades.” Whoa, 80’s flashback, sorry. 

Using Log Parser Lizard

Installing Log Parser Lizard is so straightforward it doesn’t even warrant a section. Ensure you have Log Parser and .Net 3.5 installed, then execute the LPL installer. Finito.

As described above, I’ve been working on research for a paper which includes analysis of a mass SQL injection attack, well described in detail this past December by Mark Hofman on the SANS Internet Storm Center Diary. In addition to Mark’s analysis, this popular post included many comments and replies from readers who had suffered or noted the attack in their logs and even some helpful folks who submitted log samples. You likely remember the LizaMoon attack and the Lilupophilupop attack was quite similar. In both cases, injected sites offered a URL that then caused redirection to a fake antivirus offering. Specifically, was embedded in victim sites where sl.php bounced you to the likes of hxxp://ift72hbot.rr.nu, the on to rogue AV. I actually had to look up the .rr.nu TLD; it’s the Republic of Moldova, and has been implicated recently in massive SPAM campaigns as well as the current WordPress hacks (as of this writing). 

Figure 1 represents a victim site still exhibiting typical signs of compromise.

Figure 1: Lilupophilupop victim site

 Victim sites were most often running ASP.net apps on IIS with MS-SQL back-ends. It was quickly learned that a few identifying traits of the Lilupophilupop attack included the fact that a rather large hex blob that was evident in IIS logs. I’ve always found that checking logs for 500 errors when analyzing for SQL injection attacks can typically point you down the right path. Using a log file submitted by an ISC reader (anonymized for obvious reasons), I first built a query to seek ASP application errors from a default query included in LPL. I launched LPL, clicked IIS Logs, then ASP App Errors, replaced #IISW3C# in the FROM statement with the path to my anonymized log file, and finally clicked Run Query as seen in Figure 2. Email me if you’d like me send you the log file so you can experiment for yourself.

Figure 2: LPL parsing error messages

Using this query, including FROM D:\logs\lilupophilupop\ex111201anon.log WHERE (sc-status = 500) AND (cs-uri-stem LIKE '%.asp'), prior to being aware of lilupophilupop as a keyword or part of an injected URL, would have immediately narrowed the search vectors.

Also common to attacks of this nature might be a DECLARE statement (defines variable(s)) visible in logs. A query as seen in Figure 3 produced three results that included a DECLARE statement followed by a CAST (converts an expression of one data type to another) statement wherein an attempt to pass the hex blob to the backend was noted.

Figure 3: LPL parsing DECLARE statements

 I clicked one of the results from 78.46.28.97, chose Select All, then Copy, and dropped the content to a text editor. I then grabbed the hex from just after the CAST statement to just prior to the AS VARCHAR statement and copied into a Burp Suite decoder window and chose decode as ascii hex.

Figure 4 shows the converted attack string.

Figure 4: Burp decoder converts hex

Long and short of it, the attack loops through all columns in all tables and updates their value by adding JavaScript to point to hxxp://lilupophilupop.com/sl.php.

This took all of 5 to 10 minutes with LPL and a little experimentation. Yes, you can do all of this with Log Parser at the command line but if you’re looking for strong query management, tidy reporting exports including charts, and downright convenience, LPL is the way to go.

In Conclusion

Log Parser Lizard is one of those indispensable tools that treads lightly on your system but offers a huge bang for the buck. Free or $26? Puhleeze. Keep in mind that while I used an IIS log sample for the article you can throw LPL at generic XML, CSV, TSV and W3C based logs all day long. Download it and put it to good use right away. Dimce would love to hear from you, and I look forward to hearing your success stories.

Ping me via email if you have questions (russ at holisticinfosec dot org).

Cheers…until next month.

Acknowledgements

Dimce Kuzmanov, lead developer and founder, Lizard Labs

---