Motivation: Statistics are actually useful
The implementation of a portal is seldom enough, you need to constantly improve the portal and to improve it you need to know how the users are utilizing the portal. Having as much relvant data available as possible will help you take the correct decisions. This weblog will describe a method of retrieving how many times each user has logged onto the portal. You can use this any way you want, for example:
- Give rewards to the people using the portal the most
- Send threating letters to the people not using the portal
- See if there are any correspondance between location and usage (only applicable if your LDAP structure contains this information)
How to find the numbers of times the users have logged on
I came over the table PCD.UME_STRINGS while investigating some other problem, and saw that it contained a lot of interesting information. For each user in the portal it contains multiple rows for different attributes relating to usermanagement. You can for example find unsuccessful logons, values from custom usermappings and ,as you might have guessed, how many times the user has logged on to the portal. Please note that you should take care when accessing the PCD database directly.
To find out how many times a user has been logged on to the portal, log into the database (I prefer using Toad against oracle databases) and issue the following SQL statement:
From toad you can export the result by right clicking the result pane and selecting save as.
Note that the val field which indicates how many times you have logged on to the portal is a varchar2 field and not a number. Therefore, it is not trivial to sort this query using SQL. I'll briefly describe two methods depending on if you are using windows or unix to solve this problem.
Windows using excel
Paste the results into an excel sheet and thereafter sort the cells after the val field.
Unix
When using unix (or cygwin for windows) you can use the following command to sort the values (assuming you have exported the result from the SQL to a tab separated text file called user_login.txt)
As a small challenge, maybe you can identify what this script actually does ?
Conclusion
We have shown an easy way to retrieve how many times a user has logged on to the portal. It should be rather trivial (and I might do it sometime) to implement this as a portal component, so that you need not access the database manually.
