12 Replies Latest reply on Apr 11, 2012 10:09 AM by karenpeacock

    Querying breach time and breached group




      I have been asked to create a query that states all incidents that have breached today and which group was the incident with when it breached.


      They are wanting this run at the end of the day so they are aware of which group the incident has breached with, can this be done in Service Desk? 9 times out of 10 the current assignment.group will be the same as the group it has breached with but sometimes an incident will breach and this will be assigned elsewhere afterwards.


      Is there a way to capture the group it has breached with?




        • 1. Re: Querying breach time and breached group
          karenpeacock SupportEmployee

          Hi Matt


          There have been a couple of similar discussions about this in the past.  Here are a couple of links which may be of interest to you:






          Best wishes


          • 2. Re: Querying breach time and breached group

            Hi Karen,


            Thats for the response, this will be a really handy KPI, I'm having an issue with the placeholders though which is mentioned in one of the attached articles.


            I enter a placeholders for the Group and User and select a role that no one uses but when I click 'OK' and reopen the escalation action it has only saved the selected role and removes the placeholders?




            • 3. Re: Querying breach time and breached group
              karenpeacock SupportEmployee

              Hi Matt


              You are right, I've tested and I am afraid that the software doesn't seem to accept the placeholder as being valid.  I can't see a way around this using the escalation method that was suggested as it is checking for a valid reference to an existing record in the group table.


              Another idea I have is to have a bulk action which runs against a query to select incidents where the breach time is within the last hour.  This then runs an action which sets a value stored on the incident to the runtime value for CurrentAssignment/Group.  The bulk action could be scheduled to run hourly at the most frequent , however this would mean that if the current group is changed within an hour of the incident breaching it would record the wrong value.


              Perhaps someone else has found a neater solution for this and would be prepared to share it?


              Best wishes


              • 4. Re: Querying breach time and breached group

                Hi Matt/Karen


                I'm probably the bearer of bad news!!  The 2nd link Karen posted was started by an ex employee of ours - unfortunately we still haven't been able to get this sorted.  It's currently with support to see if they can figure out the crystal report for us.  Ours is really tricky as there is an assignment OLA of 15 mins that can breach multiple times on the one Incident and we haven't been able to get the report to pull back the correct info on which team it breached with, it keep showing as the team the Incident was assigned to!


                Not sure if you've seen this ER but I think what Julian is proposing would certainly make life easier





                • 5. Re: Querying breach time and breached group
                  karenpeacock SupportEmployee

                  Helen, thanks very much for stepping in to let us know.


                  Matt (and also Helen perhaps) I've been having a bit of a think about this and I think I may have come up with a different route we can take in order to capture this information.


                  My idea is that you store on the incident assignment record whether or not the incident was already breached when the assignment was made.  Then all that needs to be done is that you report on incidents that are breached and the assignments that were made before the incident breached.  That means that if you bring back the maximum assignment serial number (of those assignments made before the incident breached) then you have got yourself the assignment that was active when the incident breached.


                  Hopefully that paragraph makes sense if you read it a few times - I realise it's kind of back-to-front logic to how we were approaching this before!


                  This is something that would only work going forwards after you make a couple of pretty simple design changes.  I've done a bit of testing and it looks like it works as I expect it to.  It uses a very basic calculation and I've also hastily put together a Crystal report to prove that you can actually get the information from this that you want, so if you think that this sounds of interest to you I can give you the report and steps to do this.  I just need to know the version of software you are running first so that I make sure my Crystal report is compatible with this.


                  Best wishes,


                  • 6. Re: Querying breach time and breached group

                    Hi Karen / Helen,


                    Thanks for your responses, Karen we are currently using 7.4, if possible that would be great to have the Crystal report.




                    • 7. Re: Querying breach time and breached group

                      Hi Karen


                      We're on 7.4 as well



                      • 8. Re: Querying breach time and breached group
                        gramsay Specialist

                        This works for us:


                        SELECT     im_incident_1.im_id, dbo.tps_group.tps_title AS Team, im_incident_1.im_breach_time
                        FROM         dbo.pm_process_assignment INNER JOIN
                                              dbo.tps_group ON dbo.pm_process_assignment.pm_group_guid = dbo.tps_group.tps_guid INNER JOIN
                                                  (SELECT     pm_process_guid, MAX(AssignmentSerial) AS AssSerial
                                                    FROM          (SELECT     pm_process_assignment_1.pm_serial_number AS AssignmentSerial, pm_process_assignment_1.pm_process_guid
                                                                            FROM          dbo.im_incident INNER JOIN
                                                                                                   dbo.pm_process AS pm_process_1 ON dbo.im_incident.pm_guid = pm_process_1.pm_guid INNER JOIN
                                                                                                   dbo.pm_process_assignment AS pm_process_assignment_1 ON
                                                                                                   pm_process_1.pm_guid = pm_process_assignment_1.pm_process_guid AND
                                                                                                   dbo.im_incident.im_breach_time > pm_process_assignment_1.pm_creation_date
                                                                            WHERE      (dbo.im_incident.im_is_breached = 1)) AS a
                                                    GROUP BY pm_process_guid) AS b ON dbo.pm_process_assignment.pm_serial_number = b.AssSerial AND
                                              dbo.pm_process_assignment.pm_process_guid = b.pm_process_guid INNER JOIN
                                              dbo.im_incident AS im_incident_1 ON dbo.pm_process_assignment.pm_process_guid = im_incident_1.pm_guid

                        • 9. Re: Querying breach time and breached group

                          Impressive Graham - would you have the time to outline how this works?

                          • 10. Re: Querying breach time and breached group
                            karenpeacock SupportEmployee

                            That is indeed very neat, works on historical data (as long as you have the is_breach flag being set at the right time, more information on this in my article link below), and from a quick look seems to me to work very well.  More information on the logic would be great and if you don't mind Graham I would then like to include it in an article.


                            (Or in the words of Burglar Bill "I'll 'ave that!" )


                            If you are still interested in the design / report solution that I mentioned, then I've written a quick article on how this would work:

                            Reporting against the group that the incident was assigned to when it breached


                            Best wishes


                            • 11. Re: Querying breach time and breached group
                              gramsay Specialist

                              The first thing to say is that I'm no SQL expert and I just knocked it up out of curiosity. I'm sure it could be improved/tidied up.


                              It relies on the following:

                              • Breach time being present in "dbo.im_incident.im_breach_time"
                              • The breach flag being set in "dbo.im_incident.im_is_breached"


                              We're looking for the last assignment created before the breach time for all Incidents that have breached. So the steps are:


                              1. Get a list of assignments created before their Incident breach time
                                "dbo.im_incident.im_breach_time > dbo.pm_process_assignment.pm_creation_date"
                                Where the Incident has breached
                                "dbo.im_incident.im_is_breached = 1"
                              2. Group these assignments by Process GUID
                                "GROUP BY pm_process_guid"
                                Return the newest assignment for each Process using the assignment serial number
                                "MAX(AssignmentSerial) AS AssSerial"
                              3. Join that result (Will be a list of process GUIDs and Assignment serial numbers) back to the various tables to get Incident number, breach time and assigned group


                              So far it appears to work fine for our set up.

                              • 12. Re: Querying breach time and breached group
                                karenpeacock SupportEmployee

                                Thanks very much for sharing your better logic.  I have updated my article Reporting against the group that the incident was assigned to when it breached to include a view which is a slight change on your select statement and based my report on this view now.  This should be much simpler to implement and will also work on historic data as long as you already had the is_breach flag working correctly.


                                I've done a little testing but not exhaustive so if anyone has any feedback or hits any issues please do let me know.