Capri-Soft Knowledge database

Problem

Alle KRITIS-Betreiber müssen seit 30.6.2019 laut IT-Sicherheitsgesetz alle 2 Jahren dem „Bundesamt für Sicherheit in der Informationstechnik“ die Unbedenklichkeit Ihrer IT Infrastruktur nachweisen. Im Zuge dessen können diese eine prüfende Stelle (interne Revision / externe Beratungsgesellschaft) beauftragen, welche die Maßnahmen des bspw. ISMS auditiert.

Ansatz

Um den Aufbau eines ISMS zu erleichtern bietet sich die folgende Vorgehensweise an:

The following statements can be used to get the Risk Measure Tree from the Bayoonet QWare Risk Manager

First you should create the view QWareSegment:

SELECT        QwareRiskmanager.dbo.Project.ProjectNo, QwareRiskmanager.dbo.Project.ProjectTitle, QwareRiskmanager.dbo.Version.VersionId AS ProjectVersion, 
                         QwareRiskmanager.dbo.Version.ProductVersion, QwareRiskmanager.dbo.FunctionSet.Type, QwareRiskmanager.dbo.FunctionSetFunction.FunctionNo, 
                         QwareRiskmanager.dbo.[Function].Description AS FunctionDescription, QwareRiskmanager.dbo.FunctionHazard.HazardNo, 
                         QwareRiskmanager.dbo.Hazard.Description AS HazardDescription, QwareRiskmanager.dbo.FunctionHazardCause.CauseNo, 
                         QwareRiskmanager.dbo.Cause.Description AS CauseDescription, QwareRiskmanager.dbo.Measure.Description AS MeasureDescription, 
                         QwareRiskmanager.dbo.FunctionHazardCauseMeasure.MeasureNo, QwareRiskmanager.dbo.Measure.NewMeasureNo, 
                         QwareRiskmanager.dbo.FunctionSetFunction.FunctionId, QwareRiskmanager.dbo.FunctionHazard.HazardId, QwareRiskmanager.dbo.FunctionHazardCause.CauseId, 
                         QwareRiskmanager.dbo.FunctionHazardCauseMeasure.MeasureId, seva.Abbreviation AS AfterSeverityAbbreviation, 
                         sevb.Abbreviation AS BeforeSeverityAbbreviation, propa.Abbreviation AS AfterProbabilityAbbreviation, propb.Abbreviation AS BeforeProbabilityAbbreviation, 
                         QwareRiskmanager.dbo.FunctionHazardCause.CommentAfter, QwareRiskmanager.dbo.FunctionHazardCause.CommentBefore
FROM            QwareRiskmanager.dbo.Project LEFT OUTER JOIN
                         QwareRiskmanager.dbo.Version ON QwareRiskmanager.dbo.Project.ProjectId = QwareRiskmanager.dbo.Version.ProjectId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.FunctionSet ON QwareRiskmanager.dbo.Version.VersionId = QwareRiskmanager.dbo.FunctionSet.VersionId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.FunctionSetFunction ON 
                         QwareRiskmanager.dbo.FunctionSet.FunctionSetId = QwareRiskmanager.dbo.FunctionSetFunction.FunctionSetId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.[Function] ON QwareRiskmanager.dbo.FunctionSetFunction.FunctionId = QwareRiskmanager.dbo.[Function].FunctionId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.FunctionHazard ON 
                         QwareRiskmanager.dbo.FunctionSetFunction.FunctionSetFunctionId = QwareRiskmanager.dbo.FunctionHazard.FunctionSetFunctionId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.FunctionHazardCause ON 
                         QwareRiskmanager.dbo.FunctionHazard.FunctionHazardId = QwareRiskmanager.dbo.FunctionHazardCause.FunctionHazardId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.FunctionHazardCauseMeasure ON 
                         QwareRiskmanager.dbo.FunctionHazardCause.FunctionHazardCauseId = QwareRiskmanager.dbo.FunctionHazardCauseMeasure.FunctionHazardCauseId LEFT OUTER
                          JOIN
                         QwareRiskmanager.dbo.Measure ON 
                         QwareRiskmanager.dbo.FunctionHazardCauseMeasure.MeasureId = QwareRiskmanager.dbo.Measure.MeasureId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.Hazard ON QwareRiskmanager.dbo.FunctionHazard.HazardId = QwareRiskmanager.dbo.Hazard.HazardId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.Cause ON QwareRiskmanager.dbo.FunctionHazardCause.CauseId = QwareRiskmanager.dbo.Cause.CauseId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.Severity AS seva ON QwareRiskmanager.dbo.FunctionHazardCause.SeverityAfterId = seva.SeverityId AND 
                         QwareRiskmanager.dbo.Version.VersionId = seva.VersionId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.Severity AS sevb ON QwareRiskmanager.dbo.FunctionHazardCause.SeverityBeforeId = sevb.SeverityId AND 
                         QwareRiskmanager.dbo.Version.VersionId = sevb.VersionId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.Probability AS propa ON QwareRiskmanager.dbo.FunctionHazardCause.ProbabilityAfterId = propa.ProbabilityId AND 
                         QwareRiskmanager.dbo.Version.VersionId = propa.VersionId LEFT OUTER JOIN
                        QwareRiskmanager.dbo.Probability AS propb ON QwareRiskmanager.dbo.FunctionHazardCause.ProbabilityBeforeId = propb.ProbabilityId AND 
                         QwareRiskmanager.dbo.Version.VersionId = propb.VersionId

