Useful SQL Queries for IBM Connections

Over the past year, I’ve created my own set of useful SQL queries to get needed information and I thought I’d share them here. Please realise that my main client uses MS SQL for their backend, so the queries are written for that, though most should be universal or easy to change for DB2 or Oracle.

Profile queries

Let’s start with some simple stuff. I want to see the basic profile information of someone of whom I know the GUID (in many companies this would match the employee number):

SELECT * FROM [PEOPLEDB].[EMPINST].[EMPLOYEE] WHERE PROF_GUID='<employee number here>';

or the beginning of their email address (in lowercase):

SELECT * FROM [PEOPLEDB].[EMPINST].[EMPLOYEE] WHERE PROF_MAIL_LOWER LIKE '<their.name>%';

Let’s go slightly harder. We want a list of all active employees and their last logon date:

SELECT e.PROF_DISPLAY_NAME,e.PROF_GUID,e.PROF_MAIL,l.PROF_LAST_LOGIN FROM [PEOPLEDB].[EMPINST].[EMPLOYEE] AS e
INNER JOIN [PEOPLEDB].[EMPINST].[PROFILE_LAST_LOGIN] AS l ON e.PROF_KEY = l.PROF_KEY
WHERE e.PROF_STATE='0' AND e.PROF_MAIL LIKE '%@%'

This shows all employees who have a logon date, so excludes those that never logged on. If you want those inluded as well, you would use a LEFT JOIN:

SELECT e.PROF_DISPLAY_NAME,e.PROF_GUID,e.PROF_MAIL,l.PROF_LAST_LOGIN FROM [PEOPLEDB].[EMPINST].[EMPLOYEE] AS e
LEFT JOIN [PEOPLEDB].[EMPINST].[PROFILE_LAST_LOGIN] AS l ON e.PROF_KEY = l.PROF_KEY
WHERE e.PROF_STATE='0' AND e.PROF_MAIL LIKE '%@%'

Or a count of all people who logged on in the current month:

SELECT COUNT([PROF_LAST_LOGIN]) FROM [PEOPLEDB].[EMPINST].[PROFILE_LAST_LOGIN] WHERE [PROF_LAST_LOGIN] >= '20180601';

Say in your environment you’ve created a custom attribute in the profile extensions, called homepageId,┬áthat defines which ICEC homepage your users will see when they visit IBM Connections. You want to know how many people will see which page:

SELECT PROF_VALUE, COUNT(PROF_VALUE) FROM [PEOPLEDB].[EMPINST].[PROFILE_EXTENSIONS] WHERE PROF_KEY IN (SELECT PROF_KEY FROM [PEOPLEDB].[EMPINST].[EMPLOYEE] WHERE PROF_STATE=0) AND PROF_PROPERTY_ID='homepageId' GROUP BY PROF_VALUE;

 

Files queries

What if you want to know which users use the most space for their personal files? This query will show you (sizes in bytes)

SELECT u.DIRECTORY_ID AS EMPLOYEE_ID,u.NAME, Sum(CAST(m.FILE_SIZE AS BIGINT)) TOTAL_FILE_SIZE FROM [FILES].[FILES].[MEDIA] AS m
INNER JOIN [FILES].[FILES].[USER] AS u ON m.LIBRARY_ID = u.LIBRARY_ID
group by u.DIRECTORY_ID,u.NAME ORDER BY TOTAL_FILE_SIZE DESC;

Or you want a mix of users and Community titles with their file size

SELECT l.[TITLE],Sum(CAST(m.[FILE_SIZE] AS BIGINT)) TOTAL_FILE_SIZE FROM [FILES].[FILES].[MEDIA] AS m
INNER JOIN [FILES].[FILES].[LIBRARY] AS L ON m.LIBRARY_ID = l.ID
GROUP BY l.[TITLE] ORDER BY TOTAL_FILE_SIZE DESC;

Community queries

Say you want to know all private communities that were last edited between the 1st of January 2015 and the 1st of January 2017 with the creator and last modifier of those communities:

SELECT com.[COMMUNITY_UUID],com.[NAME],com.[COMMUNITY_TYPE],com.[CREATED],N1.DISPLAY AS CREATED_BY,N3.PROF_MAIL AS CREATED_MAIL,com.[LASTMOD],N2.DISPLAY AS LASTMOD_BY,N4.PROF_MAIL AS LASTMOD_MAIL,com.[MEMBER_COUNT],com.[PARENT_UUID],com.[MEMBER_UPDATED]
FROM [SNCOMM].[SNCOMM].[COMMUNITY] AS com
LEFT JOIN [SNCOMM].[SNCOMM].[MEMBERPROFILE] AS N1 ON com.CREATED_BY = N1.MEMBER_UUID
LEFT JOIN [SNCOMM].[SNCOMM].[MEMBERPROFILE] AS N2 ON com.LASTMOD_BY = N2.MEMBER_UUID
LEFT JOIN [PEOPLEDB].[EMPINST].[EMPLOYEE] AS N3 ON N1.DIRECTORY_UUID = N3.PROF_GUID
LEFT JOIN [PEOPLEDB].[EMPINST].[EMPLOYEE] AS N4 ON N2.DIRECTORY_UUID = N4.PROF_GUID
WHERE com.LASTMOD < '20170101' AND com.LASTMOD > '20150101' AND com.COMMUNITY_TYPE <> 'private'

Remember that ‘not modded’ doesn’t mean ‘not used’. It can still contain valuable information that is regularly consulted by users.