if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StrCHARINDEX]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[StrCHARINDEX]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cf_GetSplit]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[cf_GetSplit]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SummaryByAuthenticatedUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SummaryByAuthenticatedUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SummaryByBrowser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SummaryByBrowser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SummaryByCountry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SummaryByCountry]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SummaryByHour]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SummaryByHour]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SummaryByLength]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SummaryByLength]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SummaryByMonthYear]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SummaryByMonthYear]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SummaryByOS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SummaryByOS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SummaryByPopularPages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SummaryByPopularPages]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SummaryByWeekDay]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SummaryByWeekDay]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SummaryByYear]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SummaryByYear]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SummaryGeneral]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SummaryGeneral]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Code_Country]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Code_Country]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Code_OS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Code_OS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GlobalIISLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GlobalIISLog]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE FUNCTION StrCHARINDEX
  ( @expression1 nvarchar(4000),
    @expression2 nvarchar(4000),
    @start_location int = 0,
    @number int )
RETURNS int
AS
BEGIN
  DECLARE @i int, @position int
  SET @i = 1
  WHILE (@i <= @number) AND (CHARINDEX(@expression1, @expression2, 
@start_location) <> 0)
    BEGIN
      SET @position = CHARINDEX(@expression1, @expression2, @start_location)
      SET @expression2 = STUFF(@expression2,
                               CHARINDEX(@expression1, @expression2, 
@start_location),
                               len(@expression1),
                               space(len(@expression1)))
      SET @i = @i + 1
    END
  RETURN @position
END
/*
Returns the starting position of the n-th entering of the specified
expression in a character string.
Syntax
CHARINDEX ( expression1, expression2, start_location, number)
Arguments
expression1
Is an expression containing the sequence of characters to be found.
expression1 is an expression of the short character data type category.
expression2
Is an expression, usually a column searched for the specified sequence.
expression2 is of the character string data type category.
start_location
Is the character position to start searching for expression1 in
expression2. If start_location is a negative number, or is zero,
the search starts at the beginning of expression2.
number
Is an integer.
Return Types
int
*/


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


