Courion Tip – User Friendly emails   Leave a comment

This could wind up being a multi part post, but I thought I should get started…

Step #1, add a GUID column to your ticketing table.  I called mine GUID, and set it to varchar(40) and checked the “Allow Nulls” column. As with any changes to the SQL Server database in Courion, restart the Courion service to pick up the change. Now we have a Globally Unique IDentifier column we can use in the ticketing database.

Step #2, add a custom macro to populate your GUI, I called mine GetGUID, Return Type of Text, Native Query set to “SELECT NEWID()”, Use Optimization is NOT checked, and Cache Results IS checked. Now we have a custom macro we can use to set the GUID in the ticketing table.

Step #3, make sure the password reset workflows populate the GUID… Go into the workflows, Actions, Password Reset, Ticketing Configuration, Modify, and for both the Resource Failure and Resource Success, scroll down to GUID, check it to make it active, and set it to %Custom Macro.GetGUID%. Now, whenever a user submitts a password change, successful on a given target or not, we have a means to aggregate that all of the individual responses of that instance of a workflow running together into one…

Step #4, add a column to your PickList table, called HTML_TableRowColor.  (I made the values italic so you would know to include the quotation marks….) If you think about the implications of this, you could ‘color code’ just about any result. However, I haven’t done anything other than success (green) and failure (red).

For a Status Code of 11 (Failed), I set HTML_TableRowColor to bgcolor=”#FFCACA” and, for a Status Code of 16 (Success), I set HTML_TableRowColor to bgcolor=”#DFF4E0″

Step #5, create a view that combines the Target ID Mapping, the Ticketing Table and something I cant think of right now. I called mine User_Friendly_Ticketing_Info:

SELECT     TOP (100) PERCENT dbo.Picklist.HTML_TableRowColor, dbo.Ticketing.Provisioner, dbo.Ticketing.Provisionee, dbo.Ticketing.Date,  dbo.TargetIDMapping.CommonName, dbo.Picklist.PickListValue, dbo.Ticketing.TargetStatus, dbo.Ticketing.TargetID, dbo.Ticketing.GUID, dbo.WorkflowConfig.Description, dbo.Ticketing.Workflow
FROM         dbo.Ticketing LEFT OUTER JOIN
                      dbo.WorkflowConfig ON dbo.Ticketing.Workflow = dbo.WorkflowConfig.WorkflowName LEFT OUTER JOIN
                      dbo.TargetIDMapping ON dbo.Ticketing.TargetID = dbo.TargetIDMapping.UBTName LEFT OUTER JOIN
                      dbo.Picklist ON dbo.Ticketing.WorkflowStatus = dbo.Picklist.PickListID
ORDER BY dbo.Ticketing.Provisionee, dbo.Ticketing.Date, dbo.Picklist.PickListValue

Step #6, create a custom macro called FriendlyResults and set it to Return Type of Text and the Native Query String

set to SELECT ‘<tr’ AS Expr1, HTML_TableRowColor, ‘><td>’ AS Expr2, UPPER(Provisioner) AS Expr4, ‘</td><td>’ AS Expr3, Date, ‘</td><td>’ AS Expr5, CommonName, ‘</td><td>’ AS Expr6, Description, ‘</td><td>’ AS Expr7, PickListValue, ‘</td><td>’ AS Expr8, TargetStatus, ‘</td></tr>’ AS Expr9
FROM User_Friendly_Ticketing_Info
WHERE (Provisionee = ‘%Provisionee Community.User%’) AND (GUID = (SELECT DISTINCT GUID FROM User_Friendly_Ticketing_Info AS User_Friendly_Ticketing_Info_2 WHERE (GUID IS NOT NULL) AND (Date = (SELECT DISTINCT MAX(Date) AS Expr10 FROM User_Friendly_Ticketing_Info AS User_Friendly_Ticketing_Info_1 WHERE (GUID IS NOT NULL))) AND (Provisionee = ‘%Provisionee Community.User%’))) ORDER BY PickListValue ASC, CommonName ASC

That probably deserves a great deal of explanation, but I am not sure I can actually give it. Basically, you want to find the lastest (MAX(Date)) GUID associated with a particular user ID… And then aggregate all that together.

Step #7, modify the summary screens. For instance, I have a self service password reset workflow, so, under Actions, Password Reset, Summary, I set the Summary Form, Summary Details to this:

%Custom Macro.GetSystemHelpDeskInfo%
<table border=”1″ width=”100%”>  <tr><th width=”10%”>Performed by</th>  <th width=”10%”>Date/Time</th>  <th width=”25%”>Target</th>  <th width=”25%”>Workflow used</th>  <th width=”10%”>Results – brief</th>  <th width=”20%”>Results – detailed</th>  </tr>  <tr>%Custom Macro.FriendlyResults%  </tr>  </table><br>Server: %Custom Macro.GetServerName% Workflow: %Workflow Name%

(I will post about the %CustomMacro.GetServerName% at a later date)

Step #8, modify the notification section, message text to this (basically the same…) Note I show the PasswordResetFailureMessage. I have also done the success message

%Custom Macro.PasswordResetFailureMessage% %Custom Macro.PasswordRules% <table border=”1″ width=”100%”>  <tr><th width=”10%”>Performed by</th>  <th width=”10%”>Date/Time</th>  <th width=”25%”>Target</th>  <th width=”25%”>Workflow used</th>  <th width=”10%”>Results – brief</th>  <th width=”20%”>Results – detailed</th>  </tr>  <tr>%Custom Macro.FriendlyResults%  </tr>  </table>      <br>  %Custom Macro.GetSystemHelpDeskInfo% <br>  Server: %Custom Macro.GetServerName% Workflow: %Workflow Name%



NOTE – I removed all indentifying marks at the request of the company I work at. 




Posted August 28, 2009 by mmdmurphy in Courion Tip, tips

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: