EventLog_Capture - creating all base tables and views

EventLog_Capture_tables_views.sql

USE [EventLog_Tracking]
GO
/****** Object:  FullTextCatalog [Eventlog_Blacklist]    Script Date: 5/12/2015 4:02:38 PM ******/
CREATE FULLTEXT CATALOG [Eventlog_Blacklist]WITH ACCENT_SENSITIVITY = ON
AS DEFAULT

GO
/****** Object:  Table [dbo].[EventLog_Blacklist_Calls]    Script Date: 5/12/2015 4:02:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventLog_Blacklist_Calls](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[computername] [varchar](255) NULL,
	[eventlog] [varchar](255) NULL,
	[insert_datetime] [datetime] NULL CONSTRAINT [DF__EventLog___inser__56B7027F]  DEFAULT (getdate()),
	[max_recordnumber] [bigint] NULL,
	[inserted_dt_staging] [datetime] NULL,
 CONSTRAINT [PK__EventLog__3214EC2754CEBA0D] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[EventLog_Daily]    Script Date: 5/12/2015 4:02:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventLog_Daily](
	[EventLog] [varchar](255) NULL,
	[RecordNumber] [int] NULL,
	[TimeGenerated] [datetime] NULL,
	[TimeWritten] [datetime] NULL,
	[EventID] [int] NULL,
	[EventType] [int] NULL,
	[EventTypeName] [varchar](255) NULL,
	[EventCategory] [int] NULL,
	[EventCategoryName] [varchar](255) NULL,
	[SourceName] [varchar](255) NULL,
	[Strings] [varchar](255) NULL,
	[ComputerName] [varchar](255) NULL,
	[SID] [varchar](255) NULL,
	[Message] [varchar](255) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[EventLog_EventBlacklist]    Script Date: 5/12/2015 4:02:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventLog_EventBlacklist](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Event_Name] [varchar](255) NOT NULL,
	[Server_Name] [sysname] NULL,
	[Event_Description] [varchar](50) NULL,
	[is_active] [bit] NULL,
 CONSTRAINT [PK__EventLog_EventBl__07020F21] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[EventLog_Servers]    Script Date: 5/12/2015 4:02:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventLog_Servers](
	[Server_Name] [varchar](30) NOT NULL,
	[Is_Active] [bit] NULL,
	[Application_RecordCount] [int] NULL,
	[System_RecordCount] [int] NULL,
	[Application_Last_Inserted] [smalldatetime] NULL,
	[System_Last_Inserted] [smalldatetime] NULL,
 CONSTRAINT [PK__EventLog_Servers__0CBAE877] PRIMARY KEY CLUSTERED 
(
	[Server_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[EventLog_Staged_Errors]    Script Date: 5/12/2015 4:02:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventLog_Staged_Errors](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[ErrorState] [int] NULL,
	[ErrorSeverity] [int] NULL,
	[errormessage] [nvarchar](4000) NULL,
	[computername] [varchar](255) NULL,
	[eventlog] [varchar](255) NULL,
	[insert_datetime] [datetime] NULL CONSTRAINT [DF__EventLog___inser__7C729713]  DEFAULT (getdate()),
 CONSTRAINT [PK__EventLog__3214EC271E72A95C] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[EventLog_Staged_PoSH]    Script Date: 5/12/2015 4:02:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventLog_Staged_PoSH](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EventLog] [varchar](255) NOT NULL,
	[RecordNumber] [bigint] NOT NULL,
	[TimeGenerated] [datetime] NULL,
	[TimeWritten] [datetime] NULL,
	[EventID] [int] NULL,
	[EventType] [int] NULL,
	[EventTypeName] [varchar](255) NULL,
	[EventCategory] [int] NULL,
	[EventCategoryName] [varchar](255) NULL,
	[SourceName] [varchar](255) NULL,
	[Strings] [varchar](max) NULL,
	[ComputerName] [varchar](255) NOT NULL,
	[SID] [varchar](255) NULL,
	[Message] [varchar](max) NULL,
	[insert_datetime] [datetime] NULL CONSTRAINT [DF__EventLog___inser__575555A6]  DEFAULT (getdate()),
 CONSTRAINT [PK__EventLog_Staged_PoSH__RecordNumber] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[EventLogs_To_Get]    Script Date: 5/12/2015 4:02:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventLogs_To_Get](
	[tinyint] [int] IDENTITY(1,1) NOT NULL,
	[EventLog] [varchar](30) NULL,
	[days_back_to_get] [tinyint] NULL,
	[minimum_recordnumber] [bigint] NULL,
	[insert_datetime] [datetime] NULL CONSTRAINT [DF__EventLogs__inser__39F9FCE9]  DEFAULT (getdate()),
 CONSTRAINT [PK__EventLog__8EC24DE83811B477] PRIMARY KEY CLUSTERED 
(
	[tinyint] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  View [dbo].[EventLog_Details]    Script Date: 5/12/2015 4:02:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[EventLog_Details] AS 
SELECT EventLog,
		RecordNumber,
		TimeGenerated,
		TimeWritten,
		EventID,
		EventType,
		EventTypeName,
		EventCategory,
		EventCategoryName,
		SourceName,
		Strings,
		ComputerName,
		SID,
		Message, insert_datetime FROM EventLog_201504
UNION ALL
SELECT EventLog,
		RecordNumber,
		TimeGenerated,
		TimeWritten,
		EventID,
		EventType,
		EventTypeName,
		EventCategory,
		EventCategoryName,
		SourceName,
		Strings,
		ComputerName,
		SID,
		Message, insert_datetime FROM EventLog_201505


GO
/****** Object:  View [dbo].[EventLog_Staged_PoSH_Insert]    Script Date: 5/12/2015 4:02:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[EventLog_Staged_PoSH_Insert]
AS 
SELECT EventLog,
		RecordNumber,
		TimeGenerated,
		TimeWritten,
		EventID,
		EventType,
		EventTypeName,
		EventCategory,
		EventCategoryName,
		SourceName,
		Strings,
		ComputerName,
		SID,
		Message FROM EventLog_Staged_PoSH

GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [ncidx__EventLog_Staged_PoSH__computername__eventlog]    Script Date: 5/12/2015 4:02:38 PM ******/
CREATE NONCLUSTERED INDEX [ncidx__EventLog_Staged_PoSH__computername__eventlog] ON [dbo].[EventLog_Staged_PoSH]
(
	[ComputerName] ASC,
	[EventLog] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  FullTextIndex     Script Date: 5/12/2015 4:02:38 PM ******/
CREATE FULLTEXT INDEX ON [dbo].[EventLog_Staged_PoSH](
[Message] LANGUAGE 'English')
KEY INDEX [PK__EventLog_Staged_PoSH__RecordNumber]ON ([Eventlog_Blacklist], FILEGROUP [PRIMARY])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)


GO