Jira Align Enterprise Insights: Date Closed is Null for Closed Risks

Summary

In the Enterprise Insights (EI) current_dw.Risk table, some Risk records may show a Risk Status of "Closed" while the Date Closed field remains NULL. This typically occurs for older records or records where the state transition did not correctly trigger the internal timestamp update in Jira Align.

Diagnosis

Before proceeding with a fix, perform the following checks to confirm the behavior:

1. Database Verification: Run the following query in your Enterprise Insights environment to identify affected records:

   SELECT [Risk ID], [Risk Status], [Date Created], [Date Closed]FROM current_dw.RiskWHERE [Risk Status] = 'Closed' AND [Date Closed] IS NULL;

2. Scope Check: Determine if the issue is systemic or isolated.

   • Test New Records: Create a new test Risk in Jira Align and move it to Closed. If the Date Closed populates in EI after the next sync, the issue is isolated to specific existing records.

   • Audit Log Review: In Jira Align, check the Audit Log for an affected Risk ID. If the transition to "Closed" is missing a corresponding timestamp or was performed via a bulk action/API that bypassed standard triggers, the date will not sync to EI.

3. Integration Check: Verify if the Risk is integrated with Jira. If the Risk status is driven by a Jira issue, ensure the Jira issue has a Resolution Date populated. If the Jira resolutiondate is null, Jira Align may not receive the necessary timestamp to pass to EI.

Cause

The Date Closed field in Enterprise Insights is populated based on the internal timestamp recorded when a Risk transitions to the "Closed" state in Jira Align. If a record was transitioned during a period of sync instability, or if it was set to "Closed" at the moment of creation without a proper transition event, the timestamp may not be captured, leading to a NULL value in the data warehouse.

Solution

To resolve the NULL values for affected records, you must force a refresh of the state transition timestamp in Jira Align.

1. Toggle the Status:

   • Log in to Jira Align.

   • Open the affected Risk record.

   • Change the Status from Closed to another state (e.g., Open or Resolved).

   • Save the record.

   • Immediately change the Status back to Closed and Save again.

2. Verify Sync:

   • Wait for the next Enterprise Insights sync cycle (typically hourly).

   • Re-run the SQL query to confirm the Date Closed field is now populated with the timestamp of the manual transition.