Find out which messages are published and which are draft in a courses's discussion forum (including its groups)

Whats New module: Discussion Board unread messages - counts Draft Posts (AS-139044)

There's a bug in 9.1 (at least up to SP3) where the Whats New module counts unread messages that are in DRAFT mode. This issue is targeted to be fixed in Release 9.1 Service Pack 4 as bug number AS-139044

Note: DRAFT posts have msg_main.lifecycle = 'DRAFT'

DECLARE @course_batch_uid NVARCHAR(256);
SET @course_batch_uid = 'ian001'
--
SELECT [msg_main].[pk1]
      ,[msg_main].[dtcreated]
      ,[msg_main].[dtmodified]
      ,[msg_main].[posted_date]
      ,[msg_main].[last_edit_date]
      ,[msg_main].[lifecycle]
      ,[msg_main].[text_format_type]
      ,[msg_main].[post_as_annon_ind]
      ,[msg_main].[cartrg_flag]
      ,[msg_main].[thread_locked]
      ,[msg_main].[hit_count]
      ,[msg_main].[subject]
      ,[msg_main].[posted_name]
      ,[msg_main].[linkrefid]
      ,[msg_main].[msg_text]
      ,[msg_main].[body_length]
      ,[msg_main].[users_pk1]
      ,[msg_main].[forummain_pk1]
      ,[msg_main].[msgmain_pk1]
 FROM [bb_bb60].[dbo].[msg_main] as [msg_main]
 LEFT JOIN [bb_bb60].[dbo].[forum_main] as [forum_main]
 ON [msg_main].[forummain_pk1] = [forum_main].[pk1]
 LEFT JOIN [bb_bb60].[dbo].[conference_main] as [conference_main]
 ON [forum_main].[confmain_pk1] = [conference_main].[pk1]
 WHERE [conference_main].[conference_owner_pk1] in
 (
   -- subselect list of conference owner pk1's related to the course or groups within the course
   SELECT [conference_owner].[pk1]
   FROM [bb_bb60].[dbo].[conference_owner] as [conference_owner]
   LEFT JOIN [bb_bb60].[dbo].[groups] as [groups]
   ON [conference_owner].[owner_table]='GROUPS' AND [conference_owner].[owner_pk1]=[groups].[pk1]
   LEFT JOIN [bb_bb60].[dbo].[course_main] as [course_main]
   ON (
     ([conference_owner].[owner_pk1] = [course_main].[pk1] and [conference_owner].[owner_table] = 'COURSE_MAIN')
       OR [groups].[crsmain_pk1] = [course_main].[pk1]
      )
   WHERE [course_main].[batch_uid] = @course_batch_uid
)