SQL query to see saved favorites in OM Web Viewer 12.1

Products

• Output Management Web Viewer

Issue/Introduction

Need SQL query to see saved favorites in Web Viewer

Environment

• Output Management Web Viewer 12.1
• MSSQL
• MS SQL Server
• Db2
• Apache Tomcat®

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 Apache 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.