Service Desk - Scoreboard to list all parent ticket which children tickets are already closed

[Bruno Zidarevic]

Dear all,

We are implementing some kind of scoreboard query that will facilitate customer in checking all the parent / child tickets and closing parent only in case if all child tickets are already closed. This scoreboard should make them easier to check only parent tickets for which all children are closed instead of checking every ticket separately. For example, lets say that one parent ticket has three children tickets. In the time when all those three tickets are resolved, parent should pop-up in scoreboard and analysts should only check the parent ticket and close it if everything is done and resolved.

I didn't find a way to implement this using stored queries. I managed to build an SQL query that is querying database and we can build a report but it is not the best way because scoreboard is an easier way for customer and there is a lot of groups for which report needs to be delivered.

Do you have any idea how to build something like this? Is there any way to build a notification when last children ticket is closed?

Version of SDM is 17.3.0.23.

Thanks in advance and BR,

Bruno

[thiagooliveira]

Hello Bruno, 

The only way that comes to my mind right now is using a spel method that will control the lifecycle of the children ticket. 
The idea is: 

Every time that a children ticket is solved your spel method will check if there are more open children tickets. 
If all children tickets are resolved, the spel method will update a new flag (you need to create a new attribute for this) in the  parent. 
You can also close the parent ticket automatically if all children are completed as well, it's up to you to decide. 

In your scoreboard, you will list all the tickets that have this new flag equal 1 and the status aren't resolved/closed/cancelled. 
 
Regards. 

------------------------------
Best regards.
LinkedIn:
https://pt.linkedin.com/in/thiago-23-oliveira
------------------------------

[Bruno Zidarevic]

Hi Thiago,

Thanks for the idea. Do you have in mind how this spel code should look like because I am not so good with writing spel. Also how to check if children ticket is resolved. As I can see this field exists only in Object Engine as QREL. Can this be used in SPEL?  

If you have some kind of sketch or draft for this SPEL it would be great.

Thanks in advance and BR,

Bruno

[Sebastian Nagy]

Hi Bruno,

I don't think it's possible to do what you are looking for without heavy customization.

Thiago's recommendation is a good start but I would recommend to cover the case when a child is added/re-opened while the flag is set, in this case the flag would need to be reset.

I don't have any spel code that I can share to accomplish this, but you would need to be familiar with spel code to create the customization.

From a very high level, you would need to do a "select_count" to get all the inactive children of the current ticket's parent, based on that you'll need to "get_co_group", "checkout", set the custom flag, "checkin".

You can check NX_ROOT\bopcfg\interp\sa_logs_filter.frg for how to perform the update. I don't have a  "select_count" example.

I hope this helps you progress with the limited information that I could provide.

Regards,

Sebastian

[Bruno Zidarevic]

Hi,

Thank you all for the info provided.

BR,

Bruno

[Felipe V]

Hi Bruno,

I developed a couple time ago a spell that's little bit similar behavior.

Can be changed and then you get the your goal.

//SAVE as z_cr_child_to_parent_attr.mod
MODIFY cr POST_VALIDATE
	z_cr_child_to_parent_attr ( persistent_id, status, parent)
	80199 FILTER ( EVENT("INSERT UPDATE") && ( status {->'RE'} && parent != NULL ) );

//SAVE as z_cr_child_to_parent_attr.spl
/////////////////////////////////////////////////////////////////////////////////////////////////////////
// Modulo:			z_cr_child_to_parent_attr.spl
// Autor:			Felipe Vandrilho
// Criado em:		22/06/2023
// Modificado por:	Felipe Vandrilho
// Modificado em:	22/26/2023
// Descrição:		O ticket pai (que já estará com o status “aguardando atendimento”) deverá voltar para o status “atribuído” quando todos os filhos forem finalizados.
/////////////////////////////////////////////////////////////////////////////////////////////////////////

