The Data Masker software requires two Masking Statistics Tables to be present in the target database or a different user defined database. The two tables have a configurable common prefix. One table is for table statistics with suffix _TSTATS and it records the affected tables with the corresponding rules. The other table is for rule statistics with suffix _RSTATS and it records the rules as they execute. The statistics table makes it possible to determine which masking rules have been executed in the database and also makes it possible to restart the rules if an error occurs.

The statistics table can be emptied at the start of each masking set run by choosing the 'Truncate the Statistics Table before each run' option on the Misc. Setup Tab.

The statistics tables are usually created before a masking set is run when the 'Autocreate the Statistics Table if they do not exist' option on the Misc. Setup Tab. The statistics tables can also be created or dropped using the tools on the options tab of the Rule Controller form. If statistics tables are not present, error like below will occur at runtime.

The structure of the Statistics Table

The two Masking Statistics Table has the structure shown below.

Table Statistics table DDL

--
-- copyright (c) 2019 - Redgate Ltd. All rights reserved
--                  www.red-gate.com
--
-- The Data Masker table statistics table records the number
--   of row operations on each table and column for each table.
--
-- Its space requirements are usually nominal, typically
--  around a few thousand rows at most.

DROP table [DMTest].[dbo].[DMSSTAT_TSTATS];
CREATE table [DMTest].[dbo].[DMSSTAT_TSTATS]
(
   runid varchar(250) NOT Null,
   ruleid varchar(50) NOT Null,
   statscreated datetime NOT NULL,
   statsupdated datetime NOT NULL,
   ruletype varchar(50) NOT Null,
   ruleblock int NOT Null,
   rulenum int NOT Null,
   rulesubscript int NOT Null,
   controllerid varchar(50),
   tabledatabase varchar(250) not null,
   tableschema varchar(250) not null,
   tablename varchar(250) not null,
   tablecolumn varchar(250),
   rowoperations int,
   coloperations int
);
CREATE INDEX IX_DMSSTAT_TSTATS_A ON [DMTest].[dbo].[DMSSTAT_TSTATS] ( runid ASC, ruleid ASC);

runid

Multiple runs of same masking set (or different masking sets) can be recorded in the statistics table. This field indicates the set name and the start time of the masking set and allows the records for individual runs to be selected.

ruleid

The ruleid of the rule recorded in that statistics table row.

statscreated

The timestamp when the stats recorded in that statistics table row was created.

statsupdated

The timestamp when the stats recorded in that statistics table row was last updated.

ruletype

A summary of the rule type of the rule recorded in that audit table row.

ruleblock

The ruleblock of the rule recorded in that statistics table row.

rulenum

The rulenum of the rule recorded in that statistics table row.

rulesubscript

The rulesubscript of the rule recorded in that statistics table row.

controllerid

The controller rule id that the rule recorded in that statistics table row belongs to.

tabledatabase

The database part of the table name affected by the rule recorded in that statistics table row.

tableschema

The schema part of the table name affected by the rule recorded in that statistics table row.

tablename

The table name affected by the rule recorded in that statistics table row.

tablecolumn

The column name affected by the rule recorded in that statistics table row.

rowoperations

The number of rows affected by the rule recorded in that statistics table row.

coloperations

The number of rows in all columns affected by the rule recorded in that statistics table row.

Rule Statistics table DDL

--
-- copyright (c) 2019 - Redgate Ltd. All rights reserved
--                  www.red-gate.com
--
-- The Data Masker rule statistics table records the start and
--   successful completion of the masking rules.
--
-- Its space requirements are usually nominal, typically
--  around a few thousand rows at most.

drop table [DMTest].[dbo].[DMSSTAT_RSTATS];
CREATE table [DMTest].[dbo].[DMSSTAT_RSTATS]
(
   runid varchar(250) NOT Null,
   ruleid varchar(50) NOT Null,
   ruleblock int NOT Null,
   rulenum int NOT Null,
   rulesubscript int NOT Null,
   ruletype varchar(50) NOT Null,
   rulecreated DateTime  NOT Null,
   ruleupdated DateTime NOT Null,
   secondsactive int NOT Null,
   rulestatus char NOT Null,
   rulesource varchar(250),
   ruletarget varchar(250),
   rowoperations int,
   coloperations int,
   rulePrevRPN int,
   ruleRPN int
);
CREATE INDEX IX_DMSSTAT_RSTATS ON [DMTest].[dbo].[DMSSTAT_RSTATS] ( runid ASC, ruleid ASC);

runid

Multiple runs of same masking set (or different masking sets) can be recorded in the statistics table. This field indicates the set name and the start time of the masking set and allows the records for individual runs to be selected.

ruleid

The ruleid of the rule recorded in that statistics table row.

ruleblock

The ruleblock of the rule recorded in that statistics table row.

rulenum

The rulenum of the rule recorded in that statistics table row.

rulesubscript

The rulesubscript of the rule recorded in that statistics table row.

ruletype

A summary of the rule type of the rule recorded in that audit table row.

rulecreated

The timestamp when the rule recorded in that statistics table row was created.

ruleupdated

The timestamp when the rule recorded in that statistics table row was last updated.

secondsactive

The execution time in seconds of the rule recorded in that statistics table row.

rulestatus

The status of the rule. Will always be a C if the rule successfully completed.

rulesource

The source table or object of the rule recorded in that statistics table row.

ruletarget

The target table or object of the rule recorded in that statistics table row.

rowoperations

The number of rows affected by the rule recorded in that statistics table row.

coloperations

The number of rows in all columns affected by the rule recorded in that statistics table row.

rulePrevRPN

The previous rule processing number of the rule recorded in that statistics table row.

ruleRPN

The rule processing number of the rule recorded in that statistics table row.