PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 11/04/2016 2:59 PM by  Josh B
Triggered Events on Assets
 2 Replies
AddThis - Bookmarking and Sharing Button
Sort:
You are not authorized to post a reply.
Author Messages
Josh B
Helpdesker
Helpdesker
Posts:15
Points:21


--
11/02/2016 11:27 AM

    I want to set up some automation for Assets but I need some help.  Rather than sending a support email through, I thought I'd share the development process on the forums.

    Background

    We have an equipment loans process built on asset records.

    • The asset status is "On Loan" when checked out and "In stock" when checked in.
    • The asset is linked to the client who has made the loan and unlinked when the item is returned.
    • The asset also has a custom date field indicating when the equipment is due to be returned.

    I built a light-weight webapp that directly updates the database to streamline this for our staff.

    Business Logic

    When equipment is overdue, we want to start by sending a reminder email to the client.  In principle we don't want a job logged yet because there's no work to be done by our staff.

    However, if the item is not returned some 3 business days after the due date we are going to lock their AD account.  While this could in theory be fully automated, at this stage we just want to log a job for the helpdesk so that they can consider the circumstances of the loan (VIPs, special events, etc.)

    So, two actions:

    1. When the custom date field on the asset is in the past and the asset status is 'On Loan', send an email to the asset's Primary Client.
    2. When the custom date field on the asset is more than 3 days in the past, create a job & assign to helpdesk.

    Triggered Events

    Triggered events are all about logging a new job when the conditions are met, but I think we can also use it to send the email with the stored procedure sp_send_dbmail().  The question then is: What do I put in to the SQL field to make this happen?  Unfortunately there is no pregenerated SQL to build on.

    Rod Weir
    HelpMaster development team
    Helpdesk Hall of Fame
    Helpdesk Hall of Fame
    Posts:545
    Points:989


    --
    11/04/2016 1:54 PM

    Hi Josh,

    In order to send email from SQL Server, there are a few things that need to be configured first.

     

    1. Create a SQL Server Database Mail profile has been created.  Use SQL Server Management Studio to do this. 

    • In SSMS, Expand "Management"
    • RIght-click "Database Mail" > select "Configure Database Mail"
    • Configure the email profile as per your corporate settings

    sql mail configuration profile settings

     

    2. Paste the following SQL into the "SQL" text box of the HelpMaster Triggered Event profile.  Update the profile name with the name that you configured in step 1.  This is the complete SQL that you'll need.  Just cut and paste the following (and then update it with your info)

     

    EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'SQL EMAIL PROFILE NAME GOES HERE',  
    @recipients = 'someone@somewhere.xyz',  
    @body = 'Body of Email',  
    @subject = 'Subject of email'


    Triggered event send sql email 


    3. Ensure that the Windows account that is used to run the HelpMaster Automation service has appropriate permission to use the sp_send_dbmail stored procedure.

    sql mail configuration security settings 

     

    Hope this helps.

     

    HelpMaster development team
    Check out the HelpMaster roadmap
    Josh B
    Helpdesker
    Helpdesker
    Posts:15
    Points:21


    --
    11/04/2016 2:59 PM
    Thanks Rod, this looks like it will help. I hope to get back to this later next week. Will update once I've made some progress.
    You are not authorized to post a reply.


    February 2020 release now available by Rod Weir in Announcements with 0 replies A new release of HelpMaster is now available! For a list of the major new features of v20, see  https://www.helpmasterpro.com/Support/Updates-...

    December 2019 release now available by Rod Weir in Announcements with 0 replies A new release of HelpMaster is now available.  This release focusses mainly on stability, with an important fix for the Email Manager if using IM...

    Adjust the date range for displaying closed jobs in Explorer by Rod Weir in Using HelpMaster with 0 replies From tech support today... By default, the Explorer screen displays closed jobs that were closed in the previous month.  This interval can be ...

    October 2019 release now available by Rod Weir in Announcements with 0 replies A new release of HelpMaster is now available.  This release focusses on workflow stability, enhancements and other fixes. For a list of the ma...

    August 2019 release now available by Rod Weir in Announcements with 0 replies A new release of HelpMaster is now available.  This release has some great workflow improvements, new control set fields, a new manager feature, ...

    February 2020 release now available by Rod Weir in Announcements A new release of HelpMaster is now available! For a list of the major new features of v20, see  https://www.helpmasterpro.com/Support/Updates-...

    December 2019 release now available by Rod Weir in Announcements A new release of HelpMaster is now available.  This release focusses mainly on stability, with an important fix for the Email Manager if using IM...

    Adjust the date range for displaying closed jobs in Explorer by Rod Weir in Using HelpMaster From tech support today... By default, the Explorer screen displays closed jobs that were closed in the previous month.  This interval can be ...

    October 2019 release now available by Rod Weir in Announcements A new release of HelpMaster is now available.  This release focusses on workflow stability, enhancements and other fixes. For a list of the ma...

    August 2019 release now available by Rod Weir in Announcements A new release of HelpMaster is now available.  This release has some great workflow improvements, new control set fields, a new manager feature, ...

    Allow staff to view all jobs, all skillgroups by Rod Weir in Installation, configuration and upgrading By default, staff members can only view and action jobs that are assigned to the skillgroup(s) that the staff member belong to.  Fine-level contr...

    June 2019 release now available by Rod Weir in Announcements A new release of HelpMaster is now available.  This release builds upon features and stability, particularly in the workflow, and change manageme...

    Change Management by Rod Weir in Using HelpMaster Change Management in HelpMaster is a relatively new feature that is evolving rapidly.  Based off live-use in small and large departments, our &qu...

    April 2019 release now available by Rod Weir in Announcements A new release of HelpMaster is now available.  This release builds upon features and stability, particularly in the workflow, change management a...

    February 2019 release now available by Rod Weir in Announcements A new release of HelpMaster is now available.  This release builds upon features and stability, particularly in the workflow and web portal. F...