Skip to the content

Hoe importeer ik IIS logfiles in SQL Server

Om verschillende redenen analyseer ik de IIS Logfiles, die standaard worden opgeslagen in inetpub\logs\LogFiles maar waar je uiteraard ook een eigen locatie voor kunt instellen. Uit deze logfiles haal ik een aantal zaken die iedere ochtend middels SQL Server Mail worden verzonden waarmee ik direct een goed overzicht heb van eventuele afwijkingen. In dit blog de methode om een IIS logfile te importeren in SQL Server.

Op internet zijn verschillende blogs te vinden over dit onderwerp maar helaas zijn deze alleen maar bruikbaar als je een standaard log gebruikt. Het is ook best lastig want het tekstbestand bevat naast de logregels ook kopregels die beginnen met een hastag #. In deze kopregels is informatie terug te vinden over het bestand en kan uit 2 tot 4 regels bestaan. Daarnaast gooien leestekens roet in het eten.

Om al dit soort problemen te omzeilen en een betrouwbaar import script te schrijven maak ik gebruik van de bulkimport functie en maak ik van iedere regel een tekstregel waarbij de gegevens met een scheidingsteken zijn gescheiden. Ik kies voor het scheidingsteken ^ om de simpele reden dat dit karakter niet voorkomt in de logbestanden.

Functie voor het splitsen van een string

