Hi ServiceNow Community,
We are tasked with creating a comprehensive report that lists all our Business Processes, which are stored in the cmdb_ci_business_process table. This report should also include the associated Business Continuity Plan (BCP) from the sn_bcp_plan table, where one exists.
Our primary challenge is the absence of a direct relationship between the cmdb_ci_business_process and sn_bcp_plan tables. To overcome this, we have attempted to build a database view by joining cmdb_ci_business_process with the sn_bcp_plan_asset table.
However, this approach has led to an issue with duplicate records. A single Business Process can be linked to multiple assets within the sn_bcp_plan_asset table, each potentially having a different Recovery Point Objective (RPO) or Recovery Time Objective (RTO). Consequently, for Business Processes that do not have an associated Business Continuity Plan (BCP), our view returns multiple, identical rows for that single process, which affects the accuracy of our reports. This is a known challenge when dealing with one-to-many relationships in database views.
Our goal is to create a view that displays a distinct line for each business process. For processes without a plan, we need it to appear only once in the list.
We are required to use out-of-the-box (OOTB) ServiceNow functionality, specifically database views. We must avoid custom development.
How can we configure our database view, perhaps through specific join conditions or 'Where' clauses, to ensure we get a unique record for each Business Process, especially when no BCP is available? Any advice on best practices for managing data duplication within OOTB views would be extremely helpful.
Thank you for your assistance
