Ever wondered how to get the list of top viewed or most commonly used Clarity portlets/pages in your organization? Your only chances were either to analyze all the app_access logs on the Clarity server or to use the resource-heavy Performance Tracking feature in CSA. But if you are running a Tomcat application server for your Clarity installation and your Clarity version is v14.1 or higher, then you can get your hands on this information pretty much easily. Interested? Read on...
Setting Up The Log Tables
This is possible due to the addition of two stock jobs from v14.1, which are:
- Tomcat Access Log Import/Analyze Job
--- imports and analyzes Clarity cluster Tomcat access log files
--- inserts thus collected information in LOG_DETAILS, LOG_SUMMARY, LOG_FILES and LOG_REPORTDEFS tables
--- Log Date parameter = date for access logs that are imported and analyzed.
--- Set this parameter to Today using the Relative Date option and schedule this job to run daily.
- Delete Log Analysis Data Job
--- Purge Log Analysis data and session tracking data over N days old
--- Log retention in days parameter = number of days data is retained in the above tables.
--- This job parameter determines the duration on which we will be doing our analysis. So set it appropriately.
Once you have scheduled both the above jobs properly to run on a daily-basis, meaningful data will start accumulating in the above mentioned LOG_XXXXX tables. Then it's just a matter of running the below given SQL query to get the list of most commonly used or viewed portlets in your Clarity application. Feel free to edit the query accordingly if you want to see only the most viewed pages in Clarity PPM.
NOTE: The output of the below query won't be so accurate. That is because there can be multiple portlets in the same tab and we only get the short version of the URL in the url_short column in LOG_DETAILS table. So if you place two portlets in the same tab, one view will be credited to both the portlets as the page URL remains the same for both of them.
Most Viewed Portlets Query
cpp.portlet_id = cportlets.id
AND cpp.page_id = cpages.id
AND cpp.principal_type = 'SYSTEM' ----- To exclude user added portlets to personalized pages/tabs like the Overview page
AND cportlets.portlet_type_code != 'SYSTEM' ----- To exclude System portlets like Action Items, Favorite Links, Favorite Photo etc
AND ccn.table_name = 'CMN_PORTLETS'
AND ccn.language_code = 'en'
AND ccn.pk_id = cportlets.id
AND cpages.page_code = ld.url_short
GROUP BY ccn.name, cportlets.portlet_code, cportlets.portlet_type_code, cportlets.source
ORDER BY COUNT(*) DESC, ccn.name
You can easily convert the above SQL code to NSQL query and then create a Grid portlet on it so that this information can be accessed by privileged users from within Clarity itself. Don't forget to provide a disclaimer that the user is only looking at data retrieved over a period of last N days (determined by the value you set for the Log retention in days parameter in your Delete Log Analysis Data job schedule).
Finally, a BIG THANKS to CA for adding this Apache Tomcat Log Analysis feature to CA PPM. We love you