Jump to content

Recommended Posts

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).

Edited by Reddime
  • Thanks 1

Share this post


Link to post
Share on other sites

I was going to figure this out myself but alas, you did the work! Thank you! We are just going to run this nightly as a schedule SQL script in Automate. But the 577, do I need to figure out the ID of the EDF or put the name of the EDF in?

Share this post


Link to post
Share on other sites

Well I figured out how to get the ID of the EDF's: SELECT * FROM extrafield WHERE NAME = "purchase date"

Also, I apparently also have a EDF for Warranty Expiration, so I updated the script to fill that out too, here it is:

######################################################
#  Warranty Master Sync Script
#
#  This script manually syncs all Purchase Dates and
#  Warranty Expirations from Warranty Master to 
#  other fields in the labtech database.
#  
#  Instructions for use:
#  Hit CTRL+F9 to execute all commands in SQLyog
######################################################

#Update WarrantyEnd Dates
UPDATE computers c, plugin_warrantymaster_aux w SET c.WarrantyEnd=w.ExpiryDate WHERE w.ComputerID=c.ComputerID AND w.ExpiryDate;
#Insert/Update Purchase Dates
INSERT INTO extrafielddata
SELECT w.ComputerID, 539, w.PurchaseDate FROM plugin_warrantymaster_aux w WHERE w.PurchaseDate
ON DUPLICATE KEY UPDATE VALUE=w.PurchaseDate;

INSERT INTO extrafielddata
SELECT w.ComputerID, 540, w.ExpiryDate FROM plugin_warrantymaster_aux w WHERE w.ExpiryDate
ON DUPLICATE KEY UPDATE VALUE=w.ExpiryDate;

#539 = purchase date EDF
#540 = warranty expiration EDF

 

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...