Identify all History List messages which are unread by the recipient

Today, I'm going to write a technique through which we can track our history list message,whether the recipient has read the history llst message or not.But before moving forward we always want to know why is this important?
 Generally Reports that are sent to the History also uses the system resource such as CPU,Disk Space,DBMS etc. and users have the ability to schedule the reports to the history list. This can have a negative impact on system performance. So its better to find a way out to monitor History List Messages and whether or not they are being used.
 For this I'm gonna create a Freeform SQL report in the Enterprise manager that lists unread History List Messages.
Step 1: Open the Project Configuration for which you want to monitor.
Step 2 : Navigate to Statistics >General >Enable the "Basic Statistics", "Report Job Steps", "Subscription deliveries", and "Inbox Messages" as exampled in this screenshot:




CREATE FREEFORM SQL (FFSQL) REPORT IN ENTERPRISE MANAGER
Use the following SQL to create the FFSQL report.

SELECT 
 a11.DAY_ID as "DAY ID", 
 max(a12.MESSAGETITLE) as Message_Title, 
 max(a12.MESSAGEID) as Message_GUID, 
 max(a12.USERNAME) as USER_NAME, 
 max(a12.SUBSCRIPTIONID) as SUB_GUID, 
 max(a11.SUBINSTNAME) as SUB_INSTANCE_NAME 
FROM 
 dbo.IS_MESSAGE_STATS a11, 
 dbo.IS_INBOX_ACT_STATS a12
WHERE 
 a12.MESSAGEID NOT IN (SELECT MESSAGEID FROM IS_INBOX_ACT_STATS WHERE INBOXACTION=6) 
 AND a11.HISTORYLISTMESSAGEID=a12.MESSAGEID
 AND a11.SESSIONID=a12.SESSIONID
GROUP BY a12.MESSAGEID, a11.DAY_ID;

In the FFSQL Edit window, the SQL should look like the following screenshot:


The report will have the following layout:



SAMPLE RESULTS

TEST 1 – NO REPORTS READ FROM HISTORY LIST
Schedule 4 reports to the History List. None of them have been read.


The FFSQL Unread History List Message report will return:



TEST 2 – ONE REPORT READ FROM HISTORY LIST
One report is read in History List in MicroStrategy Web.


Only 3 History List Messages listed in the FFSQL report:


NOTE:  The Statistics Tables keep a history of the “read”.  If you manually reset a report to back to “Unread” status in the History List, it will not display on this report.  This report lists message that have never been read.