/build/static/layout/Breadcrumb_cap_w.png

Using Custom Ticket Rules to work with Custom Assets

Just wanted to share a neat integration between Custom Assets and Custom Ticket Rules found within the Service desk.  

The custom assets are great and can be used in many different ways.  However, they don't have the native ability for email notification.  

We have a custom asset called Extended Loan, which we use for checking equipment in and out; we don't do a lot of equipment rentals so we don't have a need for anything super sophisticated like WebCheckout (which we do use in a department that lends equipment out all day long).  Our Extended Loan asset is fairly basic:

* Name
* Date Issued 
* Return Date
* Computer
* Projector
* Tablet
* Issued to email
* Issued to phone
* Requested By
* Requested By Department
* Notes

When an extended loan is issued, we key it into the Kace.  

 

We then have a Kace service desk Custom Ticket Rule that runs daily to check and see if any Extended Loans have been issued within the last 24 hours.  If it finds one, we generate an email to the Issued to email with all the details associated with the loan as well as our boilerplate if you break it you buy it text.  

One day prior to the return, we have another Custom Ticket Rule that runs and notifies the person with the equipment, and the IT team, that the extended loan is due back tomorrow; included within is the boilerplate text.  This serves as a great reminder to them, but more importantly to us so we make sure we get the equipment back.  

The hardest part was developing the SQL queries, which I did within FlySpeed but once that was figuired it we were good to go.  


Comments

  • Thanks for posting. It would be great if you shared the SQL queries you used. - chucksteel 8 years ago
  • Good point! Here is the SQL for the Extended Loan Issued:
    SELECT
    concat('someEmail@someDomain.com',', ', ASSET_DATA_12.FIELD_46) as EMAILCC,

    ASSET.NAME AS ASSET_NAME, ASSET_DATA_12.FIELD_42 as Person, A43.NAME AS Department, ASSET_DATA_12.FIELD_45 AS Phone, ASSET_DATA_12.FIELD_39 AS Issued, ASSET_DATA_12.FIELD_40 AS Return_Date, A41.NAME AS Computer, A67.NAME AS Projector, A77.NAME AS Tablet, ASSET_DATA_12.FIELD_44 AS Notes FROM ASSET_DATA_12 LEFT JOIN ASSET ON ASSET_DATA_12.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=12 LEFT JOIN ASSET_ASSOCIATION J43 ON J43.ASSET_ID = ASSET.ID AND J43.ASSET_FIELD_ID=43
    LEFT JOIN ASSET A43 ON A43.ID = J43.ASSOCIATED_ASSET_ID
    LEFT JOIN ASSET_DATA_2 AD43 ON AD43.ID = A43.ASSET_DATA_ID
    LEFT JOIN ASSET_ASSOCIATION J41 ON J41.ASSET_ID = ASSET.ID AND J41.ASSET_FIELD_ID=41
    LEFT JOIN ASSET A41 ON A41.ID = J41.ASSOCIATED_ASSET_ID
    LEFT JOIN ASSET_DATA_5 AD41 ON AD41.ID = A41.ASSET_DATA_ID
    LEFT JOIN ASSET_ASSOCIATION J67 ON J67.ASSET_ID = ASSET.ID AND J67.ASSET_FIELD_ID=67
    LEFT JOIN ASSET A67 ON A67.ID = J67.ASSOCIATED_ASSET_ID
    LEFT JOIN ASSET_DATA_13 AD67 ON AD67.ID = A67.ASSET_DATA_ID
    LEFT JOIN ASSET_ASSOCIATION J77 ON J77.ASSET_ID = ASSET.ID AND J77.ASSET_FIELD_ID=77
    LEFT JOIN ASSET A77 ON A77.ID = J77.ASSOCIATED_ASSET_ID
    LEFT JOIN ASSET_DATA_15 AD77 ON AD77.ID = A77.ASSET_DATA_ID


    WHERE ASSET_DATA_12.FIELD_39 > DATE_ADD(CURDATE(), INTERVAL -1 DAY) - Jbr32 8 years ago
  • SQL for when it is due back tomorrow
    SELECT
    concat('someone@someDomain.com',', ', ASSET_DATA_12.FIELD_46) as EMAILCC,

    ASSET.NAME AS ASSET_NAME,
    A43.NAME AS Department,
    ASSET_DATA_12.FIELD_42 as Person,
    ASSET_DATA_12.FIELD_45 AS Phone,
    ASSET_DATA_12.FIELD_39 AS Issued,
    ASSET_DATA_12.FIELD_40 AS Return_Date, A41.NAME AS Computer, A67.NAME AS Projector, A77.NAME AS Tablet, ASSET_DATA_12.FIELD_44 AS Notes FROM ASSET_DATA_12 LEFT JOIN ASSET ON ASSET_DATA_12.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=12 LEFT JOIN ASSET_ASSOCIATION J43 ON J43.ASSET_ID = ASSET.ID AND J43.ASSET_FIELD_ID=43
    LEFT JOIN ASSET A43 ON A43.ID = J43.ASSOCIATED_ASSET_ID
    LEFT JOIN ASSET_DATA_2 AD43 ON AD43.ID = A43.ASSET_DATA_ID
    LEFT JOIN ASSET_ASSOCIATION J41 ON J41.ASSET_ID = ASSET.ID AND J41.ASSET_FIELD_ID=41
    LEFT JOIN ASSET A41 ON A41.ID = J41.ASSOCIATED_ASSET_ID
    LEFT JOIN ASSET_DATA_5 AD41 ON AD41.ID = A41.ASSET_DATA_ID
    LEFT JOIN ASSET_ASSOCIATION J67 ON J67.ASSET_ID = ASSET.ID AND J67.ASSET_FIELD_ID=67
    LEFT JOIN ASSET A67 ON A67.ID = J67.ASSOCIATED_ASSET_ID
    LEFT JOIN ASSET_DATA_13 AD67 ON AD67.ID = A67.ASSET_DATA_ID
    LEFT JOIN ASSET_ASSOCIATION J77 ON J77.ASSET_ID = ASSET.ID AND J77.ASSET_FIELD_ID=77
    LEFT JOIN ASSET A77 ON A77.ID = J77.ASSOCIATED_ASSET_ID
    LEFT JOIN ASSET_DATA_15 AD77 ON AD77.ID = A77.ASSET_DATA_ID


    WHERE ASSET_DATA_12.FIELD_40 = DATE_ADD(CURDATE(), INTERVAL 1 DAY) - Jbr32 8 years ago
This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