Analyse trafic site web, SQL Server, procédure stockée


			dossier			cerig.efpg.inpg.fr
Vous êtes ici : Accueil > La technique > Internet et le Web > Site Web : analyse du trafic et mesure de l'audience > La procédure d'importation > Annexe 6 : la procédure stockée
Révision : 22 février 2005
Retour page 9	Site Web : analyse du trafic et mesure de l'audience	Retour sommaire

Florent BERLIAT et Jean-Claude SOHM (CERIG-EFPG) (18 juin 2004)
ANNEXE 7 : la procédure stockée d'importation

La procédure stockée qui figure ci-dessous correspond à l'importation du fichier journal quotidien. Elle s'exécute dans SQL Server.
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE    PROCEDURE [importation] AS

SET NOCOUNT ON 

DROP TABLE journal_brut
DROP TABLE danger
DROP TABLE echecs
DROP TABLE clients
DROP TABLE journal
DROP TABLE [URL-client_image]

CREATE TABLE journal_brut (
	[date] [smalldatetime] NOT NULL ,
	[heure] [nchar] (10) COLLATE French_CI_AS NULL ,
	[URL-client] [nvarchar] (15) COLLATE French_CI_AS NULL ,
	[méthode] [nvarchar] (10) COLLATE French_CI_AS NULL ,
	[requête] [nvarchar] (255) COLLATE French_CI_AS NULL ,
	[HTTP-code] [nchar] (10) COLLATE French_CI_AS NULL ,
	[Win-code] [nchar] (10) COLLATE French_CI_AS NULL ,
	[sc-octets] [nchar] (10) COLLATE French_CI_AS NULL ,
	[cs-octets] [nchar] (10) COLLATE French_CI_AS NULL ,
	[temps] [nchar] (10) COLLATE French_CI_AS NULL ,
	[protocole] [nvarchar] (10) COLLATE French_CI_AS NULL ,
	[logiciel-client] [nvarchar] (100) COLLATE French_CI_AS NULL ,
	[source] [nvarchar] (255) COLLATE French_CI_AS NULL 
) ON [PRIMARY]

CREATE TABLE danger (
	[date] [smalldatetime] NOT NULL ,
	[heure] [nchar] (10) COLLATE French_CI_AS NULL ,
	[URL-client] [nvarchar] (15) COLLATE French_CI_AS NULL ,
	[méthode] [nvarchar] (10) COLLATE French_CI_AS NULL ,
	[requête] [nvarchar] (255) COLLATE French_CI_AS NULL ,
	[HTTP-code] [nchar] (10) COLLATE French_CI_AS NULL ,
	[Win-code] [nchar] (10) COLLATE French_CI_AS NULL ,
	[sc-octets] [nchar] (10) COLLATE French_CI_AS NULL ,
	[cs-octets] [nchar] (10) COLLATE French_CI_AS NULL ,
	[temps] [nchar] (10) COLLATE French_CI_AS NULL ,
	[protocole] [nvarchar] (10) COLLATE French_CI_AS NULL ,
	[logiciel-client] [nvarchar] (100) COLLATE French_CI_AS NULL ,
	[source] [nvarchar] (255) COLLATE French_CI_AS NULL  
) ON [PRIMARY]

CREATE TABLE [echecs] (
	[date] [smalldatetime] NOT NULL ,
	[heure] [nchar] (10) COLLATE French_CI_AS NULL ,
	[URL-client] [nvarchar] (15) COLLATE French_CI_AS NULL ,
	[méthode] [nvarchar] (10) COLLATE French_CI_AS NULL ,
	[requête] [nvarchar] (255) COLLATE French_CI_AS NULL ,
	[HTTP-code] [nchar] (10) COLLATE French_CI_AS NULL ,
	[Win-code] [nchar] (10) COLLATE French_CI_AS NULL ,
	[sc-octets] [nchar] (10) COLLATE French_CI_AS NULL ,
	[cs-octets] [nchar] (10) COLLATE French_CI_AS NULL ,
	[temps] [nchar] (10) COLLATE French_CI_AS NULL ,
	[protocole] [nvarchar] (10) COLLATE French_CI_AS NULL ,
	[logiciel-client] [nvarchar] (100) COLLATE French_CI_AS NULL ,
	[source] [nvarchar] (255) COLLATE French_CI_AS NULL 
) ON [PRIMARY]

CREATE TABLE [clients] (
	[date] [smalldatetime] NOT NULL ,
	[URL-client] [nvarchar] (15) COLLATE French_CI_AS NULL ,
	[comptage] [int] NULL ,
	[logiciel-client] [nvarchar] (100) COLLATE French_CI_AS NULL 
) ON [PRIMARY]