After that you should create the view QWareImport that gives us the Risk Measure Tree:

CREATE VIEW [dbo].[QWareImport]
AS
SELECT     CAST(REPLACE(RTRIM(REPLACE((ISNULL(FunctionNo, '') + '+' + ISNULL(HazardNo, '') + '+' + ISNULL(CauseNo, '') + '+' + ISNULL(MeasureNo, '')), '+', ' ')), ' ', '+') 
                      AS varchar(255)) AS BB_ItemID, CAST(ProjectNo AS varchar(255)) AS ProjectNo, CAST(ProjectVersion AS varchar(255)) AS ProjectVersion, 
                      CAST(ProductVersion AS varchar(255)) AS ProductVersion, ISNULL(CAST(FunctionId AS varchar(255)), '') AS FunctionId, ISNULL(CAST(FunctionNo AS varchar(255)), '') 
                      AS BB_FID, ISNULL(CAST(FunctionDescription AS varchar(2048)), '') AS BB_FText, ISNULL(CAST(HazardId AS varchar(255)), '') AS HazardId, 
                      ISNULL(CAST(HazardNo AS varchar(255)), '') AS BB_HID, ISNULL(CAST(HazardDescription AS varchar(2048)), '') AS BB_HText, ISNULL(CAST(CauseId AS varchar(255)), 
                      '') AS CauseId, ISNULL(CAST(CauseNo AS varchar(255)), '') AS BB_CID, ISNULL(CAST(CauseDescription AS varchar(2048)), '') AS BB_CText, 
                      ISNULL(CAST(MeasureId AS varchar(255)), '') AS MeasureId, ISNULL(CAST(MeasureNo AS varchar(255)), '') AS BB_MID, 
                      ISNULL(CAST(MeasureDescription AS varchar(2048)), '') AS BB_MText, ISNULL(CAST(AfterSeverityAbbreviation AS varchar(255)), '') AS BB_EAM_Severity, 
                      ISNULL(CAST(BeforeSeverityAbbreviation AS varchar(255)), '') AS BB_EBM_Severity, ISNULL(CAST(AfterProbabilityAbbreviation AS varchar(255)), '') 
                      AS BB_EAM_Probability, ISNULL(CAST(BeforeProbabilityAbbreviation AS varchar(255)), '') AS BB_EBM_Probability, ISNULL(CAST(CommentAfter AS varchar(2048)), '') 
                      AS BB_CCommentAM, ISNULL(CAST(CommentBefore AS varchar(2048)), '') AS BB_CCommentBM, ISNULL(CAST(NewMeasureNo AS varchar(2048)), '') 
                      AS BB_MNr
