Metrics Database Tables

Topic Contents: Hide

 

The following database tables are used to store information related to Metrics and Monitoring. The burden is on the end-user to take the records contained in these tables to collate, query, and report the information that individual users may find useful.

Database Access

The database is considered to be read-only. CM4D will provide mechanisms for populating and maintaining the content of the tables. At no time should you alter the data in the database, add data to existing database tables, or alter existing stored procedures.

Deletion and Orphans

If a record is deleted, all subordinate data in other tables will be deleted as well. Orphan data is not permitted.

Database Table Structure

The CM4D Metrics and Monitoring data structure is comprised of the following objects within the database. See also the section Database Diagram of M2 Tables at the end of this topic.

Count Table

The Count table records which users are running an active Scheduler service and how many jobs each user has in the Queue at the time of the count. Each user is assigned a unique ID in the table.

Object

Description

Db Object

Type

Count table

[M2Count]

Table

Component Identifier - a unique number that identifies the record.

[M2C_ID]

ATS enumeration

 

Type - what is being monitored -

[M2C_Type]

1 = Scheduler Job Queue

Time - timestamp of when count was recorded.

[M2C_Time]

Date and Time of record (yyyy-mm-dd hh:mm:ss)

Count Description - Username of user running the component; a record is made for each individual user.

[M2C_CountDesc]

User Label

Count - number of items currently being processed by the component; in the CM4D Scheduler Manager Job Queue tab, this is the same as the field "Number of Jobs in Queue".

[M2C_Count]

Integer

Component Tables

The Component tables contain records related to when a services starts, is it still running, and when it is stopped. If the service does not end properly (forcibly closed, license server error, etc.), no record of the stopped component will be recorded in the database.

Object

Description

Db Object

Type

Component Launch table

[M2ComponentLaunch]

Table

Identifier for Component Launcher - a unique string that identifies records related to a component launch.

[MCL_ID]

Database Auto Index Value

 

Time - timestamp of when the component was launched (started).

[MCL_Time]

Date and Time of record (yyyy-mm-dd hh:mm:ss)

Component Identifier - a number that indicates which component was launched (started).

  • 105 = Launched Scheduler Client

  • 106 = Unmanaged Scheduler Client

  • 601 = CM4D Scheduler

  • 1001 = DataSmithBatch

  • 1701 = CM4D Launcher

[MCL_ComponentID]

ATS enumeration:

Hostname - indicates the host computer from which the component was launched.

[MCL_Hostname]

User Label

Process Identifer - a value that identifies the process that is running the component.

[MCL_PID]

System Value (number)

Username - the CM4D user that is running the process (Windows username).

[MCL_Username]

User Label

Component Pulse table

[M2ComponentPulse]

Table; Foreign Key - MCL_ID (M2ComponentLaunch).

Identifier for Component Pulse - a unique string that identifies the running component. This Pulse may also be referred to as the "Heartbeat" of a component.

[MCP_ID]

Database Auto Index Value

 

Identifier for Component Launched - the ID of the component launch that is connected to the pulse.

[MCP_MCL_ID]

Database Auto Index Value

Time - timestamp of when the component last sent a pulse to notify it is still running.

[MCP_Time]

Date and Time of record (yyyy-mm-dd hh:mm:ss)

Component Stop table

[M2ComponentStop]

Table; Foreign Key - MCL (M2ComponentLaunch).

Identifier for Component Pulse - a unique string that identifies which component was stopped.

[MCS_ID]

Database Auto Index Value
 

Identifier for Component Launched - a unique string that identifies which component launch was stopped.

[MCS_MCL_ID]

Database Auto Index Value

Time - timestamp of when the component was stopped.

[MCS_Time]

Date and Time of record (yyyy-mm-dd hh:mm:ss)

DataSmithBatch tables

The DataSmithBatch tables contain records related to DataSmithBatch job processing, including when a job is started, completed, what file was processed, what the result of that file was, and information about the Routine(s) related to that job.

Object

Description

Db Object

Type

DataSmithBatch Job Started table

[M2DsBJobStart] Table; Foreign Key - MCL_ID (M2ComponentLaunch)

