Insert IIS Logs Into SQL Server

Digging for information in IIS logs by opening individual files can be tiresome. Perhaps you’re tracking connections from a certain client. Maybe you were tasked with tracking what browsers are accessing your websites. Regardless of the reason, opening a file in notepad to scroll or pressing CTRL-F to dig for information isn’t fun. Instead, search IIS logs more efficiently by loading them into SQL Server.

Getting Ready to Insert

Let’s create a table to store our IIS logs.

CREATE TABLE dbo.IISLogs (
	[DATE] [DATE] NULL
	,[TIME] [TIME] NULL
	,[s-ip] [VARCHAR](48) NULL
	,[cs-method] [VARCHAR](8) NULL
	,[cs-uri-stem] [VARCHAR](255) NULL
	,[cs-uri-query] [VARCHAR](2048) NULL
	,[s-port] [VARCHAR](4) NULL
	,[s-username] [VARCHAR](256) NULL
	,[c-ip] [VARCHAR](48) NULL
	,[cs(User-Agent)] [VARCHAR](1024) NULL
	,[cs(Referer)] [VARCHAR](4096) NULL
	,[sc-STATUS] [INT] NULL
	,[sc-substatus] [INT] NULL
	,[sc-win32-STATUS] [BIGINT] NULL
	,[time-taken] [INT] NULL
	);
GO

Next, we’ll need to track down the location of our IIS logs. By default, these are stored in %SystemDrive%\inetpub\logs\LogFiles. To confirm the logging location, open up IIS and select your connection in the left pane:

Double click the Logging icon in the right pane to view the log file directory:

Bulking Up

Once we have our log file directory, we’re ready to load up our table with records. We can use BULK INSERT to load our IIS logs into the new IISLogs table.

BULK INSERT dbo.IISLogs
FROM 'C:\inetpub\logs\LogFiles\log20220731.log' WITH (
		FIRSTROW = 2
		,FIELDTERMINATOR = ' '
		,ROWTERMINATOR = '\n'
		);
GO

We have FIRSTROW set to 2 so that we skip column names in the file, FIELDTERMINATOR set to ‘ ‘ to separate our fields, and ROWTERMINATOR set to ‘\n’ to separate our rows by line.

Are you loading a few days worth of logs? Keep the same script from above, only change the date in your log file name, and re-run the BULK INSERT. You can continue loading more files to the same IISLogs table.

Once you have each of your logs loaded, that’s it! You’re ready to query your table of IIS logs for whatever your heart desires.

Nice to Know You

I haven’t had to load IIS logs into a table too often but it’s nice to know it’s an option. Loading logs into SQL Server and searching there can be a lifesaver.

You can also configure IIS to log directly into SQL Server but I haven’t personally come across a situation where that was needed. If that’s something that interests you then know that it’s possible.

Thanks for reading!