Reference The intended audience for this topic is a CM4D administrator that will create reports with SQL queries to retrieve the required information.
Topic Contents: Hide
Reference
The
intended audience for this topic is a CM4D administrator that will create
reports with SQL queries to retrieve the required information.
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.
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.
Notice
Any
access to the data should only be made via SQL Queries. Direct access
to the tables is neither recommended nor supported, and could result in
incompatibilities with future versions of CM4D. ATS reserves the right
to alter the database objects (tables, indexes, constraints, stored procedures)
at any time. ATS will, endeavour to maintain consistency, and any changes
will be documented.
If a record is deleted, all subordinate data in other tables will be deleted as well. Orphan data is not permitted.
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.
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 |
[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 |
|
Reference
Currently,
the Scheduler Job
Queue is the only table monitored for a count. Other services will
be added to monitoring in future releases.
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 |
[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).
|
[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 | |
[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) |
|
[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) | |
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 |
| [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 | |
| [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) | |
| [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 | |
| [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 |
|
| [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 | |
| [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.
|
[MDSBRPS_Name] | XML | |
Routine Processed Summary
Name Value - the value of the processed file result.
|
[MDSBRPS_value] | ATS enumeration | |
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 |
| [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) | |
| [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 | |
| [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) | |
| [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) | |
| [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 |
|
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.
