I will offer a different suggestion which doesnt queries audit history table and also provide a grouped email per user.
1. Create a table extending import set. Simplest method is to load data> create new table and attach a sample excel file with column headings like assigned to, licensed by(or entitlement whichever suits you) . And add 1 row with random value like . Does not matter what values you put in row 2 onwards and one row.
[Optional field -Processed Status, value blank]
This is just an one time activity , you dont need to again. Lets call the table as "u_entitlement_user_history" for now.
Now create another reference field on this table called assigned to , reference to sys_user table.
2. Then can use a business rule, on Delete - Before business rule. The idea is OOB BR would anyways create a record in sys audit history table, but this BR would rather create records in the new table "u_entitlement_user_history". In this BR just create gliderecord variable and insert the data on the above mentioned fields like [Untested]
var gr = new GlideRecord("u_entitlement_user_history");
gr.newRecord();
gr.u_assigned_to = current.assigned_to.toString();
gr.u_licensed_by = current.licensed_by.getDisplayValue();
gr.insert();
3. Now every data that is deleted would also be stored in this buffer table for atleast 14 days before it gets deleted.
4. Create a schedule job-
Your scheduled job that runs periodically daily or weekly.
Run a nested glide aggregate query on the table. The script can be taken in another question, as it is not part of the original question.
Below is an example script[untested]
If runs daily
Query - Created on yesterday
var entAgr = new GlideAggregate('u_entitlement_user_history');
ent.addEncodedQuery('sys_created_on', '>=', 'javascript:gs.beginningOfYesterday()');
entAgr.groupBy('u_assigned_to');
entAgr.query();
while (entAgr.next()) {
var lic_arr = [];
var user = entAgr.getValue('u_assigned_to');
var licAgr = new GlideAggregate('u_entitlement_user_history');
licAgr.addAggregate('COUNT', 'u_licensed_by');
licAgr.query();
while (licAgr.next()){
lic_arr.push(entAgr.getDisplayValue('u_assigned_to')+" : "+ga.getDisplayValue('discovery_source') );
}
gs.addEventQueue("{your custom event}", "u_entitlement_user_history", user ,lic_arr.toString());
}
5. Last step-
Your notification that triggers on event.
event 1 is the recipient sys_id, and event contains the license names in array format.
You can now use a notification script to populate your body. Do another gliderecord on sys_user table to get the name of the user. and populate the lines iterating through the array.
This a custom solutioning in your case. Go through it and in case of issue, feel free to reply back. Do mark helpful if it helped you a bit.
