report not associated with dashboards

[nitin51]

HI,

I got requirement to get all reports not associated with dashboards, I ran a script, but it is taking huge time and screen is blank. can you help me how to achieve this. 

script I used in backgroup script, but screen is going blank. used in fix script, but getting few transaction completed messages after every 5 reports, which makes difficult in copy paste. (there are around 10k reports)  

Help me please.

var grSysReport = new GlideRecord('sys_report');

grSysReport.addQuery('active','true');

grSysReport.addQuery('sys_created_by=*username*');

grSysReport.query();

while (grSysReport.next()) {

    var grSPP = new GlideRecord('sys_portal_preferences');

    grSPP.addEncodedQuery("name=sys_id^valueSTARTSWITH" + grSysReport.sys_id.toString());

    grSPP.orderBy('name');

    grSPP.query();

    if (!grSPP.hasNext()) {

        gs.print(grSysReport.title);

    }

}

[Bhavya11]

hi @nitin51 ,

please add the set limit in the code .it will help you pull the data.

var grSysReport = new GlideRecord('sys_report');
grSysReport.addEncodedQuery("");
grSysReport.orderBy('null');
grSysReport.setLimit(100);
grSysReport.query();
while (grSysReport.next()) {
    var grSPP = new GlideRecord('sys_portal_preferences');
    grSPP.addEncodedQuery("name=sys_id^valueSTARTSWITH" + grSysReport.sys_id.toString());
    grSPP.orderBy('name');
    grSPP.query();
    if (!grSPP.hasNext()) {
        gs.info(grSysReport.title); 
    }
}

Thanks,

BK

[nitin51]

But I need all reports not only few, as you said 100.

[Bhavya11]

Hi @nitin51 ,

increase the setlimit ()

var grSysReport = new GlideRecord('sys_report');
grSysReport.addEncodedQuery("active=true"); // Add filter if needed
grSysReport.orderBy('sys_created_on'); // Optional: order by a stable field
grSysReport.setLimit(10000); // Increase limit to 10,000
grSysReport.query();

var count = 0;
while (grSysReport.next()) {
var grSPP = new GlideRecord('sys_portal_preferences');
grSPP.addEncodedQuery("name=sys_id^valueSTARTSWITH" + grSysReport.sys_id.toString());
grSPP.orderBy('name');
grSPP.query();

if (!grSPP.hasNext()) {
gs.info(grSysReport.title);
}

count++;
}
gs.info("Total sys_report records processed: " + count);

but avoid running production because performance issue

[Bert_c1]

Your line 3: 

grSysReport.addQuery('sys_created_by=*username*');

 and got:

*** Script: Found 474 reports
...

*** Script: Portals found = 398

is invalid, try removing that from your script.

I tested:

var grSysReport = new GlideRecord('sys_report');
grSysReport.addQuery('active','true');
grSysReport.query();
gs.info("Found " + grSysReport.getRowCount() + " reports");
sppCount = 0;
noSPPCount = 0;
while (grSysReport.next()) {
    var grSPP = new GlideRecord('sys_portal_preferences');
    grSPP.addEncodedQuery("name=sys_id^valueSTARTSWITH" + grSysReport.sys_id.toString());
    grSPP.orderBy('name');
    grSPP.query();
    if (!grSPP.hasNext()) {
		noSPPCount++;
		gs.info('Report not associated with portal: ' + grSysReport.title);
    }
	else {
		sppCount++;
	}
}
gs.info("Portals not found = " + noSPPCount + ' found count: ' + sppCount);

in my PDI.  Your screen is going blank due the Transaction Quota Rule named 'UI Transactions'. Create a scheculed job that runs On Demand, then check script log statements for the results.  I changed line:

gs.print(grSysReport.title);

to:

gs.info('Report not associated with dashboard: ' + grSysReport.title);

for easy searching in Script Log Statements.  Query 'message', 'starts with', 'Report not associated with dashboard: '.

counts from my test

*** Script: Found 474 reports
...
*** Script: Portals not found = 398 found count: 76