A question was posted in the KACE Slack Community regarding the possibility of having a custom field for a computer's hardware address (aka MAC) be formatted in IEEE 802 format using colons. Always being up for a challenge, I created the following custom service desk rule to do that. To create this rule on your SMA, go to Service Desk, Rules and select the relevant queue. Under Action, select New (SQL)
A couple of notes:
My custom field is CUSTOM 19, in the database that means it is called CUSTOM_FIELD_VALUE18. You will have to adjust the statements below to match your custom field, notice that the database column is one less than the UI column.
My queue ID is 1, you will have to adjust that for your queue.
Here is the Select statement for the rule:
SELECT ID FROM HD_TICKET WHERE
CUSTOM_FIELD_VALUE18 not regexp BINARY '([0-9A-F]{2}[:]){5}([0-9A-F]{2})'
AND HD_QUEUE_ID = 1
Credit to https://stackoverflow.com/questions/4260467/what-is-a-regular-expression-for-a-mac-address for the regular expression.
Here is the update statement:
UPDATE HD_TICKET SET CUSTOM_FIELD_VALUE18 =
CASE
WHEN CHAR_LENGTH(CUSTOM_FIELD_VALUE18) = 12 THEN
UPPER(concat_ws(':', substr(CUSTOM_FIELD_VALUE18, 1,2),
substr(CUSTOM_FIELD_VALUE18,3,2),
substr(CUSTOM_FIELD_VALUE18,5,2),
substr(CUSTOM_FIELD_VALUE18,7,2),
substr(CUSTOM_FIELD_VALUE18,9,2),
substr(CUSTOM_FIELD_VALUE18,11,2)))
WHEN CHAR_LENGTH(CUSTOM_FIELD_VALUE18) = 17 THEN
UPPER(concat_ws(':', substr(CUSTOM_FIELD_VALUE18, 1,2),
substr(CUSTOM_FIELD_VALUE18,4,2),
substr(CUSTOM_FIELD_VALUE18,7,2),
substr(CUSTOM_FIELD_VALUE18,10,2),
substr(CUSTOM_FIELD_VALUE18,13,2),
substr(CUSTOM_FIELD_VALUE18,16,2)))
ELSE 'Please enter a valid hardware address'
END
WHERE ID = <TICKET_IDS>