Identifier for Job Started - a unique string that identifies which Batch job is started. [MDSBJS_ID] Database Auto Index Value
  Identifier for Component Launched - the ID of the component launch that was connected to the Batch job started. [MDSBJS_MCL_ID] Database Auto Index Value
Time - timestamp of when the Batch job was started. [MDSBJS_Time] Date and Time of record (yyyy-mm-dd hh:mm:ss)
Host Identifier - indicates the host computer that is processing the Batch job; since there can only be one set of jobs per host, even if there are multiple servers with the same job names, the job can be distinguished by the host ID. [MDSBJS_Hostname] User Label
Job Name - label of the DataSmithBatch job used when the component was launched. No path is included, as the full path can be referenced via the DataSmithBatch Manager. [MDSBJS_Jobname] User Label

DataSmithBatch Job Completed table

[M2DsBJobComp] Table; Foreign Key - MDSBJS (M2DsBJobStart)

Identifier for Job Completed - a unique string that identifies which Batch job is completed. [MDSBJC_ID] Database Auto Index Value
  Identifier for Job Started - the ID that identifies which started job was stopped. [MDSBJC_MDSBJS_ID] Database Auto Index Value
Time - timestamp of when the Batch job was completed (finished). [MDSBJC_Time] Date and Time of record (yyyy-mm-dd hh:mm:ss)

DataSmithBatch File Processed table

[M2DsBFileProc] Table; Foreign Key - MDSBJC (M2DsBJobComp)

Identifier for File Processed - a unique string that identifies the file processed for the Batch job. [MDSBFP_ID] Database Auto Index Value
  Identifier for Job Completed - the ID of the DataSmithBatch Job that completed the file processing. [MDSBFP_MDSBJC_ID] Database Auto Index Value
Name of Processed File - the name of the data file processed by the Batch job from the Inbox with label and extension, but no path. [MDSBFP_FileName] XML

DataSmithBatch File Processed Summary table

[M2DsBFileProSum] Table; Foreign Key - MDSBFP (M2DsBFileProc)

Identifier for Summary of File Processed - a unique string that identifies the summary for the file processed. [MDSBFPS_ID] Database Auto Index Value
  Identifier for File Processed - the ID of the file processed summarized. [MDSBFPS_MDSBFP_ID] Database Auto Index Value
File Processed Summary Name - what happened to the file that was processed; Disposition. [MDSBFPS_Name]

ATS enumeration

File Processed Summary Value - where the file ended up after processing; Outbox or Errorbox. [MDSBFPS_value]

ATS enumeration

DataSmithBatch Routine Processed table

[M2DsBRoutProc] Table; Foreign Key - MDSBJS (M2DsBJobStart)

Identifier for Routine Processed - a unique string that identifies the Routine that triggered  the Batch job. [MDSBRP_ID] Database Auto Index Value
  Identifier for Job Started - identifies which started job processed the Routine. [MDSBRP_MDSBJS_ID] Database Auto Index Value
Identifier for DataSource - identifies which DataSource the data was processed into. [MDSBRP_DataSource_ID] Database Auto Index Value
Identifier for Routine - identifies the Routine the data was processed for. [MDSBRP_Routine_ID] Database Auto Index Value

DataSmithBatch Routine Processed Summary table

[M2DsBRoutProSum] Table; Foreign Key - MDSBRP (M2DsBRoutProc)

Identifier of the Routine Processed Summary - a unique string that identifies the summary for the Routine used by the processed file. [MDSBRPS_ID] Database Auto Index Value
  Identifier of the Routine Processed - the ID of the Routine processed related to the summary. [MDSBRPS_MDSBRP_ID] Database Auto Index Value
Routine Processed Summary Name - the result of the processed file, a summary of the data processed.
  • New Routine
  • Feature Count
  • New Feature Count
  • Sample Count
  • New Sample Count
  • Actuals Count
  • New Actuals Count
[MDSBRPS_Name] XML
Routine Processed Summary Name Value - the value of the processed file result.
  • Routine Name
  • Value of Count
[MDSBRPS_value] ATS enumeration

Scheduler Tables

The Scheduler tables contain records related to CM4D Scheduler job processing, including when a job is received, queued, started, completed, and the result of the report when finished.

