I am a: Quirky sysadmin. Geek. Gamer. Comic fangirl. My little space, my home on the interwebs. I blog about personal stuffs, work, various coding projects, and anything else I feel like.

I wanted to share this in case it’s helpful to someone else. I had been tasked with finding out what was the last date and time a user had logged into our Connections environment. I was already familiar with the EMPLOYEE table, but I knew I would need a way to cross reference with the other tables to find the information I needed. With the help of a LOT of Googling, and this blog post by Ben Williams, I was able to piece together the following information. Keep in mind that login/access information is stored for each Connections application independently, so you’ll have to find this information for each app …

Using the mangled table above as a reference, you’ll need to find the user’s PROF_KEY from the EMPLOYEE table. Once you have that, use it in the following query:

SELECT PROF_KEY, PROF_LAST_LOGIN, PROF_LOCALE, TENANT_KEY FROM “EMPINST”.”PROFILE_LAST_LOGIN”
WHERE PROF_KEY = ‘PROF_KEY Value Here’

Example results:

PROF_KEY                                 PROF_LAST_LOGIN             PROF_LOCALE     TENANT_KEY
------------------------------------     -----------------------     -----------     ------------------------------------ 
6220ce7b-dcc4-4516-84e0-4d498b553f59     2012-09-26 04:41:09.166     NULL            00000000-0000-0000-0000-040508202233 

Bravo! Now let’s find the rest. Follow suit with the following queries to find the rest of the login date/time(s):

SELECT ID, EXTID, USERNAME, PASSPHRASE, FULLNAME, EMAILADDRESS, DATECREATED, LOCALE, TIMEZONE, ISENABLED, LASTLOGIN, STATE, ORGID FROM “BLOGS”.”ROLLERUSER”
WHERE FULLNAME LIKE ‘%Smith%’

SELECT MEMBER_UUID, DIRECTORY_UUID, DISPLAY, EMAIL, LOCALE, LASTLOGIN, STATE, ORG_ID, GROUP_LAST_SYNCH, LOWER_DISPLAY FROM “SNCOMM”.”MEMBERPROFILE”
WHERE EMAIL LIKE ‘%Smith%’

SELECT EXID, MEMBERID, MEMBERDISP, EMAIL, MEMBERTYPE, DYNAMIC, LOCALE, CREATED, LASTLOGIN, STATE, THEMEID, ORGMEMBERUUID, FLAGS, LEMAIL, LMEMBERDISP FROM “ACTIVITIES”.”OA_MEMBERPROFILE”
WHERE EMAIL LIKE ‘%Smith%’

SELECT ID, DIRECTORY_ID, EMAIL, NAME, ROLES, CREATE_DATE, LAST_VISIT, DIRECTORY_LAST_UPDATE, DIRECTORY_GROUP_LAST_UPDATE, COMMUNITY_GROUP_LAST_UPDATE, LIBRARY_ID, STATE, PREFERRED_PAGE_SIZE, PREFERRED_VIEW, FORMATTING_LOCALE, TRANSLATION_LOCALE, LOWERCASE_EMAIL, LOWERCASE_NAME, PREFERRED_COLUMNS, TYPE, ORG_ID, FILESYNC_PREFERENCE FROM “WIKIS”.”USER”
WHERE EMAIL LIKE ‘%Smith%’

SELECT EXID, MEMBERID, MEMBERDISP, EMAIL, MEMBERTYPE, DYNAMIC, LOCALE, CREATED, LASTLOGIN, STATE, THEMEID, ORGID FROM “FORUM”.”DF_MEMBERPROFILE”
WHERE EMAIL LIKE ‘%Smith%’

SELECT PERSON_ID, DISPLAYNAME, EXID, USER_MAIL, USER_MAIL_LOWER, DISPLAYNAME_LOWER, STATE, LAST_UPDATE, SAND_OPT, SAND_LAST_UPDATE, PROF_TYPE, CREATION_DATE, THEME_ID, COMM_VISIBILITY, MEMBER_TYPE, ORGANIZATION_ID, COMMUNITY_INTERNAL_ONLY, LAST_CONN_VISIT FROM “HOMEPAGE”.”PERSON”
WHERE DISPLAYNAME LIKE ‘%Smith%’

SELECT PERSON_ID, MEMBER_ID, EMAIL, DISPLAYNAME, FIRSTLOGIN, LASTLOGIN, LASTREFRESH, HASLINKS, LOCALE, STATE FROM “DOGEAR”.”PERSON”
WHERE EMAIL LIKE ‘%Smith%’

SELECT ID, DIRECTORY_ID, EMAIL, NAME, ROLES, CREATE_DATE, LAST_VISIT, DIRECTORY_LAST_UPDATE, DIRECTORY_GROUP_LAST_UPDATE, COMMUNITY_GROUP_LAST_UPDATE, LIBRARY_ID, STATE, PREFERRED_PAGE_SIZE, PREFERRED_VIEW, FORMATTING_LOCALE, TRANSLATION_LOCALE, LOWERCASE_EMAIL, LOWERCASE_NAME, PREFERRED_COLUMNS, TYPE, ORG_ID, FILESYNC_PREFERENCE FROM “FILES”.”USER”
WHERE EMAIL LIKE ‘%Smith%’

Not very elegant, I know, but got the job done. Later, when I get more time to play around with it, I will write a script to do all of this for me. This is the quick and dirty method to find what I needed at the time.