KB Article #150761

SQL script to track SM messages sent from Web UI

Problem

When a message is sent from within Secure Messenger Web UI it never enters and is never processed by Policy Engine (PE). Therefore, it lacks message ID and is hard to track in contrast to messages sent from outside SM Web UI.


Resolution

In order to track such messages, the following SQL script is crafted:



SELECT   t1.subject                      AS Subject        ,
        t2.address                            AS Recipient      ,
        t2.packageuserid                 AS Secure_Message ,
        t3.eventtime                         AS EventTime      ,
        t3.eventclassid                     AS EventClassID   ,
        t3.componentname              AS ComponentName  ,
        t3.eventdetails                     AS EventDetails
FROM     messengerpackage       AS t1
        INNER JOIN messengerpackageuser AS t2
        ON       t1.packageid = t2.packageid
        JOIN eventlogevents AS t3
        ON       t3.eventdetails LIKE '%message ' + parsename(convert(varchar,convert(money,t2.packageuserid),1),2) + ' from%'
WHERE    t3.eventclassid             = 15014
AND      t2.address               LIKE '%user@domain.com%'
ORDER BY t3.eventtime;



Please note that 'user@domain.com' is a placeholder for actual information needed to track a specific message. You can replace it with any valid matching pattern to reflect your search criteria.