0 / 0
Sample reporting queries for IBM Knowledge Catalog

Sample reporting queries for IBM Knowledge Catalog

Check the following use cases for reporting and the example SQL queries to create the reports.

Note:

  • The queries are applicable for all supported databases unless otherwise specified.

  • All queries use globalschema and zoneschema for schema names. You must replace them with the schema name used in your environment:

    • globalschema is the default schema chosen in the UI.
    • zoneschema is the schema specific to the catalog, project, or category.

    globalschema and zoneschema might be the same if the user chooses so in the UI.

Catalog, project, and assets queries

  • SELECT
      ASSET_TYPE,
      COUNT(*) AS TOTAL
    FROM
      zoneschema.CONTAINER_ASSETS
    GROUP BY
      ASSET_TYPE
    
  • SELECT
      CONTAINER_TYPE,
      COUNT(*)
    FROM
      zoneschema.CONTAINER_ASSETS
    WHERE
      ASSET_TYPE = 'connection'
      AND CREATED_ON >= TO_TIMESTAMP('2022-01-01', 'YYYY-MM-DD')
    GROUP BY
      CONTAINER_TYPE
    
  • SELECT
      TAG_NAME,
      ASSET_TYPE,
      COUNT(*) TOTAL
    FROM
      zoneschema.ASSET_TAGS ASSET_TAGS,
      zoneschema.CONTAINER_ASSETS CONTAINER_ASSETS
    WHERE
      ASSET_TAGS.ASSET_ID = CONTAINER_ASSETS.ASSET_ID
    GROUP BY
      TAG_NAME,
      ASSET_TYPE
    
    
  • SELECT
      *
    FROM
      zoneschema.CONTAINER_ASSETS
    WHERE
      DESCRIPTION IS NULL
      OR DESCRIPTION = ''
      AND CONTAINER_TYPE = 'catalog'
    
  • SELECT
      GA.NAME,
      AGGR_TABLE.TOTAL
    FROM
      zoneschema.GOVERNANCE_ARTIFACTS GA
      LEFT OUTER JOIN (
        SELECT
          ASSOCIATED_ARTIFACT_ID,
          COUNT(*) AS TOTAL
        FROM
          zoneschema.GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS
        WHERE
          ASSOCIATED_ARTIFACT_TYPE = 'glossary_term'
        GROUP BY
          ASSOCIATED_ARTIFACT_ID
      ) AGGR_TABLE ON GA.ARTIFACT_ID = AGGR_TABLE.ASSOCIATED_ARTIFACT_ID
    WHERE
      GA.ARTIFACT_TYPE = 'glossary_term'
    
    
  • SELECT
      HEADER,
      COUNT(*) AS TOTAL
    FROM
      (
        SELECT
          CASE WHEN GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS.ASSOCIATED_ARTIFACT_ID IS NULL THEN 'NO TERMS' ELSE 'HAS ATLEAST ONE TERM' END AS HEADER
        FROM
          zoneschema.CONTAINERS CONTAINERS,
          zoneschema.CONTAINER_ASSETS CONTAINER_ASSETS
          LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS ON CONTAINER_ASSETS.ASSET_ID = GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS.ASSET_ID
          AND GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS.ASSOCIATED_ARTIFACT_TYPE = 'glossary_term'
        WHERE
          CONTAINERS.CONTAINER_ID = CONTAINER_ASSETS.CONTAINER_ID
          AND CONTAINERS.CONTAINER_TYPE = 'catalog'
      ) TEMP_TABLE
    GROUP BY
      HEADER
    
    
  • SELECT
      CONTAINER_ASSETS.CONTAINER_ID,
      CONTAINER_ASSETS.ASSET_ID,
      CONTAINER_ASSETS.NAME,
      CONTAINER_ASSETS.ASSET_TYPE
    FROM
      zoneschema.ASSET_COLLABORATORS ASSET_COLLABORATORS,
      zoneschema.CONTAINER_ASSETS CONTAINER_ASSETS
    WHERE
      USER_ID = 'IBMid-50DW59BCGD'
      AND CONTAINER_ASSETS.CONTAINER_TYPE = 'catalog'
      AND CONTAINER_ASSETS.ASSET_ID = ASSET_COLLABORATORS.ASSET_ID
      AND CONTAINER_ASSETS.CONTAINER_ID = ASSET_COLLABORATORS.CONTAINER_ID
    
    
  • For Db2:

    SELECT
      CA.NAME AS ASSET_NAME,
      TEMP.TECH_START AS UPDATE_TIME,
      TEMP.TECH_START,
      TEMP.ASSET_ID,
      TEMP.TECH_END,
      QUALITY_SCORE
    FROM
      zoneschema.CONTAINER_ASSETS AS CA,
      (
        SELECT
          CONTAINER_DATA_ASSETS.ASSET_ID,
          CONTAINER_DATA_ASSETS.QUALITY_SCORE,
          CONTAINER_DATA_ASSETS.TECH_START,
          CONTAINER_DATA_ASSETS.TECH_END
        FROM
          zoneschema.CONTAINER_DATA_ASSETS FOR SYSTEM_TIME
        FROM
          '2021-08-19-00.00.00.000000000000' TO '9999-12-30-00.00.00.000000000000'
        WHERE
          CONTAINER_DATA_ASSETS.ASSET_ID = '85a89a9c-8fd3-4306-8931-cd15c41ae1d4'
          AND QUALITY_SCORE IS NOT NULL
      ) AS TEMP
    WHERE
      TEMP.ASSET_ID = CA.ASSET_ID
    

    For PostgreSQL:

    select
      CA.NAME as ASSET_NAME,
      TEMP.TECH_START as UPDATE_TIME,
      TEMP.TECH_START,
      TEMP.ASSET_ID,
      TEMP.TECH_END,
      TEMP.QUALITY_SCORE
    from
      zoneschema.CONTAINER_ASSETS CA,
      (
        select
          CONTAINER_DATA_ASSETS.ASSET_ID,
          CONTAINER_DATA_ASSETS.QUALITY_SCORE,
          CONTAINER_DATA_ASSETS.TECH_START,
          CONTAINER_DATA_ASSETS.TECH_END
        from
          zoneschema.CONTAINER_DATA_ASSETS
        where
          CONTAINER_DATA_ASSETS.ASSET_ID = '130ab82a-c3a4-4fbc-880b-51bc49517ebe'
          and CONTAINER_DATA_ASSETS.QUALITY_SCORE is not null
        union
        select
          HIST_CONTAINER_DATA_ASSETS.ASSET_ID,
          HIST_CONTAINER_DATA_ASSETS.QUALITY_SCORE,
          HIST_CONTAINER_DATA_ASSETS.TECH_START,
          HIST_CONTAINER_DATA_ASSETS.TECH_END
        from
          zoneschema.HIST_CONTAINER_DATA_ASSETS
        where
          HIST_CONTAINER_DATA_ASSETS.ASSET_ID = '130ab82a-c3a4-4fbc-880b-51bc49517ebe'
          and HIST_CONTAINER_DATA_ASSETS.QUALITY_SCORE is not null
      ) temp
    where
      TEMP.ASSET_ID = CA.ASSET_ID
    
    

