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:14
Points:18


--
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:517
    Points:911


    --
    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:14
    Points:18


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


    Configuration Management by Guy in Using HelpMaster with 1 replies I looking people using Helpmaster for Configuration Management (CM) down to the Configuration Item (CI) level. How have you have built your CM tr...

    HelpMaster website update by Rod Weir in Announcements with 0 replies In preparation of the v17 release, the HelpMaster website has had a make-over. The new-look site provide additional information about the capabilit...

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

    RE: Configuration Management by Rod Weir in Using HelpMaster Hi Guy, Once assets are in the system, they are generally linked to other entities (Clients, Sites, Knowledge Base articles, and even themselves).&...

    Configuration Management by Guy in Using HelpMaster I looking people using Helpmaster for Configuration Management (CM) down to the Configuration Item (CI) level. How have you have built your CM tr...

    HelpMaster website update by Rod Weir in Announcements In preparation of the v17 release, the HelpMaster website has had a make-over. The new-look site provide additional information about the capabilit...

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