MI Reporting

[AbhiShell]

Hi Team,
Recently we came across a typical scenario where we found some unrealistic numbers in MI utilization Daily /monthly table. Surprisingly this is a known issue and we are having below articles to address it.

Why does the utilization of a runtime resource exceed a maximum of 100%? :

However, I found below point need to be considered for further enhancement.

> There should be a unique combination of process and resource ID in  Shadow table . There should not be duplicate session with no -end time to previous one. 
if so, then its obvious that there is a problematic session which will lead to additional secs logged to the tables.

This query helps to identify problematic instances.  

SELECT MI1.*

FROM [dbo].[BPMIUtilisationShadow] AS MI1

WHERE MI1.enddatetime IS NULL

  AND EXISTS (

    SELECT 1

    FROM [dbo].[BPMIUtilisationShadow] AS MI2

    WHERE MI1.resourceid = MI2.resourceid

      AND MI1.processid = MI2.processid

      AND MI1.sessionid <> MI2.sessionid

      AND MI1.startdatetime < MI2.startdatetime

      AND MI2.enddatetime IS NOT NULL

  );

ASK:- 
Could you please modify the select query to an update query and add it to utilization stored procedure with parameters like 
Use feature - Yes/No
if yes , Then what is the action:- 
Delete - Delete problematic sessions
Update- Update problematic sessions with a standard endtime (Ex:- add 15 mins to start time)

This should be amended before running utilization Daily & Monthly stored Procedures.
happy to support and clarify further queries. 
Appreciate your time to consider and FastTrack.