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.
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.
Step5 : Now Click on “Configure” button and supply Data Source name and your instance name at which you have created database IISLogs.
Step6 : Select SQL Server Authentication and provide login id and password for sql server connection.
Step7 : Check Configuration.
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.
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