Have you ever wished that all those links in your ticket comments were actually clickable? Sick of copying and pasting links into a new window? Tired of manually adding UBB tags? Fear not, dear reader, for you are two custom SQL items and an incident rule away from shiny blue hyperlink-laden comment fields.

I' ve come across several places where there was a desire for the links in the comments field to be clickable. In some cases, an external system was sending links to an approval page. In others, they simply wanted to have an easier time verifying that a website sent in by a user was indeed blocked by the firewall. Even in locations where workers regularly added the UBB tags when creating tickets, incidents generated via e-mail did not get the same treatment.

Figure 1 - Default Helpdesk behavior

Click to view.

I' ve seen several workarounds for this; most involving "pre-formatting" links with characters that could be replaced with the appropriate UBB tags by an incident rule. While this certainly works some cases, I kept thinking there was a better way to do it.

Pro-Tip: Unless you have changed it, IE' s default behavior when clicking these links will be to navigate the current browser or tab to the link location. Holding down "ctrl" when clicking the link will open a new browser or tab, depending on your version of IE, leaving your original page at the console.

Figure 2 - "Activated" links - Shiny!

Click to view.

Disclaimers!

This guide will show you how to apply the custom database objects needed for your Altiris Helpdesk solution to automatically detect and "activate" links entered into the comments field, without any of that pesky pre-formatting. While I don' t think any of this will be a problem, I should let you know anyways:

• To the best of my understanding, Altiris does not support direct modification of the database unless it is performed by Professional Services.
• This customization has only been tested on the latest build of Altiris Helpdesk (6.0.308) and a SQL 2005 database. No large-scale load testing has been performed.
• I' ve tested as many permutations of comment format as I could think of. If you come across links that don' t activate, I' d love to see them so I can modify the code.
• As always, thoroughly test any customizations in a non-production environment first.

The Customization Objects

There are three pieces that go into making this work:

• A SQL Function that searches for links and loads them into a table variable
• A SQL Stored Procedure that calls the function, then adds the UBB tags [url] and [/url] to each link determined by the function
• An Altiris Incident Rule that executes the SQL Stored Procedure against the comment field every time it changes.

The "LinkSplit" SQL Table-Valued Function

The "LinkSplit" function finds the links in whatever data is passed to it. Matching a pattern in the input identifies the beginning of each link. The patterns used are 'http://' , 'ftp://' , and 'mailto://' . Once the beginning point has been found for an individual link, the function searches for the first occurrence of one of the conditions that indicate the end of the link.

The conditions searched are as follows:

• A space
• A period, comma, semicolon, or colon followed by a space
• A carriage return
• The end of the input

As each link is found, the function saves them into a table variable and moves on. In total, the function loops through the input string 3 times, one loop for each of the patterns that indicate the beginning of a link. The number of actual loops performed depends on the number of links present in the input.

To add this function to your SQL 2005 database, open a new query in SQL Management Studio. Copy the following code into the query window, making sure to change the database name in the first line if you are using a custom-named Incidents database. Clicking "Execute" will build the function, which can be found in the Object Explorer under "Altiris_Incidents->Programmability->Functions->Table-value Functions."

Use Altiris_Incidents

GO

IF OBJECT_ID (N'dbo.LinkSplit', N'TF') IS NOT NULL

  DROP FUNCTION dbo.LinkSplit;

GO



CREATE FUNCTION dbo.LinkSplit

(

	@Comment nvarchar(max)

) 

RETURNS @Links table 

(	

	Id int identity(1,1),

	Value nvarchar(max)

) 

AS 

BEGIN



DECLARE @Url varchar(10), @Mail varchar(12), @Ftp varchar(9)

DECLARE @Sub varchar(max), @Original varchar(max)

DECLARE @n int



SET @Url = '%http://%'

SET @Mail = '%mailto://%'

SET @Ftp = '%ftp://%'

SET @Original = @Comment



--Find all URL links beginning with HTTP:// and load them into a table

While (PATINDEX(@Url, @Comment) > 0)

Begin 

SELECT @Sub = SUBSTRING(@Comment, PATINDEX(@Url, @Comment), LEN(@Comment))

