Video Screencast Help
Endpoint Management Community Blog

A Quick Report for Hierarchy Replication

Created: 20 Dec 2012 • Updated: 20 Dec 2012
Ludovic Ferre's picture
0 0 Votes
Login to vote

I was asked today to help on a long running hierarchy replication task. I pointed my customer to the default report whilst I was searching a hand crafted SQL to do the same, with a slightly friendlier look to it.

I found it, so I sent it to them and I share it with the Community now:

select
		rs._eventTime as 'Event time',
		replace (src.name, '.15-cloud.fr', '') + ' --> ' +
		replace (dst.name, '.15-cloud.fr', '') + 
		case src.name when 'vbox-atrs5.15-cloud.fr' then ' (Down)' else ' (Up)' end as 'Details',
		cast(rs.TotalReplicationCount as varchar) as 'Objects (total)',
		cast (rs.FailedReplicationCount as varchar) as 'Failed',
		cast (rs.DataTransferred as varchar) as 'Size in KiB',
		cast (DATEDIFF(mi, rs.[StartTime], rs.[FinishTime] ) as varchar)AS 'Duration (mins)'
  from Evt_NS_Hierarchy_Replication_Status rs
  join vSource src
    on rs.SourceNS = src.Guid
  join vSource dst
    on rs.DestinationNS = dst.Guid
  left join Evt_NS_Hierarchy_Job_Start js
    on rs.JobID = js.JobID
 where rs._eventtime > getdate() -7
  union
select StartTime, i.name, '', '', '', ''-- j.Scope, j.Mode, j.Priority, ''
  from Evt_NS_Hierarchy_Job_Start j
  join Item i
    on j.CauseID = i.Guid
 where j.StartTime > GETDATE() - 7
 order by [Event time] desc

There are a few of notes regarding this report:

  1. You should change the bold section to meet your environment. In my case the servers are registered with their FQDN, so I shorten it from the view (it brings no value to me). Also the child server is named vbox-atrs5.
  2. The view by default reports on the last 7 days replication tasks.
  3. The view aggregates the tasks that start the replication and the replication status. It's not always the prettiest but it aggregates data available so you can relate the replication data to the replication item that initiated it.