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:539
    Points:971


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


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

    Allow staff to view all jobs, all skillgroups by Rod Weir in Installation, configuration and upgrading with 0 replies 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...

    Building knowledge management and KCS into the workflow by Rod Weir in Using HelpMaster with 0 replies Here's a workflow idea to assist with improved knowledge base articles and knowledge management / KCS As part of a support workflow, build into...

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

    Workflow email object - email account by Rod Weir in Using HelpMaster with 0 replies The up-coming release of HelpMaster will have a lot of workflow improvements.  One of these is in the email object.  It is now possible to c...

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

    Building knowledge management and KCS into the workflow by Rod Weir in Using HelpMaster Here's a workflow idea to assist with improved knowledge base articles and knowledge management / KCS As part of a support workflow, build into...

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

    Workflow email object - email account by Rod Weir in Using HelpMaster The up-coming release of HelpMaster will have a lot of workflow improvements.  One of these is in the email object.  It is now possible to c...

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

    RE: Job Templates get a new config option for web-based jobs by Rod Weir in Web interface, client self-service and remote access Just further to this topic....if using workflow to log a new job from a job that already contains control sets, and you are using the option to transf...

    November 2018 release now available by Rod Weir in Announcements A new release of HelpMaster is now available.  This release contains lots of fixes, performance tweaks, improved workflow capabilities, improved ...