Many to Many on a glidelist field

[hrng]

I've been reading up on M2M tables and relationships and database views all morning and I think I'm now more confused than when I started.

So we have a custom field on cmdb_ci_server called u_distribution_list - it's a list field that references the sys_user table. I want to start using the Targeted Communications plugin, which allows you to have big lists of users matching certain conditions. In this case, I want to filter down to communicate with all users in the u_distribution_list field of CIs matching certain patterns.

The problem with that is that the Targeted Communications plugin needs to use a reference field, not a reference list. It only actually works when u_distribution_list only contains one user.

I'm guessing what I need is a table that "explodes" that reference list, something similar to the cmdb_rel_ci table where there is a one to one mapping between users and CIs.

Would greatly appreciate a nudge in the right direction, or any ideas on how to accomplish this without rewriting some wheels.

Thanks!

Dave

[hrng]

From further reading, I think I would need to create a new M2M table and create a nightly scheduled task that empties it out and populates it again. I could set it to populate when the u_distribution_list field changes via business rules, but that has the potential to lead to stale data.

There's gotta be a neater way to do this...

[The SN Nerd]

Forgetting about the technical implementation for a moment - what is the actual business outcome you are trying to achieve?

I'm pretty confused as to what you are actually trying to accomplish here.

Try to explain it without using technical terms (ie M2M, reference, glide list)

[hrng]

We have a list of servers and a list of customer contacts (these contacts are automatically added to the watch list of incidents). We want to use the Targeted Communications plugin to communicate things like outage notifications, major announcements, etc., to a specific subset of customers. E.g. we may want to send a security vulnerability notification to all customers with linux servers, or we might want to send an outage notification to customers on a specific set of hardware.

We do have some tooling for this elsewhere, but it's gathering cobwebs and we were looking at killing it off and replacing with the Targeted Comms plugin.

The recipient list builder in that plugin seems perfect for this - we can filter down to CIs based on certain fields, it only falls over when we try to use that distribution list field.

Thanks!

[hrng · Accepted solution]

For anyone playing along at home - I went a different route in the end. Took this back to the original problem, which is that the Targeted Communications plugin does not support list fields. Confirmed with HI support that this is by design, so decided to extend that plugin to fill the gap.

The relevant Script Include is 'RecipientsListApi', however this is write protected, so you'll need to create a new one with the same name and copy and paste the existing content. Then just replace lines 236 through to 258 with the following:

// begin custom code
// is userSysId a list?
if(userSysId.indexOf(',') != -1) {
   // it's a list, run a loop over each list entry
   var userFieldList = userSysId.split(',');
   for(var i = 0; i < userFieldList.length; ++i) {
   if(targetSet[userFieldList[i]]) {
   continue;
   }


   if(!ugr.get(userFieldList[i]) || ugr.getValue('sys_class_name') != expectedUserClass) {
   gs.error("RecipientsListApi::buildRecipientsList Skipping Invalid userSysId:"+userFieldList[i]);
   continue;
   }
   targetSet[userFieldList[i]] = true;
   if(!skipInsert){
   index++;
   if(index>refreshRate){
   percentage = Number(recipientsListGr.getValue('percentage'));
   recipientsListGr.setValue('percentage',(percentage+50*refreshRate/total_count));
   recipientsListGr.update();
   index = 0;
   }
   }
   }
} else {
   // it's not a list
   if(targetSet[userSysId]) {
   continue;
   }


   if(!ugr.get(userSysId) || ugr.getValue('sys_class_name') != expectedUserClass) {
   gs.error("RecipientsListApi::buildRecipientsList Skipping Invalid userSysId:"+userSysId);
   continue;
   }
   targetSet[userSysId] = true;
   if(!skipInsert){
   index++;
   if(index>refreshRate){
   percentage = Number(recipientsListGr.getValue('percentage'));
   recipientsListGr.setValue('percentage',(percentage+50*refreshRate/total_count));
   recipientsListGr.update();
   index = 0;
   }
   }
}
// end custom code