Hello. 

We have a legacy escalation utility aLua script, which we use to take escalation actions on events when SLA escalations breach. Currently, the aLua script has two parts. The first runs an SQL query to identify which events need which escalation actions. This results in a CSV file being sent to ETM to add the action to the event. No issue with this part, it works well. 

The second part runs an SQL UPDATE statement to update the intesc1 (or intesc2, depending on escalation type) on the event’s incident data record. 

I am trying to integrate this second part into the ETM channel, but have not been able to find a field in ETM which will update this column in the database. 

For reference, here is the code from the aLua utility:

SQL_UPDATE_FIELD_1 =  EL

SELECT  
    i.incident_id ESC_EVENT_ID,
    'ESC1' ACTION_T
FROM incident i 
    INNER JOIN inc_data id ON i.incident_id = id.incident_id 
WHERE
    (i.status_enum  = 1) AND (i.inc_esc1 IS NOT NULL) 
    AND (id.intesc1 = 'n')
    and exists (select incident_id from act_reg where act_reg.incident_id = i.incident_id and act_type_id = '124')
    AND type_enum in (1,4,5) 
    AND (i.inc_esc1 < getDate())
    AND (id.u_date1 IS NULL)
    
]]

Then:

function update_field_1()

    local count = 0
--    local sImport1Line = ""

    --local sETM_add = "ESC_EVENT_ID,action_t" .. "\n"
    
    --local sTEXT = ""

    local result, err = DB:multi_row_sql(SQL_UPDATE_FIELD_1)
 
    if result then
        for x = 1, result.n do
        
            SQL = orUPDATE inc_data 
                            set intesc1 = 'y' 
                            where incident_id = ]] .. result.ESC_EVENT_ID=x]
                            
            LOGGER:info("Updating intesc1 " .. result.ESC_EVENT_ID�x])
            Result2, err = DB:update(SQL)
            if err then
                LOGGER:fatal("Error executing SQL: " .. (err or "??") .. "/nSQL:" .. SQL)
                os.exit(-3)
            end        
            count = count + 1
        end
        
    else
        LOGGER:info("No Escalations to add.  SQL result: " .. (err or "no error"))
    end
end

We have been using the intesc1and intesc2 columns to determine if an event needs escalation actions since we went live with assyst 10 years ago, so using an alternate method at this point is not possible. 

I have tried setting “Ola Esc1 Breach” / “Ola Esc2 Breach” as well as “Sla Esc1 Breach” / “Sla Esc2 Breach” to true in my mapper, but neither of these seem to update the columns in the database to have the ‘y’ value, hence the same events keep coming back in the SQL query. 

For reference, here is the SQL query used b the first part of the script, to determine events which require escalation actions:

SQL_ESCALATIONS = t 
SELECT 
    i.incident_id ESC_EVENT_ID,
    'ESC1' ACTION_T, inc_esc1 date_--, id.event_type, type_enum
FROM incident i 
    INNER JOIN inc_data id ON i.incident_id = id.incident_id 
WHERE
    (i.status_enum  = 1) AND (i.inc_esc1 IS NOT NULL) 
    AND (id.intesc1 = 'n')
    AND (type_enum in (1,4,5) )
    AND (i.inc_esc1 < getDate())
    AND (id.u_date1 IS NULL)
union 
SELECT 
    i.incident_id ESC_EVENT_ID,
    'ESC2' ACTION_T, inc_esc2 date_--,id.event_type, type_enum
FROM incident i 
    INNER JOIN inc_data id ON i.incident_id = id.incident_id 
WHERE
    (i.status_enum  = 1) AND (i.inc_esc2 IS NOT NULL) 
    AND (id.intesc2 = 'n')
    AND (type_enum in (1,4,5) )
    AND (i.inc_esc2 < getDate())
    AND (id.u_date1 IS NULL)    
    
union 
select 
i.incident_id ESC_EVENT_ID,'ESCRESP' ACTION_T, inc_resp_due date_
from incident i
where type_enum in (1,4) 
and (i.status_enum  = 1) 
and i.incident_id NOT IN (select incident_id from act_reg where act_type_id = '162' or act_type_id = '3')
and i.inc_resp_due < getDate() 
and i.inc_resp_due <> 0 
order by date_
            ]]

We are converting all of our custom logic away from aLua, so any help here would be appreciated. We’d rather not continue running scripts which do direct UPDATE statements in the database. 

Thank you, 

Duncan