A customer has asked for a list of all users and the last time they have accessed Connections. Ideally this would be a repeatable exercise and would output the user’s name, email address, last log in date/time and their DN.
As Connections is a collection of applications there are a number of databases that hold this information and it is possible to log into one and not the other, for example, a user might have logged into Blogs last in January but logged into Wikis yesterday.
There are two parts to this, 1) finding the database tables and columns that need to be referenced and 2) writing a SQL query that will return the data the customer wants. The customer wants a single entry for each user but only wants to know the latest log in date/time. In the example above, they want the date/time of the user when they logged into Wikis which was yesterday as well as the information about the user.
My bit is the easy bit which is mapping the database details for the DB2 DBA to write the query. Below describes the database tables of relevance and the columns that would need to be referenced.
The hard bit will be for the DBA to write the query since for this deployment each database runs in it’s own instance and it administered by it’s own ID. Possibly more to follow……