INCIDENT SLA TABLE CONTAIN LATEST RECORD

kun1
Tera Expert

Hi All,

We have a requirement in which we have to show only the latest record as per the SLA on the incident sla table?

 

For eg:- I created an Incident "A" and incident A contain Response and Resolution SLA . So our Response SLA stopped when the condition met and a new Response SLA is attached. So, as per the requirement i want only record of the latest SLA on incident SLA table.

 

For better reference, Please look the screenshot:-

find_real_file.png

So as per the requirement,, i want only record which is highlighted.

 

1 ACCEPTED SOLUTION

Mark Stanger
Giga Sage

Brian is correct that there's no direct way of doing this.  You will need to add another field to capture an additional data point whenever you have a duplicate 'task_sla' record entered.  Then you can easily filter what you want on this entry.  Here's how you can accomplish this.

1)  Create a new 'True/False' field on the 'task_sla' table named 'Last added (u_last_added)'.  Give it a default value of 'true'.  This field will only be updated by the system so you can also mark it as 'Read only' in the dictionary.  This is the field you'll be able to filter on to only show the latest SLA per type.  You'll be able to filter and show 'task_sla' records where this field is 'true'.

 

2)  Create a new business rule on the 'task_sla' table with the following settings...

Name: Update 'Last added' SLA

When: After

Insert: True

Advanced: True

Condition: !current.task.nil() && !current.sla.nil()

Script:

(function executeRule(current, previous /*null when async*/) {
	// Find the 'Last added' SLA for this task/sla combination and mark it
    var tsla = new GlideRecord('task_sla');
	tsla.addQuery('task', current.task);
	tsla.addQuery('sla', current.sla);
	tsla.addQuery('u_last_added', true);
	tsla.addQuery('sys_id', '!=', current.sys_id);
	tsla.query();
	if (tsla.next()) {
		tsla.u_last_added = false; // Change the 'u_last_added' field to 'false'
		tsla.autoSysFields(false); // Don't update time stamps
		tsla.setWorkflow(false); // Don't run any business rules
		tsla.update();
	}
})(current, previous);

 

3)  The above will solve the problem for SLAs added in the future, but you will also need to run a one-time background script to update any existing data.  You can run the following script from the 'Scripts -> Background' module.  You'll be updating every 'task_sla' record in your system (no business rules or time stamps will be applied) so proceed with caution and test thoroughly!

var lastTask = '';
var lastsla = '';
// Query all 'task_sla' records
var allsla = new GlideRecord('task_sla');
// Order by task and sla, then by 'sys_created_on' to group correctly
allsla.orderBy('task');
allsla.orderBy('sla');
allsla.orderByDesc('sys_created_on');
allsla.query();
while (allsla.next()) {
    allsla.u_last_added = false;
    // Find the 'Last added' SLA for this task/sla combination and mark it
    if ((allsla.task != lastTask) || (allsla.sla != lastsla)) {
        lastTask = allsla.task.toString();
        lastsla = allsla.sla.toString();
        allsla.u_last_added = true; // Change the 'u_last_added' field to 'true'
    }
    allsla.autoSysFields(false); // Don't update time stamps
    allsla.setWorkflow(false); // Don't run any business rules
    allsla.update();
}

View solution in original post

9 REPLIES 9

Brian Lancaster
Tera Sage

I think you best course of action is to do the following.

1.  Right click on the header and choose Configure > List Control.
find_real_file.png

2.  In the list control screen edit the default filter (you may need to add this to your form layout).  Change it to stage is In Progress.  This will then only show the SLA's that are currently running.
find_real_file.png

3. Click update.

I want the latest record only. If i move the In progress one to complete then how to fetch the latest only?

Sorry miss understood.  I'm not sure there is a way to do that.  I'm also not sure why you would really want to.  Most ITIL users want to see all the SLA associated with their incidents.  The only SLA's I would want to hide would be ones that are in a canceled state.  If you really want to do it you could try using a before query business rule on the task_sla table.  I'm not quite sure how to code it but I would think you would use the created date.

Any one who can help me out in this?