Jump to content
Automation Theory

Guide: Using MySQL Triggers [to fix duplicate MACs]

Recommended Posts

With recent events, the demand for remote connectivity is high. For partners using the MAC Signup functionality issues can arise when remote connectivity solutions use duplicate MAC addresses.
 
Like most issues in Automate, it's possible to work around this and use monitors/scripts/searches/etc. to create a creative solution. However, basic validity checking is a native function of MySQL, and there's an elegant approach that will help in this situation (and may not completely replace creative solutions).
 
This guide is designed to demo how to use database triggers in MySQL to enforce data validation. This can be useful for several things, but we'll use the "duplicate software MAC" problem (Cisco AnyConnect VPN) as our example.
 
Important notes:
  1. Database triggers are rather low-level items and are difficult to troubleshoot
  2. You can only have one trigger per time per operation per table
  3. This is unsupported by CW (both on the grounds of database modification and the manipulation of how the application works).
 
With that, open up your favorite MySQL client and let's get started.
 
DELIMITER $$
CREATE
    TRIGGER `computer_update` BEFORE UPDATE
    ON `labtech`.`computers` FOR EACH ROW 
    BEGIN
		IF NEW.mac IN ('00-05-9A-3C-7A-00', '00-00-00-00-00-00') AND OLD.mac IS NOT NULL THEN 
		SET  NEW.mac = OLD.mac;
        END IF;
        
        
    END$$
    
    DELIMITER ;

 

The above code creates a trigger for the updating of the MAC address in the computers table. MySQL triggers can reference the old and new values, and they do so via KEYWORD.COLUMNNAME syntax. We begin by checking if the new MAC address is in the list of known bad MAC addresses ('00-00-00-00-00-00' is just added here for example) and if the old MAC address is not null.
 
IMPORTANT: This means that if there's no existing MAC address (like on agent signup) this MAC could still be written to the DB. This is why the creative solutions might still have a place depending on the particular issue you're solving. For this particular issue, we're going to assume that these MAC addresses are already blacklisted for signup and we're just trying to solve for inventory updates.
 
Next, we set the new value for the column to be the old value, as we'd want to keep the old MAC address if a user connects a VPN client.
 
You'll notice that we switch the delimiter when creating this trigger. This is needed since our trigger itself contains semicolons, and to parse correctly we temporarily switch how we're terminating our lines. This may throw your particular SQL client for a loop.
 
At this point, once we execute the code to create this trigger we are set. If you try to update a computer's MAC address to one of the values in the list the statement will still run without errors, but the value won't change (this would appear to be black magic to anyone who did not know the trigger was in place -- please be advised if you're considering a broader use case).
 
Hopefully this guide has been useful to you. If you ever need support for the Automate database and want a professional DBA opinion feel free to reach out to us: https://automationtheory.org/ 

Share this post


Link to post
Share on other sites

@TonyPags Per the docs here the query is:

SELECT ComputerID From Computers Where LastContact <= Date_Add(NOW(),Interval {MacSignupTimeLimit} minute) and MAC like {Mac}

Using the above trigger won't prevent a computer from trying to signup with that MAC address (blacklisting is still a good idea) but it would prevent the computer from ever having it's MAC address updated to that after the fact. In the event that MAC wasn't blacklisted you would prevent anything in the computers table from matching that query.

The real usefulness of the trigger comes in different flavors of it's modifications -- you could do wild card matches, queries against other tables, etc. The Cisco AnyConnect MAC address issue just happens to be a good example for how to use a trigger for enforce data constraints -- there are plenty of other good/better solutions out there.

Share this post


Link to post
Share on other sites

I think your trigger is good to keep those MACs out of the computers table. But I swear I had a senior tech tell me it was the inv table. Maybe it's both, in a cascade. Just thinking out loud. 

I'll share something I have that pulls the list of MACs from the blacklist. Maybe you can incorporate the subquery on the blacklist into your trigger, to make it a little more automatic. (I've commented out the inv table and replaced it with computers a la your trigger). 

SELECT * FROM computers
-- SELECT * FROM inv_networkcard 
WHERE mac IS NOT NULL 
AND mac != ''
AND (
	SELECT CONCAT('\'',
    REPLACE((SELECT MACSignupBlackList FROM lt_web_servers_config WHERE MACSignupBlackList LIKE '%-%'),',','\',\''),
    '\'')
) LIKE CONCAT('%','\'',mac,'\'','%') 

I've been automating cleanup of that inv table with an adaptation of my above query. I've replaced my normal FROM clause with the table from your example. I run this and get a lot more results on the computers table. It will be interesting to see if I test against this to see if it's using one, the other, or both of these tables. 

=-=-=-=

There are inconsistencies with that document you've linked. Within the blue box that has the query, they refer to the limit number as being time in seconds AND minutes. I've also had senior support reps tell me this number is measured in DAYS, and also told me everyone thinks this setting does something different and that really, no one knows how it works exactly. I take all documented knowledge with a grain of salt. 

