CMDB Recon Tips (Part 2) - Find and Fix faulty CI associations in ITSM tickets

Version 3
    Share This:

    This is the second part of my small quality series on the CMDB, the Recon, and the associations in ITSM tickets.

     

    Part 1 - Find and Fix Errors Through Recon Processes [Fixing part still WIP]

    Part 2 - Find and Fix faulty CI associations in ITSM tickets   [Fixing part still WIP]

    Part 3 - ? more specials ?

     

    Who does not know this, once wrongly identified in the Recon and already it can come to incorrect CI links in ITSM tickets.

     

    In the GUI this is almost undetectable and can only be corrected very rarely. We therefore regularly check our ticket links with the following set of SQL commands to get an overview of the data quality of our links in the ITSM tickets and to correct errors.

    Ideally, you should have NO hits anywhere.

     

    Incident - CI Field

     

    -- HPD HPD_CI - Same Names, Different ReconIDs
    SELECT
        HD.Incident_Number,
        HD.HPD_CI,
        HD.HPD_CI_ReconID,
        BE.Name BE_Name,
        BE.ReconciliationIdentity BE_ReconID
    FROM
        HPD_Help_Desk HD LEFT JOIN BMC_Core_BMC_BaseElement BE ON HD.HPD_CI = BE.Name
    WHERE
        HD.HPD_CI_ReconID != BE.ReconciliationIdentity
    AND BE.DataSetID = 'BMC.ASSET'
    ORDER BY 2;
    
    
    -- HPD HPD_CI - Same ReconIDs, Different Names
    SELECT
        HD.Incident_Number,
        HD.HPD_CI,
        HD.HPD_CI_ReconID,
        BE.Name BE_Name,
        BE.ReconciliationIdentity BE_ReconID
    FROM
        HPD_Help_Desk HD LEFT JOIN BMC_Core_BMC_BaseElement BE ON HD.HPD_CI_ReconID = BE.ReconciliationIdentity
    WHERE
        HD.HPD_CI != BE.Name
    AND BE.DataSetID = 'BMC.ASSET'
    ORDER BY 2;
    
    
    -- HPD HPD_CI - Selected CI, but not (anymore) in Asset Dataset
    SELECT
        HD.Incident_Number,
        HD.HPD_CI,
        HD.HPD_CI_ReconID
    FROM
        HPD_Help_Desk HD
    WHERE
        HD.HPD_CI_ReconID IS NOT NULL
    AND NOT EXISTS
        (
            SELECT ReconciliationIdentity FROM BMC_Core_BMC_BaseElement 
            WHERE HD.HPD_CI_ReconID = ReconciliationIdentity AND DataSetID = 'BMC.ASSET'
        )
    ORDER BY 2;
    

     

    Solution : coming soon

     

    Incident - CI Associations

     

    -- HPD-Assoc - Same Names, Different ReconIDs
    SELECT DISTINCT
        HA.Request_ID02 HPD_Incident,
        HA.Request_Description01 HPD_CI,
        HA.Request_ID01 HPD_CI_ReconID,
        BE.Name BE_Name,
        BE.ReconciliationIdentity BE_ReconID
    FROM
        HPD_Associations HA
        LEFT JOIN BMC_Core_BMC_BaseElement BE ON HA.Request_Description01 = BE.Name
    WHERE
        HA.Request_ID01 != BE.ReconciliationIdentity
    AND BE.DataSetID = 'BMC.ASSET'
    ORDER BY 2;
    
    
    -- HPD-Assoc - Same ReconIDs, Different Names
    SELECT DISTINCT
        HA.Request_ID02 HPD_Incident,
        HA.Request_Description01 HPD_CI,
        HA.Request_ID01 HPD_CI_ReconID,
        BE.Name BE_Name,
        BE.ReconciliationIdentity BE_ReconID
    FROM
        HPD_Associations HA LEFT JOIN BMC_Core_BMC_BaseElement BE ON HA.Request_ID01 = BE.ReconciliationIdentity
    WHERE
        HA.Request_Description01 != BE.Name
    AND BE.DataSetID = 'BMC.ASSET'
    ORDER BY 2;
    
    
    -- HPD HPD_CI - Linked CI, but not (anymore) in Asset Dataset
    SELECT DISTINCT
        HA.Request_ID02 HPD_Incident,
        HA.Request_ID01 HPD_CI,
        HA.Request_Description01 HPD_CI_ReconID
    FROM
        HPD_Associations HA
    WHERE
        HA.Request_ID01 LIKE 'OI-%'
    AND NOT EXISTS
        (
            SELECT ReconciliationIdentity FROM BMC_Core_BMC_BaseElement
            WHERE HA.Request_ID01 = ReconciliationIdentity AND DataSetID = 'BMC.ASSET'
        )
    ORDER BY 2;
    

     

    Solution : coming soon

     

    Change- CI Associations

     

    --CHG-Assoc - Same Names, Different ReconIDs
    SELECT DISTINCT
        CA.Request_ID02 CRQ_Change,
        CA.Request_Description01 CRQ_CI,
        CA.Request_ID01 CRQ_ReconID,
        BE.Name BE_Name,
        BE.ReconciliationIdentity BE_ReconID
    
    FROM
        CHG_Associations CA LEFT JOIN BMC_Core_BMC_BaseElement BE ON CA.Request_Description01 = BE.Name
    WHERE
        CA.Request_ID01 != BE.ReconciliationIdentity
    AND BE.DataSetID = 'BMC.ASSET'
    ORDER BY 2;
    
    
    --CHG-Assoc - Same ReconIDs, Different Names
    SELECT DISTINCT
        CA.Request_ID02 CRQ_Change,
        CA.Request_Description01 CRQ_CI,
        CA.Request_ID01 CRQ_ReconID,
        BE.Name BE_Name,
        BE.ReconciliationIdentity BE_ReconID
    FROM
        CHG_Associations CA LEFT JOIN BMC_Core_BMC_BaseElement BE ON CA.Request_ID01 = BE.ReconciliationIdentity
    WHERE
        CA.Request_Description01 != BE.Name
    AND BE.DataSetID = 'BMC.ASSET'
    ORDER BY 2;
    
    --CHG-Assoc - Verknüpft mit CI, Recon aber nicht im Asset
    SELECT DISTINCT
        CA.Request_ID02 CRQ_Change,
        CA.Request_Description01 CRQ_CI,
        CA.Request_ID01  CRQ_ReconID
    FROM
        CHG_Associations CA
    WHERE
        CA.Request_ID01 LIKE 'OI-%'
    AND NOT EXISTS
        (
            SELECT ReconciliationIdentity FROM BMC_Core_BMC_BaseElement
            WHERE CA.Request_ID01 = ReconciliationIdentity AND DataSetID = 'BMC.ASSET'
        )
    ORDER BY 2;
    

     

    Solution : coming soon