Providing timely response to issues is a key responsibility for many IT teams. However, we have all experienced times where a wave of issues came in and that well thought out notification rule you wrote can be lost among all of the other e-mails someone might get. Other times a team may be consumed with a project or other release and might not be monitoring their queue as closely as they normally would. Maybe you have Service Level Agreements (SLAs) that you need to meet.
No matter what the reason, knowing how long something has been assigned to a queue can be critical. For awhile I have made available a report to one of the IT managers at our organization that gave us the length of time and incident has been assigned to the queues his team monitors.
Our recent focus on providing better service lead us to expand this to a Notification Policy for his team. However, this notification quickly became lost in the shuffle of other e-mails and no one quite knew how to react to it. We were tasked with coming up with a better way to provide more pertinent information to the teams.
Immediately we decided a better way to do this was to restrict these e-mails to tickets that went unassigned and were outside our acceptable window of four business hours. As many of you know there is not a built in method to track business hours. With a little bit of SQL work we developed a query to subtract non work hours for each day within the period, and 2 days for weeks within the period. I know it is not the prettiest thing in the world, but here is an example of what we are using now.
SELECT hd1.workitem_number AS 'Ticket',
hd1.workitem_title as 'Title',
hd1.assigned_to_worker_name AS Worker,
hd1.workitem_status_lookup_value as 'Status',
(DATEDIFF (minute, hd1.workitem_modified_on, getdate()) / 60.0 -
DATEDIFF (day, hd1.workitem_modified_on, getdate()) * 13.5 -
DATEDIFF (week, hd1.workitem_modified_on, getdate()) * 2 * 10.5) as 'Business Hours',
hd1.workitem_modified_on as 'Date last modified'
FROM dbo.HD_workitem_current_view hd1
WHERE hd1.[workitem_status_lookup_id] != 600
AND hd1.workitem_assigned_to_worker_id = <Queue ID here>
AND (DATEDIFF (minute, hd1.workitem_modified_on, getdate()) / 60.0 -
DATEDIFF (day, hd1.workitem_modified_on, getdate()) * 13.5 -
DATEDIFF (week, hd1.workitem_modified_on, getdate()) * 2 * 10.5) >= 4
ORDER BY hd1.workitem_number
Once we have setup a report to provide us a list of tickets we can now create a Notification Policy to e-mail our workers that information. Notification Policies can be found on the Tasks tab within Notification Server Console 6 and from “View > Tasks” within the 6.5 console. I would recommend putting these policies under the “Incident Resolution > Incidents > Helpdesk > Notification Policies”. Once you have a location simply right click and select “New Notification Policy”.
Make sure to give your new Notification Policy a descriptive name and detailed description. This will help anyone who comes after you understand the reason you have setup and enabled this policy.
Since we set this report up using a direct SQL statement we have two options for our “Source”. We could use the SQL to create a report and point to the report, or we could enter the SQL directly into this notification policy. There are advantages to doing it either way. If you are going to use the report anyway pointing to that report may be a good idea, because you will be able to make a single pointer to that report and any changes made there will update this rule as well. However using a notification policy can limit the ability to run the report as items like dropdowns can prevent your notification policy from working properly.
After selecting a source you will want to define a schedule. For our example we are going to use the default “Business Hours” schedule. A custom schedule can be defined to meet your needs if none of the built in ones exist.
Next you will want to create an action for the policy to take. We have a number of options here including creating a new or existing incident, setting the status on an asset, or an e-mail. For this example you will want to select “E-mail Automated Action” and hit “Add”.
As before, you should give your action a meaningful name and description. You may also enable this action at this time. Here you also have an option to send one e-mail for each row that is returned or just once for the whole thing. For our example we are just sending the e-mail one time. Then fill in the rest of the information as you need.
If you note the screenshot includes a line of text and then another line with %Results%. This will paste the results of your report or query into your message. After you are finished hit okay.
Do note you will need to enable your notification policy before you can test it. After testing hit apply one last time to save it and you should be done.
I know a few people have been looking for something like this for awhile so I hope this is a help to some of you. If you have any questions or comments do let me know.