FROM         DT_Qware.dbo.QWareSegment
UNION
SELECT     CAST(REPLACE(RTRIM(REPLACE((ISNULL(FunctionNo, '') + '+' + ISNULL(HazardNo, '') + '+' + ISNULL(CauseNo, '')), '+', ' ')), ' ', '+') AS varchar(255)) AS BB_ItemID, 
                      CAST(ProjectNo AS varchar(255)) AS ProjectNo, CAST(ProjectVersion AS varchar(255)) AS ProjectVersion, CAST(ProductVersion AS varchar(255)) AS ProductVersion, 
                      ISNULL(CAST(FunctionId AS varchar(255)), '') AS FunctionId, ISNULL(CAST(FunctionNo AS varchar(255)), '') AS BB_FID, 
                      ISNULL(CAST(FunctionDescription AS varchar(2048)), '') AS BB_FText, ISNULL(CAST(HazardId AS varchar(255)), '') AS HazardId, 
                      ISNULL(CAST(HazardNo AS varchar(255)), '') AS BB_HID, ISNULL(CAST(HazardDescription AS varchar(2048)), '') AS BB_HText, ISNULL(CAST(CauseId AS varchar(255)), 
                      '') AS CauseId, ISNULL(CAST(CauseNo AS varchar(255)), '') AS BB_CID, ISNULL(CAST(CauseDescription AS varchar(2048)), '') AS BB_CText, '' AS MeasureId, 
                      '' AS BB_MID, '' AS BB_MText, '' AS BB_EAM_Severity, '' AS BB_EBM_Severity, '' AS BB_EAM_Probability, '' AS BB_EBM_Probability, '' AS BB_CCommentAM, 
                      '' AS BB_CCommentBM, '' AS BB_MNr
FROM         DT_Qware.dbo.QWareSegment
UNION
SELECT     CAST(REPLACE(RTRIM(REPLACE((ISNULL(FunctionNo, '') + '+' + ISNULL(HazardNo, '')), '+', ' ')), ' ', '+') AS varchar(255)) AS BB_ItemID, CAST(ProjectNo AS varchar(255))
                       AS ProjectNo, CAST(ProjectVersion AS varchar(255)) AS ProjectVersion, CAST(ProductVersion AS varchar(255)) AS ProductVersion, 
                      ISNULL(CAST(FunctionId AS varchar(255)), '') AS FunctionId, ISNULL(CAST(FunctionNo AS varchar(255)), '') AS BB_FID, 
                      ISNULL(CAST(FunctionDescription AS varchar(2048)), '') AS BB_FText, ISNULL(CAST(HazardId AS varchar(255)), '') AS HazardId, 
                      ISNULL(CAST(HazardNo AS varchar(255)), '') AS BB_HID, ISNULL(CAST(HazardDescription AS varchar(2048)), '') AS BB_HText, '' AS CauseId, '' AS BB_CID, 
                      '' AS BB_CText, '' AS MeasureId, '' AS BB_MID, '' AS BB_MText, '' AS BB_EAM_Severity, '' AS BB_EBM_Severity, '' AS BB_EAM_Probability, 
                      '' AS BB_EBM_Probability, '' AS BB_CCommentAM, '' AS BB_CCommentBM, '' AS BB_MNr
FROM         DT_Qware.dbo.QWareSegment
UNION
SELECT     CAST(REPLACE(RTRIM(REPLACE((ISNULL(FunctionNo, '')), '+', ' ')), ' ', '+') AS varchar(255)) AS BB_ItemID, CAST(ProjectNo AS varchar(255)) AS ProjectNo, 
                      CAST(ProjectVersion AS varchar(255)) AS ProjectVersion, CAST(ProductVersion AS varchar(255)) AS ProductVersion, ISNULL(CAST(FunctionId AS varchar(255)), '') 
                      AS FunctionId, ISNULL(CAST(FunctionNo AS varchar(255)), '') AS BB_FID, ISNULL(CAST(FunctionDescription AS varchar(2048)), '') AS BB_FText, '' AS HazardId, 
                      '' AS BB_HID, '' AS BB_HText, '' AS CauseId, '' AS BB_CID, '' AS BB_CText, '' AS MeasureId, '' AS BB_MID, '' AS BB_MText, '' AS BB_EAM_Severity, 
                      '' AS BB_EBM_Severity, '' AS BB_EAM_Probability, '' AS BB_EBM_Probability, '' AS BB_CCommentAM, '' AS BB_CCommentBM, '' AS BB_MNr
