Oct 072013
 

When you need quick analysis of your traffic logs you won’t find an better tool than Microsoft’s free Log Parser. With Log Parser you can read a variety of log files including the Registry and Windows event logs. It’s ease of use comes from using SQL queries against your log file. You can get your data even faster by using multiple log parser queries in a batch file.

image

The other day I was helping someone who needed some “top 10” data from their site’s log. Since I had these in my trusty batch file I could provide the text reports within seconds. However, I like to offer a little more pizzazz when possible so this time I decided use Log Parser’s native charting capability to output the results with some nice charts.  As the saying goes a picture is worth a thousand words.

Here’s the query I used to create the chart above:

logparser.exe -i:iisw3c "select top 10 cs-uri-stem, count(*)  into top10requests.gif 
from <file> group by cs-uri-stem order by count(*) desc" 
-o:CHART -chartType:pieexploded3d -categories:off -chartTitle:"Top 10 Requests"

 

Installing Office Web Components

Charting is a native feature of Log Parser however there is a dependency for Office 2003 Add-in: Office Web Components. Depending on where you are running Log Parser the first time you try to output your query to a chart you may see this error message:

Error creating output format “CHART”: This output format requires a licensed Microsoft Office Chart Web Component to be installed on the local machine

If you didn’t see the error above then you’re all set but if you saw the error then it will be necessary to install the Office Web Components before you can start outputting charts. Once you’ve downloaded the file just accept the License Agreement and click Install.

image

The installation runs quickly. Click OK to close the window.

image

 

Example Log Parser Reports with Charts

Now you’re ready to start creating some colorful charts. The most useful parameters in my opinion are –chartType, –chartTitle, –categories, –values, and –legend. There are some 20+ chart types that you can choose from including:  Pie, PieExploded, PieExlpoded3D, LineStacked, Line3D, BarClustered, ColumnClustered, Smooothline. The default chart type is Line.  To see all the possible chart options run this simple command:

LogParser -h -o:CHART

To take your charts to the highest level of customization you can use an external configuration script with Jscript or VBscript . Take a look at the MSDN ChartSpace Object Model documentation for more information.

Here are a few different charts with various options.

image

logparser.exe -i:iisw3c "select top 10 cs-uri-stem, count(*)  into top10requests.gif 
from x.log group by cs-uri-stem order by count(*) desc" 
-o:CHART -chartType:pieexploded3d -categories:off -chartTitle:"Top 10 Requests"

 

 

image

logparser.exe -i:iisw3c "select top 10 sc-status, count(*)  into top10errorcodes.gif 
from x.log group by sc-status having sc-status not in ('200') order by count(*) desc" 
-o:CHART -chartType:column3d -categories:on -values:on -chartTitle:"Top Status Codes"

 

 

image

logparser.exe -i:iisw3c "select top 10 cs-uri-stem, count(*)  into top10_404.gif 
from x.log group by cs-uri-stem, sc-status having sc-status in ('404') order by count(*) desc" 
-o:CHART -chartType:BarClustered3D -values:on -categories:on -chartTitle:"Top 10 404 Status"

 

image

logparser.exe -i:iisw3c "select quantize(time, 60) as TimeGenerated, count(*) as Hits into 
hitsperminute.gif from %1 group by TimeGenerated" -o:chart -chartType:Line –chartTitle:"Hits per Minute"

 

 

 

image

 

logparser.exe -i:iisw3c "SELECT TOP 10 cs-uri-stem AS RequestedFile, COUNT(*) AS TotalHits, 
MAX(time-taken) AS MaxTime, AVG(time-taken) AS AvgTime into slow.gif from x.log 
where EXTRACT_FILENAME(cs-uri-stem) not in('%begin%') GROUP BY cs-uri-stem ORDER BY MaxTime, TotalHits DESC" 
-o:CHART -chartType:barclustered3d -values:off -categories:on -chartTitle:"Top 10 Slowest Requests"

 

In Summary

Microsoft’s Log Parser is a powerful tool for log file analysis. You can use it to analyze text files, csv files, Window’s event logs and even the Windows Registry.  You can make boring reports come alive with colorful charts.  There is a dependency on Office Web Components for charting to work but that is easily solved. Thanks for reading.

Nov 102012
 

