• 0 Vote(s) - 0 Average
  • 5
  • 4
  • 3
  • 2
  • 1
Thread Modes

Creating Alert to Take Ticket Off Hold
#1
In short, I am trying to develop an alert and business rule combo that will automatically take a ticket off hold once the Scheduled Return date/time stored in SD_ACTION.EXPECTED_END_DATE_UT in the row corresponding to the hold action is reached. I have the alert, business rule, and business rule related process configured and tested; the problem is that if a ticket is put on hold multiple times, the alert has difficulty discerning which on hold action is the current one it should care about. 

Here is the SQL that is filtering the data in the Alert:

Code:
SD_REQUEST.STATUS_ID IN (SELECT STATUS_ID FROM SD_STATUS WHERE META_STATUS_ID=4)  
AND
SD_REQUEST.REQUEST_ID IN (SELECT REQUEST_ID FROM AM_ACTION WHERE ACTION_TYPE_ID in (5)
and EXPECTED_START_DATE_UT IS NULL AND EXPECTED_END_DATE_UT<GETUTCDATE() )
 
Basically it's checking to see if there is a ticket on hold and whether it's reached a threshold for action (EXPECTED_END_DATE_UT, which is populated via the On Hold wizard, is prior to the current date). 

The problem is that the above SQL has a critical flaw - If there are multiple on hold actions in the action history, it will bring the ticket off hold automatically because any of the previous on hold actions are triggering the alert, even if the most recent one set hasn't yet met the threshold. 

I've been racking my brain on how to squelch the SQL down to only focus on the most recent on hold event but am struggling to find a solution for. I have attached to this thread a screenshot from MC_MONITORING that shows the critical data involved here. 

Open to any and all ideas on how to get this SQL to focus on the right action, and ignore the previous ones. I am sure there's a simple solution but for the life of me I can't figure it out. 

Thanks!

Thumbnail(s)
   
Tony Bozeglav
Systems Administrator
Hyatt Legal Plans, a MetLife Company


#2
This is a high-level reply but hopefully can send you in the right direction.

When you create your sql for actions order by the date descending and put TOP 1 after the select statement. That way you are evaluating only the latest record and ignoring the rest, if any. Similar to:

SELECT TOP 1 * FROM AM_ASSET WHERE....
ORDER BY CREATION_DATE_UT DESC
Jack Reeder
FMX Solutions Inc.
Jack.Reeder@fmxsolutions.com

#3
I had originally thought of doing that, but there are a couple issues I've found with using that limit on the returned data set:

  1. The Alert may have multiple on hold tickets that have reached their respective return date/time in the time between the alert's iteration interval and thus it needs to take off hold multiple tickets in the same returned dataset. Limiting it to just the most recent matching ticket means it's possible tickets may be missed if more on hold tickets reach their scheduled return by the time the alert runs next. 
  2. Adding that limit to the dataset still gives me false positives in the event the ticket has been put on hold more than once and the most recent on hold action did not have a Scheduled Return date/time set. The query continues to return the most recent on hold event where a scheduled return value was set. 
Both aren't exactly likely scenarios, I admit - but they're bound to happen at some point.

I've been playing around with the query and pouring over the dataset to find some kind of unique set of data that will accurately identify the correct action but can't seem to come up with the right evaluation to focus the data set to those on hold tickets where EXPECTED_END_DATE_UT is set and it's the most recent on hold event.

I did find that I can make the Scheduled Return field mandatory in the "Place On Hold" wizard, thereby guaranteeing that I have the best possible data set... this may be the path of least resistance to achieve a consistent and reliable result as the alert runs.
Tony Bozeglav
Systems Administrator
Hyatt Legal Plans, a MetLife Company


#4
Been doing additional testing. Making the field required helps, but falls short with tickets already in our system that are already on hold. This is simply due to the SQL used for the Alert not having a way to squelch the dataset down to only the most recent on hold action action. So it's working as designed, just not as intended. 

I've come up with some new SQL that I added to the Alert's SQL scripting that runs when the alert is triggered that will update the EXPECTED_START_DATE_UT field for the appropriate on hold action with the current date/time. The thinking is this is a main condition for the sql and it's never set at any point thereafter, so setting a value ensures past on hold events aren't included. I also created a standalone alert to go through the DB and update any existing tickets accordingly to basically "prep" the ticket database to what this alert requires.

