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

DROP TABLE [Security_AntiSpywareInfo]
GO

DROP TABLE [SystemAssets]
GO

DELETE
FROM [Security_DeviceDataCollectionStatus]
WHERE ManagedElementTypeGuid = 'A452910E-9F3A-4C1F-BCD5-DB23919B3C28'
GO

DELETE
FROM [ManagedElementAttributeType]
WHERE ManagedElementTypeGuid = 'A452910E-9F3A-4C1F-BCD5-DB23919B3C28'
GO

DELETE
FROM [ManagedElementType]
WHERE ManagedElementTypeGuid = 'A452910E-9F3A-4C1F-BCD5-DB23919B3C28'
GO

UPDATE [AVCC_service_configurations]
SET value = '4.2.0'
WHERE [key] = 'latest_version'
GO

--This deletion only needs to be executed on upgrades
DELETE
FROM AVCC_device_subservice_versions
WHERE DeviceGuid IN (
		SELECT DeviceGuid
		FROM AV_CloudCareAgent
		WHERE [State] = 101
		)
GO

