Delete scheduled reports along with reports

[othomas1]

Good Morning everyone,

I am working on a script to remove reports based on a give criteria, I need some assistance with adding to it. I need to add the removal of scheduled reports as well. I gave it a try in my code below but it did not turn out well. Can anyone see if im headed in the right direction?

Current Script:

(function myReports() {

reportsArray = [];
var reports = 0;
var repo_old = 0;

var repoStats = new GlideRecord('report_stats');

repoStats.addEncodedQuery('report_sys_id.created_by_user.active=true^ORreport_sys_id.created_by_user.active=false^ORnumber_executions_total=0^ORreport_sys_id.created_by_userISEMPTY^sys_updated_onRELATIVELT@month@ago@13^report_sys_id.created_by_user!=6816f79cc0a8016401c5a33be04be441^report_sys_id.created_by_user.roles!=report_group^report_sys_id.created_by_user.roles!=gauge_maker^report_sys_id.created_by_user.roles!=bcbsla_homepage_editor^report_sys_id.user!=GLOBAL');

repoStats.query();
reports = repoStats.getRowCount();

while(repoStats.next()) {

var sRep = new GlideRecord ('sysauto_report_list');
sRep.addQuery('name', repoStats.report_sys_id);
sRep.query();

while(sRep.next()) {

reportsArray.push(repoStats.report_sys_id.title.toString());
}
for(i=0; i<reportsArray.length; i++){
var repo = new GlideRecord('sys_report') ;
repo.addQuery('title',reportsArray[i]);
repo.query();
repo_old = repo.getRowCount();
if(repo.next()){

}
}
}
gs.log('Scheduled report -' + sRep + 'to be removed');
gs.log('Number of reports found - ' + reports + ' will be removed');
gs.log('These are the names of the reports -' + reportsArray.join('\n')+ ' ');
//repo.deleteMultiple();

})();

[Prateek kumar]

How  about?

reportsArray = [];
scheduledRepArray = [];
repToBeDeletedArray = [];
var repoStats = new GlideRecord('report_stats');
repoStats.addEncodedQuery('report_sys_id!=NULL'); //Change this your required encoded query
repoStats.query();
reports = repoStats.getRowCount();
gs.log(reports);
while(repoStats.next()) {
reportsArray.push(repoStats.report_sys_id.toString());
}

for(i=0; i<reportsArray.length; i++){
var sRep = new GlideRecord ('sysauto_report');
sRep.addQuery('report', reportsArray[i]);
sRep.query();
if(sRep.next()){
scheduledRepArray.push(sRep.report.title.toString());
}
}
gs.info(scheduledRepArray.join('\n'));

for(i=0; i<scheduledRepArray.length; i++){
var repo = new GlideRecord('sys_report') ;
repo.addQuery('title',scheduledRepArray[i]);
repo.query();
if(repo.next()){
repToBeDeletedArray.push(repo.title.toString());
}
}
gs.info("To be Deleted: "+repToBeDeletedArray.join('\n'));

[othomas1]

Prateek, i no longer am required to print the names of the scheduled reports. But i do need to print the names of all reports from sys_report and report_stats. I altered the code you presented a bit, but the last log statement does not print the correct amount of reports, in my first log statement i am printing out the combined number of reports, the second log i am printing the names for reports found in report_stats and for the last log i need to print out the reports from sys_report.

