

CREATE TABLE [WindowsEventNT6Monitor](
    [WindowsEventNT6MonitorID]	int			not null,
    [MonitoringRuleID]      int				not null,
	[EventLog]              nvarchar(256)	not null,
	[EventIDExpression]		nvarchar(256)	null,
    [Enabled]               bit				not null,
    [DetailsSearchOptions]  ntext	NULL,

	constraint WindowsEventNT6Monitor_PK primary key (WindowsEventNT6MonitorID)
)
go

create table WindowsEventNT6MonitorSource(
	[WindowsEventNT6MonitorID] int not null,
	[EventSource] nvarchar(128) not null,
	constraint WindowsEventNT6MonitorSource_PK primary key (WindowsEventNT6MonitorID, EventSource)
)
go

create table WindowsEventNT6MonitorLevel(
	[WindowsEventNT6MonitorID] int not null,
	[LevelID] int not null,
	constraint WindowsEventNT6MonitorSeverity_PK primary key (WindowsEventNT6MonitorID, LevelID)
	)
go

/* 
 * TABLE: [WindowsEventAlertRuleOccurrences] 
 */

CREATE TABLE [WindowsEventAlertRuleOccurrences](
    [AlertRuleID]     int       NOT NULL,
    [DeviceID]        int       NOT NULL,
    [TimestampTicks]  bigint    NOT NULL,
    [EventCount]      int       NOT NULL
)
go


go
CREATE TABLE [WindowsEventNT6AlertRule](
    [AlertRuleID]			int				not null,
    [EventLog]				nvarchar(256)	not null,
    [EventSource]			nvarchar(256)   null,
    [EventSourceOperator]	smallint		not null,
    [EventID]				int             null,
    [EventIDOperator]		smallint		not null,
    [EventLevel]			smallint		null,
	[EventLevelOperator]	smallint		not null,
    [DetailsSearchOptions]  ntext			    null,
    [OccurrencesThreshold]                int              
									CONSTRAINT [WindowsEventNT6AlertRule_OccurrencesThreshold_DF] DEFAULT 1 NOT NULL,
    [OccurrencesThresholdIntervalInMins]  int              NULL,
	constraint [WindowsEventNT6AlertRule_PK] primary key ([AlertRuleID])
)
go


CREATE TABLE [WindowsEventNT6Alert](
    [WindowsEventNT6AlertGuid]	uniqueidentifier NOT NULL,
    [AlertRuleID]				int             NOT NULL,
	[DeviceID]					int				not null,
	[MWWindowsEventCollectionMethod] int		           null,
    [TimeOfEvent]				datetime        NOT NULL,
	[EventLog]					nvarchar(256)   NOT NULL,
    [EventSource]				nvarchar(256)    NULL,
    [EventID]					int              NULL,
    [Level]						smallint		 NULL,
    [Details]					ntext			 NULL,
    [TrackerType]               smallint            CONSTRAINT [WindowsEventNT6Alert_TrackerType_DF] DEFAULT 1 NOT NULL,
	constraint WindowsEventNT6Alert_PK primary key nonclustered (WindowsEventNT6AlertGuid) 
																				
)
go

create table WindowsEventMonitoringBookmark (
	DeviceID int not null,
	EventLogName nvarchar(256) not null,
	LastWindowsEventTimeRecorded datetime null,
	[LastWindowsEventTimeRecordedInTicks]  bigint        null
   ,[LastWindowsEventRecordID]             bigint        NULL
   ,constraint WindowsEventMonitoringBookmark_PK primary key (DeviceID, EventLogName)
)

go

/*
alter table [WindowsEventNT6Monitor]
add
	--constraint WindowsEventNT6Monitor_Monitor_FK foreign key (MonitoringRuleID) references dbo.Monitor(MonitorID)
*/

alter table WindowsEventNT6MonitorSource
add
	constraint WindowsEventNT6MonitorSource_WindowsEventMonitor_FK foreign key (WindowsEventNT6MonitorID) 
		references WindowsEventNT6Monitor(WindowsEventNT6MonitorID) 
			on delete cascade
go


alter table WindowsEventNT6MonitorLevel
add
	constraint WindowsEventNT6MonitorSeverity_WindowsEventMonitor_FK foreign key (WindowsEventNT6MonitorID) 
		references WindowsEventNT6Monitor(WindowsEventNT6MonitorID) 
			on delete cascade
go

alter table [WindowsEventNT6AlertRule]
add
	constraint WindowsEventNT6AlertRule_AlertRule_FK foreign key (AlertRuleID) 
		references AlertRule(AlertRuleID) 
			on delete cascade
go

alter table [WindowsEventNT6Alert]
add
	constraint WindowsEventNT6Alert_WindowsEventNT6AlertRule_FK foreign key (AlertRuleID) 
		references [WindowsEventNT6AlertRule](AlertRuleID) 
			on delete cascade
go

alter table [WindowsEventNT6Alert]
add
	constraint WindowsEventNT6Alert_Device_FK foreign key (DeviceID) 
		references Device(DeviceID) 
			on delete cascade
go


/* 
 * INDEX: [WindowsEventAlertRuleOccurrences_CLUST] 
 */

CREATE nonCLUSTERED INDEX [WindowsEventAlertRuleOccurrences_CLUST] 
	ON [WindowsEventAlertRuleOccurrences]([AlertRuleID], [DeviceID], [TimestampTicks])
go

/* 
 * INDEX: [WindowsEventNT6Alert_AlertRuleID_IX] 
 */

CREATE NONCLUSTERED INDEX [WindowsEventNT6Alert_AlertRuleID_IX] ON [WindowsEventNT6Alert] ([AlertRuleID])
GO