Need SQL query to see saved favorites in CA Web Viewer 12.1

Products

• Output Management Web Viewer

Issue/Introduction

Need SQL query to see saved favorites in Web Viewer

Environment

Release : 12.1

Component : CA OUTPUT MANAGEMENT WEB VIEWER FOR ALL PLATFORMS

WBVLUW

MSSQL

MS SQL Server

DB2

Resolution

Here is SQL, specifically for Windows MSSQL, please modify for your specific database.  Please be aware that if you are actually running with the internal Derby database that is included with your Tomcat server, there is no way to run outside queries against that.

This will provide you a count of the total number of favorites, both for reports and for saved filters that can be defined:

SELECT COUNT(*) FROM favorieObj

Or you can drill down for specifics like a specific Repository Definition in the Administration->Repositories, or a particular User with: (ignoring <,>)

SELECT COUNT(*) FROM favorieObj WHERE REPOSITORYNAME = '<defined name>'

SELECT COUNT(*) FROM favorieObj WHERE USERID = '<user id>' 

Note: favorieObj is not a misspelling. That is the actual name.

Due to the complexity of Favorites being tied to Group Admins, User Roles, and other considerations, it is not possible to get a list of Favorites that are accessed by everyone. You would need to do a "Select *" display of the favorieObj table, sort the list in Excel or a like editor, and then do individual:
SELECT * FROM favorieObj WHERE USERID = ''

...for each of the ID's and compare those outputs for report commonalities.