LogParser – Useful Logparser scripts

rakkimk — Wed, 19 Nov 2008 02:33:06 GMT

Logparser is a powerful utility which comes handy for me whenever I’m helping my customers facing a problem with slow running pages, frequently hit pages, post mortem analysis to find what went wrong on IIS, et al. You can use Logparser to parse your IIS logs to health check the state of your server, and the requests it had served. Below are few LogParser scripts, and their corresponding output in chart format – you can choose your own format, but isn’t a picture worth a 1000 words?

Note : change the GIF file location, and chartType to get more awesome images.

Finding Top 20 hit URLs

> logparser "select top 20 cs-uri-stem, count(*) as Hits into c:\mychart.gif from c:\inetpub\logs\logfiles\w3svc1\*.log group by cs-uri-stem order by Hits DESC" -i:w3c -chartType:barstacked -view:on

Finding Error codes

> logparser "select sc-status, count(*) as Hits into mychart.gif from c:\inetpub\logs\logfiles\w3svc1\*.log group by sc-status" -i:w3c -chartType:pieexploded3d -view:on

Finding the top 10 client IPs

> logparser "select top 10 c-ip, count(*) as Hits into mychart.gif from c:\inetpub\logs\logfiles\w3svc1\*.log group by c-ip order by Hits DESC" -i:w3c -chartType:bar3d -view:on

Slow running pages

> logparser "select top 10 cs-uri-stem, time-taken from c:\inetpub\logs\logfiles\w3svc1\*.log order by time-taken DESC" -i:w3c

cs-uri-stem                                                           time-taken
------------------------------------------                        ----------
/IRTcalculator/default.aspx                                  101262
/TestingWebsite/WebsiteThumbnailImage.aspx 42495
/IRTcalculator/default.aspx                                  33711
/Qod/ScoreCard.aspx                                           28672
/IRTcalculator/default.aspx                                  24802
/IRTcalculator/default.aspx                                  24169
/IRTcalculator/default.aspx                                  22732
/QoD/                                                                   20967
/IRTcalculator/default.aspx                                  20358
/IRTcalculator/default.aspx                                  19281

This above query will list you the top 10 URL’s which are taking more time – be informed that this time-taken field in IIS 6/7 will contain the network time to, this is just not only the IIS processing time.

More to come! Hope this helps.

How to configure IIS 7.0 for ODBC logging?

rakkimk — Wed, 16 Apr 2008 20:54:46 GMT

If you select Log File format as “Custom” in the IIS manager, it doesn’t give you options to configure ODBC logging in the UI. Instead, it just gives you an alert saying it cannot be configured through IIS manager which you already know.

But, in the previous versions of IIS, you would see the below:

So, in this post I will explain how to configure IIS7.0 site for ODBC logging. You still want to check out this KB for the database, table related information which needs to be created prior to this IIS configuration change.

Changing LogFile Format and the customLogPluginClsid

To configure ODBC, you might need to know the log plugin ID for the ODBC logging. In IIS 6.0, it was LogModuleId, and if you do a search for “ODBC logging” in your Metabase.XML file, you might find this property with value “{FF16065B-DE82-11CF-BC0A-00AA006111E0}”. We are going to use the same in IIS 7.0, but in the ApplicationHost.config file as customLogPluginClsid.

You need to find the <logFile> node in ApplicationHost.config, and that should look like below:

<logFile customLogPluginClsid="{FF16065B-DE82-11CF-BC0A-00AA006111E0}" logFormat="Custom" />

Below are the AppCmds to do this:

appcmd set site /site.name:"Default Web Site" /logFile.customLogPluginClsid:"{FF16065B-DE82-11CF-BC0A-00AA006111E0}"
appcmd site set /site.name:"Default Web Site" /logFile.logFormat:"Custom"

Now, we have just configured IIS to use ODBC logging for our default website. We still need to configure the required DSN name, table-name, username and password to do the ODBC logging.

Configuring ODBC logging parameters in ApplicationHost.config

After you’ve followed this KB article to create database, table, and DSN, you need to make sure you configure ApplicationHost.config to contain the information. You need to configure those settings in <odbcLogging> node under <system.webServer>. Below is my sample configuration:

<location path="Default Web Site">
<system.webServer>
<odbcLogging dataSource="ODBCLogging" tableName="HTTPLog" userName="Username" password="mypassword” />"
</system.webServer>
</location>

Below are the AppCmds to configure the above attributes for the site:

appcmd set config "Default Web Site" /section:odbcLogging /dataSource:"ODBCLogging" /commit:appHost
appcmd set config "Default Web Site" /section:odbcLogging /tableName:"ODBCLogTable" /commit:appHost
appcmd set config "Default Web Site" /section:odbcLogging /userName:"Username" /commit:appHost
appcmd set config "Default Web Site" /section:odbcLogging /password: "mypassword" /commit:appHost

Also, we do not support configuring ODBC logging feature in IIS using the SQL Native Client ODBC driver. You must use the SQL Server ODBC driver. You might want to take a look at this KB article on this.

Hope this helps! Do post a comment if you have any questions on this.

rakkimk : Logging

LogParser – Useful Logparser scripts

How to configure IIS 7.0 for ODBC logging?