SELECT ( CASE
           WHEN mdh.runstatus = '1' THEN 'Start - ' + CAST(
                                         mdh.runstatus AS VARCHAR)
           WHEN mdh.runstatus = '2' THEN 'Succeed - ' + CAST(
                                         mdh.runstatus AS VARCHAR)
           WHEN mdh.runstatus = '3' THEN 'InProgress - ' + CAST(
                                         mdh.runstatus AS VARCHAR)
           WHEN mdh.runstatus = '4' THEN 'Idle - ' + CAST(mdh.runstatus AS
                                                          VARCHAR)
           WHEN mdh.runstatus = '5' THEN 'Retry - ' + CAST(
                                         mdh.runstatus AS VARCHAR)
           WHEN mdh.runstatus = '6' THEN 'Fail - ' + CAST(mdh.runstatus AS
                                                          VARCHAR)
           ELSE CAST(mdh.runstatus AS VARCHAR)
         END )                                   [Run Status],
       mda.subscriber_db                         [Subscriber DB],
       mda.publication                           [PUB Name],
       RIGHT(LEFT(mda.name, Len(mda.name) - ( Len(mda.id) + 1 )),
       Len(LEFT(mda.name, Len(mda.name) - ( Len(mda.id) + 1 ))) - (
       10 + Len(mda.publisher_db) + ( CASE
                                          WHEN mda.publisher_db = 'ALL' THEN 1
                                          ELSE Len(mda.publication) + 2
                                        END ) )) [SUBSCRIBER],
       CONVERT(VARCHAR(25), mdh.[time])          [LastSynchronized],
       und.undelivcmdsindistdb                   [UndistCom],
       mdh.comments                              [Comments],
       'select * from distribution.dbo.msrepl_errors (nolock) where id = ' +
       CAST(mdh.error_id AS VARCHAR(8))          [Query More Info],
       mdh.xact_seqno                            [SEQ_NO],
       ( CASE
           WHEN mda.subscription_type = '0' THEN 'Push'
           WHEN mda.subscription_type = '1' THEN 'Pull'
           WHEN mda.subscription_type = '2' THEN 'Anonymous'
           ELSE CAST(mda.subscription_type AS VARCHAR)
         END )                                   [SUB Type],
       mda.publisher_db + ' - ' + CAST(mda.publisher_database_id AS VARCHAR)
                                                 [Publisher DB],
       mda.name
       [Pub - DB - Publication - SUB - AgentID]
FROM   distribution.dbo.msdistribution_agents mda
       LEFT JOIN distribution.dbo.msdistribution_history mdh
         ON mdh.agent_id = mda.id
       JOIN (SELECT s.agent_id,
                    maxagentvalue.[time],
                    SUM(CASE
                          WHEN xact_seqno > maxagentvalue.maxseq THEN 1
                          ELSE 0
                        END) AS undelivcmdsindistdb
             FROM   distribution.dbo.msrepl_commands t (nolock)
                    JOIN distribution.dbo.mssubscriptions AS s (nolock)
                      ON ( t.article_id = s.article_id
                           AND t.publisher_database_id = s.publisher_database_id
                         )
                    JOIN (SELECT hist.agent_id,
                                 MAX(hist.[time]) AS [time],
                                 h.maxseq
                          FROM   distribution.dbo.msdistribution_history hist (
                                 nolock)
                                 JOIN (SELECT agent_id,
                                              Isnull(MAX(xact_seqno), 0x0) AS
                                              maxseq
                                       FROM
                                 distribution.dbo.msdistribution_history (
                                 nolock)
                                       GROUP  BY agent_id) AS h
                                   ON ( hist.agent_id = h.agent_id
                                        AND h.maxseq = hist.xact_seqno )
                          GROUP  BY hist.agent_id,
                                    h.maxseq) AS maxagentvalue
                      ON maxagentvalue.agent_id = s.agent_id
             GROUP  BY s.agent_id,
                       maxagentvalue.[time]) und
         ON mda.id = und.agent_id
            AND und.[time] = mdh.[time]