Search

HelpMaster helpdesk discussion forum

PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 03/10/2017 11:16 AM by  Josh B
Triggered Events on Assets
 3 Replies
AddThis - Bookmarking and Sharing Button
Sort:
You are not authorized to post a reply.
Author Messages
Josh B
Helpdesker
Helpdesker
Posts:16
Points:20


--
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 expert
    Helpdesk expert
    Posts:515
    Points:907


    --
    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:16
    Points:20


    --
    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.
    Josh B
    Helpdesker
    Helpdesker
    Posts:16
    Points:20


    --
    03/10/2017 11:16 AM
    So there were several unexpected challenges here. It took a long time to get database permissions enabled for sp_send_dbmail. Then there were some bugs with the version of Automation services (Triggered Events) that we had installed. Getting that service updated took a long time. Both those major delays were on my end.

    Unfortunately there are still some bugs and major limitations more broadly. As it turns out we can't use Triggered Events to send the overdue loans reminder emails. The service will only act on each asset record once (ever), so it just doesn't work in an equipment loans scenario. I'll be the first to admit this approach was a bit of a hack to begin with. If anybody is looking to create something like this, I would suggest developing your own service from scratch. Hopefully PRD will develop a more general purpose automation tool than what Triggered Events currently is.
    You are not authorized to post a reply.


    Latest posts

    Email tags not working. by David C in Email Manager with 1 replies Hi, I recently got the email service working and was poking around other areas (new to the software) and for some reason email template tags are not ...

    Knowledge Base improvements by Rod Weir in Wishlist with 0 replies We've recently received some requests to improve the knowledge base feature in HelpMaster via our support channel.  I'm re-posting some of the id...

    Triggered Events on Assets by Josh B in Priority Manager and Triggered Events with 3 replies 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 developme...

    Automatically unsuspending jobs by Josh B in Priority Manager and Triggered Events with 2 replies Today I implemented some Priority Manager profiles to automate the unsuspending of jobs once a target date is reached.  Thought I'd share... ...

    Use PowerBI for HelpMaster dashboards by Rod Weir in Reports and reporting with 0 replies Microsoft PowerBI is a powerful dashboard tool that allows you to quickly create dashboards based on your helpdesk / service-desk data.  With an...

    Template catalogs by Rod Weir in HelpMaster development with 0 replies Another great new feature of v17 is "Template Catalogs".  Template catalogs are groupings of Job Templates and Knowledge Base articles.  E...

    Spotlight on workflow object - Log new job by Rod Weir in HelpMaster development with 5 replies The next version of HelpMaster has a radically improved workflow system with lots of new workflow objects.  One of the most powerful and useful i...

    Clients can now link to multiple sites by Rod Weir in HelpMaster development with 0 replies Another handy feature of HelpMaster v17 is the ability to link clients to multiple sites.  Previously clients could only be linked to 1 site only...

    Control Sets - think custom fields, but much, much better... by Rod Weir in HelpMaster development with 0 replies One of the major new features of the next version of HelpMaster is the control sets. Think of control sets like mini-forms that you can design, and t...

    Workflow improvements by Rod Weir in HelpMaster development with 0 replies The workflow feature of HelpMaster is a very powerful tool to design, build and execute business processes.  It has proven to be a popular part o...

    Latest posts including replies

    RE: Triggered Events on Assets by Josh B in Priority Manager and Triggered Events So there were several unexpected challenges here. It took a long time to get database permissions enabled for sp_send_dbmail. Then there were some b...

    RE: Spotlight on workflow object - Log new job by Josh B in HelpMaster development This looks really great. Can't wait!...

    RE: Spotlight on workflow object - Log new job by Rod Weir in HelpMaster development Hi Josh, Thanks again for the feedback and ideas. The new workflow designing has some of these things already.  Keyboard support has been i...

    RE: Spotlight on workflow object - Log new job by Josh B in HelpMaster development Couple more ideas for workflows. First, I want to point out that the pattern I have adopted is to have an message box present instructions then an act...

    RE: Email tags not working. by Rod Weir in Email Manager Hi David, Thanks for the information. Can you tell us what version of HelpMaster you're using, as well as what module is sending the email (Deskto...

    Email tags not working. by David C in Email Manager Hi, I recently got the email service working and was poking around other areas (new to the software) and for some reason email template tags are not ...

    RE: Spotlight on workflow object - Log new job by Rod Weir in HelpMaster development Hi Josh, Thanks for you post – some good ideas and feedback here as usual. Your idea of having a pre-job workflow is something we’ve alr...

    RE: Spotlight on workflow object - Log new job by Josh B in HelpMaster development Over the past couple of days I have been looking at workflows in v16.10.17.4 (without the changes mentioned above). We are attempting to map one of o...

    RE: How to improve SQL Server performance and thus HelpMaster's too. by Josh B in HelpMaster SQL Server database Thanks for the tip about business time. Makes total sense. Can you elaborate on anything we can do in terms of database maintenance, indexing or o...

    RE: How to improve SQL Server performance and thus HelpMaster's too. by Rod Weir in HelpMaster SQL Server database You're right - the Field Chooser plays a big part in the speed of HelpMaster. The more fields = the bigger the database query = the bigger the ne...