Configure vaultDB for Threat Analysis VM Scan

Configure a database on the Microsoft SQL Server instance within your CommCell environment, to manage and store the VM scan results and VM scan history.

Procedure

1. On the CommServe server, log in to the SQL Server and create a DB with name vaultDB.

2. Create the Metadata table. Run the following SQL command:

   USE [VaultDB]
   GO
   /****** Object: Table [dbo].[Metadata] Script Date: 6/28/2024 6:19:47 PM ******/ SET
   ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   CREATE TABLE [dbo].[Metadata]( [attrName] [varchar](50) NOT NULL, [attrVal] [varchar](max)
   NULL
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   

3. Create the ActiveJobs table. Run the following SQL command:

   USE [VaultDB]
   GO
   /****** Object: Table [dbo].[ActiveJobs] Script Date: 6/28/2024 6:20:13 PM ******/ SET
   ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   CREATE TABLE [dbo].[ActiveJobs]( [id] [int] IDENTITY(1,1) NOT NULL,
   [vmName] [varchar](max) NOT NULL, [vmGuid] [varchar](50) NOT NULL, [scanStartTime] [bigint]
   NULL, [clientId] [int] NOT NULL,
   [jobId] [int] NOT NULL, [jobSelectionTime] [bigint] NOT NULL, [copyPrecedence] [int] NOT NULL,
   [attemptNumber] [int] NOT NULL, [recoveryTarget] [int] NULL, [mountedGuid] [varchar](50) NULL,
   [phaseNumber] [int] NOT NULL
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   GO
   

4. Create the VMHistory table. Run the following SQL command:

   USE [VaultDB]
   GO
   /****** Object: Table [dbo].[VMHistory] Script Date: 6/28/2024 6:20:38 PM ******/ SET
   ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   CREATE TABLE [dbo].[VMHistory]( [vmName] [nvarchar](max) NOT NULL, [vmGuid] [nvarchar](50)
   NOT NULL, [clientId] [int] NOT NULL, [scannedJob] [int] NOT NULL, [scanTime] [bigint] NOT NULL,
   [scanResults] [nvarchar](max) NOT NULL
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   GO
   

5. Create the ScanResults table. Run the following SQL command:

   USE [vaultDB]
   GO
   /****** Object: Table [dbo].[ScanResults] Script Date: 8/19/2024 4:25:07 PM ******/
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   SET ANSI_PADDING ON
   GO
   CREATE TABLE [dbo].[ScanResults](
   [vmName] [varchar](64) NULL,
   [vmGuid] [varchar](max) NULL,
   [scannedJobId] [int] NULL,
   [scanJobId] [int] NULL,
   [scanTime] [datetime] NULL,
   [scanResults] [varchar](max) NULL,
   [InfectedList] [nvarchar](max) NULL,
   [failedDisksList] [nvarchar](max) NULL,
   [scanEndTime] [datetime] NULL,
   [WorkerUsed] [varchar](64) NULL,
   [restoreJobIds] [nvarchar](32) NULL
   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
   GO
   SET ANSI_PADDING OFF
   GO
   

6. Create the ActiveResources table. Run the following SQL command:

   USE [VaultDB]
   GO
   /****** Object: Table [dbo].[ActiveResources] Script Date: 6/28/2024 6:21:21 PM ******/ 
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON 
   GO
   CREATE TABLE [dbo].[ActiveResources]( [resourceType] [varchar](50) NOT NULL, [available] [int]
   NOT NULL
   ) ON [PRIMARY]
   GO
   

7. Configure users and roles. Run the following SQL command:

   Use [vaultDB]
   CREATE USER sqladmin_cv
   FOR LOGIN sqladmin_cv
   WITH default_schema=sqladmin_cv
   GO
   CREATE USER sqlexec_cv
   FOR LOGIN sqlexec_cv
   WITH default_schema=dbo
   GO
   CREATE USER sqlmetrics_cv
   FOR LOGIN sqlmetrics_cv
   WITH default_schema=dbo
   GO
   Create Role db_metricsrole Authorization dbo
   GO
   EXEC sp_addrolemember 'db_metricsrole', 'sqlmetrics_cv'
   GO
   Create Role db_cvexecutor Authorization dbo
   GO
   EXEC sp_addrolemember 'db_cvexecutor', 'sqlexec_cv'
   EXEC sp_addrolemember 'db_owner', 'sqladmin_cv'
   EXEC sp_addrolemember 'db_datareader', 'sqlmetrics_cv'
   EXEC sp_addrolemember 'db_datawriter', 'sqlmetrics_cv'
   EXEC sp_addrolemember 'db_ddladmin', 'sqlmetrics_cv'
   EXEC sp_addrolemember 'db_datareader', 'sqlexec_cv'
   EXEC sp_addrolemember 'db_datawriter', 'sqlexec_cv'
   EXEC sp_addrolemember 'db_ddladmin', 'sqlexec_cv'
   GO
   

8. Insert records to the Metadata table. Run the following SQL commands:

   • insert into Metadata Values ('LastRunTime', 0);
   • insert into Metadata Values ('StartTime', 0)