(function myReports() {

reportsArray = [];
scheduledRepArray = [];
repToBeDeletedArray = [];

var reports = 0;
var repo_old = 0;
var list = 0;
var schedule = 0;
var total = 0;

var repoStats = new GlideRecord('report_stats');
/* Below is the dynamic filter for the encoded query from the report_stats table:

https://bcbslasandbox2.service-now.com/nav_to.do?uri=%2Freport_stats_list.do%3Fsysparm_first_row%3D1%26sysparm_query%3Dreport_sys_id.created_by_user.active%253Dtrue%255EORreport_sys_id.created_by_user.active%253Dfalse%255EORnumber_executions_total%253D0%255EORreport_sys_id.created_by_userISEMPTY%255Esys_updated_onRELATIVELT%2540month%2540ago%254013%255Ereport_sys_id.created_by_user%2521%253D6816f79cc0a8016401c5a33be04be441%255Ereport_sys_id.created_by_user.roles%2521%253Dreport_group%255Ereport_sys_id.created_by_user.roles%2521%253Dgauge_maker%255Ereport_sys_id.created_by_user.roles%2521%253Dbcbsla_homepage_editor%255Ereport_sys_id.user%2521%253DGLOBAL%255Ereport_sys_id.sys_updated_onRELATIVELT%2540month%2540ago%254013%255Ereport_sys_id.created_by_user%2521%253D044a1a25db984300ba48fb451d96190a%26sysparm_view%3D
*/

repoStats.addEncodedQuery('report_sys_id.created_by_user.active=true^ORreport_sys_id.created_by_user.active=false^ORnumber_executions_total=0^ORreport_sys_id.created_by_userISEMPTY^sys_updated_onRELATIVELT@month@ago@13^report_sys_id.created_by_user!=6816f79cc0a8016401c5a33be04be441^report_sys_id.created_by_user.roles!=report_group^report_sys_id.created_by_user.roles!=gauge_maker^report_sys_id.created_by_user.roles!=bcbsla_homepage_editor^report_sys_id.user!=GLOBAL^report_sys_id.sys_updated_onRELATIVELT@month@ago@13^report_sys_id.created_by_user!=044a1a25db984300ba48fb451d96190a');

repoStats.query();

reports = repoStats.getRowCount();

while(repoStats.next()) {

var gr = new GlideRecord('sys_report');
/* Below is the dynamic filter for the encoded query from the sys_report table:

https://bcbslasandbox2.service-now.com/nav_to.do?uri=%2Fsys_report_list.do%3Fsysparm_first_row%3D1%26sysparm_query%3Dcreated_by_user.active%253Dtrue%255EORcreated_by_user.active%253Dfalse%255Esys_updated_onRELATIVELT%2540month%2540ago%254013%255Ecreated_by_user%2521%253D6816f79cc0a8016401c5a33be04be441%255Euser%2521%253DGLOBAL%255Ecreated_by_user.roles%2521%253Dreport_group%255Ecreated_by_user.roles%2521%253Dgauge_maker%255Ecreated_by_user.roles%2521%253Dbcbsla_homepage_editor%26sysparm_view%3D
*/
gr.addEncodedQuery('created_by_user.active=true^ORcreated_by_user.active=false^sys_updated_onRELATIVELT@month@ago@13^created_by_user!=6816f79cc0a8016401c5a33be04be441^user!=GLOBAL^created_by_user.roles!=report_group^created_by_user.roles!=gauge_maker^created_by_user.roles!=bcbsla_homepage_editor');

gr.query();

list = gr.getRowCount();

reportsArray.push(repoStats.report_sys_id.title.toString());
}

for(i=0; i<reportsArray.length; i++){
var sRep = new GlideRecord ('sysauto_report');
sRep.addQuery('name', reportsArray[i]);
sRep.query();

schedule = sRep.getRowCount();

if(sRep.next()){
scheduledRepArray.push(sRep.report.title.toString());
}
}
//gs.info(scheduledRepArray.join('\n'));

for(i=0; i<scheduledRepArray.length; i++){

var repo = new GlideRecord('sys_report') ;
repo.addQuery('title',scheduledRepArray[i]);
repo.query();
if(repo.next()){
repToBeDeletedArray.push(repo.title.toString());

total = reports + list;

}
}

gs.log('Reports from the sys_report table - ' + total + "" + 'reports will be removed' + 'Automated Report Cleanup');
gs.log('The name of those reports are -' + reportsArray.join('\n')+ ' will be removed' + 'Automated Report Cleanup');
gs.log('Additional reports -' + "" + gr.title('\n')+ 'will be removed as well' + 'Automated Report Cleanup');

//repo.deleteMultiple();
//sRep.deleteMultiple();
//gr.deleteMultiple();

Output for last log statement in bold:

*** Script: Additional reports -undefinedwill be removed as wellAutomated Report Cleanup