There are a few ways to monitor if the ClickHome service is Running:
Describes a few ways.
Alternatively you can Also try this:
This document will describe how to put a few steps in place to enable the automatic notification, via email, if a ClickHome Service stops running. Please note this information is only valid for the ClickHome2 Product, as some of the steps will have to be altered for this to work in ClickHome3. As a requirement the user implementing this feature will need to have SQL knowledge. These steps will all need to be run from Microsoft SQL Server Management Studio
# Please note that there are two services running ClickHome. The first is for Sitemanager and the second for Presite. These steps will need to be repeated for both the Presite and Sitemanager Services.
1. Creating a tblMonitors Table
The first step is to create a tblmonitors table. This table needs to be created on the database in which the service is linked. This will provide a location to log the state of the service. Running the script provided will create a suitable table.
/****** Object: Table [dbo].[tblMonitors] Script Date: 10/19/2012 12:11:52 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[tblMonitors](
[idMonitor] [int] IDENTITY(1,1) NOT NULL,
[sgMessage] [nvarchar](200) NOT NULL,
[cdCode] [nchar](10) NULL,
[dtLogTime] [datetime] NOT NULL
) ON [PRIMARY]
2. Logging the Service Status
The following statement needs to be added to the following procedure, prcCustomAutoSchedulerTasks. This custom procedure will only run when the ClickHome Service is running. As previously stated, this will need to be completed on the same database in which the service is linked.
insert into tblMonitors
3. Sending of the Automatic Email
This next item of code will create a procedure that can prepare all the necessary information and send an email notifying the selected users if the service has been down. There are several parts of this code that’ll need to be modified depending on your preference. These will be pointed out in red below.
/****** Object: StoredProcedure [dbo].[prcCustomCheckServiceIsRunning] Script Date: 10/19/2012 11:57:01 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE proc [dbo].[prcCustomCheckServiceIsRunning]
declare @dtLastActiveTime dateTime
select top 1 @dtLastActiveTime = dtLogTime from tblMonitors
order by idMonitor desc
if datediff(hh,@dtLastActiveTime,getdate()) > 1
declare @sgMessage nvarchar(400)
declare @sgCLEmail nvarchar(4000)
declare @sgsubg nvarchar(400)
insert into tblLogentries
(1,'CHECK','Service is not running','SERVICE',getdate())
set @sgCLEmail = 'firstname.lastname@example.org'
set @sgmessage = 'Service has not responded for more than an hour, last respond time recorded was ' + cast(@dtLastActiveTime as nvarchar) + ' please restart your service'
set @sgsubg = 'ClickHome Construction Service is not responding'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Clickhome', @recipients = @sgCLEmail , @body = @sgmessage , @subject = @sgsubg;
delete from tblMonitors
where dtLogTime < dateadd(dd,-7,getdate())
4. Setting up a SQL Server Agent Job, to Prompt for the Sending of an Email
This final step will setup a SQL Server Agent Job. This job will run the procedure created in the previous step. This job is run by the SQL server and doesn’t need an active ClickHome service to run.
Firstly from the main page of the SQL Server Management Studio right click on the menu item SQL Server Agent --> New --> Job
From there under the General Page enter some detailed information. Give the Job a relevate name and description. The Owner should remain the same along with the Category. An example is provided below.
The next section to be changed is the Steps Page. The information is provided in a screenshot below. The only infromation that needs to be different is the Database Dropdown Box. This box needs to be changed to the database in which, the service being monitored is linked.
Finally a schedule needs to be set. It’s suggested this that the settings provided below are used for this feature. To enter this information click the Schedules Page and then click the new button.
After you’ve completed the steps below click ok and your automatic notifications will be ready and working. Please don’t forget to run through these steps for before the Presite and the Sitemanager services if you have both and would like them both monitored.