Governance artifacts queries

  • SELECT
      ARTIFACT_TYPE,
      COUNT(*) TOTAL
    FROM
      zoneschema.GOVERNANCE_ARTIFACTS
    GROUP BY
      ARTIFACT_TYPE
    
    
  • SELECT
      *
    FROM
      zoneschema.GOVERNANCE_ARTIFACTS
    WHERE
      DESCRIPTION IS NULL
    
  • SELECT
      COUNT(*) AS TOTAL_COUNT,
      ARTIFACT_WITH_STEWARD_COUNT
    FROM
      zoneschema.GOVERNANCE_ARTIFACTS
      LEFT OUTER JOIN (
        SELECT
          COUNT(*) AS ARTIFACT_WITH_STEWARD_COUNT
        FROM
          zoneschema.GOVERNANCE_ARTIFACTS GOVERNANCE_ARTIFACTS
          LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACT_STEWARDS GOVERNANCE_ARTIFACT_STEWARDS ON GOVERNANCE_ARTIFACTS.ARTIFACT_ID = GOVERNANCE_ARTIFACT_STEWARDS.ARTIFACT_ID
        WHERE
          GOVERNANCE_ARTIFACT_STEWARDS.USER_ID IS NOT NULL
      ) TEMP_TABLE ON 1 = 1
    GROUP BY
      ARTIFACT_WITH_STEWARD_COUNT
    
  • SELECT
      TEMPTABLE.NAME AS END1_NAME,
      RELATIONSHIP_TYPE,
      SGA.NAME AS END2_NAME
    FROM
      (
        SELECT
          GA.NAME,
          BTA.END2_ARTIFACT_ID,
          BTA.RELATIONSHIP_TYPE
        FROM
          zoneschema.BUSINESS_TERM_ASSOCIATIONS BTA
          LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACTS GA ON GA.ARTIFACT_ID = BTA.END1_ARTIFACT_ID
        ORDER BY
          BTA.END1_ARTIFACT_ID
      ) TEMPTABLE
      LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACTS SGA ON TEMPTABLE.END2_ARTIFACT_ID = SGA.ARTIFACT_ID
    
    
  • For Db2:

    WITH RPL (
      END1_CATEGORY_ID, END2_CATEGORY_ID,
      DEPTH
    ) AS (
      SELECT
        ROOT.END1_CATEGORY_ID,
        ROOT.END2_CATEGORY_ID,
        0
      FROM
        zoneschema.CATEGORY_ASSOCIATIONS ROOT
      WHERE
        ROOT.END1_CATEGORY_ID = 'b11e64a2-5466-4c60-bfc5-121c7a80703d'
      UNION ALL
      SELECT
        CHILD.END1_CATEGORY_ID,
        CHILD.END2_CATEGORY_ID,
        PARENT.DEPTH + 1
      FROM
        RPL PARENT,
        zoneschema.CATEGORY_ASSOCIATIONS CHILD
      WHERE
        PARENT.END2_CATEGORY_ID = CHILD.END1_CATEGORY_ID
        AND DEPTH < 1000
    )
    SELECT
      TEMP.DEPTH,
      TEMP.NAME AS END1_NAME,
      CATEGORIES.NAME AS END2_NAME,
      TEMP.END1_CATEGORY_ID AS END1,
      TEMP.END2_CATEGORY_ID AS END2
    FROM
      (
        SELECT
          END1_CATEGORY_ID,
          NAME,
          END2_CATEGORY_ID,
          DEPTH
        FROM
          RPL
          LEFT OUTER JOIN zoneschema.CATEGORIES CATEGORIES ON RPL.END1_CATEGORY_ID = CATEGORIES.CATEGORY_ID
      ) TEMP
      LEFT OUTER JOIN zoneschema.CATEGORIES CATEGORIES ON CATEGORIES.CATEGORY_ID = TEMP.END2_CATEGORY_ID
    ORDER BY
      DEPTH
    
    

    For PostgreSQL:

    WITH RECURSIVE RPL (
      END1_CATEGORY_ID, END2_CATEGORY_ID,
      DEPTH
    ) AS (
      SELECT
        ROOT.END1_CATEGORY_ID,
        ROOT.END2_CATEGORY_ID,
        0
      FROM
        zoneschema.CATEGORY_ASSOCIATIONS ROOT
      WHERE
        ROOT.END1_CATEGORY_ID = 'b11e64a2-5466-4c60-bfc5-121c7a80703d'
      UNION ALL
      SELECT
        CHILD.END1_CATEGORY_ID,
        CHILD.END2_CATEGORY_ID,
        PARENT.DEPTH + 1
      FROM
        RPL PARENT,
        zoneschema.CATEGORY_ASSOCIATIONS CHILD
      WHERE
        PARENT.END2_CATEGORY_ID = CHILD.END1_CATEGORY_ID
        AND DEPTH < 1000
    )
    SELECT
      TEMP.DEPTH,
      TEMP.NAME AS END1_NAME,
      CATEGORIES.NAME AS END2_NAME,
      TEMP.END1_CATEGORY_ID AS END1,
      TEMP.END2_CATEGORY_ID AS END2
    FROM
      (
        SELECT
          END1_CATEGORY_ID,
          NAME,
          END2_CATEGORY_ID,
          DEPTH
        FROM
          RPL
          LEFT OUTER JOIN zoneschema.CATEGORIES CATEGORIES ON RPL.END1_CATEGORY_ID = CATEGORIES.CATEGORY_ID
      ) TEMP
      LEFT OUTER JOIN zoneschema.CATEGORIES CATEGORIES ON CATEGORIES.CATEGORY_ID = TEMP.END2_CATEGORY_ID
    ORDER BY
      DEPTH
    
    
  • SELECT
      ENFORCEMENT_RULES.NAME,
      COUNT
    FROM
      (
        SELECT
          ENFORCEMENT_RULES.RULE_ID AS RULE_ID,
          COUNT(
            ARTIFACT_ENFORCEMENT_RULE_ASSOCIATIONS.ARTIFACT_ID
          ) AS COUNT
        FROM
          globalschema.ENFORCEMENT_RULES ENFORCEMENT_RULES
          LEFT OUTER JOIN zoneschema.ARTIFACT_ENFORCEMENT_RULE_ASSOCIATIONS ARTIFACT_ENFORCEMENT_RULE_ASSOCIATIONS ON ENFORCEMENT_RULES.RULE_ID = ARTIFACT_ENFORCEMENT_RULE_ASSOCIATIONS.RULE_ID
        GROUP BY
          ENFORCEMENT_RULES.RULE_ID
      ) TEMPTABLE
      LEFT OUTER JOIN globalschema.ENFORCEMENT_RULES ENFORCEMENT_RULES ON ENFORCEMENT_RULES.RULE_ID = TEMPTABLE.RULE_ID
    
    

