Notifications
Clear all
Topic starter
Does anyone know the table that secure messages are held in? There is a FLAG table to hod Flags, but what table holds the messages?
Want to build a report to show message traffic/activity and also provide a count for messages on a user desktop.
Thanks.
Posted : August 10, 2015 4:21 am
In our system, secure messages are stored in a separate database called "Enterprise" where our Kryptiq Patient Portal is installed. The MessageEventLog table might hold what you need.
Posted : August 10, 2015 5:39 am
Topic starter
We are using CEMR 9.8 with Oracle, and I do not see that table defined.
Thanks.
Posted : August 10, 2015 5:55 am
It shouldn't matter which Centricity platform you use. I think that secure messaging is always stored separately from the EMR. We started out on CEMR, but the Messaging database has always been on a separate Kryptiq SQL server instance.
Posted : August 10, 2015 6:05 am
Our Secure Messaging is also in a separate DB called Enterprise.
Inside of the Enterprise DB there is a table called "dbo.Message". I believe you would be able to create a report to run there as it gives you the dates messages are sent and received and a lot of other useful information.
Posted : August 10, 2015 6:26 am
In our system, the Enterprise database is on an entirely different server, so that may be why you're not seeing it.
I can tell you from experience that figuring out how to query anything useful out of the Enterprise database can be quite a task. Feeding any of that data back to a user's desktop would be an interesting challenge indeed.
Posted : August 10, 2015 6:37 am
As others have said that information is stored in a separate database (for Kryptic). There is no database documentation about how it works, as Ron experienced as well we've basically had to guess and check to pull anything out of that database...
Posted : August 11, 2015 4:45 am
Here are a couple script that will pull the users secure messages that they have in their "in box" and another that can be used to search by email address for any messages sent to that email address. We are on CPS 12.
First one is to see who a user is sending secure messages to.
select m.SenderName, m.Sender, mi.RecipientName, mi.EmailAddress,m.Subject, m.SentDate, m.ReceivedDate,
mi.IsDeleted, mi.IsRead, mi.IsReplied, mi.IsForwarded,mi.IsSaved
from message m
left outer join MessageInstance mi on mi.MessageGuid = m.MessageGuid/* Replace XX with senders email address. Example: test@test.com */
where m.Sender = 'XX'
Second one is to view messages to a particular email address:
select m.SenderName, m.Sender, mi.RecipientName, mi.EmailAddress,m.Subject, m.SentDate, m.ReceivedDate,
mi.IsDeleted, mi.IsRead, mi.IsReplied, mi.IsForwarded,mi.IsSaved, m.NumAttachments
from message m
left outer join MessageInstance mi on mi.MessageGuid = m.MessageGuid/* Replace XX with senders email address. Example: test@test.com */
where mi.EmailAddress = 'xx'
Here is a third one that is a little more complicated. It joins the enterprise database to your centricity database to be able to pull a count of sent secure messages and displays the senders email address, senders fullname and the count of messages sent. You will need to replace portions with your database name. (Highlighted in Red)
select distinct(sender), (df.first + ' ' + df.last) as FullName,count(sender) as Count
from enterprise..[Message] m
left join CentricityPS..DoctorFacility df on substring(sender,0,charindex('@',sender,0)) = df.LoginUser
where sender like '%place your email domain here%' and not(df.listname is null)
group by m.sender, df.first, df.last
order by count(sender) desc
Posted : August 13, 2015 5:07 am