The other day I was checking the traffic stats for my WordPress blog to see which of my posts were the most popular. I was a little concerned to see that wp-login.php was in the Top 5 total requests almost every month.  Since I’m the only author on my blog my logins could not possibly account for the traffic hitting that page.

image

The only explanation could be that the additional traffic was coming from automated hacking attempts. Any server administrator concerned about security knows that “footprinting” is one of the first things a hacker will do when checking for vulnerable sites. I checked a few of my non-wordpress sites and sure enough every couple of days page requests were coming in for wp-login.php and urls for other CMS products such as Plesk’s Cpanel.

Using log parser I wanted to see how many IPs were hitting my login page during a particular month. I like to keep my complex queries in a separate query file and then output the results to a text file. In a previous post I showed how you can leverage this technique to automate log parser to run multiple queries at once. Here’s the query I used to check every log file for my site in the month of October. So this query outputs a simple text file clearly showing thousands of requests to wp-login.php from across the internet during the month of October.

logparser.exe file:wplogin.sql?destination=wpadmin.txt -o:NAT

Here is the SQL query contained in wplogin.sql. I am using a couple of advanced techniques such as searching in multiple log files and filtering the dates to get a results for one month:

SELECT c-ip, COUNT(*) AS TotalHits
FROM ./log/u_ex*.log  TO %destination%
where (EXTRACT_FILENAME(cs-uri-stem) = 'wp-login.php'
and TO_STRING(To_timestamp(date, time), 'MM')='10'
GROUP BY c-ip
ORDER BY TotalHits DESC

Having always followed best practices for hosting sites I was confident that my sites were not vulnerable to these automated probes but it was still a bit irksome to know they were occurring. And there’s always the possibility of some zero-day exploit showing up so I decided the simplest thing do was to block them. IIS now has a great new module available for Dynamic IP Restrictions however that would only block the requests once a threshold was reached. I needed something more “nuclear”. I wanted a means to block everyone hitting that page except for me and the help of the IIS Url Rewrite module a simple rewrite rule was going to do the trick.

In another previous post I showed how easy it was to use Url Rewrite for SEO and security. Now you can see how to tighten security even more by aborting requests based on almost any criteria. In my particular case I wanted to abort requests to wp-login.php from any IP address except for my own IP. So the next logical question is “what’s my ip address?” There are several sites online which can offer you this information. Probably the easiest to remember is whatsmyip.org. When you visit that site, your public IP address is displayed very clearly at the top of the page. This is the IP that you want to put in the rule below. It is important to note the use of “negate=true”. This means all IPs except for the one referenced. The next logical question is what if I need to login to WordPress from more than 1 IP address such as from home and at the office? This is a simple matter of just adding an additional condition to the rule below.

<rewrite>
      <rules>   
    <rule name="Block wp login" stopprocessing="true">
          <match url="(^wp-login.php)" ignorecase="true" />
          <conditions>
            <add negate="true" pattern="10\.10\.10\.10" input="{REMOTE_ADDR}" />
          </conditions>
          <action type="AbortRequest" />
        </rule>
      </rules>
<rewrite>

So in summary Log Parser makes it very easy to get quick extracts of data from your web site’s logs and IIS 7’s Url Rewrite module not only offers powerful rewriting and redirecting of urls but also an easy way to tighten the security of your site. Thanks for reading.

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS
Sep 252012
 

Microsoft’s Log Parser is a really powerful tool for searching log files. With just a few simple SQL commands you can pull more data than you ever imagined out of your logs. It can be used to read web site log files, csv files, and even Windows event logs. Log Parser isn’t intended to compete with stats products such as Webtrends Log Analyzer or  SmarterStats by Smartertools.com which I feel is the best on the market. 

I primarily use Log Parser for troubleshooting problems with a site such as identifying http errors or long running page requests. When I look into an issue I also like to get a snapshot for the day’s traffic such as the top IP addresses making requests and the top bots hitting the site. This is all available from Log Parser once you know the right queries to run.

As one can imagine when a site is having problem you want to know as much information as quickly as possible. Manually running Log Parser queries on a site’s log files is not easy when you have an urgent problem resolve.  My biggest challenge with using Log Parser is remembering  the different queries I want and getting the syntax correct.  Solving this challenge is easier than one might think and involves just creating the scripts ahead of time that you need.

I just create a batch file called logparse.bat and use the %1 parameter for the name of the log file that I want to analyze and then I redirect the output of the query to a text file:

Here are the queries I am using:

 
"c:\temp\logparser.exe" -i:iisw3c "select top 25 count(*), cs-uri-stem from %1 group by cs-uri-stem order by count(*) desc" -rtp:-1 >top25req.txt

"c:\temp\logparser.exe" "select Top 10 count(*), c-ip from %1 group by c-ip order by Count(*) DESC" -o:csv >topIP.txt

"c:\temp\logparser.exe" "select Top 10 count(*), c-ip, cs(User-Agent) from %1 group by c-ip, cs(User-Agent) order by Count(*) DESC" -o:csv >topBot.txt

Running this from the command line is simple. You just specify path to the log file as a parameter.


c:\temp\logparse c:\wwwlogs\w3svc1\u_ex120921.log

Running this will create 3 files showing the top 25 requests, top bots hitting the site and the top IP addresses making requests. This is valuable information but we really need more information to know what’s going on with our site. Here’s a more advanced query using a SQL file. Just as before we’re going to use parameters and then call it from our logparse.bat file. This query is stored in a separate file called topstatuscodes.sql

SELECT 	STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) 
AS Status, COUNT(*) AS Total FROM %source% to %destination% GROUP BY Status ORDER BY Total DESC