Metadata import (MDI) queries

  • SELECT
      MDI.METADATA_IMPORT_NAME MDI_NAME,
      ASSET_DETAIL.NAME DISCOVERED_ASSET
    FROM
      zoneschema.METADATA_IMPORTS MDI
      INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDI_ASSETS ON MDI_ASSETS.METADATA_IMPORT_ID = MDI.METADATA_IMPORT_ID
      INNER JOIN zoneschema.CONTAINER_ASSETS ASSET_DETAIL ON ASSET_DETAIL.ASSET_ID = MDI_ASSETS.ASSET_ID
      AND ASSET_DETAIL.CONTAINER_ID = MDI_ASSETS.CONTAINER_ID
    
    
  • SELECT
        MDI.METADATA_IMPORT_NAME AS MDI_NAME, U2.USER_NAME AS MDI_OWNER,
        MDI_DETAILS.CREATED_ON AS MDI_CREATED_ON,
        U.USER_NAME INVOKED_BY, E.IS_SCHEDULED_RUN, E.START_TIME, E.END_TIME,
        E.COUNT_NEW_ASSETS, E.COUNT_UPDATED_ASSETS, E.COUNT_DELETED_ASSETS
    FROM
        METADATA_IMPORTS MDI
        INNER JOIN CONTAINER_ASSETS MDI_DETAILS
           ON MDI_DETAILS.ASSET_ID = MDI.METADATA_IMPORT_ID AND MDI_DETAILS.CONTAINER_ID = MDI.CONTAINER_ID
        LEFT JOIN METADATA_IMPORT_EXECUTIONS E
           ON E.METADATA_IMPORT_ID = MDI.METADATA_IMPORT_ID
        LEFT JOIN USER_PROFILES U ON U.USER_ID = E.INVOKED_BY
        LEFT JOIN USER_PROFILES U2 ON U2.USER_ID = MDI_DETAILS.OWNER
    ORDER BY START_TIME DESC
    
    
  • SELECT
      MDI.METADATA_IMPORT_NAME AS MDI_NAME,
      COUNT(E.JOB_RUN_ID) AS NUM_JOBS
    FROM
      zoneschema.METADATA_IMPORTS MDI
      LEFT OUTER JOIN zoneschema.METADATA_IMPORT_EXECUTIONS E ON E.METADATA_IMPORT_ID = MDI.METADATA_IMPORT_ID
    WHERE
      E.START_TIME >= TO_TIMESTAMP('2022-01-01', 'YYYY-MM-DD')
    GROUP BY
      MDI.METADATA_IMPORT_NAME
    

