次の方法で共有

Import and analyze IIS Log files using SQL Server

  • [アーティクル]

IIS generates logs where are recorded many information about HTTP requests such as what Url was called, when the request happened, what is the origin, etc. If you want to analyze information from log files you can use use text search, regular expressions, or some log analysis tools; however, this might be tedious job. SQL Server enables you to import information from IIS log files into tables and use T-SQL language to analyze information from logs. In this post you can find how to load log files generated by IIS into SQL Server table using BULK INSERT commands, and analyze the data using T-SQL.

IIS Log files

IIS generates textual log files in following format:

 #Software: Microsoft Internet Information Services 10.0
#Version: 1.0
#Date: 2016-12-14 20:43:33
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatus sc-win32-status time-taken
2016-12-14 20:43:33 10.0.0.4 GET /AdventureWorks - 80 - 168.62.177.232 Mozilla/5.0+(compatible;+MSIE+9.0;+Windows+NT+6.1;+Trident/5.0;+AppInsights) - 404 0 2 753
2016-12-14 20:43:33 10.0.0.4 GET /AdventureWorks/Employees/Create - 80 - 70.37.147.45 Mozilla/5.0+(compatible;+MSIE+9.0;+Windows+NT+6.1;+Trident/5.0;+AppInsights) - 404 0 2 7613
2016-12-14 20:44:07 10.0.0.4 GET /AdventureWorks/Employees/Create - 80 - 65.54.78.59 Mozilla/5.0+(compatible;+MSIE+9.0;+Windows+NT+6.1;+Trident/5.0;+AppInsights) - 404 0 2 54
2016-12-14 20:44:38 10.0.0.4 GET /AdventureWorks - 80 - 94.245.82.32 Mozilla/5.0+(compatible;+MSIE+9.0;+Windows+NT+6.1;+Trident/5.0;+AppInsights) - 404 0 2 202
2016-12-14 20:45:05 10.0.0.4 GET /AdventureWorks - 80 - 207.46.98.172 Mozilla/5.0+(compatible;+MSIE+9.0;+Windows+NT+6.1;+Trident/5.0;+AppInsights) - 404 0 2 43

These are textual files where cells are separated with space, and lines are separated with new-line. This can be easily imported into SQL Server using bcp, BULK INSERT commands.

Analyzing log files in SQL Server

First we need to create a table where IIS log files will be stored. Example is shown in the following code:

 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
)

When you look at the log file, you will see a line starting with #Fields: where you can see all columns that should be placed in destination table.

 #Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatus sc-win32-status time-taken

You can create the table y looking in this list.
Note that I'm using CLUSTERED COLUMNSTORE INDEX on this table. This is not mandatory, but CCI is good solution for logs because it provides high compression of data and speed-up analytic.

Loading and analyzing logs

Now when we have destination table, we can load logs using BULK INSERT command:

 BULK INSERT dbo.IISLog
FROM 'D:\Data\Documents\u_ex161214.log'
WITH (
 FIRSTROW = 2,
 FIELDTERMINATOR = ' ',
 ROWTERMINATOR = '\n'
)

You can use space and new line as terminator. I'm using FIRSTROW=2 to ignore header row. Now, when we have all data in table, we can use standard SQL to analyze it:

 select [cs-uri-stem], avg([time-taken])
from dbo.IISLOG
group by [cs-uri-stem]
order by avg([time-taken]) desc

Once you load logs into table, you can perform any kind of analysis using T-SQL.

Comments

  • Anonymous
    February 10, 2017
    Please use schema names - (dbo at least)
  • Anonymous
    February 10, 2017
    analyze the date using T-SQL. => analyze the data using T-SQL.
  • Anonymous
    February 10, 2017
    Hi, I notice that the IIS logfile will also write header information when the related app pool recycles.Can this also be taken into account with the bulk insert mechanism?Or would it be 'better' to use for example LogParserStudio to transform the original IIS logfiles into CSV files and import those?
  • Anonymous
    July 11, 2017
    I've detected that the field size are too small in some scenarioss-ip|c-ip: 48 (for ipv6, esp. ipv4 mapped)s-username: 128 (e.g. logs from a TFS AppTier using PAT authentication)sc-win32-STATUS: BIGINT (got 2148074248 in some logs)
  • Anonymous
    September 09, 2018
    The comment has been removed