FROM         DT_Qware.dbo.QWareSegment

To query a unique Risk Project in the appropriate version you have to perform the following selection

SELECT BB_ItemID, 
		ProjectVersion, 
		ProductVersion, 
		FunctionId, 
		BB_FID, 
		BB_FText, 
		HazardId, 
		BB_HID, 
		BB_HText, 
		CauseId, 
		BB_CID, 
		BB_CText, 
		MeasureId, 
		BB_MID, 
		BB_MText, 
		BB_EAM_Severity, 
		BB_EBM_Severity, 
		BB_EAM_Probability, 
		BB_EBM_Probability, 
		BB_CCommentAM, 
		BB_CCommentBM,
		BB_MNr
FROM DP_Qware.dbo.QWareImport
WHERE ProjectNo='**ProjectNo**' AND ProductVersion='**ProductVersion**' 
ORDER BY 1

Problem

In some cases you will run into trouble when you want to use the standard function of Doors to create a traceability for a depth of 2 that is not going into the same direction. To solve this problem the implementation of an own Layout DXL is necessary.

The example below shows the situation with depth 2 and the same Linkmodule in which you can use the standard function to create a Layout DXL column:

The next situation, which is not possible to master with the standard doors function, shows the problem where depth two with two different direction and link module types gives the needed traceability information for the report.

Approach – Ansatz

To get the needed traceability information within a module, you can add a layout dxl column by clicking on the column header of the current selected view in the module and add a layout DXL column. This layout DXL column can iterate through the outgoing modules of the current object, catch all objects that are linked and look for incoming links on it.

Solution – Lösung

//
/*******************************************************************************
* Author: Björn Karpenstein 
* Stakeholder: Björn Karpenstein
*
* Subversion
* ===================================================
* $Rev::               $:  Revision of last commit
* $Author::            $:  Author of last commit
* $Date::              $:  Date of last commit
*
* Change Control
* ==============
* n/a
*
* Description
* ===========
* This script gives the traceability over 2 modules with two different diection
* and module types
*******************************************************************************/
string firstLinkModule  = "/Product Lifecycle Management/10 Administration/Create";
string secondLinkModule = "/Product Lifecycle Management/10 Administration/Triggers";

Skip dublettenRaus=createString;

pragma encoding,"UTF-8";
pragma runLim, 0;

//Object obj=current;
 
Link lnk;
Link inLnk;
LinkRef lnkRef;
ModName_ otherMod = null;
 
for lnk in obj->firstLinkModule do
{
	string tmn=fullName target(lnk);
	
	if(!open module tmn)
	{
		read(tmn,false);
	}
	
	Object keypoint = target(lnk);
	//display identifier(keypoint) "\n";
	
	if(null keypoint)
	{
		 //display "no link to keypoint";
	}
	else
	{	     
		//display "keypoint : " identifier(keypoint) "";
		for lnkRef in keypoint<-secondLinkModule do
		{
		  //display "drin";
		  otherMod = module (sourceVersion lnkRef);
		  if (!null otherMod) 
		  {
		    if ((!isDeleted otherMod) &amp;&amp; (null data(sourceVersion lnkRef))) 
		    {
		      load((sourceVersion lnkRef),false);
		    }
		  }
		}
		     
		for inLnk in keypoint<-secondLinkModule do
		{
		  // Get In-Link Object
		  Object phase = source inLnk;
		  if ( isDeleted(phase) || null(phase) ) continue;
		     
		  string dummy="";
		  if(!find(dublettenRaus, phase."Object Text" "", dummy))
		  {
			  put(dublettenRaus, phase."Object Text" "", phase."Object Text" "");
		  }
		}
	}
}

int phaseCounter=0;

// Iteration through the SkipList
for myIterator in dublettenRaus do
{
	string keyValue = (string key(dublettenRaus));
	string currentObject = null;
	
	if(find(dublettenRaus, keyValue, currentObject))
	{
	   display keyValue;
	}
}

delete dublettenRaus;