CREATE TABLE [journal] (
	[date] [smalldatetime] NOT NULL ,
	[heure] [nchar] (10) COLLATE French_CI_AS NULL ,
	[URL-client] [nvarchar] (15) COLLATE French_CI_AS NULL ,
	[méthode] [nvarchar] (10) COLLATE French_CI_AS NULL ,
	[requête] [nvarchar] (255) COLLATE French_CI_AS NULL ,
	[HTTP-code] [nchar] (10) COLLATE French_CI_AS NULL ,
	[Win-code] [nchar] (10) COLLATE French_CI_AS NULL ,
	[sc-octets] [nchar] (10) COLLATE French_CI_AS NULL ,
	[cs-octets] [nchar] (10) COLLATE French_CI_AS NULL ,
	[temps] [nchar] (10) COLLATE French_CI_AS NULL ,
	[protocole] [nvarchar] (10) COLLATE French_CI_AS NULL ,
	[logiciel-client] [nvarchar] (100) COLLATE French_CI_AS NULL ,
	[source] [nvarchar] (255) COLLATE French_CI_AS NULL 
) ON [PRIMARY]

CREATE TABLE [URL-client_image](
	[URL-client] [nvarchar] (15) COLLATE French_CI_AS NULL
) ON [PRIMARY]


-- importation du fichier texte 

INSERT INTO journal_brut 
SELECT	[date] , heure , [URL-client] , methode, requete, [http-Code], [win-Code], [CS-octet],
	[SC-octet], [temp], protocole,[logiciel-Client], source 
FROM	[IMPORT-LOG]...ex#txt
WHERE	[date] IS NOT NULL 


-- ajout à la table danger 

INSERT INTO danger 
SELECT	* 
FROM	journal_brut 
WHERE	((requête LIKE '%..%' OR requête LIKE '%.exe%' OR requête LIKE '%winnt%' OR requête
	LIKE '%msadc%' OR requête LIKE '%telnet%' OR requête LIKE '%.ida%' OR requête LIKE '%vti_bin% OR
	requête LIKE '%mem_bin%' OR requête LIKE '%cgi-bin%' OR requête LIKE '%cgi-local%' OR requête
	LIKE '/script%' OR requête LIKE '/iisadmpwd%' OR requête LIKE '/pbserver%') OR
	(méthode NOT LIKE 'GET' AND méthode NOT LIKE 'HEAD' AND méthode NOT LIKE 'POST')) 


-- ajout à la table echecs
 
INSERT INTO echecs 
SELECT	journal_brut.* 
FROM	journal_brut LEFT outer JOIN Danger ON (journal_brut.requête = Danger.requête) AND
	(journal_brut.méthode = Danger.méthode) 
WHERE	(((Danger.méthode) Is Null) OR ((Danger.requête) Is Null)) and journal_brut.[HTTP-code]>399
	and journal_brut.requête not like '%favicon.ico'


-- ajout à la table journal sain 

INSERT INTO journal 
SELECT	journal_brut.[date], journal_brut.heure, journal_brut.[URL-client], journal_brut.méthode,
	journal_brut.requête, journal_brut.[HTTP-code], journal_brut.[Win-code], journal_brut.[sc-octets],
	journal_brut.[cs-octets], journal_brut.temps, journal_brut.protocole, journal_brut.[logiciel-client],
	journal_brut.source
FROM	journal_brut
WHERE	NOT (journal_brut.[HTTP-code]>399 or  ((requête LIKE '%..%' OR requête LIKE '%.exe%' OR requête
	LIKE '%winnt%' OR requête LIKE '%msadc%' OR requête LIKE '%telnet%' OR requête LIKE '%.ida%'
	OR requête LIKE '%vti_bin%' OR requête LIKE '%mem_bin%' OR requête LIKE '%cgi-bin%' OR
	requête LIKE '%cgi-local%' OR requête LIKE '/script%' OR requête LIKE '/iisadmpwd%' OR
	requête LIKE '/pbserver%') OR (méthode NOT LIKE 'GET' AND méthode NOT LIKE 'HEAD'
	AND méthode NOT LIKE 'POST')) OR requête like '/robots.txt') 


-- ajout à la la table client 

INSERT INTO clients 
SELECT	[date],[URL-client],COUNT(heure) AS Comptage, [logiciel-client] 
FROM	dbo.journal 
where	(dbo.journal.requête LIKE '%.htm%' OR dbo.journal.requête LIKE '%.php' OR 
	dbo.journal.requête LIKE '%.pdf'  OR dbo.journal.requête LIKE '%.txt'  OR 
	dbo.journal.requête LIKE '%.doc'  OR dbo.journal.requête LIKE '%.rtf') 
