Active users in Lotus Connections
A customer asked how the number of active users (not only logged in but actually added content somewhere like created an activity, made a comment etc.) could be retrieved from Lotus Connections.
As you probably know from the Info Center Lotus Connections provides statistics for each component but not consolidated for all of them on one page.
When I looked at the database it became clear that a simple SQL UNION would not work as by default different databases are created during installation.
As I personally love the functionality of IBM Mashup Center I came up with the idea to combine the results from each database script and provide the output as an ATOM feed for further processing.
Here’s what I did:
- First I created 5 SQL scripts for Profiles, Communities, Blogs, Dogear, and Activities to retrieve the GUIDs of all active users.
- Then I used those SQL scripts as feeds in InfoSphere MashupHub. Here some screenshots:
3. After I defined 5 feeds, I created a feed mashup using simple operators and published the result as an ATOM feed:
4. In Lotus Mashups I made a sample page to verify everything worked as expected. On the left hand side you can see the feeds I created in 2. and as you may notice different users were active in Activities and Communities. On the right hand side you can see the feed mashup and how it combines the active users into one consolidated result…
I’m loving it! 
Good work!
However, this kind of analytics really needs to be in the Connections product as shipped. As you’ve shown, it’s not difficult as the data is there in the RDBMS, it just needs to be exposed in the correct manner.
Perhaps for LC3.0?
This is very cool! I missed this post earlier. Great job!!!!! I’m going to blog about it!
Great example, but I agree with Stuart, more needs to be done OOB in Connections in this space. Thanks for sharing!
We’ve written a large number of custom reports using Crystal Reports on top of the SQL. In some cases we’ve used triggers to aggregate data to simplify the reporting, this looks like a nice approach.
Nice!