Here is how we call this external file from our logparse.bat file:

"c:\temp\logparser.exe" file:TopStatusCodes.sql?source=%1+destination=TopStatusCodes.txt -o:NAT 

Here is what the output of this particular query looks like:

Immediately we can see that our site had 1,564 HTTP 404 errors as well as 22 HTTP 500 errors. That many 404 errors indicates a problem with the site’s design and could impact performance. So let’s create another query and output the results to a file to see where the 404 errors are coming from:

SELECT
TOP 10
STRCAT(EXTRACT_PATH(cs-uri-stem),'/') AS RequestPath, sc-status,
EXTRACT_FILENAME(cs-uri-stem) AS RequestedFile,
COUNT(*) AS TotalHits,
MAX(time-taken) AS MaxTime,
AVG(time-taken) AS AvgTime,
AVG(sc-bytes) AS AvgBytesSent
FROM %source% TO %destination%
where sc-status=404
GROUP BY cs-uri-stem, sc-status
ORDER BY MaxTime, TotalHits DESC

Here is how we run this query from our logparse.bat file:

"c:\temp\logparser.exe" file:Top10-404-WebRequests.sql?source=%1+destination=Top10-404-WebRequests.txt -o:NAT

The output of this query provides all the information we need to track down the issue including request path and the requested file.


Let’s look at one more of my favorite Log Parser queries: the top 10 longest running page requests. This will show us the 10 slowest pages on the site. This can be invaluable information when diagnosing a problem. Here is the query which I save in a file called top10webrequests.sql:

SELECT
TOP 10
STRCAT(EXTRACT_PATH(cs-uri-stem),'/') AS RequestPath,
EXTRACT_FILENAME(cs-uri-stem) AS RequestedFile,
COUNT(*) AS TotalHits,
MAX(time-taken) AS MaxTime,
AVG(time-taken) AS AvgTime,
AVG(sc-bytes) AS AvgBytesSent
FROM %source% TO %destination%
GROUP BY cs-uri-stem
ORDER BY MaxTime, TotalHits DESC

Here is how we call it from our logparse.bat file. It will redirect the output to a file called top10webrequests.txt:

"c:\temp\logparser.exe" file:Top10WebRequests.sql?source=%1+destination=Top10WebRequests.txt -o:NAT

The numbers shown in this report seem incredibly high at first glance but do not be alarmed. The IIS log format uses microseconds so you have to divide each of the numbers by 1000. So by doing that we can see that one request took well over 2 minutes to complete and another averages 19 seconds to complete. These are red flags that need immediate investigation.

So in summary I have shown 6 great log parser queries you can you run from a batch file to automate your web log analysis. Anytime someone reports a performance problem you can provide valuable data within seconds. But this is really just scratching the surface. You could add 5-10 more queries to the batch file to get even more data depending on your needs.

Please note: if you use the examples I’ve provided be sure to change the paths to where your logparser.exe is located.

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS