Livelink Database - Some SQL to monitor Notifications

Recently I've been putting together a series of Live Reports to help my customers monitor the usage of Notifications in their Livelink system. These reports provide a series of summary information on the status and processing of the Notifications system. It is also worth reading Understanding Livelink ECM Notifications and Event Processing on the Open Text Knowledge Center to get a better understanding of how Notifications work within Livelink.

The first set show the size of the tables involved in the Notfications process. These can be used to show any bottlenecks that are beginning to appear in your system before your users notice that Notifications are running late :

SELECT COUNT(*) FROM notifyevents
SELECT COUNT(*) FROM lleventqueue
SELECT COUNT(*) FROM notifymessages

The next set group the information to show number of events in the table grouped by date so that you can see if one day was overly heavy on Notifications and how many are close to the purge date :

Oracle SQL Server

-- notifyevents
SELECT COUNT(*) "Number",TO_CHAR(eventdate,'yyyy-mm-dd') "Date"
FROM notifyevents
GROUP BY TO_CHAR(eventdate,'yyyy-mm-dd')
ORDER BY TO_CHAR(eventdate,'yyyy-mm-dd') DESC
 
-- lleventqueue
SELECT COUNT(*) "Number",TO_CHAR(eventdate,'yyyy-mm-dd') "Date"
FROM lleventqueue
GROUP BY TO_CHAR(eventdate,'yyyy-mm-dd')
ORDER BY TO_CHAR(eventdate,'yyyy-mm-dd') DESC
 
-- notifymessages
SELECT COUNT(*) "Number",TO_CHAR(eventdate,'yyyy-mm-dd') "Date"
FROM notifymessages
GROUP BY TO_CHAR(eventdate,'yyyy-mm-dd')
ORDER BY TO_CHAR(eventdate,'yyyy-mm-dd') DESC

-- notifyevents
SELECT COUNT(*) "Number",CONVERT(CHAR(11),eventdate,106) "Date"
FROM notifyevents
GROUP BY CONVERT(CHAR(11),eventdate,106)
ORDER BY CONVERT(CHAR(11),eventdate,106) DESC
 
-- lleventqueue
SELECT COUNT(*) "Number",CONVERT(CHAR(11),eventdate,106) "Date"
FROM lleventqueue
GROUP BY CONVERT(CHAR(11),eventdate,106)
ORDER BY CONVERT(CHAR(11),eventdate,106) DESC
 
-- notifymessages
SELECT COUNT(*) "Number",CONVERT(CHAR(11),eventdate,106) "Date"
FROM notifymessages
GROUP BY CONVERT(CHAR(11),eventdate,106)
ORDER BY CONVERT(CHAR(11),eventdate,106) DESC

The next set group the information to show number of events for each Event ID :

-- notifyevents
SELECT COUNT(*) "Number",eventid "Event ID"
FROM LLEVENTQUEUE
GROUP BY eventid
ORDER BY COUNT(*) DESC
 
-- lleventqueue SELECT COUNT(*) "Number",eventid "Event ID"
FROM LLEVENTQUEUE
GROUP BY eventid
ORDER BY COUNT(*) DESC
 
-- notifymessages
SELECT COUNT(*) "Number",eventid "Event ID"
FROM notifymessages
GROUP BY eventid
ORDER BY COUNT(*) DESC

The next query shows the number of processed Notifications in the NotifyMessages table, this is the table that is used to build the Personal Notifications page. If this number is too large then the page will time out, ideally all users should have thier Notifications set for E-mail Delivery (at <livelink url>?func=Personal.ChangeAgents&mode=EDIT&report=1).

SELECT kuaf.name,count(*)
FROM notifymessages,kuaf
WHERE kuaf.id=notifymessages.userid
GROUP BY kuaf.name
ORDER BY count(*) desc

The next set of queries look at users who have been deleted, and as such their notifications are not required in the system, and in general they can be deleted from the database, this is a known Open Text issue (Bug # LPAD-1547 scheduled for Livelink 10). It is also recommended to disable their Notification Interests, this can be done either by logging in as the user or via the database as described in this Knowledge Base article to stop any new Notification events being raised for Deleted users, if there are only a small number or rows in the system for Deleted users then you could just leave them to be purged automatically as per the settings for Clear Old Messages and Clear Outstanding Events settings on <livelink url>?func=notify.config :

-- list all deleted users with schedules
SELECT kuaf.name "User" ,count(*) "Number"
FROM kuaf,agentschedule
WHERE kuaf.id=agentschedule.userid
AND kuaf.deleted=1
GROUP BY kuaf.name
 
-- check for any LLEVENTQUEUE entries for deleted users
SELECT COUNT(*) "Number",kuaf.name "User"
FROM lleventqueue,kuaf
WHERE kuaf.id=lleventqueue.eventint1
AND kuaf.deleted=1
GROUP BY kuaf.name
ORDER BY COUNT(*) DESC
 
-- check for any NOTIFYMESSAGES entries for deleted users
SELECT COUNT(*) "Number",kuaf.name "User"
FROM notifymessages,kuaf
WHERE kuaf.id=notifymessages.userid
AND kuaf.deleted=1
GROUP BY kuaf.name
ORDER BY COUNT(*) DESC

Website Designed by Adservio Consulting      Bookmark and Share Valid HTML 4.01 Strict    Valid CSS!    Level A conformance icon, W3C-WAI Web Content Accessibility Guidelines 1.0