PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 11/4/2016 1: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/2/2016 10: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:555
    Points:1017


    --
    11/4/2016 12: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/4/2016 1: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.


    New version - HelpMaster v24 now available! by Rod Weir in Announcements with 0 replies A new major release of HelpMaster is now available! Read about some of the new features and improvements here:  https://docs.helpmasterpr...-f...

    New version - HelpMaster v23 now available! by Rod Weir in Announcements with 0 replies A new major release of HelpMaster is now available! Read about some of the new features and improvements here:  https://docs.helpmasterpro.com...

    March 2023 v22.7 maintenance release now available by Rod Weir in Announcements with 0 replies A maintenance release for v22.7 HelpMaster is now available! The maintenance release fixes a number of issues that have been reported since the ini...

    July 2022 release now available - New version released! by Rod Weir in Announcements with 0 replies A new feature release of HelpMaster is now available!  v22.7.22 For a list of the major new features of v22, see the new documentation site ht...

    June 2022 v22.2 maintenance release now available by Rod Weir in Announcements with 0 replies A maintenance release for v22.2 HelpMaster is now available! The maintenance release fixes a number of issues that have been reported since the ini...

    RE: Use PowerBI for HelpMaster dashboards by Rod Weir in Reports and reporting Hi SR, Thanks for the post. Yes, the PowerBI reports for HelpMaster have been well received. Download the latest version, from here. https://www...

    RE: Use PowerBI for HelpMaster dashboards by Scenic_Rim_IT in Reports and reporting Hi Rod - just wondering how PowerBI reports are going? Any chance you have a beta/pre-release version we can help test for you?...

    New version - HelpMaster v24 now available! by Rod Weir in Announcements A new major release of HelpMaster is now available! Read about some of the new features and improvements here:  https://docs.helpmasterpr...-f...

    New version - HelpMaster v23 now available! by Rod Weir in Announcements A new major release of HelpMaster is now available! Read about some of the new features and improvements here:  https://docs.helpmasterpro.com...

    March 2023 v22.7 maintenance release now available by Rod Weir in Announcements A maintenance release for v22.7 HelpMaster is now available! The maintenance release fixes a number of issues that have been reported since the ini...

    July 2022 release now available - New version released! by Rod Weir in Announcements A new feature release of HelpMaster is now available!  v22.7.22 For a list of the major new features of v22, see the new documentation site ht...

    June 2022 v22.2 maintenance release now available by Rod Weir in Announcements A maintenance release for v22.2 HelpMaster is now available! The maintenance release fixes a number of issues that have been reported since the ini...

    February 2022 release now available - New version released! by Rod Weir in Announcements A new major release of HelpMaster is now available! For a list of the major new features of v22, see the new documentation site https://docs.helpma...

    November 2021 release now available - New Major Version! by Rod Weir in Announcements A new major release of HelpMaster is now available! For a list of the major new features of v22, see the new documentation site https://docs.helpma...

    May 2021 release now available - New Major Version! by Rod Weir in Announcements A new major release of HelpMaster is now available! For a list of the major new features of v21, see the new documentation site https://docs.helpma...