SELECT @n = MIN(n) FROM (

SELECT CASE WHEN PATINDEX('%. %', @Sub) <> 0 THEN PATINDEX('%. %', @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN PATINDEX('%, %', @Sub) <> 0 THEN PATINDEX('%, %', @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN PATINDEX('%; %', @Sub) <> 0 THEN PATINDEX('%; %', @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN PATINDEX('%: %', @Sub) <> 0 THEN PATINDEX('%: %', @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN PATINDEX('% %', @Sub) <> 0 THEN PATINDEX('% %', @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN CHARINDEX(CHAR(13), @Sub) <> 0 THEN CHARINDEX(CHAR(13), @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN CHARINDEX('.'+CHAR(13), @Sub) <> 0 THEN CHARINDEX('.'+CHAR(13), @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN LEN(@Sub) <> 0 THEN LEN(@Sub) ELSE NULL END as 'n'

) n

Insert Into @Links (Value)

Select  

	Value = RTRIM(LTRIM(Substring(@Comment, PATINDEX(@Url, @Comment), @n)))

Set @Comment = Substring(@Comment, PATINDEX(@Url,@Comment)+1, len(@Comment))

End 

SET @Comment = @Original



--Find all MAILTO links beginning with MAILTO:// and load them into a table

While (PATINDEX(@Mail, @Comment) > 0)

Begin 

SELECT @Sub = SUBSTRING(@Comment, PATINDEX(@Mail, @Comment), LEN(@Comment))

SELECT @n = MIN(n) FROM (

SELECT CASE WHEN PATINDEX('%. %', @Sub) <> 0 THEN PATINDEX('%. %', @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN PATINDEX('%, %', @Sub) <> 0 THEN PATINDEX('%, %', @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN PATINDEX('%; %', @Sub) <> 0 THEN PATINDEX('%; %', @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN PATINDEX('%: %', @Sub) <> 0 THEN PATINDEX('%: %', @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN PATINDEX('% %', @Sub) <> 0 THEN PATINDEX('% %', @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN CHARINDEX(CHAR(13), @Sub) <> 0 THEN CHARINDEX(CHAR(13), @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN CHARINDEX('.'+CHAR(13), @Sub) <> 0 THEN CHARINDEX('.'+CHAR(13), @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN LEN(@Sub) <> 0 THEN LEN(@Sub) ELSE NULL END as 'n'

) n



Insert Into @Links (Value)

Select  

	Value = RTRIM(LTRIM(Substring(@Comment, PATINDEX(@Mail, @Comment), @n)))

Set @Comment = Substring(@Comment, PATINDEX(@Mail,@Comment)+1, len(@Comment))

End 

SET @Comment = @Original



--Find all FTP links beginning with FTP:// and load them into a table

While (PATINDEX(@Ftp, @Comment) > 0)

Begin 

SELECT @Sub = SUBSTRING(@Comment, PATINDEX(@Ftp, @Comment), LEN(@Comment))

SELECT @n = MIN(n) FROM (

SELECT CASE WHEN PATINDEX('%. %', @Sub) <> 0 THEN PATINDEX('%. %', @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN PATINDEX('%, %', @Sub) <> 0 THEN PATINDEX('%, %', @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN PATINDEX('%; %', @Sub) <> 0 THEN PATINDEX('%; %', @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN PATINDEX('%: %', @Sub) <> 0 THEN PATINDEX('%: %', @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN PATINDEX('% %', @Sub) <> 0 THEN PATINDEX('% %', @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN CHARINDEX(CHAR(13), @Sub) <> 0 THEN CHARINDEX(CHAR(13), @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN CHARINDEX('.'+CHAR(13), @Sub) <> 0 THEN CHARINDEX('.'+CHAR(13), @Sub)-1 ELSE NULL END as 'n'

UNION

SELECT CASE WHEN LEN(@Sub) <> 0 THEN LEN(@Sub) ELSE NULL END as 'n'

) n



Insert Into @Links (Value)

Select  

	Value = RTRIM(LTRIM(Substring(@Comment, PATINDEX(@Ftp, @Comment), @n)))

Set @Comment = Substring(@Comment, PATINDEX(@Ftp,@Comment)+1, len(@Comment))

End 



--Cleanup

SET @Comment = NULL

SET @Original = NULL

SET @Sub = NULL

SET @n = NULL

SET @Url = NULL

SET @Ftp = NULL

SET @Mail = NULL



Return

END

The "activateLinks" SQL Stored Procedure

The "activateLinks" Stored Procedure calls the "LinkSplit" function against the input parameter for the procedure. Once the function has run it' s course, a cursor is initialized. For each distinct link present in the table built by the "LinkSplit" function, a SQL REPLACE command is called to replace the link with itself wrapped in the [url] and [/url] UBB tags. These UBB tags are what cause Helpdesk to interpret the string as a hyperlink, effectively "activating" it in the console. Once all of the replacements have been performed, the stored procedure returns a modified version of the original input, with UBB tags appropriately for interpretation by Helpdesk.

To add this stored procedure to your SQL 2005 database, open a new query in SQL Management Studio. Copy the following code into the query window, making sure to change the database name in the first line if you are using a custom-named Incidents database. Clicking "Execute" will build the function, which can be found in the Object Explorer under "Altiris_Incidents->Programmability->Stored Procedures."

USE Altiris_Incidents

GO

IF OBJECT_ID ( 'dbo.activateLinks', 'P' ) IS NOT NULL 

  DROP PROCEDURE dbo.activateLinks;

GO

CREATE PROCEDURE activateLinks

	(

	 @Input varchar(max)

	)

AS



DECLARE @link_value varchar(max)

DECLARE link_cursor CURSOR

	FOR SELECT DISTINCT Value FROM Altiris_Incidents.dbo.LinkSplit(@Input)



OPEN link_cursor

FETCH NEXT FROM link_cursor INTO @link_value



--Loop through the table and add UBB tags to each link found. 

--Double tags are needed, as single tags seem to be scrubbed by the application.

WHILE @@FETCH_STATUS = 0

	BEGIN



		SET @Input = REPLACE(@Input, @link_value, '[url][url]' + @link_value + '[/url][/url]')

		FETCH NEXT FROM link_cursor INTO @link_value



	END



SELECT @Input



--Cleanup

SET @Input = NULL

SET @link_value = NULL

CLOSE link_cursor

DEALLOCATE link_cursor

The "Activate Helpdesk Links" Incident Rule

Once the function and stored procedure have been added to the database, we still need to create an Incident Rule in the Helpdesk to leverage them. The incident rule sets the "Comment" property to the output of executing the "activateLinks" stored procedure, passing the current comment as the input parameter. The output generated will be clickable links, as seen in Figure 2 above.

Figure 3 - "Activate Helpdesk Links" Incident Rule

Click to view.

Conclusion

That' s it for this one. A big "Thank You" to billyccfs for his help in testing, and his excellent suggestion to activate 'ftp://' and 'mailto://' links. Feedback is always appreciated, and keep an eye out for my next pet project - an overhaul of the Helpdesk Satisfaction Survey.