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:
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?
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?
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
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.
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.
We're on 7.4 as well
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
Impressive Graham - would you have the time to outline how this works?
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:
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:
- 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"
- 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"
- 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.
- Breach time being present in "dbo.im_incident.im_breach_time"
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.