Configure IIS 8.0 Log to SQL Server

Configure IIS 8.0 Log to SQL Server.

Before a months I had written a blog to insert IIS logs from log file to SQL Server using log parser tool.

I have configured IIS logs direct to SQL Serve instance by setting config files and using ODBC logging.

Step1 : Need to create one database and IISLog Table.


Use Master
Go
Create Database IISLogs
Go

Use IISLogs
Go
CREATE TABLE [dbo].[InternetLog](
[ClientHost] [varchar](255) NULL,
[username] [varchar](255) NULL,
[LogTime] [datetime] NULL,
[service] [varchar](255) NULL,
[machine] [varchar](255) NULL,
[serverip] [varchar](50) NULL,
[processingtime] [int] NULL,
[bytesrecvd] [int] NULL,
[bytessent] [int] NULL,
[servicestatus] [int] NULL,
[win32status] [int] NULL,
[operation] [varchar](255) NULL,
[target] [varchar](255) NULL,
[parameters] [varchar](255) NULL
)

Step2 : Configure changes in ApplicationHost.config file.

Path :
C:\Windows\System32\inetsrv\config\applicationHost.config
Add odbcLogging attribute under System.Webser.

<system.webServer>
<odbcLogging dataSource="IISLogs" tableName="InternetLog" userName="username" password="password" />
</system.webServer>

Step3 : Configure ODBC DataSource for ODBC logging, this will provide integration for IIS log to SQL Server Table.

–> On the server manager go to Tools menu top right side of window.
–> Select “ODBC Data Sources (32-bit)” or “ODBC Data Sources (64-bit)” which want to configure.

DNS1

 

Step4 : Select System DSN tab from ODBC Data Sources (64-bit)

–>Click on add button that will open “Create new Data Source” window.
–>Select SQL Server and give “IISLogs” to data source name.
–>Click Finish.

DNS3

 

Step5 : Now Click on “Configure” button and supply Data Source name and your instance name at which you have created database IISLogs.

DNS2

DNS5

Step6 : Select SQL Server Authentication and provide login id and password for sql server connection.

DNS6

 

Step7 : Check Configuration.

DNS7

 

Step8 : Select check box for “Save long running queries to the log file” and “Log ODBC driver statistics to the log file” and click on finish.

DNS8

 

Step9 : Configure APPCMD at  C:\Windows\System32\inetsrv

→ Configure Data Source.
appcmd.exe set config “Default Web Site” -section:system.webServer/odbcLogging /dataSource:”IISLogs” /commit:apphost
→ Configure Table.
appcmd.exe set config “Default Web Site” -section:system.webServer/odbcLogging /tableName:”InternetLog” /commit:apphost
→ Configure Login.
appcmd.exe set config “Default Web Site” -section:system.webServer/odbcLogging /userName:”sa” /commit:apphost
→ Configure Password.
appcmd.exe set config “Default Web Site” -section:system.webServer/odbcLogging /password:”password” /commit:apphost
→ Configure Default Format.
appcmd.exe set sites “Default Web Site” -logFile.logFormat:”Custom” /commit:apphost
appcmd.exe set sites “Default Web Site” -logFile.customLogPluginClsid:”{FF16065B-DE82-11CF-BC0A-00AA006111E0}” /commit:apphost
Note : Configuring IIS Logs to SQL Server is considered a bad practice and can be create performance issue.

Regards,

Nirav Gajjar