Useful SQL Queries for IBM Connections

Posted on by

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;

What if you want a list of all the email domains in your organisation that people are registered with in IBM Connections and the amount of people per domain:

SELECT RIGHT(PROF_MAIL_LOWER, LEN(PROF_MAIL_LOWER) - CHARINDEX('@', PROF_MAIL_LOWER)) Domain ,
COUNT(PROF_MAIL_LOWER) MailCount
FROM [PEOPLEDB].[EMPINST].[PROFILE_EXTENSIONS]
WHERE LEN(PROF_MAIL_LOWER) > 0 AND PROF_MAIL_LOWER LIKE '%@%'
GROUP BY RIGHT(PROF_MAIL_LOWER, LEN(PROF_MAIL_LOWER) - CHARINDEX('@', PROF_MAIL_LOWER))
ORDER BY MailCount DESC

Blogs queries

Say you want to know how many blog articles your 100 biggest Blogs have:

SELECT TOP (100) b.name, COUNT(a.websiteid) AS #Entries
FROM [BLOGS].[BLOGS].[weblogentry] AS a
LEFT JOIN [BLOGS].[BLOGS].[website] AS b ON a.websiteid=b.id
GROUP BY b.name ORDER BY '#Entries' DESC

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 non 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.

Wikis queries

We got a list of the Blogs with the most articles. What about such a list for Wikis? Here we go:

SELECT lib.TITLE, COUNT(med.LIBRARY_ID) AS #ARTICLES FROM WIKIS.WIKIS.MEDIA As med
LEFT OUTER JOIN WIKIS.WIKIS.[LIBRARY] AS lib ON med.LIBRARY_ID = lib.[ID] GROUP BY lib.TITLE ORDER BY #ARTICLES DESC

Replace TITLE by LABEL to get a Wiki IDs which you can use in your browser urls.

Adding rows to a table

Say you use a custom attribute in the Profile Extensions table to show an onboarding page or not, but not everyone in a certain country has that onboarding entry yet:

INSERT INTO [PEOPLEDB].[EMPINST].[PROFILE_EXTENSIONS] (PROF_KEY,PROF_PROPERTY_ID, PROF_VALUE) 
   SELECT [PROF_KEY],'onboarding','1' FROM [PEOPLEDB].[EMPINST].[EMPLOYEE] 
   WHERE PROF_STATE='0' AND PROF_ISO_COUNTRY_CODE='NL' AND NOT PROF_KEY IN (SELECT PROF_KEY FROM [PEOPLEDB].[EMPINST].[PROFILE_EXTENSIONS] WHERE PROF_PROPERTY_ID='onboarding')

Now I want to change the value for all people called “de Jong” to “0”

UPDATE [PEOPLEDB].[EMPINST].[PROFILE_EXTENSIONS]
SET PROF_VALUE='0'
WHERE PROF_PROPERTY_ID='onboarding' AND PROF_KEY IN (
	SELECT PROF_KEY FROM PEOPLEDB.EMPINST.EMPLOYEE WHERE PROF_MAIL_LOWER LIKE '%de.jong%')