/*
 * ER/Studio Data Architect 8.5 SQL Code Generation
 * Company :      LPI
 * Project :      MWData_CE.dm1
 * Author :       LPI
 *
 * Date Created : Tuesday, April 13, 2010 16:01:10
 * Target DBMS : Microsoft SQL Server 2008
 */

/* 
 * TABLE: [Alert] 
 */

CREATE TABLE [Alert](
    [AlertID]   int             IDENTITY(1,1),
    [Operator]  nvarchar(50)    NULL,
    [SelfHeal]  bit             CONSTRAINT [DF_Alert_SelfHeal] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_Alert] PRIMARY KEY ([AlertID])
)
go



/* 
 * TABLE: [AlertType] 
 */

CREATE TABLE [AlertType](
    [AlertTypeID]              int                 IDENTITY(1,1),
    [AlertTypeName]            nvarchar(50)        NOT NULL,
    [IsSelfHealable]           bit                 NULL,
    [AllowMultipleAlertRules]  bit                 NULL,
    [IsLegacy90]               bit                 NOT NULL,
    [AlertingEngine]           tinyint             NULL,
    [AlertSubjectType]         tinyint             NULL,
    [AlertSourceType]          tinyint             NULL,
    [PluginGuid]               uniqueidentifier    NULL,
    CONSTRAINT [PK_AlertType] PRIMARY KEY  ([AlertTypeID])
)
go



/* 
 * TABLE: [AlertRule] 
 */

CREATE TABLE [AlertRule](
    [AlertRuleID]  int    IDENTITY(1,1),
    [AlertID]      int    NOT NULL,
    [AlertTypeID]  int    NOT NULL,
    CONSTRAINT [PK_AlertRule] PRIMARY KEY ([AlertRuleID]), 
    CONSTRAINT [FK_AlertRule_Alert] FOREIGN KEY ([AlertID])
    REFERENCES [Alert]([AlertID]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_AlertRule_AlertType] FOREIGN KEY ([AlertTypeID])
    REFERENCES [AlertType]([AlertTypeID]) ON UPDATE CASCADE
)
go



/* 
 * TABLE: [OSType] 
 */

CREATE TABLE [OSType](
    [ID]    smallint        NOT NULL,
    [Name]  nvarchar(50)    NOT NULL,
    CONSTRAINT [OSType_PK] PRIMARY KEY ([ID])
)
go



/* 
 * TABLE: [Device] 
drop TABLE [Device]
alter table Device
    add DeviceGuid uniqueidentifier rowguidcol not null
 */

