-- GuilleSQL - Un portal sobre Microsoft SQL Server en Castellano
-- https://guillesql.es
--

TRUNCATE TABLE dbo.STG_IISLog
GO

INSERT INTO dbo.STG_IISLog
SELECT 'ServerName', LogIIS.*
FROM OPENROWSET
(
   BULK 'c:\temp\logiis.log', 
   FORMATFILE='c:\temp\LogIIS.fmt', 
   FIRSTROW=5
) AS LogIIS
GO
INSERT INTO dbo.DW_DIM_ClientIP (ClientIP)
SELECT DISTINCT A.ClientIP
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_ClientIP AS B
ON A.ClientIP = B.ClientIP
WHERE B.ClientIP IS NULL

INSERT INTO dbo.DW_DIM_ClientUserName(ClientUserName)
SELECT DISTINCT A.ClientUserName
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_ClientUSerName AS B
ON A.ClientUserName = B.ClientUserName
WHERE B.ClientUserName IS NULL

INSERT INTO dbo.DW_DIM_Method (Method)
SELECT DISTINCT A.Method
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_Method AS B
ON A.Method = B.Method
WHERE B.Method IS NULL

INSERT INTO dbo.DW_DIM_ServerIP (ServerIP)
SELECT DISTINCT A.ServerIP
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_ServerIP AS B
ON A.ServerIP = B.ServerIP
WHERE B.ServerIP IS NULL

INSERT INTO dbo.DW_DIM_ServerName (ServerName)
SELECT DISTINCT A.ServerName
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_ServerName AS B
ON A.ServerName = B.ServerName
WHERE B.ServerName IS NULL

INSERT INTO dbo.DW_DIM_SiteName (SiteName)
SELECT DISTINCT A.SiteName
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_SiteName AS B
ON A.SiteName = B.SiteName
WHERE B.SiteName IS NULL

INSERT INTO dbo.DW_DIM_URIquery (URIquery)
SELECT DISTINCT A.URIquery
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_URIquery AS B
ON A.URIquery = B.URIquery
WHERE B.URIquery IS NULL

INSERT INTO dbo.DW_DIM_URIstem (URIstem)
SELECT DISTINCT A.URIstem
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_URIstem AS B
ON A.URIstem = B.URIstem
WHERE B.URIstem IS NULL
GO
INSERT INTO dbo.DW_TH_IISLog
(
	ServerName_ID
	,DateTimeUTC
	,SiteName_ID
	,ServerIP_ID
	,Method_ID
	,URIstem_ID
	,URIquery_ID
	,Port
	,ClientUSerName_ID
	,ClientIP_ID
	,Status
	,SubStatus
	,Win32Status
)
SELECT 

	ServerName.ServerName_ID
	,CAST(REPLACE([date], '-','') + ' ' + [time] AS DATETIME) AS DateTimeUTC
	,SiteName.SiteName_ID
	,ServerIP.ServerIP_ID
	,Method.Method_ID
	,URIstem.URIstem_ID
	,URIquery.URIquery_ID
	,STG.Port
	,ClientUSerName.ClientUSerName_ID
	,ClientIP.ClientIP_ID
	,STG.Status
	,STG.SubStatus
	,STG.Win32Status

FROM dbo.STG_IISLog AS STG
	LEFT JOIN dbo.DW_DIM_ClientIP AS ClientIP
		ON STG.ClientIP = ClientIP.ClientIP
	LEFT JOIN dbo.DW_DIM_ClientUSerName AS ClientUSerName
		ON STG.ClientUserName = ClientUSerName.ClientUserName
	LEFT JOIN dbo.DW_DIM_Method AS Method
		ON STG.Method = Method.Method
	LEFT JOIN dbo.DW_DIM_ServerIP AS ServerIP
		ON STG.ServerIP = ServerIP.ServerIP
	LEFT JOIN dbo.DW_DIM_ServerName AS ServerName
		ON STG.ServerName = ServerName.ServerName
	LEFT JOIN dbo.DW_DIM_SiteName AS SiteName
		ON STG.SiteName = SiteName.SiteName
	LEFT JOIN dbo.DW_DIM_URIquery AS URIquery
		ON STG.URIquery = URIquery.URIquery
	LEFT JOIN dbo.DW_DIM_URIstem AS URIstem
		ON STG.URIstem = URIstem.URIstem
WHERE [Date] NOT Like '#%'	-- Excluir encabezados
GO