GROUP BY [URL-client], [date], [logiciel-client] 
ORDER BY COUNT(heure)DESC


-- ajout à la table URL client avec image

INSERT INTO [URL-client_image]
SELECT	[URL-client]
FROM	dbo.journal
WHERE	(requête LIKE N'%.gif') OR (requête LIKE N'%.jpeg') OR (requête LIKE N'%.jpg') OR (requête like N'%.png')
	OR (requête like N'%.swf') OR (requête like N'%.bmp') OR (requête like N'%.svg%') OR
	(requête LIKE N'%.jp2') OR (requête LIKE N'%.class')  OR (requête LIKE N'%.js') OR (requête LIKE N'%.css')
GROUP BY [URL-client]


-- ajout de la date à la table periode

INSERT INTO periode
SELECT	[date], DATEPART(yyyy, [date]), DATEPART(mm, [date]), DATEPART(dd, [date]), DATEPART(ww, [date]),
	DATEPART(qq, [date]), DATENAME(dw, [date]), DATENAME(mm, [date]), DATEPART(dw, [date])
FROM	journal_brut
GROUP BY [date]


-- ajout à la table des internautes 

INSERT INTO journal_des_internautes 
SELECT	dbo.journal.* 
FROM	(dbo.journal LEFT OUTER JOIN [URL-client_image] ON  (journal.[URL-client] =[URL-client_image].[URL-client]))left
	outer join journal_des_internautes on ((journal.[date] = journal_des_internautes.[date])
	and (journal.[heure] = journal_des_internautes.[heure]) and
	(journal.[requête] = journal_des_internautes.[requête]))
WHERE	(([URL-client_image].[URL-client]) is not null) and (dbo.journal.requête LIKE '%.htm%' OR 
	dbo.journal.requête LIKE '%.php' OR dbo.journal.requête LIKE '%.pdf'  OR 
	dbo.journal.requête LIKE '%.txt'  OR dbo.journal.requête LIKE '%.doc'  OR 
	dbo.journal.requête LIKE '%.rtf') and (journal_des_internautes.[date] is null OR
	journal_des_internautes.heure is null OR journal_des_internautes.[requête] is null) 
 

-- ajout à la table robots 

insert into journal_des_robots
SELECT     dbo.journal.* 
FROM         (dbo.journal LEFT OUTER JOIN [URL-client_image] ON  (journal.[URL-client] =[URL-client_image].[URL-client]))left outer join journal_des_robots on ((journal.[date] = journal_des_robots.[date]) and (journal.[heure] = journal_des_robots.[heure]) and (journal.[requête] = journal_des_robots.[requête]))
WHERE    (([URL-client_image].[URL-client]) is null)and (journal_des_robots.[date] is null OR journal_des_robots.heure is null OR journal_des_robots.[requête] is null)

-- ajout à la table marqueurs

insert into journal_des_marqueurs 
SELECT journal.[date], journal.heure, journal.[URL-client], journal.méthode, journal.requête, journal.[HTTP-code], journal.[Win-code], journal.[sc-octets], journal.[cs-octets], journal.temps, journal.protocole, journal.[logiciel-client], journal.source, journal.requête as nom_image, journal.[URL-client] as nom_client, '0'
from journal LEFT OUTER JOIN journal_des_marqueurs ON  ((journal.[date] = journal_des_marqueurs.[date]) and (journal.[heure] = journal_des_marqueurs.[heure]) and (journal.[cs-octets] = journal_des_marqueurs.[cs-octets]))
where journal.requête LIKE '/IMGCPT%gif' and (journal_des_marqueurs.[date] is null OR journal_des_marqueurs.heure is null OR journal_des_marqueurs.[cs-octets] is null)

-- modification de journal_des_marqueurs pour avoir seulement les images

UPDATE journal_des_marqueurs SET journal_des_marqueurs.nom_image = substring([nom_image],9,Len([nom_image])-12)
WHERE (((Left([nom_image],8)) like '/imgcpt/'))

UPDATE journal_des_marqueurs SET journal_des_marqueurs.nom_image = substring([nom_image],10,Len([nom_image])-16)
WHERE (((Left([nom_image],8)) like '/imgcpt2'))

UPDATE journal_des_marqueurs SET journal_des_marqueurs.nom_image = substring([nom_image],10,Len([nom_image])-21)
WHERE (((Left([nom_image],8)) like '/imgcpt3'))

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON

GO
Retour à la page 9	Retour sommaire

Accueil Technique Liens Actualités Formation Emploi Forums Base
Copyright © CERIG/EFPG 1996-2005
Mise en page : J.C. Sohm