Convert MAC adresse with colon to MAC Addresse with dashes

In this article you will learn how mac address entered with colon (:) can be converted to dashes (-) automatically. To make this happen you have to have permissions to the upKeeper database and some Microsoft SQL skills.

Steps

1. Verify that you have a valid backup of upKeeper database or make a backup before next step.  Verifying a backup or making a backup is not covered in this article. If not familiar with Microsoft SQL, please contact your database administrator.
2. Open Microsoft SQL Server Management Studio.
3. Connect to database server were the upKeeper database is installed with a user that have the permissions to update tables.
4. Open a new query from the File menu or the toolbar.
5. Select the upKeeper database in the dropdown for available databases.
6. Copy the following script to the query window and execute. If execution failed, look thru description and try to find out what went wrong and take action.

USE upKeeper;

CREATE TRIGGER MACAddressHandler
ON Computers
AFTER INSERT,UPDATE
AS
BEGIN

DECLARE @MACAddress nchar(17), @Id uniqueidentifier  

SELECT @Id=Id, @MACAddress=MacAddress FROM inserted
IF(CHARINDEX(':',@MACAddress)=0)
RETURN;

UPDATE Computers SET MACAddress = REPLACE(@MACAddress,':','-')
WHERE Id=@Id

END

When the script is successfully executed, you will be able to enter mac addresses with colon and they will automatically update to mac addresses with dashes when saved.

This trigger will only worj on new or updated mac adresses.