Jump to content

Mean Time between Major Incidents


chrisnutt

Recommended Posts

Hi All,

I'm trying to create a measure (or widget) that will tell me what the current mean time between all incidents with a priority of Immediate - what we use to determine if it's an MI - and I don't even know where to start with this one! Any help will be greatly appreciated.

More than happy to expand on what I'm trying to accomplish if this is not clear.

Many thanks!

Chris

Link to comment
Share on other sites

16 hours ago, chrisnutt said:

Hi All,

I'm trying to create a measure (or widget) that will tell me what the current mean time between all incidents with a priority of Immediate - what we use to determine if it's an MI - and I don't even know where to start with this one! Any help will be greatly appreciated.

More than happy to expand on what I'm trying to accomplish if this is not clear.

Many thanks!

Chris

Hi @chrisnutt

Just to clarify, when referring to the (mean) time, are you looking for the duration of time a request was open between it being logged and now? And would this just be for currently open incidents or for requests that have been resolved (in which case the time may be between the log date/time and resolution date/time)?

Kind Regards

Bob

Link to comment
Share on other sites

Hi @Bob Dickinson ,

I am looking for the time between major incidents.

For example: Let's say that in the time since measurements began, for the purposes of this example we'll say 1 January 2017, we have had 4 MIs on the following dates:

1 March 2017 (Day 60 of 2017)

26 May 2017 (Day 146, 86 days since the last MI)

30 June 2017 (Day 181, 35 days since the last MI)

31 July 2017 (Day 212, 31 days since the last MI)

and today is August 30, 2017, (Day 242, 30 days since the last MI) and is so far MI free!

I want to see the mean/average of the intervals. So in this example, it is 48.4. days (60+86+35+31+30=242/5). The number doesn't necessarily need to be days. It could be seconds.

This will be extremely useful to track, as a decreasing number indicates, well entropy basically, and an increasing number indicates increasing order and stability. I'd like to place it on the IT Management dashboard I'm building for senior managers.

I currently do as @m.vandun suggests and work this out manually but was hoping to do it automatically.

Hope that makes sense.

Chris

Link to comment
Share on other sites

Hi @chrisnutt

Ok thanks for the explanation.

This is a very complex query to formulate so unless you know a little SQL is a difficult one to quickly establish. Its just taken me quite some time, but I THINK I have managed to get something that may be suitable for you. 

Firstly this is performed in Advanced Analytics - and you will be creating a "List of Counters" Widget:

Screenshot_15.png

 

The following screenshot shows the finished article, but to get to this when you create it, you need to add some text and an icon, and then click the edit button:

Screenshot_13.png

 

This gives you the screen to enter the data for the widget. Try and enter the following based on the appropriate locations in the screenshot below

1

Avg 

2

(TIMESTAMPDIFF(second,h_datelogged,NextDate))

3 - 

  (SELECT h_pk_reference,
          h_ownerid,
		  h_datelogged,

     (SELECT Min(h_datelogged)
      FROM h_itsm_requests T2
      WHERE T2.h_datelogged > T1.h_datelogged
        AND h_fk_priorityname= 'Immediate'
        AND h_requesttype = 'Incident' ) AS NextDate
   FROM h_itsm_requests T1
   WHERE h_fk_priorityname= 'Immediate'
     AND h_requesttype = 'Incident' ) AS AllDays

The text: h_fk_priorityname= 'Immediate' and h_requesttype = 'Incident'    which is mentioned twice in number 3 above, is specific criteria for your organisation relating to the tickets you want to compare the dates between - above you mentioned this is all incidents of a particular priority (Immediate) so I have included that in the example...but you may want to add to/amend these criteria

 

Screenshot_16.png

I hope this helps. Let me know if you run into any difficulties (I ran into plenty during the investigation of this! :wacko:

Kind Regards

Bob

 

Link to comment
Share on other sites

Hi @Bob Dickinson

I really appreciate you taking the time to look into this. When I was blankly staring at the screen trying to figure out how to do it I realised how complex of an ask it was and was certainly beyond my abilities!

I've added it as per your instructions and it seems to work well.

Thank you very much! 

Chris

Link to comment
Share on other sites

  • 5 months later...

hi @Bob Dickinson

I am trying to create a similar report/ widget. I am interested on meantime, between Major Incidents, within a service.

So for a example, Service X had a Major Incident last week, Then had another today, i want to get the meantime in days, between these two MI.

We Identify our MI with a word 'MAJOR INCIDENT ' in the ticket summary, and have different services .

I have try to put h_summary like 'MAJOR INCIDENT' and I am getting 0 value. how can I filter it properly?, and get meantime in days?

 

Thanks.

Regards

Joyce

Link to comment
Share on other sites

  • 4 years later...

All,
Please DO NOT Use the above method.  A far better method is to use the MySQL Windows fucntions. The below returns the Average difference between calls logged where Summary like 'Test%.. This can be changed to meet your requirements.  

https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html


MariaDB [hornbill]> select avg(datediff(h_datelogged, lag_event_date)) avg_diff from ( select  t.*, lag(h_datelogged) over(partition by h_summary order by h_datelogged) as lag_event_date     from h_itsm_requests t where
 h_summary like 'TEST%' ) t;
+----------+
| avg_diff |
+----------+
|   0.2857 |
+----------+

Kind Regards
 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...