Importing IIS logs into MS SQL
Occasionally I have the need to analyse IIS logs, and one quick and dirty way to accomplish this is to import them into SQL server.
It is possible to do this natively in SQL using the following commands:
DROP TABLE IF EXISTS dbo.IISLOG
CREATE TABLE dbo.IISLOG (
[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,
INDEX cci CLUSTERED COLUMNSTORE
)BULK INSERT dbo.IISLog
FROM 'c:\tmp\IisLogs\u_exyymmdd.log'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n'
)Typically, however, I may want to import 6 months worth of data. In the past I have looped through files, blah, blah, blah... and it has all got a bit long-winded.
Turns out there is a Microsoft utility that can bulk import IIS log files into SQL. It can be downloaded at: https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=24659
Importing the files is as simple as running the following one-liner in a command window:
PS C:\Program Files (x86)\Log Parser 2.2> .\logparser.exe "SELECT * INTO iisLogs FROM c:\tmp\IisLogs\u_ex*.log " -i:iisw3c -o:SQL -server:localhost\SQLExpress2017 -database:IisLogs -createTable: OnReference
- Good introduction: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/02/10/import-and-analyze-iis-log-files-using-sql-server/
- In depth explanation: http://ariely.info/Blog/tabid/83/EntryId/212/Parse-and-import-IIS-log-files-using-Transact-SQL.aspx
- Source idea for using Microsoft's Log Parser: https://chrisbitting.com/2012/04/12/importing-iis-logs-into-a-sql-database-table/