We’re so close to sunsetting our Connections environment at work. I figured I would document this here showing how we’ve been monitoring activity in the ailing environment. Our Cognos is disabled, so I have been having to pull this information manually. Twice weekly, I pull the the most recent activity in Communities and Activities, coupled with the most recent login information for users in all other Connections applications.

The Jython commands needed for the former could probably be scripted, but I couldn’t make it work with our current environment and honestly it’s easy to just copy and paste the few lines needed to build those text files. I have macros written to clean up the data as needed and save as CSV to allow me to pull everything together for a report in Excel for my boss if needed.

I wrote a couple of batch files to query the DB2 database for the latter. For whatever reason I had to split things into one batch file calling the other. I’m sure someone can come up with a more elegant solution, but it worked for my needs.

First batch file is simple, open a DB2 command line window, execute the second batch file, and write all output to to a log file on the desktop.

E:\IBM\SQLLIB\BIN\db2cmd.exe /c /w /i “C:\Users\db2admin\Desktop\2.bat > C:\Users\db2admin\Desktop\out.log”

The second batch file is a list of queries for DB2:

db2 connect to OPNACT
db2 EXPORT TO \”C:\Users\db2admin\Desktop\activities.txt\” OF DEL modified by nochardel timestampformat=\”YYYY/M/D H:MM TT\” SELECT MEMBERDISP, LASTLOGIN FROM \”ACTIVITIES\”.\”OA_MEMBERPROFILE\” WHERE LASTLOGIN IS NOT NULL ORDER BY LASTLOGIN DESC
db2 connect reset
db2 connect to SNCOMM
db2 EXPORT TO \”C:\Users\db2admin\Desktop\communities.txt\” OF DEL modified by nochardel timestampformat=\”YYYY/M/D H:MM TT\” SELECT DISPLAY, LASTLOGIN FROM \”SNCOMM\”.\”MEMBERPROFILE\” WHERE LASTLOGIN IS NOT NULL ORDER BY LASTLOGIN DESC
db2 connect reset
db2 connect to FILES
db2 EXPORT TO \”C:\Users\db2admin\Desktop\files.txt\” OF DEL modified by nochardel timestampformat=\”YYYY/M/D H:MM TT\” SELECT NAME, LAST_VISIT FROM \”FILES\”.\”USER\” ORDER BY LAST_VISIT DESC
db2 connect reset
db2 connect to WIKIS
db2 EXPORT TO \”C:\Users\db2admin\Desktop\wikis.txt\” OF DEL modified by nochardel timestampformat=\”YYYY/M/D H:MM TT\” SELECT NAME, LAST_VISIT FROM \”WIKIS\”.\”USER\” WHERE LAST_VISIT IS NOT NULL ORDER BY LAST_VISIT DESC
db2 connect reset
db2 connect to PEOPLEDB
db2 EXPORT TO \”C:\Users\db2admin\Desktop\profiles.txt\” OF DEL modified by nochardel timestampformat=\”YYYY/M/D H:MM TT\” SELECT PROF_DISPLAY_NAME,PROF_LAST_LOGIN FROM \”EMPINST\”.\”PROFILE_LAST_LOGIN\” a INNER JOIN \”EMPINST\”.\”EMPLOYEE\” b ON a.PROF_KEY = b.PROF_KEY ORDER BY PROF_LAST_LOGIN DESC
db2 connect reset
db2 connect to HOMEPAGE
db2 EXPORT TO \”C:\Users\db2admin\Desktop\homepage.txt\” OF DEL modified by nochardel timestampformat=\”YYYY/M/D H:MM TT\” SELECT DISPLAYNAME, LAST_UPDATE FROM \”HOMEPAGE\”.\”PERSON\” ORDER BY LAST_UPDATE DESC
db2 connect reset
db2 terminate

I have all the results export as text files so that I can display them via a single index.htm file on the desktop. My boss liked seeing all info at once, so this was a quick and simple solution that suited my needs.

I think now my users are starting to realize we’re serious about shutting this thing down. (as if I’d kid about that sort of thing) People are reaching out to me with “I got 7 years worth of data I want to keep, please don’t shut down Connections!” to which I remind them that the entire IT department has been telling folks for nearly 3 years that this was coming. We have two WAS servers and they went toes up a few months back, and were down for nearly 2 months. I seriously thought one guy was going to have a coronary because he stored business-critical files on an antiquated platform that was being decommissioned in just a matter of days (then just 3-4 months).

Another accused me of not telling him this was coming. Thankfully we were not face to face, because I would have straight up laughed in his.