Details about this thread
  • State Not Answered
  • Replies 2 replies
  • Subscribers 315 subscribers
  • Views 1122 views
  • Users 0 members are here
Related content from around SugarClub

Comment Log-Reporting

Brianna Burke

Is there any way to pull the information added in the comment logs out of Sugar? I'm not seeing that it is a reportable field, but I want to make sure that we are able to export this data if needed.

Is this a function of the field today? If not, is it on the roadmap?

  • 0 Sugar Employee Badge

    Hi Brianna, 

    So, you kind of can. The information is all in the database, but you'll need to use Advanced Reports and SQL queries to get the data.

    There's a post in our dev club with more information if you're curious: https://sugarclub.sugarcrm.com/dev-club/f/questions-answers/6155/reporting-on-comment-log 

    I hope this helps! Let me know if there's anything else I can help with!

    -Raye

  • 0 Sugar Employee Badge

    Hi Brianna,

    I want to proide more detail to the Advanced Report approach Raye presented.

    At a database level, comment logs are treated as a related module to all of the modules you see them in. That is, all comments in all the comment logs across all modules are stored in the same table: commentlog.

    Each row in commentlog is correlated to a parent record through the commentlog_rel table.

    So, let's say you have a Lead with ID 8ad83352-b7c5-11ed-8586-02a5a97c2d5e. The Lead is listed in the commentlog_rel table where record_id = '8ad83352-b7c5-11ed-8586-02a5a97c2d5e' and module = 'Leads' and deleted = 0.

    To retrieve a list of the commentlog entries for that one Lead record in chronological order from oldest to newest, you could use a custom query like this:

    SELECT cl.date_entered, cl.created_by, cl.entry FROM commentlog cl
JOIN commentlog_rel clr ON cl.id = clr.commentlog_id
WHERE clr.record_id = '8ad83352-b7c5-11ed-8586-02a5a97c2d5e' and clr.module = 'Leads' and clr.deleted = 0 AND cl.deleted = 0 ORDER BY cl.date_entered ASC;

    If you want that same kind of result but for every record in the leads module, you could use something like:

    SELECT clr.record_id, cl.date_entered, cl.created_by, cl.entry FROM commentlog cl
JOIN commentlog_rel clr ON cl.id = clr.commentlog_id
WHERE clr.module = 'Leads' and clr.deleted = 0 AND cl.deleted = 0 ORDER BY clr.record_id ASC;

    If you want to just get a full export of all the comments in the entire instance and the records and module each is related to, you could use a query like this (but be warned that an unfiltered query like this can produce a lot of results, take a long time, or even time out):

    SELECT clr.module, clr.record_id, cl.date_entered, cl.created_by, cl.entry FROM commentlog cl
JOIN commentlog_rel clr ON cl.id = clr.commentlog_id
WHERE clr.deleted = 0 AND cl.deleted = 0 ORDER BY clr.record_id ASC;

    I hope this helps!

    Regards,
    Patrick McQueen
    Director, SugarCRM Support