Enhanced Ticket Summary Email

I got some of the code and the idea from dkerr and his Daily Ticket "Summary" E-mail, but I felt like more could be done with it and so I spent quite some time customizing it more.

Here is what the emails look like.

Click to view.

I was pretty excited when I found out that HTML can be entered into the email notification action. It just makes the email look quite a bit nicer, and I believe it should be viewable on a handheld device.

Following is the SQL query that goes in a notification policy:

SELECT   
	hd1.[assigned_to_worker_name] as 'Worker_Name',     
 	hd1.[assigned_to_worker_email] as 'Worker_Email', 
	sum(case when hd1.[workitem_status_lookup_id] = '300' then 1 else 0 end) as 'Open_Work_Requests',     
	sum(case when hd1.[workitem_status_lookup_id] = '500' then 1 else 0 end) as 'On_Hold_Work_Requests',     
	sum(case when hd1.[workitem_status_lookup_id] = '100' then 1 else 0 end) as 'Planned_Work_Items', 
	sum(case when hd1.[workitem_status_lookup_id] = '600' and datediff(d, hd1.[workitem_modified_on], getdate()) = 0 then 1 else 0 end) as 'Items_Closed_Today', 
	sum(case when hd1.[workitem_status_lookup_id] = '600' and datediff(d, hd1.[workitem_modified_on], getdate())  	<= 7 then 1 else 0 end) as 'Items_Closed_This_Week',	
 	sum(case when hd1.[workitem_priority_lookup_id] = '100' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'ASAP_Priority',   
 	sum(case when hd1.[workitem_priority_lookup_id] = '200' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'High_Priority',     
 	sum(case when hd1.[workitem_priority_lookup_id] = '300' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'Medium_Priority',    
 	sum(case when hd1.[workitem_priority_lookup_id] = '400' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'Low_Priority',   
 	sum(case when hd1.[workitem_priority_lookup_id] = '500' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'Planned_Priority'      
	 
FROM     
	dbo.HD_workitem_current_view hd1     
WHERE     
	hd1.[assigned_to_worker_status] = 'a'  
GROUP BY     
	hd1.[assigned_to_worker_name], hd1.[assigned_to_worker_email]  
ORDER BY     
	hd1.[assigned_to_worker_name]
 

I'm gonna break this code out a bit so that everyone will be able to understand what is going on here.

hd1.[assigned_to_worker_name] as 'Worker_Name',     
hd1.[assigned_to_worker_email] as 'Worker_Email',
 

This is declaring the variables Worker_Name and Worker_Email to be associated to the database field assigned_to_worker_name and assigned_to_worker_email respectively.

sum(case when hd1.[workitem_status_lookup_id] = '300' then 1 else 0 end) as 'Open_Work_Requests',     
sum(case when hd1.[workitem_status_lookup_id] = '500' then 1 else 0 end) as 'On_Hold_Work_Requests',     
sum(case when hd1.[workitem_status_lookup_id] = '100' then 1 else 0 end) as 'Planned_Work_Items',

This portion of code is saying that when the database field, workitem_status_lookup_id, equals "300" then the field is output as "1". If it doesn't equal "300" then it is a "0". Then it calls this query open_work_requests. The other two are the same thing except they deal with on hold and planned work items.

You will probably want to customize this part to your own needs. Not everyone uses planned work item, so exchange that for something else, like resolved incidents (default id "400"), or add whatever status fields you want. We set up one for equipment loans and I may just add that to this query so that we remember when equipment is loaned out.

sum(case when hd1.[workitem_status_lookup_id] = '600' and datediff(d, hd1.[workitem_modified_on], getdate()) = 0 then 1 else 0 end) as 'Items_Closed_Today', 
sum(case when hd1.[workitem_status_lookup_id] = '600' and datediff(d, hd1.[workitem_modified_on], getdate())  	<= 7 then 1 else 0 end) as 'Items_Closed_This_Week',

I find knowing how many tickets have been closed by me today and in the last 7 days to be very interesting and motivating which is why I added this code. What it is doing is looking for closed items, id of "600", and then checking the modified on date against today's date.

The second one does the same thing except it looks for items that have been closed in the last seven days.

Sadly there isn't a database field for the date a incident is closed, so this number may not always be accurate if you make the habit of modifying already closed tickets.

sum(case when hd1.[workitem_priority_lookup_id] = '100' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'ASAP_Priority',   
sum(case when hd1.[workitem_priority_lookup_id] = '200' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'High_Priority',     
sum(case when hd1.[workitem_priority_lookup_id] = '300' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'Medium_Priority',    
sum(case when hd1.[workitem_priority_lookup_id] = '400' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'Low_Priority',   
sum(case when hd1.[workitem_priority_lookup_id] = '500' and hd1.[workitem_status_lookup_id] !='600' then 1 else 0 end) as 'Planned_Priority'

These queries are pretty simple. First they check the priority of a incident then they check to make sure the incident isn't closed, !=600. Finally they return a true or false, 1 and 0.

FROM     
	dbo.HD_workitem_current_view hd1     
WHERE     
	hd1.[assigned_to_worker_status] = 'a'  
GROUP BY     
	hd1.[assigned_to_worker_name], hd1.[assigned_to_worker_email]  
ORDER BY     
	hd1.[assigned_to_worker_name]
 

The last part is pretty self explanatory.

FROM is what datebase view the query is pulling from.

WHERE is checking making a rule that the work must be active, 'a'.

GROUP BY is just that, it is grouping by the assigned to worker's name and email address.

ORDER By then sorts by the worker's name.

Next we need to configure the email notification action that looks like this:

Click to view.

Here is the code for the message portion, just change the "{}" to "<>":

Worker Name: %DS:Worker_Name%

{span style="color:red"}
Open Tickets: %DS:Open_Work_Requests%
{/span}
On Hold Tickets: %DS:On_Hold_Work_Requests%
Planned Work Items: %DS:Planned_Work_Items%
Tickets Closed Today: %DS:Items_Closed_Today%
Tickets Closed in the Last 7 Days: %DS:Items_Closed_This_Week%

{b}Tickets by Priority:{/b}
ASAP: %DS:ASAP_Priority%
High Priority: %DS:High_Priority%
Medium Priority: %DS:Medium_Priority%
Low Priority: %DS:Low_Priority%
Planned Priority: %DS:Planned_Priority%

Thats about it, please let me know of any improvements, criticisms etc...

And thanks again to dkerr for his great idea.