chrisnutt Posted August 29, 2017 Share Posted August 29, 2017 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 More sharing options...
m.vandun Posted August 30, 2017 Share Posted August 30, 2017 @chrisnutt Unfortunately it is not possible to measure the mean . You can only measure an average. Hope this is something that will change overtime. You could of course create a manual report and calculate the mean via Excel. Link to comment Share on other sites More sharing options...
Guest Posted August 30, 2017 Share Posted August 30, 2017 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 More sharing options...
chrisnutt Posted August 30, 2017 Author Share Posted August 30, 2017 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 More sharing options...
Guest Posted August 30, 2017 Share Posted August 30, 2017 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: 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: 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 I hope this helps. Let me know if you run into any difficulties (I ran into plenty during the investigation of this! Kind Regards Bob Link to comment Share on other sites More sharing options...
chrisnutt Posted August 30, 2017 Author Share Posted August 30, 2017 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 More sharing options...
Joyce Posted January 31, 2018 Share Posted January 31, 2018 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 More sharing options...
Joyce Posted January 31, 2018 Share Posted January 31, 2018 @Bob Dickinson Managed to get some results in secs, is it possible to get the average time in days? Thanks. Regards, Joyce Link to comment Share on other sites More sharing options...
Steven Boardman Posted January 31, 2018 Share Posted January 31, 2018 @Joyce you could try changing the second value to hour, day, month etc (TIMESTAMPDIFF(second,h_datelogged,NextDate)) Link to comment Share on other sites More sharing options...
Joyce Posted February 1, 2018 Share Posted February 1, 2018 thanks, it is working fine now Link to comment Share on other sites More sharing options...
HHH Posted February 5, 2018 Share Posted February 5, 2018 @Cizzling Could this be something for us, modified on for example asset level? 1 Link to comment Share on other sites More sharing options...
Keith Stevenson Posted March 28, 2022 Share Posted March 28, 2022 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now