CREATE TABLE [Device](
    [DeviceID]            int              NOT NULL,
    [DeviceGuid]          uniqueidentifier rowguidcol not null,
    [Description]         nvarchar(255)    NULL,
    [Status]              nvarchar(15)     NOT NULL,
    [MAC]                 nvarchar(500)    NULL,
    [DNSName]             nvarchar(255)    NOT NULL,
    [SnmpName]            nvarchar(255)    NOT NULL,
    [SnmpHash]            int              NOT NULL,
    [BiosOSHash]          int              NOT NULL,
    [IsManaged]             bit              NOT NULL,
    [MonitorEvents]       bit              NOT NULL,
    [MonitorAssets]       bit              NOT NULL,
    [MonitorPerfCounter]  bit              NOT NULL,
    [MonitorSnmp]         bit              NOT NULL,
    [MonitorServices]     bit              NOT NULL,
    [OSType]              smallint         NOT NULL,
    [WmiEnabled]          bit              NOT NULL,
    [SnmpEnabled]         bit              NOT NULL,
    [TimeCreated]         datetime         NOT NULL,
    [TimeLastUpdate]      datetime         NOT NULL,
    [TimeLastUp]          datetime             NULL,
    [MachineName]         nvarchar(255)    NULL,
    [AMTEnabled]          bit              CONSTRAINT [Device_AMTEnabled_DF] DEFAULT ((0)) NOT NULL,
    [StaticIP]            bit              CONSTRAINT [Device_StaticIP_DF] DEFAULT ((0)) NOT NULL,
    [TimeStatusChanged]   datetime         NOT NULL,
    [TimeLastAssetScan]   datetime         NULL,
	NetbiosName           nvarchar(16)     null,
	IPAddresses           nvarchar(255)    null,
	RequiresSeeding       bit           NOT NULL constraint Device_RequiresSeeding_DF DEFAULT (0),
    SshEnabled            bit           NOT NULL constraint Device_SshEnabled_DF  DEFAULT 0,
	RequiresDeletion      bit           NOT NULL constraint Device_RequiresDeletion_DF default(0),
	IsRespondingToArp     bit           NOT NULL constraint Device_IsRespondingToArp_DF     default(0),
	[WSManEnabled]        bit                 CONSTRAINT [Device_WSManEnabled_DF] DEFAULT 0 NOT NULL,
		  [SshName]       nvarchar(255)     NULL,
		  [Excluded]            bit               CONSTRAINT [Device_Excluded_DF] DEFAULT 0 NOT NULL,
	CONSTRAINT [PK_Device] PRIMARY KEY ([DeviceID]), 
    CONSTRAINT [OSType_Device_FK1] FOREIGN KEY ([OSType])
    REFERENCES [OSType]([ID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go

CREATE TABLE [CloudServiceType]
(
	[CloudServiceTypeGuid]	uniqueidentifier	NOT NULL,
	[CloudServiceTypeName]	[nvarchar](50)		NOT NULL,
	[Version]				[nvarchar](50)		NOT NULL,
	[Description]			[nvarchar](4000)	NULL,
	[MonitoringXml]			[ntext]				NULL
	,CONSTRAINT [AK_CloudServiceType_CloudServiceTypeName] UNIQUE  
	(
		CloudServiceTypeName
	)
   ,CONSTRAINT [PK_CloudServiceType] PRIMARY KEY 
	(
		[CloudServiceTypeGuid] 
	)
)
go

/* 
 * TABLE: [Website] 
drop table [Website]
 */

CREATE TABLE [Website](
    [WebsiteID]             int                 IDENTITY(1,1),
    [Title]                 nvarchar(255)       NOT NULL,
    [Description]           nvarchar(1000)      NOT NULL,
    [HomePageUrl]           nvarchar(255)       NULL,
    [WebsiteGuid]           uniqueidentifier    NOT NULL,
    [CloudServiceTypeGuid]  uniqueidentifier    NULL
	         
	,CONSTRAINT [PK_Website] PRIMARY KEY ([WebsiteID])
)
go



/* 
 * TABLE: [AlertSubjectLink] 
 */

CREATE TABLE [AlertSubjectLink](
    [AlertID]    int    NOT NULL,
    [DeviceID]   int    NULL,
    [WebsiteID]  int    NULL,
    [Status]     int    CONSTRAINT [DF_AlertSubjectLink_Status] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [FK_AlertObjectLink_Alert] FOREIGN KEY ([AlertID])
    REFERENCES [Alert]([AlertID]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_AlertSubjectLink_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_AlertSubjectLink_Website] FOREIGN KEY ([WebsiteID])
    REFERENCES [Website]([WebsiteID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [AMTBaseboardInfo] 
 */

CREATE TABLE [AMTBaseboardInfo](
    [DeviceID]      int             NOT NULL,
    [Manufacturer]  nvarchar(64)    NOT NULL,
    [Product]       nvarchar(50)    NOT NULL,
    [Version]       nvarchar(50)    NOT NULL,
    [SerialNumber]  nvarchar(50)    NOT NULL,
    [AssetTag]      nvarchar(50)    NOT NULL,
    [Replaceable]   bit             NOT NULL,
    CONSTRAINT [FK_AMTBaseboardInfo_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [AMTBiosInfo] 
 */

CREATE TABLE [AMTBiosInfo](
    [DeviceID]            int               NOT NULL,
    [Vendor]              nvarchar(64)      NOT NULL,
    [VersionID]           nvarchar(64)      NOT NULL,
    [ReleaseDate]         datetime          NOT NULL,
    [SupportedFunctions]  nvarchar(2054)    NOT NULL,
    CONSTRAINT [FK_AMTBiosInfo_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [AMTDeviceLatestDataTimestamp] 
 */

CREATE TABLE [AMTDeviceLatestDataTimestamp](
    [DeviceID]             int         NOT NULL,
    [LatestDataTimestamp]  datetime    NULL,
    CONSTRAINT [FK_AMTDeviceLatestDataTimestamp_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID])
)
go



/* 
 * TABLE: [AMTDeviceStatus] 
 */

CREATE TABLE [AMTDeviceStatus](
    [DeviceID]   int             NOT NULL,
    [Status]     nvarchar(20)    NOT NULL,
    [Timestamp]  datetime        NOT NULL,
    CONSTRAINT [FK_AMTDeviceStatus_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [AMTDiskInfo] 
 */

CREATE TABLE [AMTDiskInfo](
    [DeviceID]      int             NOT NULL,
    [Model]         nvarchar(50)    NOT NULL,
    [SerialNumber]  nvarchar(50)    NOT NULL,
    [Capability]    nvarchar(50)    NOT NULL,
    [Size]          int             NOT NULL,
    CONSTRAINT [FK_AMTDiskInfo_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [AMTEventAlert] 
 */

CREATE TABLE [AMTEventAlert](
    [Guid]         uniqueidentifier    NOT NULL,
    [AlertRuleID]  int                 NOT NULL,
    [DeviceID]     int                 NOT NULL,
    [EventSource]  nvarchar(64)        NOT NULL,
    [SeverityID]   smallint            NOT NULL,
    CONSTRAINT [PK_AMTEventAlert] PRIMARY KEY ([Guid]), 
    CONSTRAINT [FK_AMTEventAlert_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_AMTEventAlert_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [AMTEventRule] 
 */

CREATE TABLE [AMTEventRule](
    [AlertRuleID]      int              NOT NULL,
    [EventSource]      nvarchar(64)     NULL,
    [EventSourceOp]    bit              NULL,
    [EventSeverityID]  smallint         NULL,
    [SeverityOp]       bit              NULL,
    [Details]          nvarchar(255)    NULL,
    CONSTRAINT [PK_AMTEventRule] PRIMARY KEY ([AlertRuleID]), 
    CONSTRAINT [FK_AMTEventRule_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [AMTEvents] 
 */

CREATE TABLE [AMTEvents](
    [ID]           int              IDENTITY(1,1),
    [DeviceID]     int              NOT NULL,
    [Source]       nvarchar(255)    NOT NULL,
    [Description]  nvarchar(255)    NULL,
    [SeverityID]   int              NOT NULL,
    [Timestamp]    datetime         NULL,
    CONSTRAINT [PK_AMTEvents] PRIMARY KEY ([ID]), 
    CONSTRAINT [FK_AMTEvents_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [AmtInterface] 
 */

CREATE TABLE [AmtInterface](
    [MacAddressAmt]        nvarchar(50)     NOT NULL,
    [InterfaceTypeID]      int              CONSTRAINT [AmtInterface_InterfaceTypeID_DF] DEFAULT ((0)) NOT NULL,
    [HardwareDescription]  nvarchar(500)    NULL,
    [LinkPolicyType]       int              NULL,
    [IsDhcpEnabled]        bit              NULL,
    [IsShared]             bit              NULL,
    CONSTRAINT [AmtInterface_PK] PRIMARY KEY ([MacAddressAmt], [InterfaceTypeID])
)
go



/* 
 * TABLE: [AMTMemoryInfo] 
 */

CREATE TABLE [AMTMemoryInfo](
    [DeviceID]      int             NOT NULL,
    [Size]          nvarchar(50)    NOT NULL,
    [FormFactor]    nvarchar(50)    NOT NULL,
    [Type]          nvarchar(50)    NOT NULL,
    [TypeDetail]    nvarchar(50)    NOT NULL,
    [Speed]         nvarchar(50)    NOT NULL,
    [Manufacturer]  nvarchar(50)    NOT NULL,
    [SerialNumber]  nvarchar(50)    NOT NULL,
    [AssetTag]      nvarchar(50)    NOT NULL,
    [PartNumber]    nvarchar(64)    NOT NULL,
    CONSTRAINT [FK_AMTMemoryInfo_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [AMTMonitoredEvent] 
 */

CREATE TABLE [AMTMonitoredEvent](
    [ID]           int              IDENTITY(1,1),
    [DeviceID]     int              NOT NULL,
    [Source]       nvarchar(50)     NULL,
    [SeverityID]   int              NULL,
    [Description]  nvarchar(255)    NULL,
    [Enabled]      bit              CONSTRAINT [DF_AMTMonitoredEvent_Enabled] DEFAULT ((1)) NOT NULL,
    CONSTRAINT [PK_AMTMonitoredEvent] PRIMARY KEY ([ID]), 
    CONSTRAINT [FK_AMTMonitoredEvent_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [AMTPowerStatus] 
 */

CREATE TABLE [AMTPowerStatus](
    [DeviceID]   int             NOT NULL,
    [Status]     nvarchar(20)    NOT NULL,
    [Timestamp]  datetime        NOT NULL,
    CONSTRAINT [FK_AMTPowerStatus_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [AMTProcessorInfo] 
 */

CREATE TABLE [AMTProcessorInfo](
    [DeviceID]           int             NOT NULL,
    [ProcessorID]        nvarchar(64)    NOT NULL,
    [MaxSocketSpeed]     nvarchar(50)    NOT NULL,
    [CurrentSpeed]       nvarchar(50)    NOT NULL,
    [Status]             nvarchar(50)    NOT NULL,
    [ProcessorType]      nvarchar(50)    NOT NULL,
    [Family]             nvarchar(50)    NOT NULL,
    [UpgradeMethod]      nvarchar(50)    NOT NULL,
    [SocketPopulated]    bit             NOT NULL,
    [SocketDesignation]  nvarchar(50)    NOT NULL,
    [Manufacturer]       nvarchar(64)    NOT NULL,
    [Version]            nvarchar(50)    NOT NULL,
    CONSTRAINT [FK_AMTProcessorInfo_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [AMTSignatures] 
 */

CREATE TABLE [AMTSignatures](
    [ID]         int      IDENTITY(1,1),
    [Signature]  ntext    NOT NULL,
    CONSTRAINT [PK_AMTSignatures] PRIMARY KEY ([ID])
)
go



/* 
 * TABLE: [AMTSystemInfo] 
 */

CREATE TABLE [AMTSystemInfo](
    [DeviceID]      int             NOT NULL,
    [Model]         nvarchar(50)    NOT NULL,
    [Manufacturer]  nvarchar(64)    NOT NULL,
    [Version]       nvarchar(50)    NOT NULL,
    [SerialNumber]  nvarchar(50)    NOT NULL,
    [SystemID]      nvarchar(50)    NOT NULL,
    [VersionAMT]    nvarchar(50)    NOT NULL,
    CONSTRAINT [FK_AMTSystemInfo_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go


/* 
 * TABLE: [Authentication] 
drop TABLE [Authentication] 
 */

CREATE TABLE [Authentication](
    [AuthenticationGuid]  uniqueidentifier    rowguidcol NOT NULL,
    [UserName]            nvarchar(64)        NOT NULL,
    [PwdEncrypted]        nvarchar(256)       NULL,
    [PwdHash]             nvarchar(256)       NULL,
    [Protocol]            nvarchar(20)        NOT NULL
                          -- CONSTRAINT [Authentication_Protocol_CK] CHECK (([Protocol]=N'AMT'))
   ,
    [Port]                int                 NULL,
    [ApplicationType]     nvarchar(50)        NOT NULL
                       --   CONSTRAINT [Authentication_ApplicationType_CK] CHECK (([ApplicationType]=N'IP' OR [ApplicationType]=N'Device' OR [ApplicationType]=N'Site'))
   ,
    CONSTRAINT [Authentication_PK] PRIMARY KEY ([AuthenticationGuid])
)
go



/* 
 * TABLE: [BandwidthAlertRule] 
 */

CREATE TABLE [BandwidthAlertRule](
    [AlertRuleID]      int                 NOT NULL,
    [AssetObjectGuid]  uniqueidentifier    NOT NULL,
    [Threshold]        float               NOT NULL,
    [NbrDataPoints]    int                 NOT NULL,
    [IsUpThreshold]    bit                 NOT NULL,
    [ThresholdType]    tinyint             NOT NULL,
    CONSTRAINT [PK_BandwidthAlertRule] PRIMARY KEY ([AlertRuleID]), 
    CONSTRAINT [FK_BandwidthAlertRule_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [Buffer] 
 */

CREATE TABLE [Buffer](
    [BufferID]    int        IDENTITY(1,1),
    [BufferType]  tinyint    NOT NULL,
    CONSTRAINT [PK_Buffer] PRIMARY KEY ([BufferID])
)
go



/* 
 * TABLE: [BufferItem] 
 */

CREATE TABLE [BufferItem](
    [BufferID]		int      NOT NULL
   ,[Item]			image    NOT NULL
   ,BufferItemID	int      not null IDENTITY(1,1)
   ,CONSTRAINT BufferItem_PK          PRIMARY KEY (BufferID, BufferItemID)
   ,CONSTRAINT [FK_BufferItem_Buffer] FOREIGN KEY ([BufferID]) REFERENCES [Buffer]([BufferID])
)
go



/* 
 * TABLE: [CdRomAssets] 
 */

CREATE TABLE [CdRomAssets](
    [DeviceID]     int              NOT NULL,
    [DriveLetter]  nvarchar(1)      NOT NULL,
    [Maker]        nvarchar(255)    NOT NULL,
    CONSTRAINT [Device_CdRomAssets_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [CommAlertRule] 
 */

CREATE TABLE [CommAlertRule](
    [AlertRuleID]  int         NOT NULL,
    [Minutes]      smallint    NOT NULL,
    CONSTRAINT [FK_CommAlertRule_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [CommunicationController] 
 */

CREATE TABLE [CommunicationController](
    [CommunicationControllerID]  int    NOT NULL,
    [MaxPackageID]               int    NOT NULL,
    [AckReceivedID]              int    NOT NULL,
    [AckProcessedID]             int    NOT NULL,
    [ReceivedID]                 int    NOT NULL,
    [ProcessedID]                int    NOT NULL
	,CONSTRAINT [CommunicationController_PK] PRIMARY KEY ([CommunicationControllerID])
)
go


/* 
 * TABLE: [CommunicationPackage] 
 */

CREATE TABLE [CommunicationPackage](
    [CommunicationControllerID]  int                 NOT NULL,
    [SequenceID]                 int                 NOT NULL,
    [ReferenceID]                uniqueidentifier    NOT NULL,
    [Data]                       image               NOT NULL,
	[DataLength]                 bigint              NOT NULL,
    CONSTRAINT [PK_Package] PRIMARY KEY ([CommunicationControllerID], [SequenceID])
)
go

/* 
 * INDEX: [CommunicationPackage_iDataLength_IX] 
 */

CREATE UNIQUE INDEX [CommunicationPackage_DataLength_IX] ON [CommunicationPackage]([CommunicationControllerID], [SequenceID], DataLength)
go


/* 
 * TABLE: [Community] 
 */

CREATE TABLE [Community](
    [CommunityID]  int              IDENTITY(1,1),
    [Name]         nvarchar(255)    NULL,
    [Description]  nvarchar(255)    NULL,
    CONSTRAINT [Community_PK] PRIMARY KEY ([CommunityID])
)
go



/* 
 * TABLE: [CounterCategory] 
 */

CREATE TABLE [CounterCategory](
    [CounterCategoryID]  int              IDENTITY(1,1),
    [CategoryName]       nvarchar(255)    NOT NULL,
	 WmiClassName nvarchar(255) NULL
   , CONSTRAINT [CounterCategory_PK] PRIMARY KEY ([CounterCategoryID])
)
go



/* 
 * TABLE: [CounterInstance] 
 */

CREATE TABLE [CounterInstance](
    [CounterInstanceID]  int              IDENTITY(1,1),
    [CounterCategoryID]  int              NOT NULL,
    [InstanceName]       nvarchar(255)    NOT NULL,
    CONSTRAINT [CounterInstance_PK] PRIMARY KEY ([CounterInstanceID]), 
    CONSTRAINT [CounterCategory_CounterInstance_FK1] FOREIGN KEY ([CounterCategoryID])
    REFERENCES [CounterCategory]([CounterCategoryID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [Counter] 
 */

CREATE TABLE [Counter](
    [CounterID]                int              IDENTITY(1,1),
    [CounterInstanceID]        int              NOT NULL,
    [CounterName]              nvarchar(255)    NOT NULL,
    [DefaultSamplingInterval]  int              CONSTRAINT [DF_Counter_DefaultSamplingInterval] DEFAULT ((300)) NOT NULL,
    [IsDefaultLPI]             bit              CONSTRAINT [DF_Counter_IsDefaultLPI] DEFAULT ((1)) NOT NULL,
	 WmiPropertyName           nvarchar(255)    NULL
   ,CONSTRAINT [PK_Counter] PRIMARY KEY ([CounterID]) 
   ,CONSTRAINT [CounterInstance_Counter_FK1] FOREIGN KEY ([CounterInstanceID])
    REFERENCES [CounterInstance]([CounterInstanceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [CounterCustomer] 
 */

CREATE TABLE [CounterCustomer](
    [DeviceID]           int         NOT NULL,
    [CounterCustomerID]  int         IDENTITY(1,1),
    [CounterID]          int         NOT NULL,
    [SamplingInterval]   int         NOT NULL,
    [MonitorStartTime]   datetime    NULL,
    [MonitorEndTime]     datetime    NULL,
    [MonitorStartDate]   datetime    NULL,
    [MonitorEndDate]     datetime    NULL,
    [Enabled]            bit         NOT NULL,
    CONSTRAINT [CounterCustomer_PK] PRIMARY KEY ([CounterCustomerID]), 
    CONSTRAINT [Counter_CounterCustomer_FK1] FOREIGN KEY ([CounterID])
    REFERENCES [Counter]([CounterID]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [Device_CounterCustomer_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [CounterData] 
 */

CREATE TABLE [CounterData](
    [CounterID]        int         NOT NULL,
    [DeviceID]         int         NOT NULL,
    [SampleTimestamp]  datetime    NOT NULL,
    [SampleValue]      float       NOT NULL,
    CONSTRAINT [Counter_CounterData_FK1] FOREIGN KEY ([CounterID])
    REFERENCES [Counter]([CounterID]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [Device_CounterData_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [CPUAssets] 
 */

CREATE TABLE [CPUAssets](
    [DeviceID]    int              NOT NULL,
    [Name]        nvarchar(255)    NOT NULL,
    [Version]     nvarchar(255)    NOT NULL,
    [ClockSpeed]  int              NOT NULL,
    CONSTRAINT [Device_CPUAssets_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [CustomLog] 
 */

CREATE TABLE [CustomLog](
    [CustomLogID]  int              IDENTITY(1,1),
    [DeviceID]     int               Not NULL,
	[DeviceIDRef]  int               Not NULL,
    [Name]         nvarchar(100)     NOT NULL,
    [Description]  nvarchar(1000)     NULL,
    [FilePath]     nvarchar(255)    NOT NULL,
    CONSTRAINT [PK_CustomLog_DeviceID] PRIMARY KEY ([CustomLogID],[DeviceIDRef]), 
    CONSTRAINT [FK_CustomLog_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [CustomLogAlertRule] 
 */

CREATE TABLE [CustomLogAlertRule](
    [AlertRuleID]  int    NOT NULL,
    [CustomLogID]  int    NOT NULL
)
go

/* 
 * TABLE: [CustomLogMonitor] 
 */

CREATE TABLE [CustomLogMonitor](
    [CustomLogID]           int              NOT NULL,
	[DeviceIDRef]			int               Not NULL,
    [UserName]              nvarchar(50)     NULL,
    [Password]              nvarchar(255)    NULL,
    [PollingInterval]       int              NOT NULL,
    [RegularExpression]     nvarchar(255)     NOT NULL,
    [MatchCase]             bit              NOT NULL,
    [MatchWholeWord]        bit              NOT NULL,
    [UseRegularExpression]  bit              NOT NULL,
    [Capture]               bit              NOT NULL,
    [StartingPosition]      int              NOT NULL,
    CONSTRAINT [PK_CustomLogMonitor] PRIMARY KEY ([CustomLogID],[DeviceIDRef]), 
    CONSTRAINT [FK_CustomLogMonitor_CustomLog] FOREIGN KEY ([CustomLogID],[DeviceIDRef])
    REFERENCES [CustomLog]([CustomLogID],[DeviceIDRef]) ON DELETE CASCADE ON UPDATE CASCADE
)
go

/* 
 * TABLE: [CustomLogMonitorSearch] 
 */

CREATE TABLE [CustomLogMonitorSearch](
    [CustomLogMonitorSearchID]  int         IDENTITY(1,1),
	[DeviceIDRef]               int         Not NULL,
    [CustomLogID]               int         NOT NULL,
    [StartTime]                 datetime    NOT NULL,
    [EndTime]                   datetime    NOT NULL,
    [StartLineNumber]           int         NOT NULL,
    [EndLineNumber]             int         NOT NULL,
    CONSTRAINT [PK_CustomLogMonitorSearch] PRIMARY KEY ([CustomLogMonitorSearchID],[DeviceIDRef]), 
    CONSTRAINT [FK_CustomLogMonitorSearch_CustomLogMonitor] FOREIGN KEY ([CustomLogID],[DeviceIDRef])
    REFERENCES [CustomLogMonitor]([CustomLogID],[DeviceIDRef]) ON DELETE CASCADE ON UPDATE CASCADE
)
go

/* 
 * TABLE: [CustomLogData] 
 */

CREATE TABLE [CustomLogData](
    [CustomLogMonitorSearchID]  int               NOT NULL,
	[DeviceIDRef]				int               Not NULL,
    [LineNumber]                int               NOT NULL,
    [Capture]                   nvarchar(4000)    NULL,
    CONSTRAINT [FK_CustomLogData_CustomLogMonitorSearch] FOREIGN KEY ([CustomLogMonitorSearchID],[DeviceIDRef])
    REFERENCES [CustomLogMonitorSearch]([CustomLogMonitorSearchID],[DeviceIDRef]) ON DELETE CASCADE ON UPDATE CASCADE)
go



/* 
 * TABLE: [DeviceAmtInterfaceLink] 
 */

CREATE TABLE [DeviceAmtInterfaceLink](
    [DeviceID]         int             NOT NULL,
    [MacAddressAmt]    nvarchar(50)    NOT NULL,
    [InterfaceTypeID]  int             NOT NULL,
    CONSTRAINT [DeviceAmtInterfaceLink_PK] PRIMARY KEY ([DeviceID], [MacAddressAmt], [InterfaceTypeID]), 
    CONSTRAINT [DeviceAmtInterfaceLink_AmtInterface_FK] FOREIGN KEY ([MacAddressAmt], [InterfaceTypeID])
    REFERENCES [AmtInterface]([MacAddressAmt], [InterfaceTypeID]),
    CONSTRAINT [DeviceAmtInterfaceLink_Device_FK] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID])
)
go



/* 
 * TABLE: [DeviceAuthenticationLink] 
 */

CREATE TABLE [DeviceAuthenticationLink](
    [DeviceID]            int                 NOT NULL,
    [AuthenticationGuid]  uniqueidentifier    NOT NULL,
    CONSTRAINT [DeviceAuthenticationLink_PK] PRIMARY KEY ([DeviceID], [AuthenticationGuid]), 
    CONSTRAINT [DeviceAuthenticationLink_Authentication_FK] FOREIGN KEY ([AuthenticationGuid])
    REFERENCES [Authentication]([AuthenticationGuid]),
    CONSTRAINT [DeviceAuthenticationLink_Device_FK] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID])
)
go



/* 
 * TABLE: [DeviceExclusionRule] 
 */

CREATE TABLE [DeviceExclusionRule](
    [DeviceExclusionRuleID]  int              IDENTITY(1,1),
    [DNSName]                nvarchar(255)    NULL,
    [SystemName]             nvarchar(255)    NULL,
    [MACAddress]             nvarchar(17)     NULL,
	ArpMacAddress			 nvarchar(17)     null
   ,CONSTRAINT [PK_DeviceExclusionRule] PRIMARY KEY ([DeviceExclusionRuleID])
)
go



/* 
 * TABLE: [MacAddress] 
drop table MacAddress
 */

CREATE TABLE [MacAddress](
    [MAC]          nvarchar(50)    NOT NULL,
    [DateCreated]  datetime        CONSTRAINT [DF_MacAddress_DateCreated] DEFAULT (getDate()) NOT NULL, -- was getutcdate
    CONSTRAINT [PK_MacAddress] PRIMARY KEY ([MAC])
)
go



/* 
 * TABLE: [DeviceMacLink] 
 */

CREATE TABLE [DeviceMacLink](
    [DeviceID]      int             NOT NULL,
    [MAC]           nvarchar(50)    NOT NULL,
    [DateCreated]   datetime        CONSTRAINT [DF_DeviceMacLink_DateCreated] DEFAULT (getDate()) NOT NULL, -- was getutcdate
    [NonManagedIP]  nvarchar(4000)             NULL,                                                                   -- was xml
    [IsStatic]      bit             CONSTRAINT [DeviceMacLink_IsStatic_DF] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_DeviceMacLink] PRIMARY KEY ([DeviceID], [MAC]), 
    CONSTRAINT [FK_DeviceMacLink_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]),
    CONSTRAINT [FK_DeviceMacLink_MacAddress] FOREIGN KEY ([MAC])
    REFERENCES [MacAddress]([MAC])
)
go



/* 
 * TABLE: [DeviceMonitoringState] 
 */

CREATE TABLE [DeviceMonitoringState](
    [DeviceID]                      int         NOT NULL,
    [LastWindowsEventTimeRecorded]  datetime    NULL,
    CONSTRAINT [PK_DeviceMonitoringState] PRIMARY KEY ([DeviceID]), 
    CONSTRAINT [FK_DeviceMonitoringState_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [DeviceSequence] 
 */

CREATE TABLE [DeviceSequence](
    [DeviceID]  int    IDENTITY(1,1),
    [Bogus]     int    CONSTRAINT [DeviceSequence_Bogus_DF] DEFAULT ((0)) NULL,
    CONSTRAINT [DeviceSequence_PK] PRIMARY KEY ([DeviceID])
)
go



/* 
 * TABLE: [DeviceStatus] 
 */

CREATE TABLE [DeviceStatus](
    [DeviceID]      int              NOT NULL,
    [Status]        nvarchar(20)     NOT NULL,
    [IpAddresses]   nvarchar(255)    NOT NULL,
    [TimeRecorded]  datetime         NOT NULL,
    CONSTRAINT [Device_DeviceStatus_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [DiscoveredNetworkService] 
 */

CREATE TABLE [DiscoveredNetworkService](
    [DeviceID]          int              NOT NULL,
    [IP]                nvarchar(15)     NOT NULL,
    [NetworkServiceID]  int              NOT NULL,
    [ServerInfo]        nvarchar(255)    NOT NULL,
    [Timestamp]         datetime         NOT NULL
)
go



/* 
 * TABLE: [DiskAssets] 
 */

CREATE TABLE [DiskAssets](
    [DeviceID]     int            NOT NULL,
    [DriveLetter]  nvarchar(1)    NOT NULL,
    [Capacity]     int            NOT NULL,
    [Used]         int            NOT NULL,
    CONSTRAINT [Device_DiskAssets_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [ExcludeAddress] 
 */

CREATE TABLE [ExcludeAddress](
    [ID]             int             IDENTITY(1,1),
    [StartAddress]   nvarchar(15)    NULL,
    [EndAddress]     nvarchar(15)    NULL,
    [SingleAddress]  nvarchar(15)    NULL
)
go



/* 
 * TABLE: [FortiGateAlertRule] 
 */

CREATE TABLE [FortiGateAlertRule](
    [AlertRuleID]       int    NOT NULL,
    [CategoryID]        int    NULL,
    [CategoryOperator]  bit    NULL,
    [Severity]          int    NULL,
    [SeverityOperator]  bit    NULL,
    CONSTRAINT [PK_FortiGateAlertRule] PRIMARY KEY ([AlertRuleID]), 
    CONSTRAINT [FK_FortiGateAlertRule_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [FortiGateCategory] 
 */

CREATE TABLE [FortiGateCategory](
    [CategoryID]    int             IDENTITY(0,1),
    [CategoryName]  nvarchar(20)    NOT NULL,
    [Column1]       nvarchar(25)    NOT NULL,
    [Column2]       nvarchar(25)    NOT NULL,
    [Column3]       nvarchar(25)    NOT NULL,
    [Column4]       nvarchar(25)    NOT NULL,
    CONSTRAINT [PK_FortiGateCategory] PRIMARY KEY ([CategoryID])
)
go



/* 
 * TABLE: [FortiGateData] 
 */

CREATE TABLE [FortiGateData](
    [FortiGateDataID]  int               IDENTITY(1,1),
    [CategoryID]       int               NOT NULL,
    [Timestamp]        datetime          NOT NULL,
    [Severity]         int               NOT NULL,
    [LogType]          nvarchar(20)      NOT NULL,
    [LogSubType]       nvarchar(50)      NOT NULL,
    [Column1]          nvarchar(50)      NOT NULL,
    [Column2]          nvarchar(50)      NOT NULL,
    [Column3]          nvarchar(50)      NOT NULL,
    [Column4]          nvarchar(50)      NOT NULL,
    [Message]          nvarchar(512)     NOT NULL,
    [RawData]          nvarchar(1024)    NOT NULL,
    CONSTRAINT [PK_FortiGateData] PRIMARY KEY ([FortiGateDataID])
)
go


/* 
 * TABLE: [InstalledProgsAssets] 
 */

CREATE TABLE [InstalledProgsAssets](
    [DeviceID]  int              NOT NULL,
    [Name]      nvarchar(255)    NOT NULL,
    CONSTRAINT [Device_InstalledProgsAssets_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [InternetConnectivity] 
 */

CREATE TABLE [InternetConnectivity](
    [DeviceID]      int              NOT NULL,
    [Status]        nvarchar(20)     NOT NULL,
    [IpAddresses]   nvarchar(255)    NOT NULL,
    [TimeRecorded]  datetime         NOT NULL,
    CONSTRAINT [Device_InternetConnectivity_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [IPAddress] 
 */

CREATE TABLE [IPAddress](
    [IP]              nvarchar(15)      NOT NULL,
    [DeviceID]        int               NOT NULL,
    [SubnetID]        int               NOT NULL,
    [Description]     nvarchar(50)      NULL,
    [Comments]        nvarchar(50)      NULL,
    [Status]          nchar(10)         NOT NULL,
    [MAC]             nvarchar(50)      NULL,
   -- [DNSName]         nvarchar(255)     NULL,
    [SnmpName]        nvarchar(255)     NOT NULL,
    [SnmpHash]        int               NOT NULL,
    [BiosOSHash]      int               NOT NULL,
    [OSType]          smallint          NOT NULL,
    [TimeLastUpdate]  datetime          NOT NULL,
    [TimeLastUp]      datetime          NULL,
    [WmiEnabled]      bit               NOT NULL,
    [SnmpEnabled]     bit               NOT NULL,
    [MachineName]     nvarchar(255)     NULL,
    [SnmpCommunity]   nvarchar(64)      NULL,
    [SnmpVersion]     int               CONSTRAINT [DF_IPAddress_SnmpVersion] DEFAULT ((1)) NOT NULL,
    [AMTEnabled]      bit               CONSTRAINT [IPAddress_AMTEnabled_DF] DEFAULT ((0)) NOT NULL,
    [IsStatic]        bit               CONSTRAINT [IPAddress_IsStatic_DF] DEFAULT ((0)) NOT NULL,	
    [DnsNames]        nvarchar(1000)    NULL,
    NetbiosName       nvarchar(16)      null,
    [DeviceGuidTag]   uniqueidentifier    NULL,
    [ArpMac]          nvarchar(50)        NULL,
	IsRespondingToArp bit NOT NULL constraint IPAddress_IsRespondingToArp_DF  default(0),
    [WSManEnabled]       bit       CONSTRAINT [IPAddress_WSManEnabled_DF] DEFAULT 0 NOT NULL,
    CONSTRAINT [IPAddress_PK] PRIMARY KEY ([IP]) /* empty-random-pk-name before 7.0 */
)
go



/* 
 * TABLE: [IPAddressAmtInterfaceLink] 
 */

CREATE TABLE [IPAddressAmtInterfaceLink](
    [IP]               nvarchar(15)    NOT NULL,
    [MacAddressAmt]    nvarchar(50)    NOT NULL,
    [InterfaceTypeID]  int             NOT NULL,
    CONSTRAINT [IPAddressAmtInterfaceLink_PK] PRIMARY KEY ([IP], [MacAddressAmt], [InterfaceTypeID]), 
    CONSTRAINT [IPAddressAmtInterfaceLink_AmtInterface_FK] FOREIGN KEY ([MacAddressAmt], [InterfaceTypeID])
    REFERENCES [AmtInterface]([MacAddressAmt], [InterfaceTypeID]),
    CONSTRAINT [IPAddressAmtInterfaceLink_IPAddress_FK] FOREIGN KEY ([IP])
    REFERENCES [IPAddress]([IP])
)
go



/* 
 * TABLE: [IPAddressAssets] 
 */

CREATE TABLE [IPAddressAssets](
    [DeviceID]  int             NOT NULL,
    [Address]   nvarchar(20)    NOT NULL,
    CONSTRAINT [Device_IPAddressAssets_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [IPAddressAuthenticationLink] 
 */

CREATE TABLE [IPAddressAuthenticationLink](
    [IP]                  nvarchar(15)        NOT NULL,
    [AuthenticationGuid]  uniqueidentifier    NOT NULL,
    CONSTRAINT [IPAddressAuthenticationLink_PK] PRIMARY KEY ([IP], [AuthenticationGuid]), 
    CONSTRAINT [IPAddressAuthenticationLink_Authentication_FK] FOREIGN KEY ([AuthenticationGuid])
    REFERENCES [Authentication]([AuthenticationGuid]),
    CONSTRAINT [IPAddressAuthenticationLink_IPAddress_FK] FOREIGN KEY ([IP])
    REFERENCES [IPAddress]([IP])
)
go



/* 
 * TABLE: [IPAddressHistory] 
 */

CREATE TABLE [IPAddressHistory](
    [IP]              nvarchar(15)      NOT NULL,
    [DeviceID]        int               NOT NULL,
    [SubnetID]        int               NOT NULL,
    [Description]     nvarchar(50)      NULL,
    [Comments]        nvarchar(50)      NULL,
    [Status]          nchar(10)         NOT NULL,
    [MAC]             nvarchar(50)      NULL,
    --[DNSName]         nvarchar(255)     NULL,
    [SnmpName]        nvarchar(255)     NOT NULL,
    [SnmpHash]        int               NOT NULL,
    [BiosOSHash]      int               NOT NULL,
    [OSType]          smallint          NOT NULL,
    [TimeLastUpdate]  datetime          NOT NULL,
    [TimeLastUp]      datetime          NULL,
    [WmiEnabled]      bit               NOT NULL,
    [SnmpEnabled]     bit               NOT NULL,
    [MachineName]     nvarchar(255)     NULL,
    [SnmpCommunity]   nvarchar(64)      NULL,
    [SnmpVersion]     int               NOT NULL,
    [AMTEnabled]      bit               NOT NULL,
    [IsStatic]        bit               NOT NULL,
    [DnsNames]        nvarchar(1000)        NULL,
	NetbiosName       nvarchar(16)          null,
    [DeviceGuidTag]   uniqueidentifier    NULL,
    [ArpMac]          nvarchar(50)        NULL
    ,[WSManEnabled]    bit                 CONSTRAINT [IPAddressHistory_WSManEnabled_DF] DEFAULT 0 NOT NULL
)
go



/* 
 * TABLE: [IPAddressMacLink] 
 */

CREATE TABLE [IPAddressMacLink](
    [IP]            nvarchar(15)    NOT NULL,
    [MAC]           nvarchar(50)    NOT NULL,
    [DateCreated]   datetime        CONSTRAINT [DF_IPAddressMacLink_DateCreated] DEFAULT (getDate()) NOT NULL, -- was getUtcDate
    [NonManagedIP]  nvarchar(4000)             NULL,        -- was xml
    [IsStatic]      bit             CONSTRAINT [IPAddressMacLink_IsStatic_DF] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_IPAddressMacLink] PRIMARY KEY ([IP], [MAC]), 
    CONSTRAINT [FK_IPAddressMacLink_IPAddress] FOREIGN KEY ([IP])
    REFERENCES [IPAddress]([IP]),
    CONSTRAINT [FK_IPAddressMacLink_MacAddress] FOREIGN KEY ([MAC])	
    REFERENCES [MacAddress]([MAC])
)
go



/* 
 * TABLE: [License] 
 */

CREATE TABLE [License](
    [DeviceID]       int                 NOT NULL,
    [GUID]           uniqueidentifier    NOT NULL,
    [ProductID]      int                 NOT NULL,
    [InstallDate]    datetime            NOT NULL,
    [LastCheckedIn]  datetime            NULL,
    [LastUpdated]    datetime            NULL
)
go



/* 
 * TABLE: [ManagedElementType] 
 */

CREATE TABLE [ManagedElementType](
    [ManagedElementTypeGuid]  uniqueidentifier    NOT NULL,
    [TypeName]                nvarchar(50)        NOT NULL,
	[IsSingleInstance]        bit                 NOT NULL,
	TableName                    nvarchar(128) null,
	ManagedElementGuidColumnName nvarchar(128) null,
	ME2CategoryID	             int		   null,
	[SchemaName]              nvarchar(128)    NULL,
	[IsForSite]				BIT	CONSTRAINT [ManagedElementType_IsForSite_DF] DEFAULT(0) NOT NULL,
    CONSTRAINT [ManagedElementType_PK] PRIMARY KEY ([ManagedElementTypeGuid])
)
go



/* 
 * TABLE: [ManagedElement] 
 */

CREATE TABLE [ManagedElement](
    [ManagedElementGuid]      uniqueidentifier    NOT NULL,
    [DeviceGuid]              uniqueidentifier    NOT NULL,
    [ManagedElementTypeGuid]  uniqueidentifier    NOT NULL,
    [DateCreated]             datetime            NOT NULL,
    [DateDeleted]             datetime            NULL,
    [HierarchyLevel]          int                 NOT NULL,
    CONSTRAINT [ManagedElement_PK] PRIMARY KEY ([ManagedElementGuid]), 
    CONSTRAINT [ManagedElement_ManagedElementTypeGuid_ManagedElementType_ManagedElementTypeGuid_FK] 
		FOREIGN KEY ([ManagedElementTypeGuid])
			REFERENCES [ManagedElementType]([ManagedElementTypeGuid])
)
go

create nonclustered index ManagedElement_ManagedElementTypeGuid_IX on [ManagedElement](ManagedElementTypeGuid)
GO


/* 
 * TABLE: [ManagedElementAttributeType] 
 */

CREATE TABLE [ManagedElementAttributeType](
    [ManagedElementAttributeTypeGuid]  uniqueidentifier    NOT NULL,
    [ManagedElementTypeGuid]           uniqueidentifier    NOT NULL,
    [AttributeName]                    nvarchar(50)        NULL,
    [IsKey]                            bit                 NOT NULL,
    [IsMonitored]                      bit                 NOT NULL,
    [DataType]                         nvarchar(50)        NOT NULL,
    [IsArray]                          bit                 NOT NULL,
    [SqlDataType]                      nvarchar(50)         NOT NULL,
	DataLength      int                 null,
	IsIdentity      bit            not  null constraint ManagedElementAttributeType_IsIdentity_DF default (0),
	UnitOfMeasure   nvarchar(100)       null,
	IsPKMember      bit            not  null constraint ManagedElementAttributeType_IsPKMember_DF default (0),
    CONSTRAINT [ManagedElementAttributeType_PK] PRIMARY KEY ([ManagedElementAttributeTypeGuid]),
    CONSTRAINT [ManagedElementAttributeType_ManagedElementTypeGuid_ManagedElementType_ManagedElementTypeGuid_FK] FOREIGN KEY ([ManagedElementTypeGuid])
    REFERENCES [ManagedElementType]([ManagedElementTypeGuid])
)
go



/* 
 * TABLE: [ManagedElementAttributeData] 
 */

CREATE TABLE [ManagedElementAttributeData](
   		[ManagedElementGuid] uniqueidentifier NOT NULL
	, 	[ManagedElementAttributeTypeGuid] uniqueidentifier NOT NULL
	, 	[DateCreated] datetime NOT NULL
	, 	[DateDeleted] datetime NULL
	, 	[TextValue] nvarchar(4000) NULL
	, 	[NumericValue] float NULL
	, 	[DateTimeValue] datetime NULL
	, 	[GuidValue] uniqueidentifier NULL
	, 	[BooleanValue] bit NULL
	,   [DateModified]                     datetime            NOT NULL
   --CONSTRAINT [ManagedElementAttributeData_PK] PRIMARY KEY ([ManagedElementGuid], [ManagedElementAttributeTypeGuid], [DateCreated]), 
    , CONSTRAINT [ManagedElementAttributeData_ManagedElementAttributeTypeGuid_ManagedElementAttributeType_ManagedElementAttributeTypeGuid_FK] 
              FOREIGN KEY ([ManagedElementAttributeTypeGuid])
                      REFERENCES [ManagedElementAttributeType]([ManagedElementAttributeTypeGuid]),
    CONSTRAINT [ManagedElementAttributeData_ManagedElementGuid_ManagedElement_ManagedElementGuid_FK] 
              FOREIGN KEY ([ManagedElementGuid])
                      REFERENCES [ManagedElement]([ManagedElementGuid])
)
go

alter table [ManagedElementAttributeData]
	add CONSTRAINT [ManagedElementAttributeData_PK] PRIMARY KEY  ([ManagedElementGuid], [ManagedElementAttributeTypeGuid], [DateCreated])
go

CREATE INDEX [ManagedElementAttributeData_ManagedElementAttributeTypeGuid_IX] 
		ON [ManagedElementAttributeData]([ManagedElementAttributeTypeGuid])
go

/* 
 * TABLE: [ManagedElementTypeLink] 
 */

CREATE TABLE [ManagedElementTypeLink](
    [ParentGuid]      uniqueidentifier    NOT NULL,
    [ChildGuid]       uniqueidentifier    NOT NULL,
    [RelationTypeID]  int                 NOT NULL,
    CONSTRAINT [ManagedElementTypeLink_PK] PRIMARY KEY ([ParentGuid], [ChildGuid]), 
    CONSTRAINT [ManagedElementTypeLink_ChildGuid_ManagedElementType_ManagedElementTypeGuid_FK] FOREIGN KEY ([ChildGuid])
    REFERENCES [ManagedElementType]([ManagedElementTypeGuid]),
    CONSTRAINT [ManagedElementTypeLink_ParentGuid_ManagedElementType_ManagedElementTypeGuid_FK] FOREIGN KEY ([ParentGuid])
    REFERENCES [ManagedElementType]([ManagedElementTypeGuid])
)
go

/* 
 * TABLE: [Monitor] 
 */

CREATE TABLE [Monitor](
    [MonitorID]            int                 NOT NULL,
    [Name]                 nvarchar(50)        NOT NULL,
    [Description]          nvarchar(1000)      NOT NULL,
    [Enabled]              bit                 NOT NULL,
    [MonitorType]          int             NOT NULL,
    [SamplingInterval]     int                 NULL,
    [MonitorStateData]     image               NULL,
    [MonitoredObjectGuid]  uniqueidentifier    NOT NULL,
    [MonitoredObjectType]  nvarchar(50)        NOT NULL,
    CONSTRAINT [PK_Monitor] PRIMARY KEY 
		([MonitoredObjectGuid], [MonitoredObjectType], [MonitorID])
)
go
CREATE INDEX [Monitor_MonitorID_IX] ON [Monitor]([MonitorID])
go



/* 
 * TABLE: [MonitorAssets] 
 */

CREATE TABLE [MonitorAssets](
    [DeviceID]  int              NOT NULL,
    [Name]      nvarchar(255)    NOT NULL,
    CONSTRAINT [Device_MonitorAssets_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [MonitorAttribute] 
 */

CREATE TABLE [MonitorAttribute](
    [MonitorID]     int               NOT NULL,
    [Name]          nvarchar(200)     NOT NULL,
    [TextValue]     nvarchar(1000)    NULL,
    [NumericValue]  float             NULL,
    [BinaryValue]   image             NULL,
    [MonitoredObjectGuid]  uniqueidentifier    NOT NULL,
    [MonitoredObjectType]  nvarchar(50)        NOT NULL,
    CONSTRAINT [PK_MonitorAttribute] PRIMARY KEY ([MonitoredObjectGuid], [MonitoredObjectType], [MonitorID], [Name]), 
    CONSTRAINT [FK_MonitorAttribute_Monitor] FOREIGN KEY ([MonitoredObjectGuid], [MonitoredObjectType], [MonitorID])
    REFERENCES [Monitor]([MonitoredObjectGuid], [MonitoredObjectType], [MonitorID])
)
go
CREATE INDEX [MonitorAttribute_MonitorID_IX] ON [MonitorAttribute]([MonitorID])
go


/* 
 * TABLE: [NetworkService] 
 */

CREATE TABLE [NetworkService](
    [NetworkServiceID]   int              IDENTITY(1,1),
    [Name]               nvarchar(255)    NOT NULL,
    [DefaultPort]        int              NOT NULL,
    [TransportProtocol]  nvarchar(10)     CONSTRAINT [DF_NetworkService_TransportProtocol] DEFAULT ('TCP') NOT NULL,
    [Timeout]            int              CONSTRAINT [DF_NetworkService_Timeout] DEFAULT ((5000)) NOT NULL,
    [IsCustom]           bit              CONSTRAINT [DF_NetworkService_IsCustom] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_NetworkService] PRIMARY KEY ([NetworkServiceID])
)
go



/* 
 * TABLE: [MonitoredNetworkService] 
 */

CREATE TABLE [MonitoredNetworkService](
    [MonitoredNetworkServiceID]  int             IDENTITY(1,1),
    [NetworkServiceID]           int             NOT NULL,
    [DeviceID]                   int             NOT NULL,
    [IP]                         nvarchar(15)    NOT NULL,
    [Port]                       int             NOT NULL,
    [TimeOutValue]               int             NOT NULL,
    [PollingInterval]            int             NOT NULL,
    [MonitorStartTime]           datetime        NULL,
    [MonitorEndTime]             datetime        NULL,
    [MonitorStartDate]           datetime        NULL,
    [MonitorEndDate]             datetime        NULL,
    [Enabled]                    bit             NOT NULL,
    CONSTRAINT [PK_MonitoredNetworkService] PRIMARY KEY ([MonitoredNetworkServiceID]), 
    CONSTRAINT [FK_MonitoredNetworkService_IPAddress] FOREIGN KEY ([IP])
    REFERENCES [IPAddress]([IP]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_MonitoredNetworkService_NetworkService] FOREIGN KEY ([NetworkServiceID])
    REFERENCES [NetworkService]([NetworkServiceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [MWActivityLog] 
 */

CREATE TABLE [MWActivityLog](
    [DeviceID]         int             NOT NULL,
    [ID]               int             IDENTITY(1,1),
    [Timestamp]        datetime        NOT NULL,
    [MWFeatureID]      smallint        NOT NULL,
    [EventLog]         nvarchar(50)    NULL,
    [EventSource]      nvarchar(50)    NULL,
    [EventID]          int             NULL,
    [Severity]         smallint        NOT NULL,
    [ActivityDetails]  ntext           NOT NULL,
    [Result]           smallint        NOT NULL,
    [LogFlag]          smallint        NOT NULL,
    [MWAlert]          smallint        NOT NULL,
    [EventDescID]      int             NULL,
    CONSTRAINT [Device_MWActivityLog_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [MWConfig] 
 */

CREATE TABLE [MWConfig](
    [ID]     int              IDENTITY(1,1),
    [Key]    nvarchar(30)     NOT NULL,
    [Value]  nvarchar(511)    NOT NULL,
    CONSTRAINT [MWConfig_PK] PRIMARY KEY ([ID])
)
go



/* 
 * TABLE: [NwkCardsAssets] 
 */

CREATE TABLE [NwkCardsAssets](
    [DeviceID]  int              NOT NULL,
    [CardIdx]   smallint         NOT NULL,
    [Name]      nvarchar(255)    NOT NULL,
    [DHCP]      bit              NOT NULL,
    [MAC]       nvarchar(50)     NULL,
    [DrvName]   nvarchar(255)    NULL,
    [DrvVer]    nvarchar(255)    NULL,
    CONSTRAINT [Device_NwkCardsAssets_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [NwkCardsIPAssets] 
 */

CREATE TABLE [NwkCardsIPAssets](
    [DeviceID]   int             NOT NULL,
    [CardIdx]    smallint        NOT NULL,
    [IPAddress]  nvarchar(50)    NULL,
    CONSTRAINT [Device_NwkCardsIPAssets_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [NwkServiceCommAlertRule] 
 */

CREATE TABLE [NwkServiceCommAlertRule](
    [AlertRuleID]       int        NOT NULL,
    [NetworkServiceID]  int        NOT NULL,
    [Minutes]           tinyint    NULL,
    CONSTRAINT [PK_NwkServiceCommAlertRule] PRIMARY KEY ([AlertRuleID]), 
    CONSTRAINT [FK_NwkServiceCommAlertRule_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [OMMonitoredEvent] 
 */

CREATE TABLE [OMMonitoredEvent](
    [ID]           int              IDENTITY(1,1),
    [EventID]      int              NOT NULL,
    [Severity]     smallint         NOT NULL,
    [Log]          nvarchar(50)     NOT NULL,
    [Source]       nvarchar(255)     NOT NULL,
    [SearchText]   nvarchar(2000)    NOT NULL,
    [Priority]     int              NOT NULL,
    [Title]        nvarchar(255)    NOT NULL,
    [Description]  nvarchar(16)     NOT NULL,
    [Response]     nvarchar(255)    NOT NULL,
	DetailsSearchOptions ntext          NULL,
    CONSTRAINT [OMMonitoredEvent_PK] PRIMARY KEY ([ID])
)
go



/* 
 * TABLE: [OMCustomEvent] 
 */

CREATE TABLE [OMCustomEvent](
    [DeviceID]            int    NOT NULL,
    [ID]                  int    IDENTITY(1,1),
    [OMMonitoredEventID]  int    NOT NULL,
    [Enabled]             bit    NOT NULL,
    CONSTRAINT [OMCustomEvent_PK] PRIMARY KEY ([ID]), 
    CONSTRAINT [Device_OMCustomEvent_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [OMMonitoredEvent_OMCustomEvent_FK1] FOREIGN KEY ([OMMonitoredEventID])
    REFERENCES [OMMonitoredEvent]([ID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go


/* 
 * TABLE: [OMDefaultEvent] 
 */

CREATE TABLE [OMDefaultEvent](
    [ID]                  int         IDENTITY(1,1),
    [OMMonitoredEventID]  int         NOT NULL,
    [OSTypeID]            smallint    NOT NULL,
    [Enabled]             bit         NOT NULL,
    CONSTRAINT [OMDefaultEvent_PK] PRIMARY KEY ([ID]), 
    CONSTRAINT [OMMonitoredEvent_OMDefaultEvent_FK1] FOREIGN KEY ([OMMonitoredEventID])
    REFERENCES [OMMonitoredEvent]([ID]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [OSType_OMDefaultEvent_FK1] FOREIGN KEY ([OSTypeID])
    REFERENCES [OSType]([ID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go






/* 
 * TABLE: [PatchDeviceLink] 
 */

CREATE TABLE [PatchDeviceLink](
    [DeviceID]             int                 NOT NULL,
    [PatchGuid]            uniqueidentifier    NOT NULL,
    [PatchDeviceStatusID]  tinyint             NOT NULL,
    [TimeLastUpdate]       datetime            NOT NULL,
    CONSTRAINT [PK_PatchDeviceLink] PRIMARY KEY ([DeviceID], [PatchGuid])
)
go



/* 
 * TABLE: [PatchDeviceLink_Temp] 
 */

CREATE TABLE [PatchDeviceLink_Temp](
    [DeviceID]             int                 NOT NULL,
    [PatchGuid]            uniqueidentifier    NOT NULL,
    [PatchDeviceStatusID]  tinyint             NOT NULL,
    [TimeLastUpdate]       datetime            NOT NULL,
    CONSTRAINT [PK_PatchDeviceLink_Temp] PRIMARY KEY ([DeviceID], [PatchGuid])
)
go



/* 
 * TABLE: [PatchManagedDevice] 
 */

CREATE TABLE [PatchManagedDevice](
    [DeviceID]              int                 NOT NULL,
    [PatchManagedDeviceID]  uniqueidentifier    NOT NULL,
    [TimeLastQueried]       datetime            NOT NULL,
    [WaitingForPatchDownload] [bit] NOT NULL constraint PatchManagedDevice_WaitingForPatchDownload_DF default(0),
    [LastReportedStatusTime]    datetime            NULL,
    [LastSyncTime]             datetime            NULL
	,
    CONSTRAINT [PK_PatchManagedDevice] PRIMARY KEY ([DeviceID]), 
    CONSTRAINT [FK_PatchManagedDevice_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go

 

/* 
 * TABLE: [PerfAlertRule] 
 */

CREATE TABLE [PerfAlertRule](
    [AlertRuleID]    int      NOT NULL,
    [CounterID]      int      NOT NULL,
    [Threshold]      float    NOT NULL,
    [NbrDataPoints]  int      NOT NULL,
    [IsUpThreshold]  bit      NOT NULL,
    CONSTRAINT [PK_PerfAlertRule] PRIMARY KEY ([AlertRuleID]), 
    CONSTRAINT [FK_PerfAlertRule_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [PortProbeReport] 
 */

CREATE TABLE [PortProbeReport](
    [PortProbeReportID]  int              IDENTITY(1,1),
    [DeviceID]           int              NOT NULL,
    [IP]                 nvarchar(15)     NOT NULL,
    [Port]               int              NOT NULL,
    [Status]             bit              NOT NULL,
    [Service]            nvarchar(50)     NOT NULL,
    [TransportProtocol]  nvarchar(10)     NOT NULL,
    [Timeout]            int              NOT NULL,
    [RoundTripTime]      int              NOT NULL,
    [IsTimeout]          bit              NOT NULL,
    [ServerInfo]         nvarchar(255)    NOT NULL,
    [Timestamp]          datetime         NOT NULL
)
go



/* 
 * TABLE: [Protocol] 
 */

CREATE TABLE [Protocol](
    [ProtocolID]  int         IDENTITY(1,1),
    [Protocol]    nvarchar(10)    NOT NULL   -- was char(10)
)
go



/* 
 * TABLE: [QFEAssets] 
 */

CREATE TABLE [QFEAssets](
    [DeviceID]  int             NOT NULL,
    [HotFixID]  nvarchar(50)    NOT NULL,
    CONSTRAINT [Device_QFEAssets_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [SCEAlert] 
 */

CREATE TABLE [SCEAlert](
    [AlertGuid]    uniqueidentifier    NOT NULL,
    [AlertRuleID]  int                 NOT NULL,
    [DeviceID]     int                 NOT NULL,
    [SeverityID]   smallint            NOT NULL,
    CONSTRAINT [PK_SCEAlert] PRIMARY KEY ([AlertGuid]), 
    CONSTRAINT [FK_SCEAlert_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_SCEAlert_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [SCEAlertRule] 
 */

CREATE TABLE [SCEAlertRule](
    [AlertRuleID]                  int              NOT NULL,
    [SeverityID]                   smallint         NULL,
    [SeverityOp]                   bit              NULL,
    [PriorityID]                   smallint         NULL,
    [PriorityOp]                   bit              NULL,
    [CategoryID]                   smallint         NULL,
    [CategoryOp]                   bit              NULL,
    [NameSearchString]             nvarchar(50)     NULL,
    [NameSearchOp]                 bit              NULL,
    [DescriptionSearchString]      nvarchar(200)    NULL,
    [DescriptionSearchOp]          bit              NULL,
    [SourceSearchString]           nvarchar(50)     NULL,
    [SourceSearchOp]               bit              NULL,
    [ManagementGroupSearchString]  nvarchar(50)     NULL,
    [ManagementGroupSearchOp]      bit              NULL,
    CONSTRAINT [PK_SCEAlertRule] PRIMARY KEY ([AlertRuleID]), 
    CONSTRAINT [FK_SCEAlertRule_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [ServiceAlertRule] 
 */

CREATE TABLE [ServiceAlertRule](
    [AlertRuleID]  int              NOT NULL,
    [ServiceName]  nvarchar(255)    NULL,
    CONSTRAINT [PK_ServiceAlertRule] PRIMARY KEY ([AlertRuleID]), 
    CONSTRAINT [FK_ServiceAlertRule_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [ServicesAssets] 
 */

CREATE TABLE [ServicesAssets](
    [Caption]      nvarchar(255)    NOT NULL,
    [Description]  nvarchar(511)    NOT NULL,
    [Name]         nvarchar(255)    NULL,
    [StartMode]    nvarchar(9)      NULL,
    [DeviceID]     int              NULL,
    CONSTRAINT [Device_ServicesAssets_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [SnmpMib] 
 */

CREATE TABLE [SnmpMib](
    [SnmpMibID]                int               IDENTITY(1,1),
    [SnmpMibName]              nvarchar(255)     NOT NULL,
    [SnmpMibOID]               nvarchar(400)     NOT NULL,
    [SnmpMibDesc]              nvarchar(1000)    NULL,
    [DefaultSamplingInterval]  int               NOT NULL,
    [IsDefaultLPI]             bit               NOT NULL,
	    IsTabular [bit] NOT NULL constraint SnmpMib_IsTabular_DF default (0)
	  , SnmpDataType           int null
   ,CONSTRAINT [SnmpMib_PK] PRIMARY KEY ([SnmpMibID])
)
go



/* 
 * TABLE: [SnmpMibAlertRule] 
 */

CREATE TABLE [SnmpMibAlertRule](
    [AlertRuleID]              int              NOT NULL,
    [SnmpMibID]                int              NOT NULL,
    [Threshold]                float            NOT NULL,
    [NbrPoints]                int              NOT NULL,
    [IsUpThreshold]            bit              NOT NULL,
    [TextValueSearchString]    nvarchar(255)    NULL,
    [TextValueSearchOperator]  bit              NULL,
    [MatchCase]                bit              CONSTRAINT [SnmpMibAlertRule_MatchCase_DF] DEFAULT ((0)) NOT NULL,
    [TextValueSearchOptions]   ntext            NULL,
    CONSTRAINT [PK_SnmpMibAlertRule] PRIMARY KEY ([AlertRuleID]), 
    CONSTRAINT [FK_SnmpMibAlertRule_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [SnmpMibCustomer] 
 */

CREATE TABLE [SnmpMibCustomer](
    [SnmpMibCustomerID]  int         IDENTITY(1,1),
    [DeviceID]           int         NOT NULL,
    [SnmpMibID]          int         NOT NULL,
    [SamplingInterval]   int         NOT NULL,
    [MonitorStartTime]   datetime    NULL,
    [MonitorEndTime]     datetime    NULL,
    [MonitorStartDate]   datetime    NULL,
    [MonitorEndDate]     datetime    NULL,
    [Enabled]            bit         NOT NULL,
	    [SCSnmpMibCustomerID] [int] NOT NULL  constraint SnmpMibCustomer_SCSnmpMibCustomerID_DF default (0)
   ,
    CONSTRAINT [SnmpMibCustomer_PK] PRIMARY KEY ([SnmpMibCustomerID]), 
    CONSTRAINT [Device_SnmpMibCustomer_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [SnmpMib_SnmpMibCustomer_FK1] FOREIGN KEY ([SnmpMibID])
    REFERENCES [SnmpMib]([SnmpMibID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [SnmpMibData] 
 */

CREATE TABLE [SnmpMibData](
    [SnmpMibID]        int              NOT NULL,
    [DeviceID]         int              NOT NULL,
    [SampleTimestamp]  datetime         NOT NULL,
    [SampleValue]      nvarchar(255)    NOT NULL,
    [SnmpMibDataID]    int              IDENTITY(1,1),
    CONSTRAINT [SnmpMibData_PK] PRIMARY KEY ([SnmpMibDataID]), 
    CONSTRAINT [Device_SnmpMibData_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [SnmpMib_SnmpMibData_FK1] FOREIGN KEY ([SnmpMibID])
    REFERENCES [SnmpMib]([SnmpMibID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [SnmpTrap] 
 */

CREATE TABLE [SnmpTrap](
    [SnmpTrapID]            int              IDENTITY(1,1),
    [AgentIP]               nvarchar(15)     NOT NULL,
    [DeviceID]              int              NOT NULL,
    [UpTime]                nvarchar(50)     NULL,
    [GenericMessage]        nvarchar(24)     NULL,
    [TimeOfTrapGeneration]  datetime         NULL,
    [EnterpriseOid]         nvarchar(400)    NULL,
    [SpecificNumber]        nvarchar(10)     NULL,
    CONSTRAINT [PK_SnmpTrap] PRIMARY KEY ([SnmpTrapID])
)
go



/* 
 * TABLE: [SnmpTrapAlertRule] 
 */

CREATE TABLE [SnmpTrapAlertRule](
    [AlertRuleID]    int              NOT NULL,
    [Operator]       bit              NULL,
    [SnmpTrapMsg]    nvarchar(255)    NULL,
    [EnterpriseOID]  nvarchar(400)    NULL,
    [GenericType]    int              NULL,
    [OccurrencesThreshold]                int              CONSTRAINT [SnmpTrapAlertRule_OccurrencesThreshold] DEFAULT 1 NOT NULL,
    [OccurrencesThresholdIntervalInMins]  int              NULL,
    [SnmpTrapMsgSearchOptions]            ntext            NULL,
    CONSTRAINT [PK_SnmpTrapAlertRule] PRIMARY KEY ([AlertRuleID]), 
    CONSTRAINT [FK_SnmpTrapAlertRule_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go

/* 
 * TABLE: [SnmpTrapAlertRuleOccurrences] 
 */

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



/* 
 * TABLE: [SnmpTrapVarBindingData] 
 */

CREATE TABLE [SnmpTrapVarBindingData](
    [SnmpTrapID]     int               NOT NULL,
    [Oid]            nvarchar(400)     NULL,
    [SpecificValue]  nvarchar(1024)    NULL,
    CONSTRAINT [FK_SnmpTrapVarBindingData_SnmpTrap] FOREIGN KEY ([SnmpTrapID])
    REFERENCES [SnmpTrap]([SnmpTrapID]) ON DELETE CASCADE
)
go



/* 
 * TABLE: [SoundCardAssets] 
 */

CREATE TABLE [SoundCardAssets](
    [DeviceID]  int              NOT NULL,
    [Name]      nvarchar(255)    NOT NULL,
    [Maker]     nvarchar(255)    NOT NULL,
    CONSTRAINT [Device_SoundCardAssets_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [Subnet] 
 */

CREATE TABLE [Subnet](
    [SubnetID]       int             IDENTITY(1,1),
    [Mask]           nvarchar(15)    NULL,
    [StartAddress]   nvarchar(15)    NULL,
    [EndAddress]     nvarchar(15)    NULL,
    [SingleAddress]  nvarchar(15)    NULL,
    [ProtocolID]     int             NULL,
    [IsVpn]          bit             CONSTRAINT [DF_Subnet_IsVpn] DEFAULT ((0)) NOT NULL
)
go



/* 
 * TABLE: [SysLogAlert] 
 */

CREATE TABLE [SysLogAlert](
    [Guid]         uniqueidentifier    NOT NULL,
    [AlertRuleID]  int                 NOT NULL,
    [DeviceID]     int                 NOT NULL,
    [Facility]     int                 NOT NULL,
    [Severity]     int                 NOT NULL,
    [ProcessName]  nvarchar(128)       NULL,
    [Msg]          nvarchar(2500)      NOT NULL,
    CONSTRAINT [PK_SysLogAlert] PRIMARY KEY ([Guid]), 
    CONSTRAINT [FK_SysLogAlert_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_SysLogAlert_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [SysLogAlertRule] 
 */

CREATE TABLE [SysLogAlertRule](
    [AlertRuleID]       int              NOT NULL,
    [Facility]          int              NULL,
    [FacilityOperator]  bit              NULL,
    [Severity]          int              NULL,
    [SeverityOperator]  bit              NULL,
    [MessageSubString]  nvarchar(255)    NULL,
    [MsgOpeator]        bit              NULL,
    CONSTRAINT [PK_SysLogAlertRule] PRIMARY KEY ([AlertRuleID]), 
    CONSTRAINT [FK_SysLogAlertRule_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go


/* 
 * TABLE: [SyslogAlertRuleOccurrences] 
 */

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



/* 
 * TABLE: [SyslogFacility] 
 */

CREATE TABLE [SyslogFacility](
    [Facility]      int             IDENTITY(0,1),
    [FacilityName]  nvarchar(50)    NOT NULL,
    CONSTRAINT [PK_SyslogFacility] PRIMARY KEY ([Facility])
)
go



/* 
 * TABLE: [SyslogSeverity] 
 */

CREATE TABLE [SyslogSeverity](
    [Severity]      int             IDENTITY(0,1),
    [SeverityName]  nvarchar(50)    NOT NULL,
    CONSTRAINT [PK_SyslogSeverity] PRIMARY KEY ([Severity])
)
go

/* 
 * TABLE: [VideoAdptAssets] 
 */

CREATE TABLE [VideoAdptAssets](
    [DeviceID]    int              NOT NULL,
    [Name]        nvarchar(255)    NOT NULL,
    [ColorBits]   smallint         NOT NULL,
    [Resolution]  nvarchar(255)    NOT NULL,
    [Ram]         smallint         NOT NULL,
    [Driver]      nvarchar(255)    NOT NULL,
    CONSTRAINT [Device_VideoAdptAssets_FK1] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [WebsiteAlertRule] 
 */

CREATE TABLE [WebsiteAlertRule](
    [AlertRuleID]         int    NOT NULL,
    [DownTime]            int    NULL,
    [SearchFailed]        bit    NULL,
    [ResponseTime]        int    NULL,
    [NumberOfDataPoints]  int    NULL,
    CONSTRAINT [FK_WebsiteAlertRule_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [WebsiteMonitor] 
 */

CREATE TABLE [WebsiteMonitor](
    [WebsiteID]             int              NOT NULL,
    [Protocol]              nvarchar(5)       NOT NULL, -- was varchar
    [Host]                  nvarchar(255)    NOT NULL,
    [Port]                  int              NULL,
    [Path]                  nvarchar(255)    NULL,
    [UserAgentString]       nvarchar(100)    NOT NULL,
    [UserLanguage]          nvarchar(50)     NOT NULL,
    [PostData]              ntext            NULL,
    [AuthenticationType]    nvarchar(10)      NOT NULL, -- was varchar
    [UserDomain]            nvarchar(50)     NULL,
    [UserName]              nvarchar(50)     NULL,
    [Password]              nvarchar(255)    NULL,
    [SearchRange]           tinyint          NOT NULL,
    [SearchString]          nvarchar(300)    NULL,
    [ShouldFindString]      bit              NULL,
    [MatchCase]             bit              NULL,
    [MatchWholeWord]        bit              NULL,
    [UseRegularExpression]  bit              NULL,
    [AllowRedirect]         bit              NOT NULL,
    [Timeout]               int              NULL,
    [MonitoringInterval]    int              NOT NULL,
    [MonitorStartTime]      datetime         NULL,
    [MonitorEndTime]        datetime         NULL,
    [MonitorStartDate]      datetime         NULL,
    [MonitorEndDate]        datetime         NULL,
    [SearchOptions]         ntext            NULL,
    CONSTRAINT [PK_WebsiteMonitor] PRIMARY KEY ([WebsiteID]), 
    CONSTRAINT [FK_WebsiteMonitor_Website] FOREIGN KEY ([WebsiteID])
    REFERENCES [Website]([WebsiteID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [WebsiteMonitoringData] 
 */

CREATE TABLE [WebsiteMonitoringData](
    [WebsiteID]                    int               NOT NULL,
    [TimeOfSample]                 datetime          NOT NULL,
    [Status]                       bit               NOT NULL,
    [ResponseTime]                 int               NULL,
    [SearchResult]                 bit               NULL,
    [FailureCaptureHeader]         nvarchar(200)     NULL,
    [FailureCaptureContent]        ntext             NULL,
    [ErrorMessage]                 nvarchar(255)     NULL,
    [TransactionDuration]          int               NULL,
    [ErrorMessageResxName]         nvarchar(255)     NULL,
    [ErrorMessageResxValueParams]  nvarchar(3500)    NULL,
    CONSTRAINT [PK_WebsiteMonitoringData] PRIMARY KEY ([WebsiteID], [TimeOfSample]), 
    CONSTRAINT [FK_WebsiteMonitoringData_Website] FOREIGN KEY ([WebsiteID])
    REFERENCES [Website]([WebsiteID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [WinEventAlert] 
 */

CREATE TABLE [WinEventAlert](
    [Guid]         uniqueidentifier    NOT NULL,
    [AlertRuleID]  int                 NOT NULL,
    [DeviceID]     int                 NOT NULL,
    [EventLog]     nvarchar(64)        NULL,
    [EventSource]  nvarchar(64)        NULL,
    [EventID]      int                 NULL,
    [Severity]     smallint            NULL,
    CONSTRAINT [PK_WinEventAlert] PRIMARY KEY ([Guid]), 
    CONSTRAINT [FK_WinEventAlert_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_WinEventAlert_Device] FOREIGN KEY ([DeviceID])
    REFERENCES [Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * TABLE: [WinEventRule] 
 */

CREATE TABLE [WinEventRule](
    [AlertRuleID]    int              NOT NULL,
    [EventLog]       nvarchar(64)     NULL,
    [EventLogOp]     bit              NULL,
    [EventSource]    nvarchar(64)     NULL,
    [EventSourceOp]  bit              NULL,
    [EventID]        int              NULL,
    [EventIDOp]      bit              NULL,
    [Severity]       smallint         NULL,
    [SeverityOp]     bit              NULL,
    [Details]        nvarchar(2000)    NULL,
	DetailsSearchOptions ntext        NULL,
    CONSTRAINT [PK_WinEventRule] PRIMARY KEY ([AlertRuleID]), 
    CONSTRAINT [FK_WinEventRule_AlertRule] FOREIGN KEY ([AlertRuleID])
    REFERENCES [AlertRule]([AlertRuleID]) ON DELETE CASCADE ON UPDATE CASCADE
)
go



/* 
 * INDEX: [IX_AMTEvents] 
 */

CREATE INDEX [IX_AMTEvents] ON [AMTEvents]([DeviceID])
go


/* 
 * INDEX: [Authentication_UserName_Protocol_ApplicationType_PwdHash_Port] 
 */

CREATE UNIQUE INDEX [Authentication_UserName_Protocol_ApplicationType_PwdHash_Port] ON [Authentication]([UserName], [Protocol], [ApplicationType], [PwdHash], [Port])
go




/* 
 * INDEX: Device_DeviceGuid_AK
 */
CREATE UNIQUE INDEX Device_DeviceGuid_AK ON Device(DeviceGuid)
go


/* 
 * INDEX: [ManagedElement_DeviceGuid_ManagedElementTypeGuid_DateDeleted_ManagedElementTypeGuid_DateCreated_IX]
 */
CREATE INDEX [ManagedElement_DeviceGuid_ManagedElementTypeGuid_DateDeleted_ManagedElementTypeGuid_DateCreated_IX] ON [ManagedElement] 
(
	 [DeviceGuid] 
	,[ManagedElementGuid] 
	,[DateDeleted]
	,[ManagedElementTypeGuid] 
	,[DateCreated] 
)
GO


CREATE TABLE [MWDiagnostics_Action](
	[ActionID] [int] IDENTITY(1,1) NOT NULL,
	[TimeOfEntry] [datetime] NOT NULL,
	[TimeTakenInMilliseconds] [int] NOT NULL,
	[ClassName] [nvarchar](50) NULL,
	[ActionName] [nvarchar](500) NULL,
	[ObjectName] [nvarchar](50) NULL,
	[Exception] [nvarchar](4000) NULL,
 CONSTRAINT [PK_MWDiagnostics_Action] PRIMARY KEY 
(
	[ActionID] 
) 
)

GO


CREATE TABLE [File]
(
    [FileID]        int              NOT NULL,
    [Name]          nvarchar(255)    NOT NULL,
    [Folder]        smallint         NOT NULL,
    [DateModified]  bigint           NULL,
    [Binary]        image            NULL,
	constraint File_Name_Folder_AK unique(Name,Folder)
   ,CONSTRAINT [PK_File] PRIMARY KEY([FileID])
)
go


GO
CREATE TABLE [Plugin]
(
	[PluginGuid]			[uniqueidentifier]		NOT NULL,
	[Name]					[nvarchar](255)			NOT NULL  constraint Plugin_Name_AK unique,
	[DateInstalled]			[datetime]				NOT NULL,
		PackageIdentifier       nvarchar(128)           null,
		PluginState             int                 NOT NULL constraint Plugin_PluginState_DF DEFAULT (1),
 CONSTRAINT [PK_ServicePlugin] PRIMARY KEY([PluginGuid])
)
GO

CREATE TABLE [PluginVersion]
(
	[PluginVersionGuid]			[uniqueidentifier]	NOT NULL,
	[PluginGuid]				[uniqueidentifier]	NOT NULL,
	[Version]					[nvarchar](50)		NOT NULL,
	[DateInstalled]				[datetime]			NOT NULL,
	-----------------------------
	 MinSCVersion			nvarchar(50)	NULL
    ,MinOMVersion			nvarchar(50)	NULL
    ,[LicensingStatus]		int			NOT NULL constraint PluginVersion_LicensingStatus_DF DEFAULT(1)
	-------------------------------------
	,
 constraint PluginVersion_PluginGuid_Version_AK		unique(PluginGuid,[Version]),
 CONSTRAINT [PK_ServicePluginVersion]				PRIMARY KEY([PluginVersionGuid]),
 CONSTRAINT [FK_PluginVersion_Plugin]				FOREIGN KEY([PluginGuid])	REFERENCES [Plugin] ([PluginGuid])
) 

GO

CREATE TABLE [PluginArtifact]
(
	[PluginArtifactGuid]		[uniqueidentifier]	NOT NULL,
	[PluginVersionGuid]			[uniqueidentifier]	NOT NULL,
	[ArtifactType]				[nvarchar](255)		NOT NULL,
	[FileID]					[int]					NULL,
	[VirtualPath]				[nvarchar](1000)		NULL,
	[LabelResourceName]			[nvarchar](1000)		NULL,
	[HyperLinkRelativePath]		[nvarchar](1000)		NULL,
	[SCFileID]					[int]					NULL,
 CONSTRAINT [PK_PluginArtifact]						PRIMARY KEY([PluginArtifactGuid]), 
 CONSTRAINT [FK_PluginArtifact_File]				FOREIGN KEY([FileID])REFERENCES [File] ([FileID]),
 CONSTRAINT [FK_PluginArtifact_PluginVersion]		FOREIGN KEY([PluginVersionGuid]) REFERENCES [PluginVersion] ([PluginVersionGuid])
) 
go

CREATE TABLE [SaDeviceStatus](
	[DeviceGuid] [uniqueidentifier] NOT NULL,
	[Ipaddress] [nvarchar](64) NOT NULL,
	[CheckWithSCPending] [bit] NULL,
	[ConfiguredSaVersion] [nvarchar](16) NULL,
	[ConfiguredProfileId] [int] NULL,
	[ConfiguredTemplateGuid] [uniqueidentifier] NULL,
	[OverriddenTemplate] [bit] NULL,
	[UrlPending] [bit] NULL,
	[SaConfirmed] [bit] NULL,
 CONSTRAINT [PK_SaDeviceStatus] PRIMARY KEY  ([DeviceGuid])
 )
GO


/*
	92-RemoteControl
*/

CREATE TABLE [RemoteControl_PremiumDevice]
(
    [DeviceGuid]         uniqueidentifier    NOT NULL,
    [OMRowVersion]       rowversion          NOT NULL,
    [DateCreated]        datetime            NOT NULL,
    [DateModified]       datetime            NOT NULL,
    [SyncGuid]           uniqueidentifier    NOT NULL,
    [PremiumComputerID]  nvarchar(256)           NULL,
    [PasswordSet]		 nvarchar(256)           NULL,
    [PasswordRetrieved]  nvarchar(256)           NULL
    ,InstallStatus        int                not NULL 
	 constraint RemoteControl_PremiumDevice_InstallStatus_DF default (0)   /*0 is Unknown */
    ,CONSTRAINT [RemoteControl_PremiumDevice_SyncGuid_UC]  UNIQUE ([SyncGuid])
    ,CONSTRAINT [RemoteControl_PremiumDevice_DeviceGuid_PK]  PRIMARY KEY 
	                                                                      nonclustered 
	                                                        ([DeviceGuid])
)
go

CREATE TABLE [Security_Firewall](
	[FirewallAssetGuid]			[uniqueidentifier]	NOT NULL,
	[DeviceGuid]				[uniqueidentifier]	NOT NULL,
	[Name]						[nvarchar](200)		NOT NULL,
	[Version]					[nvarchar](100)			NULL,
	[Vendor]					[nvarchar](200)			NULL,
	[InstallDate]				[datetime]				NULL,
	[FirewallPolicies]			nvarchar(4000)			null,
	[FirewallRules]				nvarchar(4000)			null,
	[DateCreated]				[datetime]			NOT NULL,
	[DateModified]				[datetime]			NOT NULL,
	[OMRowVersion]				[timestamp]		NOT NULL	
) ;
GO

alter table [Security_Firewall]
	add CONSTRAINT [Security_Firewall_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [Device] ([DeviceGuid])
go

CREATE nonCLUSTERED INDEX [Security_Firewall_DeviceGuid_IX]
    ON [Security_Firewall]([DeviceGuid] ASC);
go

create unique nonclustered index Security_Firewal_FirewallAssetGuid_UX 
		on Security_Firewall(FirewallAssetGuid)  	
go



CREATE TABLE [Security_AvInfo]
(
		AvInfoGuid          uniqueidentifier  NOT NULL,
		DeviceGuid          uniqueidentifier  NOT NULL,
		DisplayName         nvarchar(200)     NOT NULL,
		ProductState        nvarchar(200)     NOT NULL,
		Enabled             bit               NOT NULL,
		UptoDate            bit               NOT NULL,
		DateCreated         datetime          NOT NULL,
		DateModified        datetime          NOT NULL,
		OMRowVersion        timestamp         NOT NULL
);


GO
alter table [Security_AvInfo]
	add CONSTRAINT [Security_AvInfo_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [Device] ([DeviceGuid])
go

CREATE nonCLUSTERED INDEX [Security_AvInfo_DeviceGuid_IX]
    ON [Security_AvInfo]([DeviceGuid] ASC);
go

create unique nonclustered index Security_AvInfo_AvInfo_i_DeviceGuid_OMRowVersion_UX 
		on Security_AvInfo(AvInfoGuid)  
go


/* Add concreate table for local User Info Asset */
CREATE TABLE [Security_LocalUserInfo](
	[UserInfoGuid]			[uniqueidentifier]	NOT NULL,
	[DeviceGuid]			[uniqueidentifier]	NOT NULL,
	[LogOnName]				[nvarchar](200)		NOT NULL,	
	[SID]                   [nvarchar](50)      NOT NULL,
	[AccountEnabled]          [bit] NOT NULL,
	[AccountCreatedTime]      [datetime] NULL,
	[AccountExpirationDate]   [datetime] NULL,
	[CannotChangePassword]   [bit] NOT NULL,
	[IsPasswordNotRequired]  [bit] NOT NULL,
	[IsPasswordNeverExpired] [bit] NOT NULL,	
	[LastPwdChangeTime]     [datetime] NULL,
	[DateCreated]			[datetime]			NOT NULL,
	[DateModified]			[datetime]			NOT NULL,
	[OMRowVersion]			[timestamp]		NOT NULL	
) ;
GO

alter table [Security_LocalUserInfo]
	add CONSTRAINT [Security_LocalUserInfo_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [Device] ([DeviceGuid])
go

CREATE nonCLUSTERED INDEX [Security_LocalUserInfo_DeviceGuid_IX]
    ON [Security_LocalUserInfo]([DeviceGuid] ASC);
go

create unique nonclustered index Security_LocalUserInfo_UserInfoGuid_UX 
		on Security_LocalUserInfo(UserInfoGuid)  	
go


/* Add concreate table for domain User Info Asset */
CREATE TABLE [Security_DomainUserInfo](
	[UserInfoGuid]			 [uniqueidentifier]	NOT NULL,
	[CustomerGuid]			 [uniqueidentifier]	NOT NULL,
	[LogOnName]				 [nvarchar](200)	NOT NULL,	
	[DistinguishedName]      [nvarchar](1000)   NULL,
	[SID]                    [nvarchar](50)     NOT NULL,
	[AccountEnabled]         [bit]				NOT NULL,
	[AccountCreatedTime]     [datetime]			NULL,
	[AccountExpirationDate]  [datetime]			NULL,
	[CannotChangePassword]   [bit]				NOT NULL,
	[IsPasswordNotRequired]  [bit]				NOT NULL,
	[IsPasswordNeverExpired] [bit]				NOT NULL,
	[AllowReversiblePasswordEncryption] [bit]	NOT NULL,
	[LastPwdChangeTime]      [datetime]			NULL,
	[DateCreated]			 [datetime]			NOT NULL,
	[DateModified]			 [datetime]			NOT NULL,
	[OMRowVersion]			 [timestamp]		NOT NULL,
	[Name]					 [nvarchar](200)	NULL,
	[LastLogonTimestamp]     [datetime]			NULL,
	[Mail]					 [nvarchar](200)	NULL
) ;
GO


create unique nonclustered index Security_DomainUserInfo_UserInfoGuid_UX 
		on Security_DomainUserInfo(UserInfoGuid)  	
go

/* Add concreate table for security domain group policy Info Asset */
CREATE TABLE [Security_DomainGroupPolicyInfo](
	[PolicyInfoGuid]		[uniqueidentifier]	NOT NULL,
	[CustomerGuid]			[uniqueidentifier]	NOT NULL,
	[DisplayName]			[nvarchar](200)		NOT NULL,
	[DomainName]			[nvarchar](200)		NOT NULL,
	[Path]					[nvarchar](1000)    NOT NULL,
	[ID]					[uniqueidentifier]	NOT NULL,
	[PolicyLastModifiedTime] [datetime]		NULL,
	[PasswordHistorySize]	[int] NULL,
	[MaxPasswordAge]        [int] NULL,
	[PasswordComplexity]	[bit] NULL,
	[StorePasswordWithReversibleEncryption] [bit] NULL,
	[LockoutBadCount]	    [int] NULL,
	[DateCreated]			[datetime]	NOT NULL,
	[DateModified]			[datetime]	NOT NULL,
	[OMRowVersion]			[timestamp]	NOT NULL	
) ;
GO

CREATE nonCLUSTERED INDEX [Security_DomainGroupPolicyInfo_CustomerGuid_IX]
    ON [Security_DomainGroupPolicyInfo]([CustomerGuid] ASC);
go

create unique nonclustered index Security_DomainGroupPolicyInfo_PolicyInfoGuid_UX 
		on Security_DomainGroupPolicyInfo(PolicyInfoGuid)  	
go

/*  Add concreate table for device security policy info asset */
CREATE TABLE [Security_LocalSecurityPolicyInfo](
	[PolicyInfoGuid]		[uniqueidentifier]	NOT NULL,
	[DeviceGuid]			[uniqueidentifier]	NOT NULL,
	[PasswordHistorySize]	[int] NULL,
	[MaxPasswordAge]        [int] NULL,
	[PasswordComplexity]	[bit] NULL,
	[StorePasswordWithReversibleEncryption] [bit] NULL,
	[LockoutBadCount]	    [int] NULL,
	[DateCreated]			[datetime]	NOT NULL,
	[DateModified]			[datetime]	NOT NULL,
	[OMRowVersion]			[timestamp]	NOT NULL	
) ;
GO
alter table [Security_LocalSecurityPolicyInfo]
	add CONSTRAINT [Security_LocalSecurityPolicyInfo_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [Device] ([DeviceGuid])
go

CREATE nonCLUSTERED INDEX [Security_LocalSecurityPolicyInfo_DeviceGuid_IX]
    ON [Security_LocalSecurityPolicyInfo]([DeviceGuid] ASC);
go

create unique nonclustered index Security_LocalSecurityPolicyInfo_PolicyInfoGuid_UX 
		on [Security_LocalSecurityPolicyInfo](PolicyInfoGuid)  
go


/* Newly added tables for Security Patch features */

/* Table for device missing patch  */
CREATE TABLE [Security_MissingPatchInfo]
(
	[PatchInfoGuid]			       uniqueidentifier	NOT NULL,
	[DeviceGuid]			       uniqueidentifier	NOT NULL,
	[UpdateID]			           uniqueidentifier	NOT NULL,   	
	[DateCreated]			       datetime			NOT NULL,
	[DateModified]			       datetime			NOT NULL,
	[OMRowVersion]			       timestamp		NOT NULL	
) ;
GO

alter table [Security_MissingPatchInfo]
	add CONSTRAINT [Security_MissingPatchInfo_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [Device] ([DeviceGuid])
go

CREATE nonCLUSTERED INDEX [Security_MissingPatchInfo_DeviceGuid_IX]
    ON [Security_MissingPatchInfo]([DeviceGuid] ASC);
go

create unique nonclustered index Security_MissingPatchInfo_PatchInfoGuid_UX 
		on [Security_MissingPatchInfo](PatchInfoGuid) ;
go

/*Table for device Windows Update Configuration  */

CREATE TABLE [Security_WUAConfig]
(
	[WUAConfigGuid]			       uniqueidentifier	NOT NULL,
	[DeviceGuid]			       uniqueidentifier	NOT NULL,
    [IsAutoUpdateEnabled]          bit              NOT NULL,
	[DateCreated]			       datetime			NOT NULL,
	[DateModified]			       datetime			NOT NULL,
    [OMRowVersion]			       timestamp		NOT NULL	
) ;
GO

alter table [Security_WUAConfig]
	add CONSTRAINT [Security_WUAConfig_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [Device] ([DeviceGuid])
go

CREATE nonCLUSTERED INDEX [Security_WUAConfig_DeviceGuid_IX]
    ON [Security_WUAConfig]([DeviceGuid] ASC);
go

create unique nonclustered index [Security_WUAConfig_WUAConfigGuid_UX]
		ON [Security_WUAConfig] (WUAConfigGuid) ;
go


/*  Table for Device data collection status  */
CREATE TABLE [Security_DeviceDataCollectionStatus]
(
	[DeviceDataCollectionStatusGuid]			       uniqueidentifier	NOT NULL,
	[DeviceGuid]			       uniqueidentifier	NOT NULL,
    [ManagedElementTypeGuid]       uniqueidentifier NOT NULL,
	[Status]                       int              NOT NULL,
	[Reason]                       int              NULL,
	[StatusMessage]                nvarchar(500)    NULL,
	[DateCreated]			       datetime			NOT NULL,
	[DateModified]			       datetime			NOT NULL,
	[OMRowVersion]			       timestamp		NOT NULL	
) ;

go

alter table [Security_DeviceDataCollectionStatus]
   add constraint [Security_DeviceDataCollectionStatus_FK_DeviceGuid] foreign key ([DeviceGuid]) references [Device] ([DeviceGuid])
go

alter table [Security_DeviceDataCollectionStatus]
   add constraint [Security_DeviceDataCollectionStatus_FK_METypeGuid] foreign key ([ManagedElementTypeGuid]) references [ManagedElementType] ([ManagedElementTypeGuid])
go

create nonClustered index [Security_DeviceDataCollectionStatus_CLUST_IX]
    on [Security_DeviceDataCollectionStatus]( DeviceGuid, ManagedElementTypeGuid  ASC)

go

create unique nonclustered index [Security_DeviceDataCollectionStatus_StatusGuid_i_DeviceGuid_OMRowVersion_UX] 
		on  [Security_DeviceDataCollectionStatus](DeviceDataCollectionStatusGuid)
go

/* Table for Site Data Collection Status */

CREATE TABLE [Security_SiteDataCollectionStatus]
(
	[SiteDataCollectionStatusGuid]			       uniqueidentifier	NOT NULL,
	[CustomerGuid]			       uniqueidentifier	NOT NULL,
    [ManagedElementTypeGuid]       uniqueidentifier NOT NULL,
	[Status]                       int              NOT NULL,
	[Reason]                       int              NULL,
	[StatusMessage]                nvarchar(500)    NULL,
	[DateCreated]			       datetime			NOT NULL,
	[DateModified]			       datetime			NOT NULL,
	[OMRowVersion]			       timestamp		NOT NULL	
) ;

go

alter table [Security_SiteDataCollectionStatus]
   add constraint [Security_SiteDataCollectionStatus_FK_METypeGuid] foreign key ([ManagedElementTypeGuid]) references [ManagedElementType] ([ManagedElementTypeGuid])
go

create nonClustered index [Security_SiteDataCollectionStatus_CLUST_IX]
    on [Security_SiteDataCollectionStatus]( CustomerGuid, ManagedElementTypeGuid  ASC)

go
create unique nonclustered index [Security_SiteDataCollectionStatus_StatusGuid_i_DeviceGuid_OMRowVersion_UX] 
		on  [Security_SiteDataCollectionStatus](SiteDataCollectionStatusGuid)
go

CREATE TABLE [Security_WifiInfo] (
	[WifiInfoGuid] [uniqueidentifier] not null,
	[DeviceGuid] [uniqueidentifier] not null,
	[SSID] nvarchar(32) not null,
	[SSIDNonBroadcast] [bit] not null,
	[DateCreated] [datetime] not null,
	[DateModified] [datetime] not null,
	[OMRowVersion] [timestamp] not null,	
	[Authentication] nvarchar(10) not null,
	[Encryption] nvarchar(10) null,
	[PasswordLength] tinyint not null,
	[PasswordContainSpecialChar] [bit] not null,
	[PasswordContainNumber] [bit] not null,
	[PasswordContainMixedUpperLowerCaseLetters] [bit] not null,
	[IsConnected] [bit] not null, 
	[LastConnectedTime] [DateTime]  null 
)
go
ALTER TABLE [Security_WifiInfo] ADD CONSTRAINT [Security_WifiInfo_Device_FK] FOREIGN KEY([DeviceGuid])
REFERENCES [Device] ([DeviceGuid])
GO


CREATE TABLE [Security_UACInfo] (
	[UACInfoGuid] [uniqueidentifier] not null,
	[DeviceGuid] [uniqueidentifier] not null,
	[UACEnabled] [tinyint] not null,	
	[DateCreated] [datetime] not null,
	[DateModified] [datetime] not null,
	[OMRowVersion] [timestamp] not null
)
go
ALTER TABLE [Security_UACInfo] ADD CONSTRAINT [Security_UACInfo_Device_FK] FOREIGN KEY([DeviceGuid])
REFERENCES [Device] ([DeviceGuid])

GO
CREATE TABLE [Security_OpenPortInfo] (
	[OpenPortInfoGuid] [uniqueidentifier] not null,
	[DeviceGuid] [uniqueidentifier] not null,
	[Port] [int] not null,	
    [Protocol] [nvarchar](300) not null,	
    [Description] [nvarchar](300) not null,	
	[DateCreated] [datetime] not null,
	[DateModified] [datetime] not null,
	[OMRowVersion] [timestamp] not null
)
GO
ALTER TABLE [Security_OpenPortInfo] ADD CONSTRAINT [Security_OpenPortInfo_Device_FK] FOREIGN KEY([DeviceGuid])
REFERENCES [Device] ([DeviceGuid])

GO
CREATE TABLE [Security_ThirdPartyFirewallInfo] (
	[FirewallInfoGuid] [uniqueidentifier] not null,
	[DeviceGuid] [uniqueidentifier] not null,
	[Name] [nvarchar](200) not null,
	[Enabled] [bit] not null,
	[InstallDate] [datetime] null,
	[DateCreated] [datetime] NOT NULL,
	[DateModified] [datetime] NOT NULL,		
	[OMRowVersion] [timestamp] NOT NULL
  ) 
 
GO
ALTER TABLE [Security_ThirdPartyFirewallInfo] ADD CONSTRAINT [Security_ThirdPartyFirewallInfo_Device_FK] FOREIGN KEY([DeviceGuid])
REFERENCES [Device] ([DeviceGuid])
GO

CREATE TABLE [ASM_IVNT_ApprovalGroup] (
	[ApprovalGroupGuid] [uniqueidentifier] NOT NULL,
	[ApprovalGroupName] [nvarchar](255) NULL,
	CONSTRAINT [PK_ASM_IVNT_ApprovalGroup] PRIMARY KEY  ([ApprovalGroupGuid])
  ) 
 
GO

CREATE TABLE [ASM_IVNT_DeviceDesiredState] (
	[DeviceID] [int] NOT NULL,
	[ApprovalGroupGuid] [uniqueidentifier] NOT NULL,
	[PatchToolsMode] [nvarchar](10) NOT NULL,
	[DetectionFrequency] [int] NOT NULL,
	[ExcludedVendorIDs] [nvarchar](50) NOT NULL,
	[ScheduleType] [tinyint] NULL,
	[ScheduleTime] [tinyint] NULL,
	[Monday] [bit] NULL,
	[Tuesday] [bit] NULL,
	[Wednesday] [bit] NULL,
	[Thursday] [bit] NULL,
	[Friday] [bit] NULL,
	[Saturday] [bit] NULL,
	[Sunday] [bit] NULL,
	[DateOfMonth] [tinyint] NULL,
	[WeekOfMonth] [tinyint] NULL,
	[DayOfWeek] [tinyint] NULL,
	CONSTRAINT [PK_ASM_IVNT_DeviceDesiredState] PRIMARY KEY  ([DeviceID])
  ) 
 
GO
ALTER TABLE [ASM_IVNT_DeviceDesiredState] ADD CONSTRAINT [FK_ASM_IVNT_DeviceDesiredState_Device] FOREIGN KEY([DeviceID])
REFERENCES [Device] ([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
GO

CREATE TABLE [ASM_IVNT_Device] (
	[DeviceID] [int] NOT NULL,
	[PatchToolsVersion] [nvarchar](20) NOT NULL,
	[PatchToolsState] [tinyint] NOT NULL,
	[LastReportTime] [datetime] NULL,
	[LastReportedStatusTime] [datetime] NULL,	
      [PatchToolsInstallReturnCode] [int] NULL,
	CONSTRAINT [PK_ASM_IVNT_Device] PRIMARY KEY  ([DeviceID])
  ) 
 
GO
ALTER TABLE [ASM_IVNT_Device] ADD CONSTRAINT [FK_ASM_IVNT_Device_Device] FOREIGN KEY([DeviceID])
REFERENCES [Device] ([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
GO

CREATE TABLE [ASM_IVNT_BulletinApproval] (
	[ApprovalGroupGuid] [uniqueidentifier] NOT NULL,
	[BulletinID] [nvarchar](36) NOT NULL,
    [OSType] [tinyint] NOT NULL,
	[Action] [tinyint] NOT NULL,
	CONSTRAINT [PK_ASM_IVNT_BulletinApproval] PRIMARY KEY  ([ApprovalGroupGuid], [BulletinID], [OSType])
  ) 
 
GO

CREATE TABLE [ASM_IVNT_PatchScanResult] (
	[ScanResultID] bigint identity(1,1) not null,
	[DeviceID] int not null,
	[PatchUID] uniqueidentifier not null,
    [BulletinID] [nvarchar](36) not null,
	[ScanStatus] tinyint not null,
	[ScanStartTime] [datetime] not null,
	[ScanEndTime] [datetime] not null,
	[DateCreated] [datetime] not null,
	[DateUpdated] [datetime] null	
)
go

ALTER TABLE [ASM_IVNT_PatchScanResult]
ADD CONSTRAINT [PK_ASM_IVNT_PatchScanResult] primary key  ([ScanResultID]) 
go

ALTER TABLE [ASM_IVNT_PatchScanResult]
ADD CONSTRAINT [FK_ASM_IVNT_PatchScanResult_ASM_IVNT_Device] foreign key ([DeviceID]) references [ASM_IVNT_Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
go

CREATE nonCLUSTERED INDEX [IX_ASM_IVNT_PatchScanResult]
    ON [ASM_IVNT_PatchScanResult]([DeviceID] ASC);
go

CREATE UNIQUE INDEX [UIX_ASM_IVNT_PatchScanResult] ON [ASM_IVNT_PatchScanResult](DeviceID, PatchUID)
go

CREATE TABLE [ASM_IVNT_PatchDeployResult] (
	[DeployResultID] bigint identity(1,1) not null,
	[DeviceID] int not null, 
	[PatchUID] uniqueidentifier not null,
    [BulletinID] [nvarchar](36) null,
	[DeploymentTime] [datetime] not null,
	[DeploymentStatus] [tinyint] not null,
	[Failure] [bit] not null,
	[ReturnCode] [int] null,
	[DeploymentID] [uniqueidentifier] not null,
	[DateCreated] [datetime] not null,
	[DateUpdated] [datetime] null
) 
go
ALTER TABLE [ASM_IVNT_PatchDeployResult]
ADD CONSTRAINT [PK_ASM_IVNT_PatchDeployResult] primary key ([DeployResultID]) 
go

ALTER TABLE [ASM_IVNT_PatchDeployResult]
ADD CONSTRAINT [FK_ASM_IVNT_PatchDeployResult_ASM_IVNT_Device] foreign key ([DeviceID]) references [ASM_IVNT_Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
go
CREATE nonClustered INDEX [IX_ASM_IVNT_PatchDeployResult_DeviceID] on [ASM_IVNT_PatchDeployResult]([DeviceID] ASC) ;
go

CREATE nonClustered INDEX [IX_ASM_IVNT_PatchDeployResult] on [ASM_IVNT_PatchDeployResult]([DeviceID], [DeploymentID], [PatchUID]) ;
go

CREATE UNIQUE INDEX [UIX_ASM_IVNT_PatchDeployResult]  ON [ASM_IVNT_PatchDeployResult](DeviceID, PatchUID) ;
go


CREATE TABLE [ASM_IVNT_DeviceUrlUpdateHistory] (
     [DeviceID] int not null,
	  [UrlUpdateStatus] bit default 0,
	 [LastUpdateTime] [datetime] default getdate()
)
go 

ALTER TABLE [ASM_IVNT_DeviceUrlUpdateHistory]
ADD CONSTRAINT [PK_ASM_IVNT_DeviceUrlUpdateHisory] primary key ([DeviceID])
go

ALTER TABLE [ASM_IVNT_DeviceUrlUpdateHistory]
ADD CONSTRAINT [FK_ASM_IVNT_DeviceUrlUpdateHisory] foreign key ([DeviceID]) references [ASM_IVNT_Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
go


CREATE TABLE [ASM_IVNT_PatchStatus] (
   [ID] bigint identity(1,1) not null,
   [DeviceID] int not null,
   [PatchUID] uniqueidentifier not null,
   [BulletinID] [nvarchar](36) not null,
   [PatchScanTime] [datetime] null,
   [PatchDeploymentTime] [datetime] null,
   [PatchDeploymentStatus] int null,
   [PatchStatus] int not null,
   [DateUpdated] [datetime] default getdate()
) 
go

ALTER TABLE [ASM_IVNT_PatchStatus]
ADD CONSTRAINT [PK_ASM_IVNT_PatchStatus] primary key ([ID])
go

ALTER TABLE [ASM_IVNT_PatchStatus]
ADD CONSTRAINT [FK_ASM_IVNT_PatchStatus_ASM_IVNT_Device] foreign key ([DeviceID]) references [ASM_IVNT_Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
go

CREATE UNIQUE INDEX [UIX_ASM_IVNT_PatchStatus] on [ASM_IVNT_PatchStatus]([DeviceID],[PatchUID]) ;
go

CREATE nonClustered INDEX [IX_ASM_IVNT_PatchStatus_DeviceID] on [ASM_IVNT_PatchStatus]([DeviceID] ASC) ;

go

CREATE TABLE [ASM_IVNT_PatchNow] (
   [DeviceID] int not null,
   [BulletinID] [nvarchar](36) not null,
   [OSType] [tinyint] not null,
   [Status] smallint not null,
   [ReasonCode] smallint null
) 
go
ALTER TABLE [ASM_IVNT_PatchNow]
ADD CONSTRAINT [PK_ASM_IVNT_PatchNow] primary key ([DeviceID], [BulletinID], [OSType]) 
go

ALTER TABLE [ASM_IVNT_PatchNow]
ADD CONSTRAINT [FK_ASM_IVNT_PatchNow_ASM_IVNT_Device] foreign key ([DeviceID]) references [ASM_IVNT_Device]([DeviceID]) ON DELETE CASCADE ON UPDATE CASCADE
go

CREATE TABLE [DeviceRegistryInfo] (
	[DeviceGuid] [UNIQUEIDENTIFIER]	NOT NULL,
	[InstallationType] NVARCHAR(100) NULL,	
	[ReleaseId] INT NULL,
    [DisplayVersion] NVARCHAR(10) NULL,
	[UpdateBuildRevision] INT NULL,	
	[DateCreated] [DATETIME] NOT NULL,
	[DateModified][DATETIME] NOT NULL,
	[OMRowVersion] TIMESTAMP NOT NULL,
    CONSTRAINT [DeviceRegistryInfo_DeviceGuid_PK] PRIMARY KEY  ([DeviceGuid])
);
GO

ALTER TABLE [DeviceRegistryInfo] ADD CONSTRAINT [DeviceRegistryInfo_DeviceGuid_Device_DeviceGuid_FK] FOREIGN KEY([DeviceGuid])
REFERENCES [Device] ([DeviceGuid])
GO

ALTER TABLE [CustomLog]
ALTER COLUMN DeviceID INT NULL;
GO

CREATE TABLE [S1Deployment] (
	[DeviceGuid] [UNIQUEIDENTIFIER]	NOT NULL,
	[RegistrationToken] NVARCHAR(255) NOT NULL,	
	[DeploymentStatus] INT NOT NULL,	
    [DeploymentStatusReasonCode] INT NOT NULL,
    CONSTRAINT [S1Deployment_DeviceGuid_PK] PRIMARY KEY  ([DeviceGuid])
);
GO

CREATE TABLE [BitLockerPolicyDevice]
(
	[DeviceGuid] NVARCHAR(100) NOT NULL,
    CONSTRAINT [BitLockerPolicyDevice_DeviceGuid_PK] PRIMARY KEY ([DeviceGuid])
);
GO