Op de eerste plaats heb ik een functie gevonden die de string (tekstregel) kan splitsen en de waarde op een vastgestelde positie terug geeft. (https://social.technet.microsoft.com/wiki/contents/articles/26937.t-sql-splitting-a-string-into-multiple-columns.aspx)

De functie STRING_SPLIT van SQL Server geeft de gesplitste waarde in een tabelvorm weer. Ook prima bruikbaar maar toch levert het selecteren van een specifieke rij op gezette tijden een probleem op.

Met onderstaande functie geef je de string mee die je wilt scheiden, het scheidingsteken en de positie van de waard in de string.

Dus bijvoorbeeld : Uit onderstaande string wil ik graag de waarde W3SVC99 ophalen.

2019-05-11^00:00:38^W3SVC99^bbb-1234^000.000.0.000^GET^/favicon.ico^-^443^-^000.000.000.000^HTTP/1.1^Googlebot-Image/1.0^-^-^reinder.eu^404^0^2^621^367^46

In SQL Server roep ik dan de functie als volgt aan waarbij @CSV de representatie is van de bovenstaande string, 3 de waarde uit de string gerekend vanaf 1, en ^ het scheidingsteken.

dbo.UFN_SEPARATES_COLUMNS(@CSV, 3, '^')

CREATE FUNCTION dbo.UFN_SEPARATES_COLUMNS(
 @TEXT      varchar(8000)
,@COLUMN    tinyint
,@SEPARATOR char(1)
)RETURNS varchar(8000)
AS
  BEGIN
       DECLARE @POS_START  int = 1
       DECLARE @POS_END    int = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
 
       WHILE (@COLUMN >1 AND @POS_END> 0)
         BEGIN
             SET @POS_START = @POS_END + 1
             SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
             SET @COLUMN = @COLUMN - 1
         END
 
       IF @COLUMN > 1  SET @POS_START = LEN(@TEXT) + 1
       IF @POS_END = 0 SET @POS_END = LEN(@TEXT) + 1
 
       RETURN SUBSTRING (@TEXT, @POS_START, @POS_END - @POS_START)
  END
GO

Tabel maken voor resultaten IIS Logbestand

Voer onderstaande tabelmaak query uit in SQL Server waarbij je de velden die in je log bestand niet voorkomen verwijderd en de velden die je nog mist toevoegt.

/****** Object:  Table [dbo].[IISLOG]    Script Date: 5/12/2019 12:12:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[IISLOG](
	[DATE] [date] NULL,
	[TIME] [time](7) NULL,
	[s-sitename] [varchar](255) NULL,
	[s-computername] [varchar](100) 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-version] [varchar](100) NULL,
	[cs(User-Agent)] [varchar](1024) NULL,
	[cs(Cookie)] [varchar](8000) NULL,
	[cs(Referer)] [varchar](4096) NULL,
	[cs-host] [varchar](255) NULL,
	[sc-STATUS] [int] NULL,
	[sc-substatus] [int] NULL,
	[sc-win32-STATUS] [bigint] NULL,
	[sc-bytes] [int] NULL,
	[cs-bytes] [int] NULL,
	[time-taken] [int] NULL,
	[Id] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_IISLOG] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Importeren IIS Logbestand in SQL Server

Nu kunnen we de data uit de logbestanden importeren. Een veelgehoorde opmerking is dat je een cursor moet vermijden in de scripts. Mede door de mate van vrijheid, in het manipuleren van data, kies ik toch voor een cursor ook omdat de snelheid van deze functie voor mijn doel niet echt belangrijk is. Toch ook hier de opmerking wees terughoudend in het gebruik van een cursor want eindeloze lussen kunnen je database heel snel vol laten lopen of je data beschadigen.

Voer onderstaande script uit in SQL Server waarmee het gekozen logbestand wordt geïmporteerd in je tabel. Pas wel de verwijzing naar je eigen logbestand aan en bewerk de INSERT INTO SQL nog naar de velden die jij gebruikt in je logbestand.

	--VARIABELEN
	DECLARE 
		@VALUE VARCHAR(MAX), 
		@CSV VARCHAR(MAX)

	--DECLAREER DE CURSOR
	DECLARE iis_cursor CURSOR FOR
	SELECT [Value],
		[csv] = CONVERT (
			VARCHAR(MAX),
			N'' +
			-- Issues met tekens wegnemen en velden scheiden met een ;
			REPLACE(REPLACE(REPLACE([Value],'&','&'),CHAR(13),''), ' ','^') + --^ gebruiken als scheidingsteken ivm tekens in strings
			N''
			)
	FROM OPENROWSET(
		BULK 'C:\inetpub\logs\LogFiles\W3SVC99\u_ex190511.log',
		SINGLE_CLOB
	) AS LogFile
	CROSS APPLY STRING_SPLIT(LogFile.BulkColumn,char(10))-- Char(10) is "Line Feed"
	where not [Value] LIKE '#%'

	OPEN iis_cursor
	FETCH NEXT FROM iis_cursor 
	INTO @VALUE, @CSV

	WHILE @@FETCH_STATUS = 0
	BEGIN
		--SPLITS DE STRING EN SCHRIJF WEG IN DE TABEL
		INSERT INTO [dbo].[IISLOG]
			([DATE]
			,[TIME]
			,[s-sitename]
			,[s-computername]
			,[s-ip]
			,[cs-method]
			,[cs-uri-stem]
			,[cs-uri-query]
			,[s-port]
			,[s-username]
			,[c-ip]
			,[cs-version]
			,[cs(User-Agent)]
			,[cs(Cookie)]
			,[cs(Referer)]
			,[cs-host]
			,[sc-STATUS]
			,[sc-substatus]
			,[sc-win32-STATUS]
			,[sc-bytes]
			,[cs-bytes]
			,[time-taken])
		VALUES
			(CAST(dbo.UFN_SEPARATES_COLUMNS(@CSV, 1, '^') AS DATE)
			,CAST(dbo.UFN_SEPARATES_COLUMNS(@CSV, 2, '^') AS TIME)
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 3, '^')
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 4, '^')
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 5, '^')
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 6, '^') 
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 7, '^') 
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 8, '^') 
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 9, '^') 
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 10, '^') 
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 11, '^') 
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 12, '^') 
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 13, '^') 
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 14, '^') 
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 15, '^') 
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 16, '^') 
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 17, '^') 
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 18, '^')
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 19, '^') 
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 20, '^') 
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 21, '^') 
			,dbo.UFN_SEPARATES_COLUMNS(@CSV, 22, '^'))
		
		FETCH NEXT FROM iis_cursor 
		INTO @VALUE, @CSV
	END
	CLOSE iis_cursor
	DEALLOCATE iis_cursor	

Ik hoop dat je wat aan dit blog hebt gehad en een leuke recentie of opbouwende kritiek op Facebook of LinkedIn is altijd welkom.