
alter table WindowsEventNT6AlertRule
	add OccurrencesThreshold int not null 
		constraint WindowsEventNT6AlertRule_OccurrencesThreshold_DF default (1)
go

alter table WindowsEventNT6AlertRule
	add OccurrencesThresholdIntervalInMins int null
go

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


/* this does not work for CE because of Details nvarchar(max)/Details ntext - the last one

alter table WindowsEventNT6Alert
ALTER COLUMN MWWindowsEventCollectionMethod int null

alter table WindowsEventNT6Alert
ALTER COLUMN EventSource nvarchar(256) null

alter table WindowsEventNT6Alert
ALTER COLUMN EventID int null

alter table WindowsEventNT6Alert
ALTER COLUMN [Level] smallint null


alter table WindowsEventNT6Alert
ALTER COLUMN Details nvarchar(max) null

*/

CREATE TABLE [WindowsEventNT6Alert_temp]
(
 [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
)
go

insert  [WindowsEventNT6Alert_temp]
(
	 [WindowsEventNT6AlertGuid]			
	,[AlertRuleID]						
	,[DeviceID]							
	,[MWWindowsEventCollectionMethod]	
	,[TimeOfEvent]						
	,[EventLog]							
	,[EventSource]						
	,[EventID]							
	,[Level]							
	,[Details]							
)
select 
		 [WindowsEventNT6AlertGuid]			
		,[AlertRuleID]						
		,[DeviceID]							
		,[MWWindowsEventCollectionMethod]	
		,[TimeOfEvent]						
		,[EventLog]							
		,[EventSource]						
		,[EventID]							
		,[Level]							
		,[Details]							
from WindowsEventNT6Alert
go

drop table WindowsEventNT6Alert
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
)
go


insert  [WindowsEventNT6Alert]
(
	 [WindowsEventNT6AlertGuid]			
	,[AlertRuleID]						
	,[DeviceID]							
	,[MWWindowsEventCollectionMethod]	
	,[TimeOfEvent]						
	,[EventLog]							
	,[EventSource]						
	,[EventID]							
	,[Level]							
	,[Details]							
)
select 
		 [WindowsEventNT6AlertGuid]			
		,[AlertRuleID]						
		,[DeviceID]							
		,[MWWindowsEventCollectionMethod]	
		,[TimeOfEvent]						
		,[EventLog]							
		,[EventSource]						
		,[EventID]							
		,[Level]							
		,[Details]							
from WindowsEventNT6Alert_temp
go

drop TABLE [WindowsEventNT6Alert_temp]
go 

/*
---------------------------------------------------
*/

ALTER TABLE [WindowsEventNT6Alert]  ADD  CONSTRAINT [WindowsEventNT6Alert_Device_FK] FOREIGN KEY([DeviceID])
REFERENCES [Device] ([DeviceID])
ON DELETE CASCADE
GO

ALTER TABLE [WindowsEventNT6Alert]   ADD  CONSTRAINT [WindowsEventNT6Alert_WindowsEventNT6AlertRule_FK] FOREIGN KEY([AlertRuleID])
REFERENCES [WindowsEventNT6AlertRule] ([AlertRuleID])
ON DELETE CASCADE
GO

create nonclustered index WindowsEventNT6Alert_AlertRuleID_IX
	on WindowsEventNT6Alert(AlertRuleID)
go	


alter table WindowsEventNT6Alert
add TrackerType smallint not null 
	constraint WindowsEventNT6Alert_TrackerType_DF default(1)
go