The doc also says that -1 forces agents to sign up as new devices but that is not how my server behaves using this setting, in fact it's the opposite. I saw your post and was curious because I had exactly the same idea, to make a trigger, but on the inv table! I'll have to do some reverse engineering, and design a case to test the edges of this blackbox logic. 

=-=-=-=

Do you know where they hide the MAC Signup function or stored procedure in the DB? That would be so much easier, to settle this for me. 

  • Thanks 1

Share this post


Link to post
Share on other sites

Hey @Automation Theory

I had a case where an agent DEFINITELY used the computers table MAC value for Mac signup, like you said. 

I have a few questions about this trigger for you: 

  • Are you running it, how is it working?
  • What maintenance is required for it?
    • For example, when you upgrade your server instance, do you verify it is still present and functional?
    • How? 

I still want to find a case where I can test the opposite case: if no MACs exist in the computers table but do in the inv tables, will Mac Signup look at both tables for a match? But without a safe way to clear out the computers table of bad MACs (like your trigger would do, over time), I won't have a way to test that case. 

Share this post


Link to post
Share on other sites

Hi @TonyPags,

The trigger lives blissfully below the application layer, Automate is unaware of it's existence. I've been running it on my 10k agent prod server for about a year now without issue. There's no maintenance apart from adding additional MACs, and I've only needed to do that once.

To test if it's working you can try the command:

update computers set mac = '00-00-00-00-00-00' where computerid = x;

If you follow that with a select statement it should still show the old MAC.

At that point you could update the bad MACs to a dummy value, find them in the advanced search, and resend the inventory. That ought to fix your source data.

I'm not entirely sure about the inv table secnario. I don't have any triggers setup on mine and I've never had issues. 

Share this post


Link to post
Share on other sites
Posted (edited)

Adding on to this, I have a monitor and autofix script to change the mac address to something else on the agent. Just make sure to set the alert template to a Run Script one and choose the script after import. 

 

Known Duplicate MAC Address Repair.xml

Edited by johnduprey
Updated script

Share this post


Link to post
Share on other sites

I added my condition to OP's trigger to avoid manually editing the list of MACs every time you want to change it. Instead it pulls the list from the MAC Signup Blacklist, which is the native place to store these MACs. 

DELIMITER $$
  CREATE
    TRIGGER `mycompany_computers_illegalMACs` BEFORE UPDATE
    ON `labtech`.`computers` FOR EACH ROW 
    BEGIN
      IF(
        SELECT CONCAT(
          '\'',
          REPLACE(
            (
                SELECT MACSignupBlackList 
                FROM lt_web_servers_config 
                WHERE MACSignupBlackList LIKE '%-%'
            ),
            ',',
            '\',\''
          ),
          '\''
        )
      ) LIKE CONCAT('%','\'',NEW.mac,'\'','%') 
      AND OLD.mac IS NOT NULL THEN 
      SET  NEW.mac = OLD.mac;
      END IF;
    END$$
DELIMITER ;

Here I am pulling a single cell value (which is the comma-separated list of MACs in the blacklist) from the config table. Because MySQL has no SPLIT function, I had to be a little creative to get the conditional to work properly: 

  • Replacing all commas with a single quote and a comma and another single quote.
  • Concatenating that result with one more single quote on each end to make the long string.
  • Comparing the long string with the new MAC, after adding in the wildcards and the single quotes to match our long string's format.

Everything else is the same. Except the trigger name I changed for my own purposes. 

Share this post


Link to post
Share on other sites

@TonyPags that is a very nice solution! My only thought/concern is that this trigger runs every time the computers table is updated (check in, signup, etc.). Having lots of operations in triggers on busy tables could impact performance, but it depends on a lot of particulars. However, adding a custom table and adding a clause like

... and NEW.mac not in (select customColumnName from customTable) ...

would be a simple way to optimize. You could probably find a programmatic way to update your custom table off your blacklist if you wanted.

Share this post


Link to post
Share on other sites
11 minutes ago, Automation Theory said:

You could probably find a programmatic way to update your custom table off your blacklist if you wanted.

Good idea. Not sure how I would accomplish that but worth a ponder. 

Share this post


Link to post
Share on other sites
2 hours ago, TonyPags said:

Good idea. Not sure how I would accomplish that but worth a ponder. 

You could probably build a pure-MySQL solution, but the quick-and-dirty way to do it would be to use an Automate script to read the blacklisted MACs from the lt_web_servers_config, split them, and insert into the custom table (either if missing or using a "on duplicate key update" clause).

Share this post


Link to post
Share on other sites

I have a status to report.

After adding the trigger a week ago, I was down to 1 remaining record using an illegal MAC address today. Not too shabby! I manually updated that record and now I have 0. So I can finally test to see if that inv_ table I mentioned has any effect on agent signup. I have disabled my script which purges illegal MACs from that inv table and I waited for it to repopulate.

