Hi Stevenm,
As far as I know, your ask may not be possible.
But if you relax the need to send attachments in email and go with sending the link to the list of articles that requires user attention to each unique Author who has articles expiring with in 30 days you can do that easily. (In the below approach, the system also gives the list of articles already expired too).
Note: At the end of the article, I suggested an approach using which you can form a link which directly exports the data from instance in excel format.
Please find the steps I have taken to do this:
1) Go to "System Policy" > "Events" > "Registry" in the system and register a new event.
2) Go to "System Notification" >"Email" > "Notification Email Scripts" and create a new script as shown below:
Since I have less data in OOB instance I kept valid_to greater than today in the encoded query in screenshot.
In you case you may want to modify it as shown in below script block. (In the filter condition, I am using "Assigned to is (dynamic) Me" query as one of the conditions) so that I need not write additional logic to find each user and modify filter in the scheduled job.
(function runMailScript( /* GlideRecord */ current, /* TemplatePrinter */ template,
/* Optional EmailOutbound */
email, /* Optional GlideRecord */ email_action,
/* Optional GlideRecord */
event) {
var kbArticlesLink = gs.getProperty('glide.servlet.uri') + "nav_to.do?uri=kb_knowledge_list.do?sysparm_query=workflow_state=published^valid_toRELATIVELT@dayofweek@ahead@30^authorDYNAMIC90d1921e5f510100a9ad2572f2b477fe";
template.print("Please click <a href='" + kbArticlesLink + "' target='_blank'>here</a> to navigate to the list of articles expiring soon!");
})(current, template, email, email_action, event);
Along with this you may want to provide links to each article that is pending users attention in email body. You may modify template as per your need.
3) Now create a email notification which triggers when even is fired as shown in below screenshots.
Go to "System Notification" > "Email" > "Notifications" in left navigation menu and create notification as shown below.
In the "Who will receive" tab shown below, "Force Delivery" is not available OOB on form. If you want system to deliver emails irrespective of user preferences for email channel. You may want to add this field and check it. Or you may update this field later from list view.
4) Go to "System Definition" > "Scheduled Jobs" and click "New" and choose "Automatically run a script of your choosing" at the interceptor and create a script which runs periodically as shown below:
In the screenshot, I used valid_to > today as I was doing this in my OOB instance which has less data. In your case you may want to to change it as mentioned below:
(function() {
var kbas = new GlideAggregate("kb_knowledge");
kbas.addEncodedQuery("workflow_state=published^valid_toRELATIVELT@dayofweek@ahead@30");
kbas.groupBy("author");
kbas.query();
while (kbas.next()) {
var currAuthor = kbas.getValue("author");
gs.eventQueue("kb.expiring.articles.email", current, currAuthor, "");
}
})();
Additionally if you want to provide a link in email using which users can directly export the EXCEL file of KB's that needed their attention. You can do this.
First create a new view for kb_knowledge table list alone and give it some name such as "kb_article_export_view", then form the url in below mentioned manner and use this in email template as a link and mention text which clearly directs end user to import the file.
https://<instance_name>/kb_knowledge_list.do?sysparm_query=workflow_state=published^valid_to>javascript:gs.endOfToday()^authorDYNAMIC90d1921e5f510100a9ad2572f2b477fe&sysparm_view=kb_article_export_view&EXCEL"&EXCEL" is a format processor provided by servicenow using which a given list can be exported directly in excel format.
And the reason for suggesting a new list view creation is, all the end users who try to export always exports the columns which admin or process owner decides.
Hope this helps!
--
Thanks and regards,
Subrahmanyam Satti
