13 Replies Latest reply on May 3, 2017 2:56 PM by mushoku

    Query to show incidents at a specific escalation


      All our Incident response levels have an escalation at 90% to change the colour, what I am trying to do is to produce a query that will show all the incidents that have reached this escalation (ie that escalation has expired) but have not reached the next (100% and breached)



      I have created a query on IM_INCIDENT_ESC_POINT, and used the following critria


      Action.Action Time Is Equal to (90)

      Incident.Is Breached Is Equal To (False)

      Incident.Status.Is Active Is Equal To (True)


      which is getting there, but we have a couple of other escalations that are at 90 minutes meaning we get a couple of ring-ins....


      I then tried to add the criteria


      Action.Action Time Type Is Equal To (0)      (or = 1)


      but when I run the query I get an error:


      Results view encountered the following issue and cannot run: Input string was not in correct format


      If I then Try and modify that query, I get the error:

      System.FormatException: Input string was not in a correct format.


      and can't run or modify it.


      Am I approaching this the right way?


      I had originally tried to query on the Incident Colour, but most of the colours are represented by a negative number which the Query designer does not let you enter......



      Another related idea was to create a pie chart on a welcome screen that showed the number of incidents at each escalation, and be able to double click on a given segment and see all the incidents in that status.


      A query on the Incident object that includes the Colour can be grouped by colour in a pie chart, but the labels are just the numeric values for the colour..... which is annoying but not a show stopper.  However, again may of these numeric values are negative (eg orange = -32704) and when you double click on them you get an error:


      Results view encountered the following issue and cannot run: Value of '-32704'  is not a valid for 'Value'.  'Value' should be between 'Minimum' and 'Maximum'.  Parameter name: Value


      Double clicking works fine on White (numeric value = 16777215)






        • 1. Re: Query to show incidents at a specific escalation

          Oops, should say we're on 7.25

          • 2. Re: Query to show incidents at a specific escalation
            aparker Employee



            For your specific situation, the easiet solution may well be to write the query to test for the value of the Incident.Colour attribute. If it equals the colour number for the red it's one you want. You'll need to add a couple more criteria to ensure it's still a live incident, but it should work.


            Having said that, the way you are looking to do it is the technically correct way and having just added some info to the other htread about breach call reporting, I should really be pushing you down that path. The issue you are having is with enumeration of the database value. If you write a query to show the Action Time Type as a column, what value do you see? Have you tried to use that value in the query rather than a 1 or 0? If you still have those errors, please log this with support as it is a bug.



            • 3. Re: Query to show incidents at a specific escalation

              I'd love to test the incident.colour attribute, but the colours have negative numbers and SD (TP) wont let me test for negative numbers....


              I will test the action.time type and see what it shows as in a column, but again it will only allow us to test for positive numeric values...





              Message was edited by: Stuart Walker [typo]

              • 4. Re: Query to show incidents at a specific escalation

                OK, I stuck the Action TIme Type in a column, the one's I'm after are shown as 'Percentage' however when I add Action Time Type as a criteria I only get the option to test for numerical value.


                Also, if I tell it to prompt user, I can enter a '1' in and it works.  If I put the value '1' into the criteria directly I get the same error as before.


                Will be logging with our provider (Hi Ben & David!)





                • 5. Re: Query to show incidents at a specific escalation



                  Hayden at HelpDesk Solutions has pointed the way;


                  instead of using


                  Action.Action Time Type Is Equal To (1)




                  Action.Action Time Type Is One Of   (1)




                  Can't use the same work around on the Incdent.colour though, as you still can't test for negative numbers

                  • 6. Re: Query to show incidents at a specific escalation
                    aparker Employee

                    HI Guys,


                    Just as a totally different approach that I have implemented to measure the point at which the call gets resolved, how about using the Priority attribute to reflect the progression through the service level. I admit, this will only work if you are not using the global priority field that can be set through an escalation action.


                    What we did was set up ten priorities representing 10% increments through the SL life. There is an escalation created to move through those as the SL progresses. When the call is completed the priority that the call is at shows that state. We then wrote some nice reports to show the profile of completion. This could actually be represented on the dashboard with a bar or pie chart.



                    • 7. Sorting of the workload list if not using the Priority attribute
                      elizabethcombrink Expert

                      Hi Andy,

                      This sounds like a really good way to actually see what the profile of your closed calls are in terms of where in their service level they have been resolved if resolved within service level.  Very useful if you need to identify where your stated service levels are being achieved by a mile - Good for the customer but can help in identifiing where people possibly do not have enough to do!


                      I currently use the Priority attribute to sort my workload list.  Any suggestions on how to do it differently so that I can use the Priority field as you have suggested.  I want to sort the workload list based on next call to breach.  Any suggestions on how to achieve this in any of 7.2.4 / 7.2.5 / 7.2.6?

                      • 8. Re: Query to show incidents at a specific escalation
                        aparker Employee

                        Hi Elizabeth,


                        You could always use the Order value on the Escalation to sort the workload list. You could actually use this to measure the progress as well. It's really just a matter of identifiying a unique attribute that can be changed and does not affect any other reporting or management of calls.



                        1 of 1 people found this helpful
                        • 9. Re: Query to show incidents at a specific escalation

                          Hy Stuart;


                          I was doing the same today... but I went on filtering the incidetns on specific color. I had the same problem, negative number, its not a valid value. Tried some different combinations, none of them worked.


                          So did you find any work-around on this?


                          *Here the query what i have is showing some 700 records, where some of them are on GREEN color, and rest on RED. I need to show only RED ones.


                          So, is this possible in 7.2.6?

                          • 10. Re: Query to show incidents at a specific escalation

                            I actually work around it a different way.


                            I wanted the colour so we could easily find calls at 90% of the SLA  - we have an escalation at 90% that turns the incidents orange, and while I couldn't test for the colour, I WAS able to test for the 'Order' that was set by the same escalation.  The catch was that I had to test for all the 'order' values for 90% in all of our SLA's.


                            so my criteria looks like:

                            Order Is One Of ( 900, 1400, 1900.......etc)

                            AND Status.Title Is Not One Of ( Closed, Resolved)



                            Not as neat, but it gets there in the end.



                            • 11. Re: Query to show incidents at a specific escalation
                              masterpetz ITSMMVPGroup

                              Hi Stuart,


                              our customer had the same need. He wants queries on the welcome page for the different colors which represents different escalation parts. So i ran in the same problem as you, that colors are negativ values. So my workaround was, i created a new attribute in the pm_process called colorcode with type string. After that, i created an update trigger on the pm_process table, every time, the original color attribute changes, update my colorcode attribute. Because of its a string, you now can query on the negativ values.

                              I will attach you the trigger, maybe its helpful. Trigger is running about 8 month without any problems but you should test it first in an test environment, if it fits your needs.


                              Best regards


                              • 12. Re: Query to show incidents at a specific escalation
                                aparker Employee

                                Hi All,


                                Although there is nothing wrong in one sense with what Christian has suggested, I would strongly advise against introducing triggers anywhere in the database. They are a means to an end, but they are not supported and if there is a change in the code that for some reason impacts their operation, support is not obliged to spend time fixing it for you and more importantly if you like, how long might it take for you to realise that there is actually a problem with your system.


                                This discussion raises a really important issue that can be related to all of the ITBM product and that is the one of understanding as much as possible at design time in order to incorporate the necessary data that would be needed for reporting at a later date. Now, I'm not going to sit here and suggest that we are able to predict every possible occurrence, but conceptually this is very important.


                                Having said all of that, there a couple of alternatives that you can look at to sort out this query. Yes, the use of the order value is one, the second is the use of the priority value. Again, you need to be in a position where you are not using the Priority attribute within the system for anything else. If that is the case, then you can set the Priority to be a consistent value for all SLA's that reach the 90% limit. Then it only requires one criteria in your query.


                                The other approach which is a bit more complex and may not apply in this case, is choosing the right business object to write the query from. It may well possible to achieve what you are looking to do by writing the query on the Escalation Point object and criteria relating to that rather than basing it on Incident. As I don't know the exact design of your SLA's, I couldn't comment specifically on that.



                                • 13. Re: Query to show incidents at a specific escalation
                                  mushoku Apprentice

                                  I know this is an old post, I found it and was able to determine a solution from this.  I used a similar solution to the colorcode attribute, just didn't use a SQL trigger for this.

                                  Instead, I created the ColorCode attribute (String - 20) on the Process object as a Before Save calculation.  There are a number of ways to do the calculation:

                                  - Return the Colour attribute with .ToString() -- works great for queries, not so great if you will ever display this info

                                  - Return a string like "%%% - Color" (e.g., "050 - Yellow") -- lets you display the information and sort on it, great for displaying information in grouped queries, like "tickets assigned to me, grouped by colorcode", otherwise you have groupings named "-1" and "-65536" which mean nothing to any who doesn't have the color numbers memorized


                                  Obviously, I opted for the latter.  I can now generate pie charts of "my/group un/assigned tickets" that are grouped by escalation point (color).  I can, of course, also query for any tickets that are >=50% and <100%, for example.


                                  More info: How To: Create a Query to see Incidents Breaching.