/build/static/layout/Breadcrumb_cap_w.png

Blog Posts by chucksteel

Ask a question

Custom rule to format Hardware Address

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>
The rule handles three scenarios:
The user entered twelve characters - I'm assuming that the user entered twelve characters that are valid for a hardware address.
The user entered seventeen characters - I'm assuming that the user entered seventeen characters that are valid for a hardware address, but they are lower case or are using the wrong separator.
The user did not enter a value or it is neither twelve or seventeen characters - The field is set to 'Please enter a valid hardware address'

Those rules aren't by any means going to cover all possible scenarios in the best way, but they will get the basic job done.

Set the rule to run on ticket save and watch the magic happen.


Be the first to comment

Dualboot imaging macOS 10.13 and Windows 10 with Deploystudio and the Quest K2000 SDA

I have updated my techniques for deploying dual boot systems and wanted to share my workflows with the community.

http://chucksteel.blogspot.com/2018/04/dualboot-imaging-macos-1013-and-windows.html

Enjoy!

View comments (1)

Possibly useful queries

I tend to write a lot of queries for the K1000 and I have decided to share them. I created a GitHub repository a while back and have been putting more effort into maintaining it and even started to add comments to the queries. Hopefully it will be a resource for others in the community.

View comments (3)

Linking Power BI to the K1000

I have started a series on my blog on using Microsoft Power BI with the K1000 database. I know that there have been others doing similar work and wanted to contribute to the effort.
View comments (1)

Custom Inventory Rules Querying values in plist files

Problem: 
I am trying to use the PlistValueEquals function in a Custom Inventory Rule to check the value of an item but it won't work properly. I believe the issue is being caused by the value being nested inside of a dictionary. The plist looks like this:
Dict {
RepeatingPowerOn = Dict {
time = 1200
weekdays = 8
eventtype = wakepoweron
}
}

When I try to check the value for time it does not return properly. Even when using PlistValueReturn(/Library/Preferences/SystemConfiguration/com.apple.AutoWake.plist, time, NUMBER) to see what the value is I do not get anything back.

I have also tried PlistValueReturn(/Library/Preferences/SystemConfiguration/com.apple.AutoWake.plist, RepeatingPowerOn, TEXT) and this returns the following:
Dict {
time = 1260
weekdays = 8
eventtype = wakepoweron
} [string]

Note that in the database the value appears as:
'Dict {<br/> time = 1260<br/> weekdays = 8<br/> eventtype = wakepoweron<br/>}'

However, this rule: 
PlistValueEquals(/Library/Preferences/SystemConfiguration/com.apple.AutoWake.plist, RepeatingPowerOn, TEXT, Dict {<br/> time = 1260<br/> weekdays = 8<br/> eventtype = wakepoweron<br/>})
does not work to identify machines with those settings.

We are trying to detect the power on settings for the machines in our inventory so that we can deploy the settings that we would like to be in place.

Solution: 
By checking the syntax for the PlistBuddy command on macOS I found that you can query keys inside of a dictionary by using colons. The same syntax works for the Plist functions in custom inventory rules. Using that logic, this rule works:
PlistValueEquals(/Library/Preferences/SystemConfiguration/com.apple.AutoWake.plist, :RepeatingPowerOn:weekdays, NUMBER, 8) and PlistValueEquals(/Library/Preferences/SystemConfiguration/com.apple.AutoWake.plist, :RepeatingPowerOn:time, NUMBER, 1200) and PlistValueEquals(/Library/Preferences/SystemConfiguration/com.apple.AutoWake.plist, :RepeatingPowerOn:eventtype, TEXT, wakepoweron)
Be the first to comment
Showing 1 - 5 of 18 results

Top Contributors

Talk About Microsoft PowerShell