Metadata enrichment (MDE) queries

  • SELECT
      PROJECTS.NAME PROJECT,
      MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
      U.USER_NAME AS MDE_OWNER,
      MDE_DETAILS.CREATED_ON AS MDE_CREATED_ON
    FROM
      zoneschema.METADATA_ENRICHMENTS MDE
      INNER JOIN zoneschema.CONTAINER_ASSETS MDE_DETAILS ON MDE_DETAILS.ASSET_ID = MDE.METADATA_ENRICHMENT_ID
      AND MDE_DETAILS.CONTAINER_ID = MDE.CONTAINER_ID
      INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
      LEFT JOIN globalschema.USER_PROFILES U ON U.USER_ID = MDE_DETAILS.OWNER
    
    
  • SELECT
      PROJECTS.NAME PROJECT,
      MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
      MDI.METADATA_IMPORT_NAME AS MDI_NAME
    FROM
      zoneschema.METADATA_ENRICHMENTS MDE
      INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
      LEFT JOIN zoneschema.METADATA_IMPORTS MDI ON MDI.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
      AND MDI.CONTAINER_ID = MDE.CONTAINER_ID
    
    
  • SELECT
      PROJECTS.NAME PROJECT,
      MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
      MDE_ASSET_DETAILS.NAME ASSET_NAME,
      MDE_ASSETS.REVIEWED_ON
    FROM
      zoneschema.METADATA_ENRICHMENTS MDE
      INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
      INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
      INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
      AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
    
    
  • SELECT
      PROJECTS.NAME PROJECT,
      MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
      MDE_ASSET_DETAILS.NAME ASSET_NAME,
      MDE_ASSETS.REVIEWED_ON,
      MDE_ASSET_COLUMNS.NAME COLUMN_NAME,
      MDE_ASSET_COLUMNS.REVIEWED_ON COL_REVIEWED_ON
    FROM
      zoneschema.METADATA_ENRICHMENTS MDE
      INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
      INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
      INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
      AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
      INNER JOIN zoneschema.CONTAINER_DATA_ASSET_COLUMNS MDE_ASSET_COLUMNS ON MDE_ASSET_COLUMNS.ASSET_ID = MDE_ASSETS.ASSET_ID
      AND MDE_ASSET_COLUMNS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
    ORDER BY
      PROJECT,
      MDE_NAME,
      ASSET_NAME
    
    
  • SELECT
      PROJECTS.NAME PROJECT,
      MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
      MDE_ASSET_DETAILS.NAME ASSET_NAME,
      MDE_ASSETS.REVIEWED_ON,
      TERM_DETAILS.NAME TERM_NAME,
      TERMS.ASSIGNMENT_STATE TERM_STATE,
      TERMS.ASSIGNMENT_DATE,
      U.USER_NAME ASSIGNED_BY,
      TERMS.CONFIDENCE
    FROM
      zoneschema.METADATA_ENRICHMENTS MDE
      INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
      INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
      INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
      AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
      LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS TERMS ON TERMS.ASSET_ID = MDE_ASSETS.ASSET_ID
      AND TERMS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
      AND UPPER(TERMS.ASSOCIATED_ARTIFACT_TYPE) = 'GLOSSARY_TERM'
      LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACTS TERM_DETAILS ON TERM_DETAILS.ARTIFACT_ID = TERMS.ASSOCIATED_ARTIFACT_ID
      LEFT JOIN globalschema.USER_PROFILES U ON U.USER_ID = TERMS.ASSIGNED_BY
    ORDER BY
      PROJECT,
      MDE_NAME,
      ASSET_NAME
    
    
  • SELECT
      PROJECTS.NAME PROJECT,
      MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
      MDE_ASSET_DETAILS.NAME ASSET_NAME,
      MDE_ASSETS.REVIEWED_ON,
      MDE_ASSET_COLUMNS.NAME COLUMN_NAME,
      MDE_ASSET_COLUMNS.REVIEWED_ON COL_REVIEWED_OM,
      COL_TERM_DETAILS.NAME COL_TERM_NAME,
      COL_TERMS.ASSIGNMENT_STATE COL_TERM_STATE,
      COL_TERMS.ASSIGNMENT_DATE COL_TERM_ASSIGNMENT_DATE,
      U.USER_NAME COL_TERM_ASSIGNED_BY,
      COL_TERMS.CONFIDENCE COL_TERM_CONFIDENCE
    FROM
      zoneschema.METADATA_ENRICHMENTS MDE
      INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
      INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
      INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
      AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
      INNER JOIN zoneschema.CONTAINER_DATA_ASSET_COLUMNS MDE_ASSET_COLUMNS ON MDE_ASSET_COLUMNS.ASSET_ID = MDE_ASSETS.ASSET_ID
      AND MDE_ASSET_COLUMNS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
      LEFT JOIN zoneschema.DATA_ASSET_COLUMN_ARTIFACT_ASSOCIATIONS COL_TERMS ON COL_TERMS.NAME = MDE_ASSET_COLUMNS.NAME
      AND COL_TERMS.ASSET_ID = MDE_ASSET_COLUMNS.ASSET_ID
      AND COL_TERMS.CONTAINER_ID = MDE_ASSET_COLUMNS.CONTAINER_ID
      AND UPPER(
        COL_TERMS.ASSOCIATED_ARTIFACT_TYPE
      ) = 'GLOSSARY_TERM'
      LEFT JOIN zoneschema.GOVERNANCE_ARTIFACTS COL_TERM_DETAILS ON COL_TERM_DETAILS.ARTIFACT_ID = COL_TERMS.ASSOCIATED_ARTIFACT_ID
      LEFT JOIN globalschema.USER_PROFILES U ON U.USER_ID = COL_TERMS.ASSIGNED_BY
    ORDER BY
      PROJECT,
      MDE_NAME,
      ASSET_NAME,
      COLUMN_NAME
    
    
  • SELECT
      PROJECTS.NAME PROJECT,
      MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
      MDE_ASSET_DETAILS.NAME ASSET_NAME,
      MDE_ASSETS.REVIEWED_ON,
      MDE_ASSET_COLUMNS.NAME COLUMN_NAME,
      MDE_ASSET_COLUMNS.REVIEWED_ON COL_REVIEWED_OM,
      COL_DC_DETAILS.NAME COL_DC_NAME,
      COL_DC.ASSIGNMENT_STATE COL_DC_STATE,
      COL_DC.ASSIGNMENT_DATE COL_DC_ASSIGNMENT_DATE,
      U.USER_NAME COL_DC_ASSIGNED_BY,
      COL_DC.CONFIDENCE COL_DC_CONFIDENCE
    FROM
      zoneschema.METADATA_ENRICHMENTS MDE
      INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
      INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
      INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
      AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
      INNER JOIN zoneschema.CONTAINER_DATA_ASSET_COLUMNS MDE_ASSET_COLUMNS ON MDE_ASSET_COLUMNS.ASSET_ID = MDE_ASSETS.ASSET_ID
      AND MDE_ASSET_COLUMNS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
      LEFT JOIN zoneschema.DATA_ASSET_COLUMN_ARTIFACT_ASSOCIATIONS COL_DC ON COL_DC.NAME = MDE_ASSET_COLUMNS.NAME
      AND COL_DC.ASSET_ID = MDE_ASSET_COLUMNS.ASSET_ID
      AND COL_DC.CONTAINER_ID = MDE_ASSET_COLUMNS.CONTAINER_ID
      AND UPPER(
        COL_DC.ASSOCIATED_ARTIFACT_TYPE
      ) = 'DATA_CLASS'
      LEFT JOIN zoneschema.GOVERNANCE_ARTIFACTS COL_DC_DETAILS ON COL_DC_DETAILS.ARTIFACT_ID = COL_DC.ASSOCIATED_ARTIFACT_ID
      LEFT JOIN globalschema.USER_PROFILES U ON U.USER_ID = COL_DC.ASSIGNED_BY
    ORDER BY
      PROJECT,
      MDE_NAME,
      ASSET_NAME,
      COLUMN_NAME
    
    
  • SELECT
      PROJECTS.NAME PROJECT,
      MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
      MDE_ASSET_DETAILS.NAME ASSET_NAME,
      MDE_ASSET_DETAILS.ASSET_TYPE,
      CATALOG.NAME PUBLISHED_TO_CATALOG
    FROM
      zoneschema.METADATA_ENRICHMENTS MDE
      INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
      INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
      INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
      AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
      LEFT JOIN zoneschema.CONTAINERS CATALOG ON CATALOG.CONTAINER_ID = MDE_ASSETS.PUBLISHED_TO_CONTAINER_ID
    
    

