Get replication Status from Multiple Publishers multiple subscribers and distributors

USE [Distribution]
GO

/****** Object:  Table [dbo].[ReplicationStatus]    Script Date: 6/24/2016 2:09:00 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ReplicationStatus](
[PublisherServername] [varchar](250) NULL,
[SubscriberServerName] [varchar](250) NULL,
[PublisherDB] [varchar](250) NULL,
[SubscriberDB] [varchar](250) NULL,
[DistributionStatus] [varchar](250) NULL,
[LogReaderStatus] [varchar](250) NULL,
[DateInserted] [datetime] NOT NULL DEFAULT (getdate())
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO






USE [Distribution]
GO

/****** Object:  StoredProcedure [dbo].[CheckReplicationStatus]    Script Date: 6/24/2016 1:45:42 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Proc [dbo].[CheckReplicationStatus]
 as
BEGIN

---SQl1
BEGIN

INSERT INTO Distribution.dbo.ReplicationStatus
SELECT
'FPVI-SQL4' as Servername,
dl.subscriber_server,
dl.publisher_db,
dl.subscriber_db,
CASE dl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS DistributionRunStatus,
CASE rl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS LogReaderRunStatus,
GETDATE()
FROM (SELECT agent_id,
publisher_db,
Upper(ss.datasource) AS subscriber_server,
subscriber_db AS subscriber_db,
[name] AS distribution_agent,
DH.runstatus,
CASE
WHEN DH.runstatus = 1 THEN Datediff(SS, DH.TIME, Getdate())
WHEN DH.runstatus = 3 THEN DH.delivery_latency
ELSE 0
END AS distribution_latency
FROM (SELECT DHMAX.*
FROM distribution.dbo.MSdistribution_history DHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS TIME
FROM distribution.dbo.MSdistribution_history DH WITH(NOLOCK)
GROUP BY agent_id) MTDH
ON DHMAX.agent_id = MTDH.agent_id
AND DHMAX.TIME = MTDH.TIME) DH
JOIN distribution.dbo.MSdistribution_agents DA WITH(NOLOCK)
ON DH.agent_id = DA.id
JOIN MASTER.dbo.sysservers ss WITH(NOLOCK)
ON da.subscriber_id = ss.srvid) AS DL
LEFT OUTER JOIN (SELECT agent_id,
la.publisher_db,
lh.runstatus,
CASE
WHEN lh.runstatus = 1 THEN Datediff(SS, LH.TIME, Getdate())
WHEN lh.runstatus = 3 THEN lh.delivery_latency
ELSE 0
END AS reader_latency
FROM (SELECT LHMAX.*
FROM distribution.dbo.MSlogreader_history LHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS [time]
FROM distribution.dbo.MSlogreader_history WITH(NOLOCK)
GROUP BY agent_id) MTLH
ON LHMAX.agent_id = MTLH.agent_id
AND LHMAX.TIME = MTLH.TIME) LH
JOIN distribution.dbo.mslogreader_agents LA WITH(NOLOCK)
ON lH.agent_id = lA.id) AS RL
ON DL.publisher_db = RL.publisher_db
ORDER BY dl.publisher_db,
dl.distribution_agent
END





 BEGIN
---sql1
INSERT INTO Distribution.dbo.ReplicationStatus
SELECT
'FPSQL1' as Servername,
dl.subscriber_server,
 dl.publisher_db,
dl.subscriber_db,
 CASE dl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS DistributionRunStatus,
 CASE rl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS LogReaderRunStatus,
GETDATE()
FROM (SELECT agent_id,
publisher_db,
Upper(ss.datasource) AS subscriber_server,
subscriber_db AS subscriber_db,
[name] AS distribution_agent,
DH.runstatus,
CASE
WHEN DH.runstatus = 1 THEN Datediff(SS, DH.TIME, Getdate())
WHEN DH.runstatus = 3 THEN DH.delivery_latency
ELSE 0
END AS distribution_latency
FROM (SELECT DHMAX.*
FROM [FPMC-DIST1].distribution.dbo.MSdistribution_history DHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS TIME
FROM [FPMC-DIST1].distribution.dbo.MSdistribution_history DH WITH(NOLOCK)
GROUP BY agent_id) MTDH
ON DHMAX.agent_id = MTDH.agent_id
AND DHMAX.TIME = MTDH.TIME) DH
JOIN [FPMC-DIST1].distribution.dbo.MSdistribution_agents DA WITH(NOLOCK)
ON DH.agent_id = DA.id
JOIN [FPMC-DIST1]. MASTER.dbo.sysservers ss WITH(NOLOCK)
ON da.subscriber_id = ss.srvid) AS DL
LEFT OUTER JOIN (SELECT agent_id,
la.publisher_db,
lh.runstatus,
CASE
WHEN lh.runstatus = 1 THEN Datediff(SS, LH.TIME, Getdate())
WHEN lh.runstatus = 3 THEN lh.delivery_latency
ELSE 0
END AS reader_latency
FROM (SELECT LHMAX.*
FROM [FPMC-DIST1].distribution.dbo.MSlogreader_history LHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS [time]
FROM [FPMC-DIST1].distribution.dbo.MSlogreader_history WITH(NOLOCK)
GROUP BY agent_id) MTLH
ON LHMAX.agent_id = MTLH.agent_id
AND LHMAX.TIME = MTLH.TIME) LH
JOIN [FPMC-DIST1]. distribution.dbo.mslogreader_agents LA WITH(NOLOCK)
ON lH.agent_id = lA.id) AS RL
ON DL.publisher_db = RL.publisher_db
ORDER BY dl.publisher_db,
dl.distribution_agent
END


BEGIN
----fpvi-sql5
INSERT INTO Distribution.dbo.ReplicationStatus
SELECT
'FPVI-SQL5' as Servername,
dl.subscriber_server,
 dl.publisher_db,
dl.subscriber_db,
 CASE dl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS DistributionRunStatus,
 CASE rl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS LogReaderRunStatus
,GETDATE()
FROM (SELECT agent_id,
publisher_db,
Upper(ss.datasource) AS subscriber_server,
subscriber_db AS subscriber_db,
[name] AS distribution_agent,
DH.runstatus,
CASE
WHEN DH.runstatus = 1 THEN Datediff(SS, DH.TIME, Getdate())
WHEN DH.runstatus = 3 THEN DH.delivery_latency
ELSE 0
END AS distribution_latency
FROM (SELECT DHMAX.*
FROM [fpvi-sql5].distribution.dbo.MSdistribution_history DHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS TIME
FROM [fpvi-sql5].distribution.dbo.MSdistribution_history DH WITH(NOLOCK)
GROUP BY agent_id) MTDH
ON DHMAX.agent_id = MTDH.agent_id
AND DHMAX.TIME = MTDH.TIME) DH
JOIN [fpvi-sql5].distribution.dbo.MSdistribution_agents DA WITH(NOLOCK)
ON DH.agent_id = DA.id
JOIN [fpvi-sql5]. MASTER.dbo.sysservers ss WITH(NOLOCK)
ON da.subscriber_id = ss.srvid) AS DL
LEFT OUTER JOIN (SELECT agent_id,
la.publisher_db,
lh.runstatus,
CASE
WHEN lh.runstatus = 1 THEN Datediff(SS, LH.TIME, Getdate())
WHEN lh.runstatus = 3 THEN lh.delivery_latency
ELSE 0
END AS reader_latency
FROM (SELECT LHMAX.*
FROM [fpvi-sql5].distribution.dbo.MSlogreader_history LHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS [time]
FROM [fpvi-sql5].distribution.dbo.MSlogreader_history WITH(NOLOCK)
GROUP BY agent_id) MTLH
ON LHMAX.agent_id = MTLH.agent_id
AND LHMAX.TIME = MTLH.TIME) LH
JOIN [fpvi-sql5]. distribution.dbo.mslogreader_agents LA WITH(NOLOCK)
ON lH.agent_id = lA.id) AS RL
ON DL.publisher_db = RL.publisher_db
ORDER BY dl.publisher_db,
dl.distribution_agent
END

BEGIN
---fpvisql6
INSERT INTO Distribution.dbo.ReplicationStatus
SELECT
'FPVI-SQL6' as Servername,
dl.subscriber_server,
 dl.publisher_db,
dl.subscriber_db,
 CASE dl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS DistributionRunStatus,
 CASE rl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS LogReaderRunStatus
,GETDATE()
FROM (SELECT agent_id,
publisher_db,
Upper(ss.datasource) AS subscriber_server,
subscriber_db AS subscriber_db,
[name] AS distribution_agent,
DH.runstatus,
CASE
WHEN DH.runstatus = 1 THEN Datediff(SS, DH.TIME, Getdate())
WHEN DH.runstatus = 3 THEN DH.delivery_latency
ELSE 0
END AS distribution_latency
FROM (SELECT DHMAX.*
FROM [fpvi-sql6].distribution.dbo.MSdistribution_history DHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS TIME
FROM [fpvi-sql6].distribution.dbo.MSdistribution_history DH WITH(NOLOCK)
GROUP BY agent_id) MTDH
ON DHMAX.agent_id = MTDH.agent_id
AND DHMAX.TIME = MTDH.TIME) DH
JOIN [fpvi-sql6].distribution.dbo.MSdistribution_agents DA WITH(NOLOCK)
ON DH.agent_id = DA.id
JOIN [fpvi-sql6]. MASTER.dbo.sysservers ss WITH(NOLOCK)
ON da.subscriber_id = ss.srvid) AS DL
LEFT OUTER JOIN (SELECT agent_id,
la.publisher_db,
lh.runstatus,
CASE
WHEN lh.runstatus = 1 THEN Datediff(SS, LH.TIME, Getdate())
WHEN lh.runstatus = 3 THEN lh.delivery_latency
ELSE 0
END AS reader_latency
FROM (SELECT LHMAX.*
FROM [fpvi-sql6].distribution.dbo.MSlogreader_history LHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS [time]
FROM [fpvi-sql6].distribution.dbo.MSlogreader_history WITH(NOLOCK)
GROUP BY agent_id) MTLH
ON LHMAX.agent_id = MTLH.agent_id
AND LHMAX.TIME = MTLH.TIME) LH
JOIN [fpvi-sql6]. distribution.dbo.mslogreader_agents LA WITH(NOLOCK)
ON lH.agent_id = lA.id) AS RL
ON DL.publisher_db = RL.publisher_db
ORDER BY dl.publisher_db,
dl.distribution_agent
END

END




GO