cr::z_cr_child_to_parent_attr(...)
{
	string zs_method, zs_persistent_id, zs_persistent_id_child, zs_persistent_id_child_status, zs_persistent_id_parent;
	string zs_description, zs_status_update, zs_status_parent;
	int zi, zi_debug, zi_list_count;
	object zo_gl, zo_list;
	uuid zuuid_who;	
	
	zs_method = "z_cr_child_to_parent_attr";
	zi_debug = 1;
	zs_persistent_id = argv[3];
	zs_status_update = argv[6];
	zs_persistent_id_parent = argv[9];
	zs_description	= "SISTEMA: Status alterado, todos os filhos foram fechados.";
	
	if(zi_debug > 0){
		logf(SIGNIFICANT, "============================================================="); 
        logf(SIGNIFICANT, "%s Inicio - $s", zs_method, (string) now());
        logf(SIGNIFICANT, "%s User - '%s'", zs_method, thread_userid());
        logf(SIGNIFICANT, "%s ARG Count : %d", zs_method, argc);
        for (zi = 0; zi < argc; zi += 1) {
            logf(SIGNIFICANT, "%s ARG #%s : %s", zs_method, (string) zi, (string) argv[zi]);          
        }
        logf(SIGNIFICANT, "============================================================="); 
	}
		
	//usuário atual
	send_wait(0, top_object(), "call_attr", "cnt", "current_user_id");
	zuuid_who = msg[0];
	
	if(is_empty(zs_persistent_id_parent) == 1){
		logf(SIGNIFICANT, "EH PAI\n");
		logf(SIGNIFICANT, "SAINDO ...\n");
		return;
	}else{
		logf(SIGNIFICANT, "EH FILHO\n");
	}
	
	zs_status_parent = (string) expand(format("&{%s = cr.persistent_id->status}", zs_persistent_id_parent));
	
	if(zi_debug > 0){
		logf(SIGNIFICANT, "persid do PAI = '%s'\n", (string) zs_persistent_id_parent);
		logf(SIGNIFICANT, "Status do PAI = %s\n", zs_status_parent);
	}
		
	// Instancia um objeto CR utilizando um persistent_id DO PAI.
	send_wait(0, top_object(), "call_attr", "pr", "dob_by_persid", 0, zs_persistent_id_parent);
	if (msg_error()) {
		logf(SIGNIFICANT, "%s ERRO %d em dob_by_persid: %s", zs_method, msg_error(), msg[0]);
		logf(ERROR, "%s ERRO %d em dob_by_persid: %s", zs_method, msg_error(), msg[0]);
		return;
	}
	
	if(zi_debug > 0)
		logf(SIGNIFICANT, "Obtendo lista de objetos...\n");
	
	send_wait(0, top_object(), "call_attr", "api", "get_list", "cr", zuuid_who, format("parent='%s'", zs_persistent_id_parent), -1);
	zo_list = msg[0]; //lista de objetos
	zi_list_count = (int) msg[1]; //tamanho da lista
	
	if(zi_debug > 0){
		logf(SIGNIFICANT, "msg[0] = '%s'\n", (string) zo_list);
		logf(SIGNIFICANT, "Obtendo itens da lista de objetos...(Instanciando)\n");
		logf(SIGNIFICANT, "Tamanho da lista: %d\n", zi_list_count);
	}
	
	for (zi = 0; zi < zi_list_count ; zi++) {
		send_wait(0, zo_list, "dob_by_index", "DEFAULT", zi, zi);
		object zo_in;
		zo_in = msg[0];
		
		if(zi_debug > 0)
			logf(SIGNIFICANT, "msg[%d] = '%s'\n", zi, (string) zo_in);
	}
	
	send_wait(0, top_object(), "call_attr", "api", "get_list_values", zuuid_who, zo_list, 0, zi_list_count, 0, "ref_num");
	
	if(zi_debug > 0)
		logf(SIGNIFICANT, "PERSIDs dos objetos da lista:\n");
	
	zi_list_count = (zi_list_count * 3);
	
	for(zi = 0; zi < zi_list_count; zi+=3) {	
		zs_persistent_id_child = (string) msg[zi];
		zs_persistent_id_child_status = (string) expand(format("&{%s = cr.persistent_id->status}", zs_persistent_id_child));
		
		if(zs_persistent_id_child_status != "RE" ){
			if(zs_persistent_id_child != zs_persistent_id){
				logf(SIGNIFICANT, "zs_persistent_id_child = %s | zs_persistent_id = %s \n", zs_persistent_id_child, zs_persistent_id);
				logf(SIGNIFICANT, "Há tickets FILHOS NAO FECHADOS, SAINDO DO SPEL:\n");
				return;
			}
		}
		
		if(zi_debug > 0)
			logf(SIGNIFICANT, "Persid: msg[%d] = '%s'\n", zi, (string) msg[zi]);
	}

	z_change_status_parent(zuuid_who, zs_persistent_id_parent, zs_description, "crs:5209",  zi_debug, zs_method);

}

string z_change_status_parent(uuid zu_who, string zs_persid, string zs_description, string zs_status_persid, int zi_debug, string zs_method)
{

	zs_method += " z_change_status_parent";

	if (zi_debug > 0) logf(SIGNIFICANT, "%s Alterando status do objeto '%s' para '%s'...", zs_method, zs_persid, zs_status_persid);
	send_wait(0, top_object(), "call_attr", "api", "generic_status_change", zu_who, zs_persid, zs_description, zs_status_persid);
	if (msg_error()) {
		logf(ERROR, "%s ERRO ao alterar status no objeto '%s': '%s'", zs_method, zs_persid, msg[0]);
	} else {
		if (zi_debug > 0) logf(SIGNIFICANT, "%s Persid do log de atividade gerado: '%s'", zs_method, msg[0]);
	}
	return msg[0];
}

------------------------------
Regards,
Felipe Vandrilho

Linkedin: https://www.linkedin.com/in/vandrilho/
------------------------------

[Bruno Zidarevic]

Thanks very much Felipe. I will check it out.

BR,

Bruno