How To - clean out orphaned custom dashboard reports

[RSAAdmin]

For numerous reasons it is possible that there may be orphaned Custom Dashboard Reports found on the Manage Dashboards page that when compared against the Dashboards Report page are not present.  There is no performance impact to this, nor does hurt anything to have orphaned reports.  But if you manage permissions on a lot of them, it's nice to keep that list as clean as possible and free from clutter.  So, in the interest of keeping enVision healthy and fine tuned, I felt it necessary to share the resolution steps with others here on the forum.  The following steps are what I had to perform in order to remove them. Many thanks to enVision support engineer Greg Wetmore for assisting me with this effort. If you're at all in question, I'd highly suggest you perform these steps under the guidance of an RSA support engineer in the context of an official support case.   Mileage may vary, use at your own risk, etc, etc. 

How do I know if I have orphaned dashboard reports?  Making a list.
--------------------------------------------------------------------
1. In the GUI, expand the Dashboard Reports on the left hand tree, so you can see all of the dashboard reports.
2. Click once on "Manage Dashboard" on the left hand tree to fill the right side of the GUI with the Manage Dashboard Items page.
3. Sort by Name.
4. At this point you should be able to compare the entries under Dashboard Reports (on the left) to the entries in Manage Dashboard Items (on the right).
5. For all the entries you find on the right, that aren't on the left... these are orphaned reports.  Fire up your favorite notepad program and copy/paste the Name into a file that you will later reference.

How do I clean them out?
------------------------
Well basically we do a little cosmetic surgury on the nic.db by deleting the report from the dashboarditem table and it's corresponding permissions entry(ies) in the permissions table.  It sounds easy enough but specifically here's how...
6. Log into the appliance (ASRV on a multi-appliance site) and launch %_envision%\database\startISQL.cmd
7. In the SQL Statements window type select * from dashboarditem and click on the execute button. (looks like a play button).
8. For each truncated report you found in the above step 5, you should notice that there is a matching entry for it in the Report Name field of this table.  What we're after here is it's GUID.   The report GUID should be a long hexadecimal string starting with 0x and can be found in the guid field.  Copy that into your reference text file, since your going to be using it later.  MAKE SURE you have the right one.   Someone will be angry if you nuke the wrong report! While your here, you might as well repeat this step for all the orphaned reports, making note of their GUIDS.
9. Double check that you did step 8 correctly.  No seriously, go do it.
10. Now while I'm here, I right click on the ones I want to delete, and click delete.  Repeating this for each orphaned report.  This cleans them out of the dashboarditem table.   We're not done yet though.
11. In the SQL statements window type select * from permission  where (restype = 'dash' and resguid=[your guid from step 8]) and click on the execute button.  
For example: select * from permission  where (restype = 'dash' and resguid=0x0346b5bc1c9b9841011caec4e1e45f94)
12. Confirm that the entries returned are just the ones for your orphaned report.  In some cases, you may have more than one entry returned, but the resguid's should all match the GUID you wrote down.  I noticed some of my orphaned reports had as many as 8 or 12 permissions entries in this table.
13. Delete all of the entries in the permission table for your particular orphaned report.  It's easy enough to just change your select SQL statement to delete from permission  where (restype = 'dash' and resguid=0x0346b5bc1c9b9841011caec4e1e45f94), click execute.  You'll notice the records disapear. Be sure to clear out the statement window, type commit and click execute again.   You've now just deleted the permissions entries for this one report.  Obviously if you have more reports, you need to repeat this step for each one.
14. Go to the etc\reports\custom\Dashboard directory and remove the offending report .xml file from there also.
15. So to recap.. you located the orphaned reports, deleted their entries in the dashboarditem table and in the permission table, and deleted the .xml files.
16. Last step is to bounce the NIC Web Server service.  If you did the above steps correctly, it will restart and you'll be able to log back in and notice all the orphaned reports will be gone, and things should match up well between the Dashboard Reports and Manage Dashboard pages.

If something goes wrong, you might get an error message when trying to log into the GUI... GUI will be useless until you find and remove the offending entries in the permissions table that are causing the web service to hang up.  If this happens, you can reference your %_envision%\logs\pi_webserver.log file for the offending GUID.  There are other things that can be done also, but at this point you'd want to call support to open up a case.

I hope you never end up with orphaned reports in the first place, but if you do best of luck in your effort to remove them.

Cheers,
ryan