Using Workflow to Monitor and Notify on Failed HelpDesk SLA/SLO's

Hello and thanks for stopping by to read this article. Today we are going to discuss creating a monitor to watch our HelpDesk Solution for tickets that are over X number of days old. While the HD solution does offer you some ability to do this, using the Workflow Solution will offer you greater flexibility as to what you can report on.

So, let’s get started.

Requirements for this article.

• HelpDesk Solution
• Workflow Server
• Workflow Designer
• Access to your Altiris_Incidnets database on your SQL server

Let's go ahead and open up the Workflow Designer and create a new Integration Tool called HelpDeskSLAOver5Days.

On the Select Provider click the Drop Down and Select SQL Server. On the connection string (and this may vary depending on if your SQL Server is installed on your NS or independent; the example I will show is if the SQL Server is on another server other than the NS) and click.

Network Library=DBMSSOCN;Data Source=xxx.xxx.xxx.xxx,1433;Initial Catalog=myDatabaseName;User ID=myUsername;Password=myPassword;

Replace xxx.xxx.xxx.xxx with your SQL server IP Address. Replace myDatabaseName with Altiris_Incidents. Replace ID with your SQL Username and myPassword with that SQL username's Password. Ok now that the easy stuff is out of the way let's move on the "maybe a little" complex things.

Paste the following SQL Query into the SQL Query box:

SELECT DISTINCT hd1.[workitem_number] AS '#',   
 hd1.[workitem_created_on] AS 'Create Date',   
 hd1.[workitem_modified_on] AS 'Last Modified',   
 DATEDIFF(MINUTE, hd1.[workitem_created_on], GETDATE()) AS 'Age(Minutes)',   
 hd1.[workitem_status_lookup_value] AS 'Status',   
 hd1.[workitem_priority_lookup_value] AS 'Priority',   
 hd1.[workitem_category_tree_value] AS 'Category',   
 hd1.[assigned_to_worker_name] AS 'Worker',   
 hd1.[workitem_title] AS 'Title'   
FROM workitem_current_view hd1   
WHERE DATEDIFF(dd, hd1.[workitem_created_on], GETDATE()) <= 30  
 AND hd1.[workitem_status_lookup_id] NOT IN (600)   
 AND hd1.[workitem_priority_lookup_id] IN (100,200,300,400,500)    
 AND DATEDIFF(MINUTE, hd1.[workitem_created_on], GETDATE()) >= 7200   
ORDER BY 'Age(Minutes)' DESC

Click to view.

Now thought this looks like a lot of mumbo jumbo, all it says is Show me all tickets from the CURRENT view that are less than 30 days old, in anything but a close status (close ID is 600) and is greater than 7200min (5 days) old. You can modify those fields in order to tailor them better to your company's SLA/SLO standards. So let's go ahead and click Next. Select to populate the connection settings into the component and click Next.

Click to view.

Click Next again through the Properties Name screen and arrive at the Fields Screen. This is a good place to test your query out to make sure that you will get all the field returns we asked for in the query. So go ahead and click return data and then click Fill Schema and you should get some data that looks like this.

Click to view.

Click Next and then arrive at the Screen where you name your new component. Lets call ours HDSLAOver5days. Click Finish, then Recompile and Close and it should take you back to the Designer Start Screen.

Now you have made your custom component. But how are we going to use it? Simple; we just create a monitor workflow.

So go and Make a new Monitor Workflow and Name is SLA5Days.

Click to view.

As always you will start with your Start, create notification credentials, and finish. Go to the project pane (left hand side of the screen) and click on the name of the project. On the right hand side the properties of this project will display. Click on the publishing tab.

Click to view.

We are going to publish this as a service on the Workflow server when we are done so go down to Service Name and name is the same as the project (SLA5Days). After this go down to your business time span and open this up. Here you will select when this service will start and stop. Go ahead and fill out all of your holidays, business hours, etc.etc and click ok.

Click to view.

Go down a little more and you will see a section called Deployment. Put a check mark in Auto Start and then click the drop down below it and select Windows Service. Ok now lets go back to the Model:Primary in the Project pane and get back to making out Monitor.

Go ahead and click on Import Components, click on the custom libraries tab, and select your custom component (HDSLAOver5days) click add and Ok. You should see it appear in your toolbox with a red star on it (denoting that it is new). Click and drag that over into your Workflow and connect it to the Create NS Credentials. Believe it or not that is all that you have to do for this to run. There is not setting a property or anything else like that because we set it up to run when we created it and by setting the correct properties on the publishing page.

So now that we have a way to collect this data what do you want to do with it? Well I love to get emails when SLA's fail that way I can see what is going on. So lets put a Send Email in our workflow and connect it to the HDSLAOver5days.

You would fill this out as normal for the Too and From line. When you get down to the subject enter Ticket over 5 days old. Now when you get down to the body of the message be sure that you are using HTML Comments and click the ... box to the right.

In the drop down menu in the upper left hand corner where you see String Formatter; click on it and change it to Table from Array. And drag over the HDSLAOver5days. A box will pop up asking you to select the columns that you want in the message. Just use all of them (if you want) or pick what you want to see and click Ok. Click Ok again and be sure your email settings are correct and then click Ok to return to the Workflow.

Go ahead and save the project and then publish it. When you do that it will step you through a series of steps and let you know where the files are and the location of the bat file you will need to click on to publish this as a service.

That's it. You have created a monitoring workflow that will let you know on a 24hr basis if a ticket is over the time frame specified. Again if you want to change the time frame for use in your business just adjust the SQL Query.

Remember that Juice only works for us as much as we use it so if you make something post it, if you have a question, post it, and if you just want to tell us how you're doing POST IT.

Thank you all for your time any attention.