Here you go... This is the way I would go about it... you will need DB Mail enabled and a profile created and change those sections according to your environement below... also change the @MaxOfflineInHours= variable to whatever amount of hours of inactivity you would like to alert on... Create the stored procedure usp_GetExpiringSubscribersList then place the email script below in a SQL job and schedule it. The email script is designed to return a result set to you via email. Let me know if you have any issues with it and I can work through it with you. Let me know if you need anything else.
Thanks!
-VM
--==============================================================================
Create Procedure dbo.usp_GetExpiringSubscribersList
AS
declare @maxOffLineInHours int
set @maxOffLineInHours = 72 -- <SET YOUR NUMBER OF HOURS TO ALERT ON HERE>
SELECT srvname,
Max_start_time,
DATEDIFF(hh, Max_start_time, getdate())
FROM distribution.dbo.msmerge_sessions
JOIN ( SELECT agent_id,
Max_start_time = MAX(start_time)
FROM distribution.dbo.msmerge_sessions
GROUP BY agent_id
) AS k ON k.agent_id =
distribution.dbo.msmerge_sessions.agent_id
AND distribution.dbo.msmerge_sessions.start_time = max_Start_Time
JOIN ( SELECT id AS agent_id,
srvname
FROM distribution.dbo.msmerge_agents
JOIN sys.sysservers ON sys.sysservers.srvid = distribution.dbo.msmerge_agents.Subscriber_ID
) AS l ON l.agent_id = k.agent_id
WHERE DATEDIFF(hh, max_start_time, GETDATE()) > @maxOffLineInHours
ORDER BY DATEDIFF(hh, max_start_time, GETDATE()) DESC
--====================================================================
-- Put this code in a SQL Job to run daily or whatever interval suits you
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorks2008R2 Administrator',
@recipients = 'VinnyAdmin@Adventure-Works.com',
@query = 'exec dbo.usp_GetExpiringSubscribersList' ,
@subject = 'usp_GetExpiringSubscribersList',
@attach_query_result_as_file = 1 ;