Data Privacy dashboard queries

  • -- Join from data assets in a container to associated governance artifacts (glossary terms and data classes),
    -- then from governance artifacts to classifications to determine items that are classified as Personal Data.
    -- This query also retrieves data protection rules that are associated with the governance artifacts.
    SELECT
      c.NAME container_name,
      c.CONTAINER_TYPE container_type,
      tab.name table_name,
      col.name col_name,
      gov.name gov_artifact_name,
      gov.artifact_type,
      col_artifact_xref.assignment_state,
      classif.name classification,
      er.NAME rule_name,
      er.ACTION_NAME rule_action
    FROM
      zoneschema.CONTAINERS c
      INNER JOIN zoneschema.container_assets tab ON c.CONTAINER_ID = tab.CONTAINER_ID
      INNER JOIN zoneschema.container_data_asset_columns col ON col.asset_id = tab.asset_id
      AND col.container_id = tab.container_id
      INNER JOIN zoneschema.data_asset_column_artifact_associations col_artifact_xref ON col_artifact_xref.asset_id = col.asset_id
      AND col_artifact_xref.container_id = col.container_id
      AND col_artifact_xref.name = col.name
      INNER JOIN zoneschema.governance_artifacts gov ON col_artifact_xref.associated_artifact_id = gov.artifact_id
      LEFT OUTER JOIN zoneschema.governance_artifact_associations gov_gov_xref ON (
        gov.artifact_id = gov_gov_xref.end2_artifact_id
        AND GOV_GOV_XREF.END2_ARTIFACT_TYPE = 'glossary_term'
        AND GOV_GOV_XREF.END1_ARTIFACT_TYPE = 'classification'
      )
      OR (
        gov.artifact_id = gov_gov_xref.end1_artifact_id
        AND GOV_GOV_XREF.END1_ARTIFACT_TYPE = 'data_class'
        AND GOV_GOV_XREF.END2_ARTIFACT_TYPE = 'classification'
      )
      LEFT OUTER JOIN zoneschema.governance_artifacts classif ON (
        classif.artifact_id = gov_gov_xref.end1_artifact_id
        AND classif.artifact_type = 'classification'
        AND classif.name LIKE '%Personal%'
      )
      OR (
        classif.artifact_id = gov_gov_xref.end2_artifact_id
        AND classif.artifact_type = 'classification'
        AND classif.name LIKE '%Personal%'
      )
      LEFT OUTER JOIN zoneschema.ARTIFACT_ENFORCEMENT_RULE_ASSOCIATIONS aera ON aera.ARTIFACT_ID = gov.ARTIFACT_ID
      LEFT OUTER JOIN globalschema.ENFORCEMENT_RULES er ON ER.RULE_ID = aera.RULE_ID
    
    

Parent topic: Setting up reporting for IBM Knowledge Catalog

Generative AI search and answer
These answers are generated by a large language model in watsonx.ai based on content from the product documentation. Learn more