While waiting, I checked out the h_agentsignups table for issues.

-- Get-MacSignupIssues
SELECT COUNT(h.computerid) AS Count, h.Computerid, h.clientid,GROUP_CONCAT(DISTINCT h.name) AS Names,GROUP_CONCAT(DISTINCT h.user) AS Users, GROUP_CONCAT(DISTINCT h.mac) AS MACs, h.errorlevel,h.errormessage,h.ActionDate
FROM h_agentsignup AS h
JOIN computers AS c ON h.ComputerID=c.ComputerID
WHERE h.computerid > 0
AND h.MAC IN ('00-05-9A-3C-7A-00','6C-4B-90-09-1D-D4','B4-AE-2B-3A-A2-9B','00-FF-22-44-A7-CF','00-09-0F-FE-00-01','02-50-41-00-00-01')
AND c.LastContact > DATE_SUB(NOW(), INTERVAL 15 MINUTE)
GROUP BY h.computerid
HAVING COUNT(h.computerid) > 1
ORDER BY COUNT(h.computerid) DESC, h.ActionDate DESC;

Sure enough, I see only 1 issue in the last day and the ID matches the item I manually edited today. That is just a sanity check. 

Tired of waiting, I resent network info and verified the inv table had that nasty Cisco AnyConnect MAC present. Then I remoted into another device to reset the agent registration. 

# This will reset everything: MAC, ID, and LocationID.
(new-object Net.WebClient).DownloadString('https://bit.ly/ltposh') | iex; Reset-LTService

After doing that I get no ID in the result. The device was not added to Automate. This is because the MAC address is blacklisted. The MAC blacklist works when there are no records in the database already! Finally! 

LTService  v190.333      - 6/4/2020 12:42:53 PM  - Registered LTService for remoting on 42000.:::
LTService  v190.333      - 6/4/2020 12:42:53 PM  - REMOTING PORT IS:42000:::
LTService  v190.333      - 6/4/2020 12:42:53 PM  - INIT CLR:4.0.30319.42000:::
LTService  v190.333      - 6/4/2020 12:42:53 PM  - Platform\OS: Win32NT\10 0:::
LTService  v190.333      - 6/4/2020 12:42:53 PM  - Janus Enabled based on Windows Version:::
LTService  v190.333      - 6/4/2020 12:42:53 PM  - Using Standard Event Log Engine:::
LTService  v190.333      - 6/4/2020 12:42:56 PM  - Status Result Error: :::
LTService  v190.333      - 6/4/2020 12:43:10 PM  - Attempting SignUp:::
LTService  v190.333      - 6/4/2020 12:43:21 PM  - Failed Signup, Will wait over 30 minutes to try again.:::

Also, the inv table has no effect! It's nice to have empirical results. Makes me feel warm and safe. Probably also makes OP happy too (?) 

It seems like when registration fails, it will keep retrying twice an hour until that VPN connection drops and the MAC is back to the hardware one. Now I just have to figure out a way to run that reset command on all rest of the endpoints with the agent ID sharing issue. I'm thinking a script to schedule a task to run the command. But that's for another day. 

Share this post


Link to post
Share on other sites

@TonyPags Empirical results are always good, especially when they are the ones we want. I'm glad to hear that all is well with your server!

 

On the topic of signup: In my production stack I err on the side of getting an agent installed -- I don't use the blacklist for software MACs; I let the trigger take care of inventory data and I send a script out to scrape the physical MAC and update the DB with that value (I don't want to have a tech on the phone trying to fight an agent install that will never work). However, this is a matter of preference, and what is appropriate will depend a lot upon the environment. Others have shared good solutions in the past, and those are worth using with the trigger if they apply.

Share this post


Link to post
Share on other sites

Sir Darren sent me this on Slack, so I can't take credit. Putting it here for eternity. 

>>

Just poking at (link to my comment): The function you want is called FIND_IN_SET .. I haven't tested this, but it should be something like this:

-- I DID NOT TEST THIS YET
DELIMITER $$
  CREATE
    TRIGGER `mycompany_computers_illegalMACs` BEFORE UPDATE
    ON `labtech`.`computers` FOR EACH ROW 
    BEGIN
      IF(
        SELECT COUNT(*)=0 
        FROM lt_web_servers_config 
        WHERE FIND_IN_SET(NEW.mac,MACSignupBlackList)>0
      )
      AND OLD.mac IS NOT NULL THEN 
      SET  NEW.mac = OLD.mac;
      END IF;
    END$$
DELIMITER ;

Since my current trigger is working so well, I don't have this high on my to-do list. I DID NOT TEST THIS! 

So you don't have to scroll up, this will grab the MACs from the blacklist under advanced config, instead of hard-coding the values into the trigger and updating it whenever you want to add another MAC. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...