Tickets assigned to DSDS→ Service Type: "User Service Restoration","Security incident","user service request"
For the site view, based on the user log_in, the site of the user will be detected and the incidents created only in the similar site will be considered for the calculation.
Sources:
prj-data-dm-dt-dev.ODS.ODS_HLX_0000_F001_I_H_workorder
Note: Only two sites to be considered for the reporting at this stage. For that, we need to consider tickets that are raised by the users based on the corresponding site. "Site ID": "STE000000003884", What are the codes of the sites to involve?
The dashboard's refresh frequency is set to hourly, updating every hr.
The data for the dashboard is sourced from a daily snapshot stored in BigQuery.
| Terminology | Description | Source | Notes | Existing/ to develop |
|---|---|---|---|---|
| Backlog incidents | All incidents excluding the ones with "Status": "Resolved" AND "Canceled" per site. | "Status" | Status: Resolved | Existing |
| Mean time to resolve incidents=MTTR=Total time spent resolving incidents/number of incident |
| "Last Resolved Date" "Submit date" |
prj-data-dm-dt-dev.DS_DT_Dashboard.V_DIM_Incident
prj-data-dm-dt-dev.DS_DT_Dashboard.V_DIM_Status
prj-data-dm-dt-dev.DS_DT_Dashboard.V_DIM_UserName_Site
SELECT Incident_Number, status, Priority, Last_Resolved_Date, Service_Type, Site, Assignee_Login_ID, Assignee, Submit_Date, FROM `prj-data-dm-dt-dev.DS_DT_Dashboard.V_DIM_Incident
| Context | Indicators/graphs | Description | Visualization block (view) |
|---|---|---|---|
| Incident | Indicator: Backlog incidents today | The number of incidents excluding the ones with "Status": "Resolved" AND "Canceled" globally in the back log today | Global |
| Incident | Graph: Backlog incident last seven days (daily) | The number of backlog incidents per day globally over the last seven days | Global |
| Incident | Indicator: Submitted incidents today | The number of incidents globally with "Submit date" = today | Global |
| Incident | Graph: Submitted incident last seven days (daily) | The number of Submitted incidents per day globally over the last seven days | Global |
| Incident | Indicator: Backlog incident per site | The number of incidents excluding the ones with "Status": "Resolved" AND "Canceled" per site | Site |
| Incident | Indicator: Backlog incident per site | The number of Backlog incidents with "Priority"= "Critical" | Site |
| Critical incidents weekly growth rate | Calculates the number of critical incidents in yesterday and the number reported by the day before yesterday. Then, computes the ratio by dividing the count of yesterday's critical incidents by the count of backlog incidents of the day before yesterday. Repeat the calculation for the seven last day. | ||
| Oldest critical incidents (Time) | Descoped | ||
| Created tickets last 7 days | Created incidents: the number of backlog incidents where the "Submit Date" falls within the specified calendar day. To be reported in the dashboard for the last seven days | ||
| Resolved incidents | Backlog incidents with "last resolved day" falls within the specified calendar day. | ||
| Resolved incident daily - last 7 days | Backlog incidents with "last resolved day" falls within the specified calendar day. To be reported in the dashboard for the last seven days | ||
| MRTT of resolved incident | Mean time to resolve incidents=MTTR=Total time spent resolving incidents/number of incident 11)Retrieve the incident numbers with the 'Last Resolved Date' being yesterday. 2)Obtain the 'Submit Date' of each incident. 3)Calculate the Time to Resolve by subtracting the 'Submit Date' from the 'Created Date' for each incident. 4) Sum up the time to resolve for all incidents resolved yesterday. 5)Calculate the Mean Time to Resolve | ||
| MRTT daily growth rate |