Object

Description

Db Object

Type

Scheduler Job Started table

[M2SchJobStart] Table; Foreign Key - M2ComponentLaunch; M2SchJobQueue

Identifier for Job Started - a unique string that identifies the Scheduler job started. [MSJS_ID] Database Auto Index Value
  Identifier for Component Launched - the ID of the component launched that started the Scheduler Job. [MSJS_MCL_ID] Database Auto Index Value
Identifier for Job Queued - the ID of the Job Queued as a result of the Scheduler job started. [MSJS_MSJQ_ID] Database Auto Index Value
Time - timestamp of when the Scheduler job was started. [MSJS_Time] Date and Time of record (yyyy-mm-dd hh:mm:ss)

Scheduler Job Queue table

[M2SchJobQueue] Table; no foreign key;

Identifier of Job Queue for DataSmithBatch - a unique string that identifies the DataSmith Batch job that triggered the Scheduler job in the Queue. [MDSBJQ_ID] Database Auto Index Value
  Identifier for DataSmithBatch Job Routine Processed - the ID of the Routine used by the processed data file in DataSmith Batch to trigger the Scheduler job; <null> is a valid entry, as Timed Jobs are not triggered by a Routine. [MDSBJQ_MDSBRP_ID] Database Auto Index Value
Time - timestamp of when the Scheduler job was entered in the Job Queue. [MDSBJQ_Time] Date and Time of record (yyyy-mm-dd hh:mm:ss)
Scheduler Job Identifier - a unique string that identifies the Scheduler job used to process the job in the Queue. [MDSBJQ_SS_SCHEDULEID] Database Auto Index Value
Managed Document Identifier - a unique string that identifies the Managed Document used to process the Scheduler job. [MDSBJQ_MD_ID] Database Auto Index Value
Sample Label - the label of the sample that triggered the queued job. [MDSBJQ_S_LABEL] ATS enumeration

Scheduler Job Received table

[M2SchJobReceived] Table; Foreign Keys - MCL (M2ComponentLaunch); MSJS (M2SchJobStart)

Identifier for Job Received - a unique string that identifies the job received in the Scheduler queue. [MSJR_ID] Client ID
  Identifier for Component Launched - the ID of the component launched that triggered the received job. [MSJR_MCL_ID] Database Auto Index Value
Identifier for Job Started - the ID of the job started that triggered the received job. [MSJR_MSJS_ID] Database Auto Index Value
Time - timestamp of when the Scheduler job was received. [MSJR_Time] Date and Time of record (yyyy-mm-dd hh:mm:ss)

Scheduler Job Completed table

[M2SchJobComp] Table; Foreign Key - MSJR (M2SchJobReceived)

Identifier for Job Completed - a unique string that identifies the Scheduler job that was completed. [MSJC_ID] Database Auto Index Value
  Identifier for Job Received - the ID of the job received that was completed. [MSJC_MSJR_ID] Database Auto Index Value
Time - timestamp of when the Scheduler job was completed (finished). [MSJC_Time] Date and Time of record (yyyy-mm-dd hh:mm:ss)

Scheduler Job Completed Summary table

[M2SchJobCompSum] Table; Foreign Key - MSJC (M2SchJobComp)

Job Completed Summary Identifier - a unique string that identifies the completed job summary. [MSJCS_ID] Database Auto Index Value
  Job Completed Identifier - the ID of the completed Scheduler job that is summarized. [MSJCS_MSJC_ID] Database Auto Index Value
Job Completed Summary Name - the result of the completed Scheduler job. [MSJCS_Name] ATS enumeration:
FileNameOfReport - full path and filename of report
TotalSheets - total number of sheets in report
JobDisposition - end result of job; either JobFinished or JobFailed
Job Completed Summary Name Value - the vaule of the result of the completed Scheduler job. [MSJCS_value] ATS enumeration:
Path, Value, or Disposition

Database Diagram of M2 Tables

Below is a Database Diagram built by SQL Server Management Studio that includes all of the M2 (Metrics and Monitoring) tables in the Site database. This diagram shows how the tables are related to one another, and where Foreign Keys are present.