Daily Ticket "Summary" E-mail

During our helpdesk roll-out, one of the tasks I set out to complete was the creation of a daily ticket reminder to users. Basically, this is a little summary to be sent every day to users and queues (if e-mail addresses were provided) with a count of Open, WIP (work in progress), and re-opened tickets. Here is how I went about creating this e-mail.

1. Create a new notification policy: This is found by going to: View -> Tasks -> Incident Resolution -> Incidents -> Helpdesk -> Notification Policies
2. Use the following Query:
   

   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_Incidents',  
   	sum(case when hd1.[workitem_status_lookup_id] = '450' then 1 else 0 end) as 'ReOpened_Incidents',  
   	sum(case when hd1.[workitem_status_lookup_id] = '150' then 1 else 0 end) as 'WIP_Incidents'  
   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]
   
   

3. Create a new Automation Action similar to this:
   
   

   Click to view.

The above process sends an e-mail based on the schedule we set. For us it is every Mon - Fri at 8:00 am. Here is a breakdown of what the SQL lines above do:

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

This code simply selects the Worker Name and E-mail address. We select name to help personalize the e-mail. It also helps the workers identify if this e-mail is to their Queue or their Worker Queue.

sum(case when hd1.[workitem_status_lookup_id] = '300' then 1 else 0 end) as 'Open_Incidents',  
sum(case when hd1.[workitem_status_lookup_id] = '450' then 1 else 0 end) as 'ReOpened_Incidents',  
sum(case when hd1.[workitem_status_lookup_id] = '150' then 1 else 0 end) as 'WIP_Incidents'  

This code basically creates a column with a sum of the values and totals them up for us. In the above case, 300 is our "Open" ID, 450 is our Re-Opened ID, and 150 is our WIP ID. You can customize this as you see fit and increase/remove anything. The syntax is the same as above. Just change the ID value, and the "as" part to something descriptive so you can use it in the e-mail.

FROM   
	dbo.HD_workitem_current_view hd1  
WHERE   
	hd1.[assigned_to_worker_status] = 'a' 

This code tells the query where to find the data. I did find that I had to add the HD_ to get this to function from the Notification Policy itself. Using Query Analizer I had to remove that little bit for testing. The "Where" piece basically limits the results to any "Active" Worker/Queue. This way if you inactivate a worker or queue, it will no longer try to send e-mails to them. Just more of a clean-up to reduce the e-mail being sent by this system.

I hope this helps someone else who might have a similar request/desire. The e-mail that is sent is pretty short and sweet and is meant for viewing on a handheld device.