Reporting
- Mikko Juola (Deactivated)
- Mikko Juola
DCM Audit creates a lot of useful information that can be reported in various ways depending on the targets for the data management activities, responsible persons and point-of-view.
This page briefly describes the DCM table structure and how to use Audit Messages, Unique Audit Messages, Audit Results and DCM Tasks data when creating your own reports.
DCM Audit tables
Each "Audit run" involves multiple tables in order to record the audit results on different levels of detail. The following picture shows where most detailed Audit Messages are recorded and how they connect to other "Audit tables". Unique Audit Messages are exactly what they are called, unique versions of the audit messages. Most reporting on the audit message level should be done based on Unique Audit Messages.
In the data model diagram above, the "Configuration Item" class is used as an example. The Audited record, Real element and Real record references are using the Document ID field type, which can refer to any table within ServiceNow. Configuration Items are often used with DCM, but for example foundation data, services, contracts, SLAs and ticket data are also linked to Audit results and messages.
Each of the Audit Messages includes lots of useful information for reporting and delivering the audit results to responsible persons. Audit Message itself mostly records the result type and related descriptions, but it also includes two very useful references:
- Audit Result - Reference to parent record connected to Root record against which the audit has been run
- Audit Result includes all messages related to a single Root record
- Audit Result has reference to audited Root record which again includes more useful information for reporting
- Real Element - Document ID type of reference to actual record that's related to audit message
- Real element is called "Real record" on Unique Audit Messages
- These are the records connected to the Root record via different links in the blueprint
- For example if an Application blueprint includes CI relationship to child Server and that Server has mandatory Managed by reference to User, the "Real Element" includes sys_id of the Server from which the mandatory reference was missing. While the reference to audited root record (application in this case) can be found via Audit Result reference.
Example of Unique Audit Message below.
Moving forward to the Audit Result record gives us more details about the Audited Record. The Audited Record field on the Audit Result table is Document ID type of reference to root record being audited. This information can be used to fetch responsible persons to fix the audit deviations for example.
When reporting from Audit messages table, it's important to understand that repeating audits will delete audit messages under previous audit instances. For example, when a scheduled audit is run, the audit messages are only available for the latest audit instance. Audit messages are mainly used to identify Unique Audit Messages and to collect messages counts on Audit result and Audit instance levels.
Useful field
DCM data structure related to audits includes lots of useful details for reporting. Some details may be hard to find, so here's a quick list of useful details available on different tables.
Blueprint versions
All paths start from the Blueprint Version (x_jugr_dcm_bp_blueprint_version) table.
| Field | Field type | Path from Blueprint version (bpv) | Meaning |
|---|---|---|---|
| Implementation phase | Choice | bpv.implementation_phase | Models created for particular implementation phases. Values refer to Crawl, Walk, Run and Fly analogy for the maturity of the organization to implement different data models. |
| Data domain | Reference → Reference | bpv.blueprint.data_domain | Into which data domain the blueprint belongs to. Data domains define the scope and responsible persons for managing data. |
| Data tier | Reference → Reference → Reference | bpv.blueprint.data_domain.data_tier | Data tiers are the highest level of abstraction when managing data within ServiceNow. |
| Category | Reference → Reference → Reference | bpv.blueprint.bp_category | Blueprint categories are a parallel grouping structure for data models. |
Unique Audit Messages
All paths start from the Unique Audit Messages (x_jugr_dcm_unique_audit_message) table.
| Field | Field type | Path from Unique Audit Message (uam) | Meaning |
|---|---|---|---|
| Audit Result Type | Reference → String | uam.audit_result_type.short_description | More detailed audit deviation type like what was missing, or what was according to the blueprint. For example "Mandatory relationship missing" instead of just "Critical" deviation. |
| Deviation Type | Choice | uam.audit_result_type.deviation_type | Deviation type for a single audit message. |
| Real Record | Document ID | uam.real_record | Record connected to root record which is related to audit message. For example, if root record is "Application", but one of the servers is missing some details, then Real Record can refer to the actual Server record. |
| Blueprint element | Reference | uam.blueprint_element | Blueprint element to which the audit message is related to. For example, if an Application is missing a Managed by reference to User, then the "Managed by" link between Application and User is the blueprint element. This way it's easy to pinpoint which parts of the blueprint require the most attention. |
| Created | Date/Time | uam.created | Date when Audit message (or Audit Result, or Audit Instance) was created. Can be very useful when defining conditions for reports, since usefully you only want the latest audit results (unless creating a trend). |
| Updated | Date/Time | uam.updated | Date when Unique Audit Message (or Audit Result, or Audit Instance) was updated. Can be very useful when defining conditions for reports, since usefully you only want the latest audit results (unless creating a trend). Unique audit messages are updated for example when the weight is changed due to most recent audit. |
Audit Messages
All paths start from the Audit Messages (x_jugr_dcm_audit_messages) table, so if you are creating reports from Audit Results for example, just skip the audit_messages part of the path.
| Field | Field type | Path from Audit message (am) | Meaning |
|---|---|---|---|
| Audit Result Type | Reference → String | am.audit_result_type.short_description | More detailed audit deviation type like what was missing, or what was according to the blueprint. For example "Mandatory relationship missing" instead of just "Critical" deviation. |
| Deviation Type | Choice | am.audit_result_type.deviation_type | Deviation type for a single audit message. |
| Real Element | Document ID | am.real_element | Record connected to root record which is related to audit message. For example, if root record is "Application", but one of the servers is missing some details, then Real Element can refer to the actual Server record. |
| Audited Record | Reference → Document ID | am.audit_result.audited_record | Root record for the particular audit instance. Audit messages are grouped per root record and that information is available on the Audit Results table. Note! In order to create reports from fields behind the Document ID, you need to create a Database View to connect Audit Results with that particular table. Below we have instructions on how to connect Configuration Item table to Audit Results. |
| Deviation Level | Reference → Choice | am.audit_result.deviation_level | Deviation level for the root record. This is defined based on the "highest" deviation level on Audit Messages related to root record. If root record has even one critical deviation, then the audit level for the root record is critical. |
| Blueprint version | Reference → Reference → Reference | am.audit_result.audit_instance.blueprint_version | Which blueprint version was used when running the audit. This information is available on the Audit instance record which is three steps away from the Audit message. This is very useful information when building conditions for the reports. Note! Sometimes you might want to go one step further to "Blueprint" instead of Blueprint version to make the report blueprint, not version, specific. |
| Audit Schedule | Reference → Reference → Reference | am.audit_result.audit_instance.audit_schedule | Schedule used to run the audit. Can be useful when creating conditions for reports. |
| Created | Date/Time | am.created | Date when Audit message (or Audit Result, or Audit Instance) was created. Can be very useful when defining conditions for reports, since usefully you only want the latest audit results (unless creating a trend). |
Example report
Example scenario
- We have an Application portfolio managed in ServiceNow
- Applications are related to different Business Services
- Applications have managers defined on Application CIs
- Applications are dependent on Servers and other infrastructure
- We have created a data blueprint Applications in order to manage the data quality related to our Application portfolio
- We need to report Audit deviations per Application manager responsible for the applications.
Actual deviations with detailed messages are recorded into "Audit Messages" table. This table refers to Audit Results which has a Document ID reference to Audited Record. In this example scenario the "audited records" are Applications. And the applications should have "Managed by" reference to User.
In order to create a meaningful report we need information from multiple tables to both show and filter the information.
Here's some examples on how to filter the Audit Messages related to example scenario:
- Audit Message → Audit Result Type → Deviation Type: can be used to filter what type of audit results we want to include on the report
- Audit Message → Audit Result → Audit Instance → Blueprint Version: can be used to filter based on particular blueprint
- Audit Message → Audit Result → Audit Instance → Audit Started: can be used to limit the report to only include latest audit run for example
Report content, on the other hand, can benefit from the data behind the Real Element and Actual Record Document ID fields. But one problem with Document ID is that, it does not support "dot walking". This limitation can be bypassed by creating a Database View between DCM tables and the Configuration Item table for example.
Database View
In this example we have created a Database View that combines Audit Messages into Audit Results and Configuration Item table, since our blueprints are often related to CIs. This Database view will connect Configuration Item table to Audit Results via Audited Record Document ID field. This will make all attributes behind Audited record available for reporting.
Note!
This database view has been added to DCM application version 3.0.
Name of the Database view is x_jugr_dcm_audit_message_to_root
| Table | Purpose | Prefix | Where clause |
|---|---|---|---|
Audit Messages (x_jugr_dcm_audit_messages) | Includes the Audit Messages we want to report | am | <none> |
Audit Results (x_jugr_dcm_audit_results) | Connects Audit Messages to Root records for more information | ar | am_audit_result = ar_sys_id |
Configuration Item (cmdb_ci) | Get access to fields behind Real Element Document ID field on Audit Message table when assuming the Real Elements are different types of CIs | ci | ar_audited_record = ci_sys_id |
Prefix descriptions:
- am = Audit message
- ar = Audit record
- ci = CI record on Audit Result = Audited record (also known as Root record)
Useful fields from database view
DCM data structure related to Audit includes lots of useful details for reporting. Some details may be hard to find, so here's a quick list of useful details available on different tables. All paths start from the Audit Messages (x_jugr_dcm_audit_messages) table, so if you are creating reports from Audit Results for example, just skip the audit_messages part of the path.
| Field | Field type | Path from Audit message | Meaning |
|---|---|---|---|
| Managed by | Reference → Reference → Reference | audit_messages.audit_result.audited_record.managed_by | Managed by User reference for the Root record (= audited record). This can be useful field for grouping audit deviations for example when the Managed by user is responsible for keeping CI data up-to-date. |
| Support group | Reference → Reference → Reference | audit_messages.audit_result.audited_record.support_group | Support Group reference for the Root record (= audited record). This can be useful field for grouping audit deviations for example when the Support group members are responsible for keeping CI data up-to-date. |
| Unique Audit Message | Reference | audit_messages.unique_message | Using the Unique Audit Message to group and show report details can be very useful way to avoid duplicate records on reports. |
Creating the report
Actual report creation now starts from the newly created Database View. Database Views can be used in reports just like any other "table" in ServiceNow.
- Create new report where the new Database view is used as a table
- Set the Type as Horizontal bar for example
- Configure the Group by based on Managed by (based on the Root record CI's reference field = ci_managed_by)
- Set Condition to filter the audit messages to be included
- In this case we want to include recent messages related to particular Blueprint and ignore compliant messages
- Audit Message → Audit Result Type → Deviation Type: can be used to filter what type of audit results we want to include on the report
- Audit Message → Audit Result → Audit Instance → Blueprint Version: can be used to filter based on particular blueprint
- Audit Message → Audit Result → Audit Instance → Audit Ended: can be used to limit the report to only include latest audit run for example
- Audit Message → Unique Message: Can be used for grouping or filtering
- Run the report and make adjustments as needed.
- Report should look something like this
© Qualdatrix Ltd 2021 | All rights reserved.