Code:
UPDATE AM_ACTION
SET EXPECTED_START_DATE_UT = GETUTCDATE()
WHERE AM_ACTION.REQUEST_ID IN (#LIST_ID#) AND
AM_ACTION.EXPECTED_START_DATE_UT IS NULL AND
AM_ACTION.EXPECTED_END_DATE_UT<GETUTCDATE() AND
AM_ACTION.ACTION_TYPE_ID IN (5);

This issue now is that if the ticket is taken off hold manually before the scheduled return date/time, the alert starts having issues going forward if the ticket ever gets put back on hold. This is because the hold action that was cancelled before the scheduled return never had the EXPECTED_START_DATE_UT field updated accordingly, and thus the next time it's put on old this "unmarked" hold action triggers the alert. 

Ultimately this is an issue with the query that I can't solve to account for tickets that inevitably are put on hold multiple times. I could create a 3rd alert that regularly "cleans up" these manual returns to set the data as needed for the main alert, but that just seems messy. That may be my only option, though. Any input is appreciated.
Tony Bozeglav
Systems Administrator
Hyatt Legal Plans, a MetLife Company


#5
We have a process configured that does what you are trying to accomplish.

Here are the steps to set it up:
  • Create a Scheduled Alert (Alert Type: Automatic) with the following SQL Script
Code:
UPDATE    SD_REQUEST
SET    AVAILABLE_FIELD_1 = 'End of On Hold'
WHERE    REQUEST_ID IN
    (
    SELECT    REQUEST_ID
    FROM    AM_ACTION
    WHERE    ACTION_ID IN
        (
        SELECT    ACTION_ID
        FROM    (
            SELECT    AM_ACTION.REQUEST_ID
                ,MAX(AM_ACTION.ACTION_ID) AS 'ACTION_ID'
            FROM    SD_REQUEST
                INNER JOIN AM_ACTION ON SD_REQUEST.REQUEST_ID = AM_ACTION.REQUEST_ID
            WHERE    SD_REQUEST.STATUS_ID = '5' /*On Hold*/
                AND AM_ACTION.ACTION_TYPE_ID = '5' /*On Hold*/
            GROUP BY AM_ACTION.REQUEST_ID
            ) AS TOP_HOLD_ACTIONS
        )
        AND AM_ACTION.EXPECTED_START_DATE_UT IS NULL
        AND AM_ACTION.EXPECTED_END_DATE_UT <= GETUTCDATE()
    )
  • Configure a Business Rule to trigger when the SD_REQUEST.AVAILABLE_FIELD_1 field is updated to 'End of On Hold'
  • The Business Rule will trigger a Related Process that uses the EZV_RestartRequest WEB Service to take the ticket off hold and then sets SD_REQUEST.AVAILABLE_FIELD_1 back to NULL
Sarah Schumacher
Service Desk Tool Administrator

#6
(06-21-2019, 12:22 PM)sarahschumacher Wrote: We have a process configured that does what you are trying to accomplish.

Here are the steps to set it up:
  • Create a Scheduled Alert (Alert Type: Automatic) with the following SQL Script
  • Configure a Business Rule to trigger when the SD_REQUEST.AVAILABLE_FIELD_1 field is updated to 'End of On Hold'
  • The Business Rule will trigger a Related Process that uses the EZV_RestartRequest WEB Service to take the ticket off hold and then sets SD_REQUEST.AVAILABLE_FIELD_1 back to NULL

Thank you for the reply! It's helpful to hear what others have done to accomplish this. I wasn't really aware or had spent any time understanding Automatic Alerts so this is an entirely new approach for me.  I did some basic testing with the SQL you've provided with promising results, but had a couple questions about how you implemented this that I am hoping you could answer for me:

  1. Why an Automatic Alert as opposed to a standard one? What's the functional difference in this case? 
  2. How does the alert handle tickets that are placed on hold more than once, or had been taken off hold before the expected return and then put back on hold? 
  3. Perhaps I missed it, but how does the alert handle the condition where there are more than 1 ticket at the alert's next iteration that need to be taken off hold? 
Tony Bozeglav
Systems Administrator
Hyatt Legal Plans, a MetLife Company


#7
(06-21-2019, 01:29 PM)tbozeglav Wrote: Thank you for the reply! It's helpful to hear what others have done to accomplish this. I did some basic testing with the SQL you've provided with promising results, but had a couple questions about how you implemented this that I am hoping you could answer for me:

  1. What, if any, SQL do you have set as the Alert's filter? It appears as though you don't have any filter and instead doing the filtering within the UPDATE SQL itself. Does that approach have any impact, negative or not, on the overall efficiency of the Alert? I currently have been trying to front load the filtering but I wasn't sure if there's any value to one practice over another. 
  2. How does your alert react to tickets that are placed on hold more than once? What if they take the ticket off hold before the scheduled return date/time?

All of the filtering is done in the UPDATE SQL.  Because of the need to use nested queries, I wasn't able to figure out how to do this using the other filtering method.  We have had this Scheduled Alert/Business Rule implemented for a few years and have not noticed any impacts to our system's performance.  The efficiency is going to be affected most by how frequently you run the Scheduled Alert.  For us, we run this alert once a day in the morning before most of our users get logged in.

The alert will work even if the single ticket is put On Hold several times or manually taken off hold since it is only looking for the top 'On Hold' action for tickets that are currently On Hold.
Sarah Schumacher
Service Desk Tool Administrator






Users browsing this thread: 1 Guest(s)