Capri-Soft Knowledge database

Problem

C# code shall be executed in the wordpress.

Aproach

https://dotnetfiddle.net can be used to run C# Code.

Solution

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