Search the Community
Showing results for tags 'trigger'.
Found 2 results
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: Database triggers are rather low-level items and are difficult to troubleshoot You can only have one trigger per time per operation per table 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/
Forgive me if this write-up is crap, I'm fairly new to Automate. But we have the Warranty Master plugin to help pull warranty information for devices. Unfortunately, Warranty Master does not sync with the computers table in the labtech database by default. We are connected to Manage and want warranty dates to sync so that we can automatically start ticketing based on Warranty expiration dates. We have found that the WarrantyEnd dates in the computers table is often random and totally unreliable. To solve this problem, I wrote a few database modifications to sync the dates. Disclaimer: All code is provided for reference use only. You MUST customize it to your own use case. Prerequisites: Just an EDF for PurchaseDate at the Computer Level. Solution: Since our labtech database is running on a MySQL database the solution for this is easy. Triggers. You can learn more about MySQL triggers here: https://dev.mysql.com/doc/refman/8.0/en/triggers.html Basically, the purpose of a trigger is to detect when a change is made to a table and to perform a certain action when it does. In our case, we are detecting changes to the `plugin_warrantymaster_aux` table and updating the `WarrantyEnd` field in the appropriate rows in the computers table with the new values. We are also updating the `extrafielddata` Value field where the row shares the same `ComputerID` and `ExtraFieldID`. This can get a little tricky as the `extrafielddata` table is a junction table for the many-to-many relationship between computers and `extrafield` tables. Be sure you understand what my code does before running it. Scripts: I have attached two scripts which I used to create this sync: AGAIN, ALL CODE IS PROVIDED FOR REFERENCE USE ONLY. !PRNWarrantySyncCommands.sql This script will provide an initial sync for your database. Since our triggers only run AFTER UPDATE and AFTER INSERT on the plugin_warrantymaster_aux table It may be necessary to do an inital sync. MAKE SURE YOU USE THE CORRECT ExtraFieldID(Not 577). !PRNWarrantyMasterSyncTriggersSetup.sql This script will automatically setup the triggers required for the sync to work automatically. MAKE SURE YOU USE THE CORRECT ExtraFieldID(Not 577).