Clarity

Expand all | Collapse all

SQL listing users and the names of portlets they have accessed (and dates)

  • 1.  SQL listing users and the names of portlets they have accessed (and dates)

    Posted 10-06-2015 04:36 PM


    Hi,

     

    For Clarity version 12, looking for some SQL to pull a list from the database of the Clarity users, the names of the custom portlets that they have accessed over a given time period, and the dates they accessed the portlets.  Need this to determine what portlets we can omit during upgrade.

     

    something similar to this:

     

     


     

    Clarity User ID


     

     

    Page/Portlet


     

     

    Data Accessed / Used


     

     

    12345


     

     

    odf_***2


     

     

    25-Sep-15


     

     

    23456


     

     

    odf_xx234


     

     

    25-Sep-15


     

     

    34567


     

     

    odf_xx234


     

     

    1-Oct-15


     

     

    Anyone have any sample code for this to get me started?  Appreciate any help.

     

    Thanks,

    Sam



  • 2.  Re: SQL listing users and the names of portlets they have accessed (and dates)

    Posted 10-06-2015 04:55 PM

    Sam,

     

    As far as i'm aware the logging of what portlets run/pages viewed is not available in the database.

    You can find some of this information in the log, but you'd need to scrape it.

    https://communities.ca.com/message/111650874

     

    For BO reports, you can find this out from the CMC via a query



  • 3.  Re: SQL listing users and the names of portlets they have accessed (and dates)

    Posted 10-06-2015 05:02 PM

    Thanks Andy. Appreciate your prompt reply.

     

    -Sam



  • 4.  Re: SQL listing users and the names of portlets they have accessed (and dates)

    Posted 10-07-2015 10:25 AM

    Andy, et. all,

     

    Can you let me know which logs the infomation would be contained in?  Also, has anyone tried to "scrape" the log for this user/portlet access information and be willing to share the steps you took?  For instance is there perhaps some unix script, perl or other code that can be shared?

     

    Thanks,

    Sam



  • 5.  Re: SQL listing users and the names of portlets they have accessed (and dates)

    Posted 10-07-2015 12:29 PM

    You could use the access logs, but it would be somewhat complicated.

     

    1. You would need to resolve the session id in the log against the user. So you would need to make sure that you were able to persist the contents of CMN_SESSIONS after a session was destroyed.

     

    2. You would need to decide how you define "accessed". Does the user have to click on the filter button of a portlet for it to count as accessed? Or just navigate to a page containing the portlet for it to count as accessed? In some cases, it would make sense for you to count the latter as an access, but others, not. In the case of the former, I'm not even sure that you can distinguish a "Filter" click for a specific portlet.

     

    3. For any sort of non-trivial implementation, you will have a ton of data to parse.