/*-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<StoredProcedure name="cf_GetSplit">
	<Object>	...																	</Object> 
	<History  Author     = "Philippe TROTIN - ptrotin@microsoft.com"  Date = "02/10/2003"> Core 2.0 								</History>
</StoredProcedure>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
CREATE FUNCTION cf_GetSplit
(
	@sText 	nvarchar(4000), 
	@sDelim 	nvarchar(20) = ' ', 
	@occ 		int
)
RETURNS nvarchar(1000)
AS
BEGIN
	DECLARE @Val_Next_1 int 
	DECLARE @Val_Next_2 int 
	DECLARE @bcontinue bit
	DECLARE @LenMax int
	DECLARE @sTemp nvarchar(4000)
	
	SET @LenMax = LEN(@sText)
	IF @occ = 1 OR @occ = 0
	BEGIN
		SET @Val_Next_1 = CHARINDEX(@sDelim, @sText)
		IF @Val_Next_1 <> 0
		BEGIN
			SET @sText = SUBSTRING(@sText, 1, @Val_Next_1 - 1)
		END
	END
	ELSE
	BEGIN
		SET @bcontinue = 1
		WHILE @bcontinue = 1
		BEGIN
			SET @occ = @occ - 1
		
			SET @Val_Next_1 = CHARINDEX(@sDelim, @sText)
			SET @Val_Next_2 = CHARINDEX(@sDelim, SUBSTRING(@sText, @Val_Next_1 + 1, @LenMax))
		
			SET @sText = SUBSTRING(@sText, @Val_Next_1 + 1, 100)
			
		 	--Exit the WHILE loop.
			IF @occ = 1 
			BEGIN
				IF @Val_Next_2 <> 0
				BEGIN
					SET @sText = SUBSTRING(@sText, 1, @Val_Next_2 - 1)
				END
		   		SET @bcontinue = 0
			END
		END
	END
		
	RETURN @sText
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE TABLE [dbo].[Code_Country] (
	[Country_Code] [varchar] (8) COLLATE French_CI_AS NOT NULL ,
	[Country_Name] [varchar] (50) COLLATE French_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Code_OS] (
	[OS_code] [varchar] (100) COLLATE French_CI_AS NULL ,
	[os_Name] [varchar] (500) COLLATE French_CI_AS NULL ,
	[os_Group] [varchar] (50) COLLATE French_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[GlobalIISLog] (
	[date] [datetime] NULL ,
	[time] [datetime] NULL ,
	[Hour] [varchar] (255) COLLATE French_CI_AS NULL ,
	[Month] [int] NULL ,
	[year] [int] NULL ,
	[cIp] [varchar] (255) COLLATE French_CI_AS NULL ,
	[csUsername] [varchar] (255) COLLATE French_CI_AS NULL ,
	[sSitename] [varchar] (255) COLLATE French_CI_AS NULL ,
	[sComputername] [varchar] (255) COLLATE French_CI_AS NULL ,
	[sIp] [varchar] (255) COLLATE French_CI_AS NULL ,
	[sPort] [int] NULL ,
	[csMethod] [varchar] (255) COLLATE French_CI_AS NULL ,
	[csUriStem] [varchar] (255) COLLATE French_CI_AS NULL ,
	[csUriQuery] [varchar] (255) COLLATE French_CI_AS NULL ,
	[scStatus] [int] NULL ,
	[scSubstatus] [int] NULL ,
	[scWin32Status] [int] NULL ,
	[scBytes] [int] NULL ,
	[csBytes] [int] NULL ,
	[timeTaken] [int] NULL ,
	[csVersion] [varchar] (255) COLLATE French_CI_AS NULL ,
	[csHost] [varchar] (255) COLLATE French_CI_AS NULL ,
	[csUserAgent] [varchar] (255) COLLATE French_CI_AS NULL ,
	[csCookie] [varchar] (255) COLLATE French_CI_AS NULL ,
	[csReferer] [varchar] (255) COLLATE French_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE NONCLUSTERED
	INDEX [IDX_sComputerName] ON [dbo].[GlobalIISLog] ([sComputername])
GO

CREATE NONCLUSTERED
	INDEX [IDX_year] ON [dbo].[GlobalIISLog] ([year])
GO

CREATE NONCLUSTERED
	INDEX [IDX_Month] ON [dbo].[GlobalIISLog] ([Month])
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE SummaryByAuthenticatedUser
AS
	SELECT     MONTH(date) AS Month, SUM((scBytes + csBytes) / 1024) AS nb_Kbytes, csUsername, COUNT(*) AS nb_visi
FROM         GlobalIISLog
GROUP BY MONTH(date), csUsername
HAVING      (csUsername IS NOT NULL)
ORDER BY COUNT(*) DESC
	RETURN 


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE procedure SummaryByBrowser 
	(@month int,
	@year int,
	@ComputerName varchar(50))
as 
Declare @Query varchar(1000)
Declare @Clause varchar(150)
set @Query = '
select   
MainBrowser = case substring(dbo.cf_GetSplit(csuseragent, '';'',2),1,3) 
	when ''+U''  then dbo.cf_GetSplit (dbo.cf_GetSplit(dbo.cf_GetSplit(csuseragent, '';'',6),''+'',4),
			''/'',1)
	when ''+MS'' then ''Microsoft Internet Explorer''
	else ''Unknown''
end,
Version = case substring(dbo.cf_GetSplit(csuseragent, '';'',2),1,3) 
	when ''+U'' then dbo.cf_GetSplit (dbo.cf_GetSplit(dbo.cf_GetSplit(csuseragent, '';'',6),''+'',4),''/'',2)
	when ''+MS'' then dbo.cf_GetSplit(dbo.cf_GetSplit(csuseragent, '';'',2) ,''+'',3)
	else ''Unknown''
end,
convert(varchar(10),date,103) [date]
from globaliislog'
if @ComputerName <> 'ALL'
	set @Clause = ' where month = ' + convert(varchar(2),@Month) + ' and year = ' + convert(char(4), @Year) + ' and sComputerName = ''' + @ComputerName + ''''
Else
	set @Clause = ' where month = ' + convert(varchar(2),@Month) + ' and year = ' + convert(char(4), @Year)
execute (@Query + @Clause)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE procedure SummaryByCountry  
	(@month int,
	@year int,
	@ComputerName varchar(50))
AS
Declare @Query varchar(500)
set @Query = '
select country_name, [date]
from globaliislog iis, code_country cc
where   dbo.cf_GetSplit(csuseragent, '';'',4) = cc.country_code
and month  = ' + convert(varchar(2),@Month) + ' and year = ' + convert(char(4), @Year)
if @ComputerName <> 'ALL'
	set @Query  = @Query + ' and sComputerName = ''' + @ComputerName + ''''
execute (@Query)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE SummaryByHour
	(@month int,
	@year int,
	@ComputerName varchar(50))
AS
Declare @Query varchar(500)
set @Query = '
	SELECT     MONTH(date) AS Month, COUNT(DISTINCT csUriQuery) AS nb_Pages, COUNT(*) AS nb_Hits, COUNT(DISTINCT cIp) AS nb_visitors, 
                      SUM((scBytes + csBytes) / 1024) AS nb_Kbytes, Hour AS Hour
	FROM  GlobalIISLog where  [month]  = ' + convert(varchar(2),@Month) + ' and [year] = ' + convert(char(4), @Year) 
if @ComputerName <> 'ALL'
	set @Query  = @Query + ' and sComputerName = ''' + @ComputerName + ''''
set @Query = @Query + ' GROUP BY MONTH(date), Hour'
	
execute (@Query)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE SummaryByLenght  
	(@month int,
	@year int,
	@ComputerName varchar(50))
AS
Declare @Query varchar(1000)
set @Query = '
select Lenght =
case 
	when sum(timetaken) between 0 and 60 then ''00-01 minutes''
	when sum(timetaken) between 61 and 300 then ''01-05 minutes''
	when sum(timetaken) between 301 and 600 then ''05-10 minutes''
	when sum(timetaken) between 601 and 1200 then ''10-20 minutes''
	when sum(timetaken) between 1201 and 1800 then ''20-30 minutes''
	when sum(timetaken) between 1801 and 3600 then ''30-60 minutes''
	when sum(timetaken) between 3600 and 7200 then ''01-02 hours''
	when sum(timetaken) between 7200 and 18000 then ''02-05 hours''
	else ''More than 5 hours ''
	end
from globalIISLog 
where  [month]  = ' + convert(varchar(2),@Month) + ' and [year] = ' + convert(char(4), @Year) 
if @ComputerName <> 'ALL'
	set @Query  = @Query + ' and sComputerName = ''' + @ComputerName + ''''
set @Query = @Query + ' GROUP BY cIp'
execute (@Query)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE  PROCEDURE dbo.SummaryByMonthYear
	(@month int,
	@year int,
	@ComputerName varchar(50))
AS
Declare @Query varchar(1000)
set @Query = '
SELECT     datepart(day, [date]) as [date], COUNT(DISTINCT csUriQuery) AS nb_Pages, COUNT(*) AS nb_Hits, COUNT(DISTINCT cIp) AS nb_visitors, 
                       isnull(SUM((scBytes + csBytes) / 1024),0) AS nb_Kbytes
FROM         GlobalIISLog where   [month]  = ' + convert(varchar(2),@Month) + ' and [year] = ' + convert(char(4), @Year) 
if @ComputerName <> 'ALL'
	set @Query  = @Query + ' and sComputerName = ''' + @ComputerName + ''''
set @Query = @Query + ' GROUP BY datepart(day, [date])'
execute (@Query)



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE SummaryByOS
(@month int,
	@year int,
	@ComputerName varchar(50))
AS
Declare @Query varchar(1000)
set @Query = '
	select count(*) as nb, os_name,os_group from globaliislog gil, code_os co
where co.os_code = dbo.cf_GetSplit(csuseragent, '';'',3)
and [month]  = ' + convert(varchar(2),@Month) + ' and [year] = ' + convert(char(4), @Year) 
if @ComputerName <> 'ALL'
	set @Query  = @Query + ' and sComputerName = ''' + @ComputerName + ''''
set @Query = @Query + ' group by os_group, os_name'
execute (@Query)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE procedure SummaryByPopularPages 
	(@Month int,
	@Year int,
	@ComputerName varchar(50))
as 
Declare @Query varchar(500)
Declare @ClauseComputerName varchar(50)
Set @Query = 'select   count(*) Nb , csUriStem Page,  sum(csBytes)/ 1024 BandWidth from GlobalIISLog where csUriStem is not null and month = ' + convert(varchar(2),@Month) + ' and year = ' + convert(char(4), @Year)
Set @ClauseComputerName = 'and sComputerName = ''' + @ComputerName + ''''
if @ComputerName <> 'ALL'
	execute (@Query + @ClauseComputerName + 'group by csUriStem order by nb desc')
Else
	execute (@Query + 'group by csUriStem order by nb desc')


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE SummaryByWeekDay
	(@month int,
	@year int,
	@ComputerName varchar(50))
AS
Declare @Query varchar(1000)
set @Query = 'SELECT     MONTH(date) AS Month, COUNT(DISTINCT csUriQuery) AS nb_Pages, COUNT(*) AS nb_Hits, COUNT(DISTINCT cIp) AS nb_visitors, 
                      SUM((scBytes + csBytes) / 1024) AS nb_Kbytes, DATENAME(weekday, date) AS dayweek
FROM         GlobalIISLog where   [month]  = ' + convert(varchar(2),@Month) + ' and [year] = ' + convert(char(4), @Year) 
IF @ComputerName <> 'ALL'
	set @Query  = @Query + ' and sComputerName = ''' + @ComputerName + ''''
set @Query = @Query + '  GROUP BY MONTH(date), DATENAME(weekday, date), DATEPART(dw, date) order by DATEPART(dw, date)  '
execute (@Query)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE SummaryByYear
	(@ComputerName as varchar(50) )
	AS
	SET NOCOUNT ON
	
Declare @Query varchar(1000)
set @Query = '	
SELECT     [Year], [Month], COUNT(DISTINCT cIp) AS nb_visitors, SUM((scBytes + csBytes) / (1024)) / 1024 AS nb_Mbytes
FROM         GlobalIISLog where  [Year] > Year(getdate()) -3'
IF @ComputerName <> 'ALL'
	set @Query  = @Query + ' and sComputerName = ''' + @ComputerName + ''''
set @Query = @Query + '  GROUP BY [Year], [Month]'
execute (@Query)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE SummaryGeneral 
	(@Month int,
	@Year int, 
	@ComputerName varchar(50) )
AS
SET NOCOUNT ON 
set ansi_warnings OFF
declare @query1 varchar(500)
declare @query2 varchar(500)
declare @query3 varchar(1000)
declare @query4 varchar(500)

set @query1 = 'create table #resultTable(Current_nb_Pages integer, Current_nb_Hits integer,Current_nb_visitors integer,
Current_nb_Kbytes integer,Previous_nb_Pages integer,Previous_nb_Hits integer,
Previous_nb_visitors integer,Previous_nb_Kbytes integer, sComputerName varchar(25) )'

set @query2 = 'insert into #resultTable(Current_nb_Pages, Current_nb_Hits, Current_nb_visitors, Current_nb_Kbytes, Previous_nb_Pages,
 Previous_nb_Hits, Previous_nb_visitors, Previous_nb_Kbytes, sComputerName )'

set @query3 = 'SELECT COUNT(DISTINCT csUriQuery) AS Current_nb_Pages, COUNT(*) AS Current_nb_Hits, COUNT(DISTINCT cIp) AS Current_nb_visitors, 
                      SUM((scBytes + csBytes) / 1024) AS Current_nb_Kbytes, 0 AS Previous_nb_Pages, 0 AS Previous_nb_Hits, 0 AS Previous_nb_visitors, 
                      0 AS Previous_nb_Kbytes, sComputerName FROM globaliislog WHERE  [Month] = ' + convert(varchar(2),@Month) + ' and [year] = ' + convert(char(4), @Year) 
+ ' GROUP BY MONTH(date), sComputerName UNION '
+ ' SELECT 0 AS Current_nb_Pages, 0 AS Current_nb_Hits, 0 AS Current_nb_visitors, 0 AS Current_nb_Kbytes, COUNT(DISTINCT csUriQuery) 
                      AS Previous_nb_Pages, COUNT(*) AS Previous_nb_Hits, COUNT(DISTINCT cIp) AS Previous_nb_visitors, SUM((scBytes + csBytes) / 1024) 
                      AS Previous_nb_Kbytes, sComputerName FROM  globaliislog WHERE [Month] = ' + convert(varchar(2),@Month) + ' -1 and [year] = ' + convert(char(4), @Year) 
+ ' GROUP BY MONTH(date), sComputerName '

set @Query4 = ' select sum(Current_nb_Pages) as Current_nb_pages, sum(Current_nb_Hits) as Current_nb_Hits, sum(Current_nb_visitors) as Current_nb_visitors, sum(Current_nb_Kbytes) as Current_nb_Kbytes,
	sum(Previous_nb_Pages) as Previous_nb_pages, sum(Previous_nb_Hits) as Previous_nb_Hits, sum(Previous_nb_visitors) as Previous_nb_visitors, sum(Previous_nb_Kbytes) as Previous_nb_Kbytes from #resultTable '

IF @ComputerName <> 'ALL'
	set @Query4  = @Query4 + ' where sComputerName = ''' + @ComputerName + ''''

set @Query4 = @Query4 +' drop table #resultTable '

execute (@Query1+@Query2+@Query3+@Query4)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--------------------------------------------------------------------------------------------------------------

SET NOCOUNT ON
PRINT 'Inserting values into [Code_Country]'
--------------------------------------------------------------------------------------------------------------
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+ca-AD','Canada')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+ca-CA','Canada')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+cs-CZ','Czech Republic')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+de-AT','Austria')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+de-DE','Germany')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+DT-AT','Austria')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+el-GR','Greece')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+en-GB','United Kingdom')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+en-US','United States')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+es-AR','Argentina')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+es-ES','Spain')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+fi-FI','Finland')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+fr-FR','France')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+hu-HU','Hungary')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+it-IT','Italy')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+ja-JP','Japan')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+ko-KR','Korea')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+nb-NO','Norway')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+pl-PL','Poland')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+pt-BR','Bresil')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+ru-RU','Russia')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+sl-SI','Slovenia')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+sv-SE','Sweden')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+zh-CN','China')
INSERT INTO [Code_Country] ([Country_Code],[Country_Name])VALUES('+zh-TW','Taiwan')

PRINT 'Done'
 
 
SET NOCOUNT OFF
--------------------------------------------------------------------------------------------------------------

SET NOCOUNT ON
 
 
PRINT 'Inserting values into [Code_OS]'
--------------------------------------------------------------------------------------------------------------
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+AOL+5.0','AOL+5.0','AOL')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+AOL+7.0','AOL+7.0','AOL')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+AOL+8.0','AOL+8.0','AOL')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+AOL+9.0','AOL+9.0','AOL')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+FreeBSD)','FreeBSD','FreeBSD')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+FreeBSD+i386','FreeBSD+i386','FreeBSD')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+http://help.yahoo.com/help/us/ysearch/slurp)','Unknow','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+I','Unknow','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+I)','Unknow','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+IRIX+6.5+IP32)','Unknow','Unix')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Linux+DEVIL+2.6.4+i686)','Linux','Linux')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Linux+i686','Linux','Linux')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Mac_PowerPC','Mac_PowerPC','Mac')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Mac_PowerPC)','Mac_PowerPC','Mac')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+MSN+2.5','MSN+2.5','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+MSNIA','MSNIA','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+NetBSD+i386','NetBSD+i386','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+PPC)','Windows CE','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+PPC+Mac+OS+X+Mach-O','Windows CE','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+SaferSurf','SaferSurf','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+SunOS+5.6+sun4u','SunOS+5.6+sun4u','Unix')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+SunOS+5.8+sun4u)','SunOS+5.8+sun4u','Unix')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+SunOS+sun4u','SunOS+sun4u','Unix')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+TweakMASTER)','TweakMASTER','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Win+9x+4.90','Windows 98','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Win95','Windows 95','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Win98','Windows 98','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows)','Windows','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+2000)+Opera+6.01++[ru]','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+2000)+Opera+6.05++[ja]','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+2000)+Opera+6.06++[ja]','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+95','Windows 95','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+95)','Windows 95','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+98','Windows 98','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+98)','Windows 98','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+98)+Opera+5.11++[de]','Windows 98','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+98)+Opera+7.54++[en]','Windows 98','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+98)+Opera+7.54++[es-ES]','Windows 98','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+98)+WebWasher+3.3','Windows 98','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+CE','Windows CE','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+CE)','Windows CE','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+ME)+Opera+6.04++[en]','Windows ME','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+ME)+Opera+6.05++[ja]','Windows ME','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT','Windows NT','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT)','Windows NT','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+4.0','Windows NT','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+4.0)','Windows NT','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+4.0)+WebWasherEE/3.4.1','Windows NT','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.0','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.0)','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.0)+Fetch+API+Request','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.0)+Microsoft+Scheduled+Cache+Content+Download+Service','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.0)+Opera+7.21++[de]','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.0)+Opera+7.22++[ru]','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.0)+Opera+7.23++[en]','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.0)+Opera+7.50++[en]','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.0)+Opera+7.50++[ru]','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.0)+Opera+7.51++[en]','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.0)+Opera+7.52++[en]','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.0)+Opera+7.53++[ja]','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.0)+Opera+7.54++[en]','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.0)+WebWasherEE/3.4.1+3.4.1','Windows 2000','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.1','Windows XP','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.1)','Windows XP','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.1)+Opera+7.11++[ru]','Windows XP','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.1)+Opera+7.50++[en]','Windows XP','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.1)+Opera+7.53++[ja]','Windows XP','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.1)+Opera+7.54++[de]','Windows XP','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.1)+Opera+7.54++[en]','Windows XP','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.1)+Opera+7.54++[nb]','Windows XP','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.1)+Opera+7.54++[pl]','Windows XP','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.1)+via+Avirt+Gateway+Server+v4.2','Windows XP','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.1)+WebWasherEE/3.4.1+3.4.1','Windows XP','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.2','Window.Net server','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+5.2)','Window.Net server','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+NT+Windows+CE)','Windows CE','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+XP)+Opera+6.04++[en]','Windows XP','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows+XP)+Opera+6.05++[en]','Windows XP','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+Windows-NT)','Windows NT','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+WinHttp.WinHttpRequest.5)','WinHttp.WinHttpRequest.5','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('+WinNT4.0','Windows NT','Windows')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('Cafi/1.02+(OSIX,+128-bit)','Unknow','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('ClusterSentinel+HTTP+Monitor','Unknow','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('ClusterSentinel+Http+Test','ClusterSentinel+Http+Test','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('Cuam+Ver0.050bx','Cuam+Ver0.050bx','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('Eudora','Eudora','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('explorer','explorer','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('Fast)','Fast','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('FirstFlowAgent','FirstFlowAgent','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('HelpSupportServices','Unknow','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('HSlide/1.5','HSlide/1.5','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('ImuXP-Surf','ImuXP-Surf','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('Internet+Explorere,Mozilla/4.0','Internet+Explorere,Mozilla/4.0','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('Microsoft-WebDAV-MiniRedir/5.1.2600','Microsoft-WebDAV','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('Mozilla/4.0','Mozilla/4.0','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('NSPlayer/10.0.0.3646+WMFSDK/10.0','NSPlayer/10.0.0.3646+WMFSDK/10.0','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('P3P+Client','P3P+Client','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('Sleipnir+Version+1.66','Sleipnir+Version+1.66','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('TB)','Unknow','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('W28H15)','Unknow','Other')
INSERT INTO [Code_OS] ([OS_code],[os_Name],[os_Group])VALUES('Other','Unknow','Other')

PRINT 'Done'

SET NOCOUNT OFF
--------------------------------------------------------------------------------------------------------------
