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 /*----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ... Core 2.0 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/ 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 --------------------------------------------------------------------------------------------------------------