BULK insert IIS
/SQL/BULK insert IIS.sql
--CREATE TABLE _my_log(
---- my_id numeric(18, 0) IDENTITY(1,1) NOT NULL,
---- my_date datetime NOT NULL DEFAULT (GETDATE()),
---- my_info varchar(8000) NULL,
--[date] varchar(80), -- 2012-12-24
--[time] varchar(80), -- 00:00:35
--[cs-method] varchar(80), -- POST
--[cs-uri-stem] varchar(8000), -- /Catalogue/Catalogue_envoi.asp
--[cs-uri-query] varchar(8000), -- -
--[c-ip] varchar(80), -- 96.47.225.74
--[cs(Cookie)] varchar(800), -- ASPSESSIONIDCADRCSST=JHMNJIMDFEFLPCMOGJJHNNLM
--[cs(Referer)] varchar(8000), -- http://www.odalys-vacances.com/catalogue/Catalogue_commande.asp
--[cs-host] varchar(80), -- www.odalys-vacances.com
--[sc-status] varchar(80), -- 404
--[sc-substatus] varchar(80), -- 0
--[time-taken] varchar(80) -- 234
---- CONSTRAINT pk_my_log PRIMARY KEY CLUSTERED (my_id ASC)
---- WITH (PAD_INDEX = OFF
---- , STATISTICS_NORECOMPUTE = OFF
---- , IGNORE_DUP_KEY = OFF
---- , ALLOW_ROW_LOCKS = ON
---- , ALLOW_PAGE_LOCKS = ON
---- , FILLFACTOR = 90
---- )
--)
-- SELECT TOP 50 * FROM #_my_log
-- DROP TABLE #_my_log
-- TRUNCATE TABLE #_my_log
-- INSERT INTO _my_log(my_info) VALUES('XXX')
--BULK INSERT _my_log
--FROM 'I:\logs_site_sirocco fev mars 2013\ex130220.log'
--WITH
--(
-- FIELDTERMINATOR = ' ',
-- ROWTERMINATOR = '\n',
-- MAXERRORS = 5000
--)
--GO
-- SELECT COUNT(*) FROM _my_log
-- EXEC sp_spaceused '_my_log'
-- SELECT TOP 5000 * FROM _my_log
-- SELECT TOP 14000 * FROM _my_log WHERE [c-ip] = '195.167.196.100' -- 195.167.196.100
SELECT TOP 500 [c-ip], COUNT(*) FROM _my_log GROUP BY [c-ip] ORDER BY COUNT(*) DESC
SELECT COUNT(*) FROM _my_log WHERE [cs(Referer)] NOT LIKE 'http://www.odalys-vacances%'
SELECT TOP 500 * FROM IIS WHERE [cs(Referer)] LIKE 'http://www.odalys-vacances.com/Location-de-vacances-au-Cap-d-Agde%'
--INSERT INTO urls(url)
--SELECT SUBSTRING([cs(Referer)], 31, 8000) FROM IIS
--WHERE 1=1
--AND [cs(Referer)] LIKE 'http://www.odalys-vacances.com/%'
SELECT COUNT(*), url FROM urls GROUP BY url ORDER BY COUNT(*) DESC
--DELETE urls FROM urls
--WHERE 1=2
--OR url LIKE '/partenaire%'
--OR url LIKE '/resa/%'
--OR url LIKE '/sejour/%'