Using ASP to Monitor MonitorService | Inside Server-Based Applications (DV-MPS General)

Listings 15-1 and 15-2 show Global.asa and Default.asp, the application file and initial script used in the ASP section of the Web monitoring example presented in this chapter. Global.asa contains only one of the four possible event handlers, SessionOnStart . SessionOnStart sets a session variable that defines the parameters required to log on to the monitoring ODBC data source and then redirects the application to Default.asp. Doing so ensures that all users start at the same place in the application. Another possible reason to force all users through a single entry screen is to ensure that users enter through an application login screen. While a network monitoring system should be restricted in some way, this chapter's ISAPIMonitor program is an example of an application that is best secured using the user authentication options present in IIS. Appendix A discusses setting up virtual directories in IIS and details ways of securing IIS applications.

Listing 15-1

Global.asa

 <SCRIPTLANGUAGE="VBScript" RUNAT="Server"> 'You can add special event handlers in this file that will run ' automatically when special ASP events occur. To create these ' handlers, create a subroutine with a name from the ' list below that corresponds to the event you want to use. For ' example, to create an event handler for Session_OnStart, ' put the following code into this file (without the comments): Sub Session_OnStart     session("SQLLOGIN")="UID=sa;PWD=;DSN=Monitoring"     Response.Redirect("Default.asp") End Sub 'Sub Session_OnEnd     'End Sub 'Application_OnStart ' Runs once when the first page of your                      '  application is run for the first time (by                      '  any user) 'Application _OnEnd   ' Runs once when the Web server shuts down </SCRIPT>

Listing 15-2

Default.asp

 <%@Language=VBScript %> <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0"> <TITLE>Monitoring Main Screen</TITLE> </HEAD> <BODY> <% dim rstAlerts dim db dim strSQL dim link Set db=Server.CreateObject("ADODB.Connection") db.Open(Session("SQLLOGIN"))     strSQL="Select MIN(DateTimeOfAlert) as FirstAlert, "     strSQL=strSQL & "MAX(DateTimeOfAlert) as LastAlert, "     strSQL=strSQL & " COUNT(DateTimeOfAlert) as CountAlerts, "     strSQL=strSQL & " MAX(AlertComment) as Comment, "     strSQL=strSQL & " MAX(Name) as TaskName, "     strSQL=strSQL & " MAX(TaskID) as MaxTaskID "     strSQL=strSQL & " from QryAlertsAndTasks "     strSQL=strSQL & " WHERE DateTimeReset is NULL "     strSQL=strSQL & " GROUP BY TaskID "     strSQL=strSQL & " ORDER BY TaskName "     set rstAlerts = db.Execute(strSQL)     Response.Write("<CENTER>")     link="<CENTER><A HREF=SelectTask.asp"      link=link&">Add, Edit or Delete Tasks</A><BR>"       link=link&"<A HREF=ISAPIMonitor.dll?"      link=link&">Monitor Trace</A><BR>"       if rstAlerts.EOF then         Response.Write("<H2>No Alerts</H2>")         Response.Write(link)     else         Response.Write("<H2>Alerts</H2><BR>")         Response.Write(link)         Response.Write("Click on task name to see details of ")         Response.Write("all active alerts for that task.<BR>  ")         rstAlerts.MoveFirst         Response.Write("<TABLE WIDTH=85% BORDER=1 CELLSPACING=1")         Response.Write(" CELLPADDING=1><TR>")         Response.Write("<TD BGCOLOR=aqua><B><CENTER>")         Response.Write("Task Name</CENTER></B></TD>")         Response.Write("<TD BGCOLOR=aqua><B><CENTER>")         Response.Write("First/Last<BR>Alert</CENTER></B></TD>")         Response.Write("<TD BGCOLOR=aqua><B><CENTER># Alerts")         Response.Write("</CENTER></B></TD>")         Response.Write("<TD BGCOLOR=aqua><B><CENTER>Alert Comment")         Response.Write("</CENTER></B></TD>")         Response.Write("</TR>"&vbcrlf)         Do While Not rstAlerts.EOF             Response.Write("<TR BGCOLOR=WHITE>")             link="<CENTER><A HREF=TaskAlerts.asp?TaskID="              link=link & rstAlerts("MaxTaskID")               link=link&">"&rstAlerts("TaskName")&"</CENTER></A>"              Response.Write("<TD BGCOLOR=white><B>" & link & "</B>")             Response.Write("</TD><TD BGCOLOR=white><B><CENTER>")             Response.Write(rstAlerts("FirstAlert"))             Response.Write("<BR>" & rstAlerts("LastAlert"))             Response.Write("</CENTER></B></TD>")             Response.Write("<TD BGCOLOR=white><B><CENTER>")             Response.Write(rstAlerts("CountAlerts") & "</CENTER>")             Response.Write("</B></TD><TD BGCOLOR=white><B>")             Response.Write(rstAlerts("Comment") & "</B></TD>")             Response.Write("</TR>"&vbcrlf)             rstAlerts.MoveNext         loop         Response.Write("</CENTER>")     end if     set rstAlerts=nothing     set db=nothing %> </BODY> </HTML>

Interacting with Alerts

The general structure of all ASP source files in the Web monitoring example is similar to Default.asp in Listing 15-1, so I'll explain the Default.asp listing in detail with only differences explained when describing other ASP source file listings. Default.asp creates the main screen of this chapter's Web monitoring application. From this screen ”directly or indirectly ”a user can get to the rest of the application. First I create an ADODB connection by calling the CreateObject method of the Server object. With the connection string set in the SessionOnStart method in Global.asa , I call the Open method of the database connection object returned from CreateObject .

Next I create a SQL string to get a list of the tasks that have outstanding alerts. To do this, I create a SELECT query with a GROUP BY clause on a SQL Server view I created in the monitoring database first described in Chapter 14. While it is a good idea to use relational design when you create tables, sometimes it is useful to look at the data with tables joined. In this case, it is convenient to have TblTasks and TblAlerts joined. Using the joined tables in a view offers better performance and better network utilization than separately getting records from TblAlerts and TblTasks and manually linking them on the client side. While you don't need to modify the fields in the view we create here, a view can also modify data types or sizes. This can be useful when you need to combine data from several databases with similar but not identical information.

Listing 15-3 shows the code to create the view named QryAlertsAndTasks . Using a GROUP BY clause allows me to get one record returned per task that has outstanding alerts, with the first and most recent alert time and the count of the number of alerts.

Listing 15-3

SQL script for QryAlertsAndTasks

 ifexists (select * from sysobjects where id =     object_id(N'[dbo].[QryAlertsAndTasks]') and     OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[QryAlertsAndTasks] GO SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON GO CREATE VIEW dbo.QryAlertsAndTasks AS SELECT TblAlerts.DateTimeOfAlert, TblAlerts.DateTimeReset,     TblAlerts.DateTimeIntervention, TblAlerts.ResetComment,     TblTasks.Name, TblTasks.DateTimeLastRun,     TblTasks.DateTimeNextRun, TblTasks.Beeper,     TblTasks.EMail, TblResetType.Description,     TblAlerts.AlertComment, TblAlerts.TaskID FROM TblAlerts INNER JOIN     TblTasks ON     TblAlerts.TaskID = TblTasks.TaskID LEFT OUTER JOIN     TblResetType ON     TblAlerts.ResetTypeID = TblResetType.ResetTypeID GO SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON GO

The SQL string created in Default.asp is passed to the Execute method of db , returning a recordset object, saved into rstAlerts . If this recordset contains any records, EOF will be false , and an HTML table to list the tasks with exceptions will be created.

Default.asp creates a link for each task in the table so that the user can click on the task name to see details of the task. The resulting screen is shown in Figure 15-1.

Each task's hyperlink in the table shown in Figure 15-1 serves as a link to TaskAlerts.asp. TaskAlerts.asp shows the details for each task, including all active alerts related to the task. This information can be used to pick out the details of each alert, including the alert comments that contain information specific to the failure, such as the AlertComment for each alert. Default.asp uses the SQL MAX keyword to select the AlertComment field from one of the alerts. All returned values in a GROUP BY query must be returned by an aggregate function, such as MAX, MIN, COUNT SUM, or AVG. The decision to use MAX is arbitrary. Microsoft Jet SQL supports an aggregate function, LAST, that seems more appropriate, but LAST is not supported by SQL Server. Listing 15-4 shows the code for TaskAlerts.asp, and Figure 15-2 shows the page that script creates.

click to view at full size.

Figure 15-1 The default screen for the Web monitoring ASP application.

Listing 15-4

TaskAlerts.asp

 <%@Language=VBScript%> <HTML> <HEAD> <METANAME="GENERATOR"Content="MicrosoftVisualStudio6.0"> <TITLE>TaskAlertDetailsScreen</TITLE> </HEAD> <BODY> <% dimrstAlerts dimdb dimstrSQL dimlink dimtask task=request("TaskID") Setdb=Server.CreateObject("ADODB.Connection") db.Open(Session("SQLLOGIN")) strSQL="SelectDateTimeOfAlert," strSQL=strSQL&"AlertComment," strSQL=strSQL&"DateTimeIntervention," strSQL=strSQL&"Name," strSQL=strSQL&"TaskID" strSQL=strSQL&"fromQryAlertsAndTasks" strSQL=strSQL&"WHERETaskID="&CStr(Task) strSQL=strSQL&"ANDDateTimeResetisNULL" strSQL=strSQL&"ORDERBYDateTimeOfAlertDESC" setrstAlerts=db.Execute(strSQL) Response.Write("<CENTER>") ifrstAlerts.EOFthen Response.Write("<H2>NoAlertsactiveforthistask.</H2>") else rstAlerts.MoveFirst Response.Write("<H2>Alertsfor") Response.Write(rstAlerts("Name")&"</H2><BR>") link="<CENTER><AHREF=ResetAlerts.asp?TaskID="&_ rstAlerts("TaskID") link=link&">ResetAllAlertsforthisTask</CENTER></A>" Response.Write(link) Response.Write("<TABLEWIDTH=85%BORDER=1CELLSPACING=1") Response.Write("CELLPADDING=1><TR>") Response.Write("<TDBGCOLOR=aqua><B><CENTER>Date/Time<BR>") Response.Write("ofAlert</CENTER></B></TD>") Response.Write("<TDBGCOLOR=aqua><B><CENTER>Date/Time<BR>") Response.Write("ofNotification</CENTER></B></TD>") Response.Write("<TDBGCOLOR=aqua><B><CENTER>AlertComment") Response.Write("</CENTER></B></TD>") Response.Write("</TR>"&vbcrlf) DoWhileNotrstAlerts.EOF Response.Write("<TRBGCOLOR=WHITE>") Response.Write("<TDBGCOLOR=white><B>") Response.Write(rstAlerts("DateTimeOfAlert")&"</B></TD>") Response.Write("<TDBGCOLOR=white><B>") Response.Write(rstAlerts("DateTimeIntervention")&"</B>") Response.Write("</TD><TDBGCOLOR=white><B>") Response.Write(rstAlerts("AlertComment")&"</B></TD>") Response.Write("</TR>"&vbcrlf) rstAlerts.MoveNext loop Response.Write("</CENTER>") endif setrstAlerts=nothing setdb=nothing %> </BODY> </HTML>

At the top of Figure 15-2 you'll see a hyperlink, Reset All Alerts For This Task. This hyperlink calls ResetAlerts.asp to allow the user to reset all alerts associated with the task displayed in TaskAlerts.asp. Clicking on this hyperlink displays the screen shown in Figure 15-3. This screen allows the user to enter a reset comment to be placed in the record in tblAlerts . One comment is used as a comment for all reset alerts. Allowing each alert to be reset with its own reset comment could be a useful addition, especially in a case in which multiple failure types cause a single alert. Listing 15-5 shows the source for ResetAlerts.asp.

click to view at full size.

Figure 15-2 The Task Alert Details Screen created by TaskAlerts.asp.

click to view at full size.

Figure 15-3 Reset Alert screen created by ResetAlerts.asp.

Listing 15-5

ResetAlerts.asp

 <%@Language=VBScript%> <HTML> <HEAD> <METANAME="GENERATOR"Content="MicrosoftVisualStudio6.0"> <TITLE>ResetAlert</TITLE> </HEAD> <BODY> <% dimrstTasks dimdb dimstrSQL dimtaskID taskID=request("taskID") Setdb=Server.CreateObject("ADODB.Connection") db.Open(Session("SQLLOGIN")) strSQL="SELECT*FROMtblTasksWHEREtaskID="&taskID setrstTasks=db.Execute(strSQL) Response.Write("<CENTER>") ifrstTasks.eofthen Response.Write("<H2>Sorry,Task"&taskID&"wasdeleted") Response.Write("byanotherworkstation!</H2>") Response.Write("<FORMaction=""Default.asp""") Response.Write("method=POSTid=form1name=form1>") Response.Write("<INPUTtype=""submit""") Response.Write("value=""ReturntoMainScreen""") Response.Write("id=submit1name=submit1>") Response.Write("</FORM>") else Response.Write("<H2>ResetAlertsforTask"&taskID&"<BR>") Response.Write(rstTasks("Name")&"</H2>") Response.Write("<BR>ResetComments:<BR>") Response.Write("<FORMaction=""ProcessReset.asp?TaskID=") Response.Write(""&taskID&"""method=POSTid=form1name=form1>") Response.Write("<TEXTAREArows=3cols=50") Response.Write("id=ResetCommentsname=ResetComments>") Response.Write("</TEXTAREA><BR>") Response.Write("<INPUTtype=""submit""") Response.Write("value=""Submit""") Response.Write("id=submit1name=submit1>") Response.Write("<INPUTtype=""Reset""") Response.Write("value=""Reset""") Response.Write("id=Reset1name=Reset1>") Response.Write("</FORM>") endif setrstTasks=nothing setdb=nothing %> </BODY> </HTML>

Once the user enters a reset comment on the screen shown in Figure 15-3, he or she can click on the Submit button to reset the alerts in the database. Clicking on the Submit button calls ProcessReset.asp, shown in Listing 15-6. This is a short routine that creates and executes a SQL UPDATE command to set a reset date, reset comment, and reset type. A SQL UPDATE returns no records, so I need to pass an additional parameter to the Execute method of the database connection object db . The parameter value, adExecuteNoRecords , is passed as the third parameter. Since I don't need to use the second parameter, it is passed as an empty parameter represented by two consecutive commas, as in the following fragment:

 db.ExecutestrSQL,,adExecuteNoRecords

ProcessReset.asp does not return a page to the browser but instead redirects the browser back to Default.asp. Unless an additional alert arrives after the SQL UPDATE command that resets all of the outstanding alerts for the task involved, returning to Default.asp will present the user with a screen without an entry for the task whose alerts were just reset.

Listing 15-6

ProcessReset.asp

 <%@Language=VBScript%> <% Response.Buffer=true dimdb dimstrSQL dimtaskID dimrecs dimrstResetType dimuserReset taskID=request("taskID") Setdb=Server.CreateObject("ADODB.Connection") db.Open(Session("SQLLOGIN")) 'WetrytodeterminetheUserIntervention 'resettypeID,butdefaultto2ifwefail. strSQL="SELECTResetTypeIDFROMtblResetType" strSQL=strSQL&"WHEREDescriptionLIKE'UserIntervention%'" setrstResetType=db.Execute(strSQL) ifnotrstResetType.EOFthen userReset=rstResetType("ResetTypeID") else userReset=2 endif strSQL="UPDATEtblAlertsSETResetTypeID="&userReset&"," strSQL=strSQL&"ResetComment='"&request("ResetComments")&"'," strSQL=strSQL&"DateTimeReset='"&Now()&"'" strSQL=strSQL&"WHEREtaskID="&taskID db.ExecutestrSQL,,adExecuteNoRecords setdb=nothing Response.Redirect("Default.asp") %>

Interacting with Tasks

One last ability is required to allow complete Web maintenance of the tables that drive MonitorService . While working with alerts is all that is needed most of the time, the ability to add, edit, or delete tasks from the Web is sometimes necessary. Take a look back at the screen created by Default.asp, shown in Figure 15-1. There is a hyperlink at the top of the page: Add, Edit, Or Delete Tasks. Clicking on this link brings you to a screen created by SelectTask.asp, shown in Figure 15-4.

click to view at full size.

Figure 15-4 The Select A Task screen created by SelectTask.asp.

SelectTask.asp presents all tasks in alphabetical order in an HTML table. The Next Run column of tasks that have outstanding alerts are highlighted in red. The last column of the table contains hyperlinks that allow the tasks to be edited, deleted, or run. Listing 15-7 shows the code for SelectTask.asp.

Listing 15-7

SelectTask.asp

 <%@Language=VBScript%> <HTML> <HEAD> <METANAME="GENERATOR"Content="MicrosoftVisualStudio6.0"> <TITLE>SelectaTask</TITLE> </HEAD> <BODY> <% Response.Buffer=true dimdb dimstrSQL dimtaskID dimrecs dimrstTasks dimrstAlerts Setdb=Server.CreateObject("ADODB.Connection") db.Open(Session("SQLLOGIN")) 'WetrytodeterminetheUserIntervention 'resettypeID,butdefaultto2ifwefail. strSQL="SELECT*FROMtblTasksORDERBYName" setrstTasks=db.Execute(strSQL) Response.Write("<CENTER>") ifrstTasks.EOFthen Response.Write("<H2>NoTasksDefined</H2>") else Response.Write("<H2>Tasks</H2><BR>") Response.Write("Taskswith'NextRun'inREDhave") Response.Write("outstandingalerts") link=link&"<BR><AHREF=EditTask.asp?TaskID=0" link=link&">AddaTask</A>" Response.Write(link) rstTasks.MoveFirst Response.Write("<TABLEWIDTH=85%BORDER=1CELLSPACING=1") Response.Write("CELLPADDING=1><TR>") Response.Write("<TDBGCOLOR=aqua><B><CENTER>") Response.Write("TaskName</CENTER></B></TD>") Response.Write("<TDBGCOLOR=aqua><B><CENTER>LastRun") Response.Write("</CENTER></B></TD>") Response.Write("<TDBGCOLOR=aqua><B><CENTER>NextRun") Response.Write("</CENTER></B></TD>") Response.Write("<TDBGCOLOR=aqua><B><CENTER>Action") Response.Write("</CENTER></B></TD>") Response.Write("</TR>"&vbcrlf) DoWhileNotrstTasks.EOF Response.Write("<TRBGCOLOR=WHITE>") Response.Write("<TDBGCOLOR=white><B>") Response.Write(rstTasks("Name")&"</B></TD>") Response.Write("<TDBGCOLOR=white><B><CENTER>") Response.Write(rstTasks("DateTimeLastRun")) Response.Write("</CENTER></B></TD>") strSQL="SELECTAlertIDFROMtblAlerts" strSQL=strSQL&"WHERETaskID="&rstTasks("TaskID") strSQL=strSQL&"ANDDateTimeResetISNULL" setrstAlerts=db.Execute(strSQL) ifrstAlerts.EOFthen Response.Write("<TDBGCOLOR=WHITE><B><CENTER>") else Response.Write("<TDBGCOLOR=RED><B><CENTER>") endif Response.Write(rstTasks("DateTimeNextRun")) Response.Write("</CENTER></B></TD>") Response.Write("<TDBGCOLOR=white><B><CENTER>") link="<AHREF=EditTask.asp?TaskID=" link=link&rstTasks("TaskID") link=link&">Edit</A>/" link=link&"<AHREF=DeleteTask.asp?TaskID=" link=link&rstTasks("TaskID") link=link&">Delete</A>/" link=link&"<AHREF=RunNow.asp?TaskID=" link=link&rstTasks("TaskID") link=link&">RunNow</A>" Response.Write(link) Response.Write("</CENTER></B></TD>") Response.Write("</TR>"&vbcrlf) rstTasks.MoveNext loop endif Response.Write("</CENTER>") setrstTasks=nothing setdb=nothing %> </BODY> </HTML>

EditTask.asp allows users to edit most elements of the task. Figure 15-5 shows the screen produced by EditTask.asp that results when you click on the Edit hyperlink in the screen shown in Figure 15-4. The current values are placed in each of the fields and can be left alone or edited. For the ExecuteType and AtOrEvery fields, combo boxes are provided to restrict entries to valid values. Listing 15-8 shows EditTask.asp.

click to view at full size.

Figure 15-5 Add/Edit A Task screen created by EditTask.asp.

Listing 15-8

EditTask.asp

 <%@Language=VBScript%> <HTML> <HEAD> <METANAME="GENERATOR"Content="MicrosoftVisualStudio6.0"> <TITLE>Add/EditaTask</TITLE> </HEAD> <BODY> <% dimrstTasks dimdb dimstrSQL dimtaskID dimcbStr taskID=request("taskID") Setdb=Server.CreateObject("ADODB.Connection") db.Open(Session("SQLLOGIN")) strSQL="SELECT*FROMtblTasksWHEREtaskID="&taskID setrstTasks=db.Execute(strSQL) Response.Write("<CENTER>") iftaskID<>0ANDrstTasks.eofthen Response.Write("<H2>Sorry,Task"&taskID&"wasdeleted") Response.Write("byanotherworkstation!</H2>") Response.Write("<FORMaction=""SelectTask.asp""") Response.Write("method=POSTid=form1name=form1>") Response.Write("<INPUTtype=""submit""") Response.Write("value=""ReturntoTaskSelctionScreen""") Response.Write("id=submit1name=submit1>") Response.Write("</FORM>     ") else iftaskID<>0then rstTasks.MoveFirst endif Response.Write("<H2>EditTask") iftaskID<>0then Response.Write(taskID) endif Response.Write("<BR>") iftaskID<>0then Response.Write(rstTasks("Name")&"</H2>") else Response.Write("NewTask</H2>") endif Response.Write("<FORMmethod=POST") Response.Write("action=savetask.asp?TaskID="&taskID) Response.Write("id=form1name=form1><P>") Response.Write("<P><TABLEBORDER=0cellspacing=0><TR>") Response.Write("<TDwidth=""50%%""><B></B></TD>") Response.Write("<TD><B></B></TD>") Response.Write("</TR>") Response.Write("<TRBGCOLOR=WHITE>") Response.Write("<TDalign=rightwidth=""50%%"">") Response.Write("<B>TaskName:</B></TD>") Response.Write("<TD><B><INPUTtype=textNAME=Name") iftaskID<>0then Response.Write("SIZE=50VALUE="""&rstTasks("Name")&""">") else Response.Write("SIZE=50>") endif Response.Write("</B></TD></TR><TRBGCOLOR=WHITE>") Response.Write("<TDalign=rightwidth=""50%%""><B>Run:</B></TD>") cbStr="<SELECTid=cbRunTypename=cbRunType>" iftaskID<>0then ifrstTasks("AtOrEvery")="E"then cbStr=cbStr+"<OPTIONVALUE=ESELECTED>EverynMinutes" cbStr=cbStr+"</OPTION><OPTIONVALUE=A>Ataspecifictime" cbStr=cbStr+"</OPTION>" else cbStr=cbStr+"<OPTIONVALUE=E>EverynMinutes</OPTION>" cbStr=cbStr+"<OPTIONVALUE=ASELECTED>Ataspecifictime" cbStr=cbStr+"</OPTION>" endif else cbStr=cbStr+"<OPTIONVALUE=E>EverynMinutes</OPTION>" cbStr=cbStr+"<OPTIONVALUE=A>Ataspecifictime</OPTION>" endif cbStr=cbStr+"</SELECT>" Response.Write("<TD><B>"+cbStr+"</B></TD>") Response.Write("</TR><TRBGCOLOR=WHITE>") Response.Write("<TDalign=rightwidth=""50%%"">") Response.Write("<B>ExecutableType:</B></TD>") cbStr="<SELECTid=cbExeTypename=cbExeType>" iftaskID<>0then ifrstTasks("ExecuteType")=1then cbStr=cbStr+"<OPTIONVALUE=1SELECTED>" cbStr=cbStr+"Executable(.EXE)</OPTION>" cbStr=cbStr+"<OPTIONVALUE=2>DynamicLink" cbStr=cbStr+"Library(.DLL)</OPTION>" else cbStr=cbStr+"<OPTIONVALUE=1>Executable" cbStr=cbStr+"(.EXE)</OPTION>" cbStr=cbStr+"<OPTIONVALUE=2SELECTED>" cbStr=cbStr+"DynamicLinkLibrary" cbStr=cbStr+"(.DLL)</OPTION>" endif else cbStr=cbStr+"<OPTIONVALUE=1>Executable" cbStr=cbStr+"(.EXE)</OPTION>" cbStr=cbStr+"<OPTIONVALUE=2>DynamicLinkLibrary" cbStr=cbStr+"(.DLL)</OPTION>" endif cbStr=cbStr+"</SELECT>" Response.Write("<TD><B>"+cbStr+"</B></TD>") Response.Write("</TR><TRBGCOLOR=WHITE>") Response.Write("<TDalign=rightwidth=""50%%"">") Response.Write("<B>Frequency:</B></TD>") Response.Write("<TD><B><INPUTtype=textNAME=Interval") iftaskID<>0then Response.Write("SIZE=5VALUE=") Response.Write(rstTasks("RunEveryInterval")&">") else Response.Write("SIZE=5>") endif Response.Write("</B></TD></TR><TRBGCOLOR=WHITE>") Response.Write("<TDalign=rightwidth=""50%%"">") Response.Write("<B>Executable:</B></TD>") Response.Write("<TD><B><INPUTtype=textNAME=Executable") iftaskID<>0then Response.Write("SIZE=50VALUE=""") Response.Write(rstTasks("Executable")&""">") else Response.Write("SIZE=50>") endif Response.Write("</B></TD></TR><TRBGCOLOR=WHITE>") Response.Write("<TDalign=rightwidth=""50%%"">") Response.Write("<B>Function:</B></TD>") Response.Write("<TD><B><INPUTtype=textNAME=Function") iftaskID<>0then Response.Write("SIZE=50VALUE="""&rstTasks("Function")&""">") else Response.Write("SIZE=50>") endif Response.Write("</B></TD></TR><TRBGCOLOR=WHITE>") Response.Write("<TDalign=rightwidth=""50%%"">") Response.Write("<B>Parameter1:</B></TD>") Response.Write("<TD><B><INPUTtype=text") Response.Write("MAXLENGTH=255NAME=Parameter1") iftaskID<>0then Response.Write("SIZE=50VALUE="""&_ rstTasks("Parameter1")&""">") else Response.Write("SIZE=50>") endif Response.Write("</B></TD></TR><TRBGCOLOR=WHITE>") Response.Write("<TDalign=rightwidth=""50%%"">") Response.Write("<B>Parameter2:</B></TD>") Response.Write("<TD><B><INPUTtype=text") Response.Write("MAXLENGTH=255NAME=Parameter2") iftaskID<>0then Response.Write("SIZE=50VALUE="""&_ rstTasks("Parameter2")&""">") else Response.Write("SIZE=50>") endif Response.Write("</B></TD></TR><TRBGCOLOR=WHITE>") Response.Write("<TDalign=rightwidth=""50%%"">") Response.Write("<B>Parameter3:</B></TD>") Response.Write("<TD><B><INPUTtype=text") Response.Write("MAXLENGTH=255NAME=Parameter3") iftaskID<>0then Response.Write("SIZE=50VALUE="""&_ rstTasks("Parameter3")&""">") else Response.Write("SIZE=50>") endif Response.Write("</B></TD></TR><TRBGCOLOR=WHITE>") Response.Write("<TDalign=rightwidth=""50%%"">") Response.Write("<B>Parameter4:</B></TD>") Response.Write("<TD><B><INPUTtype=text") Response.Write("MAXLENGTH=255NAME=Parameter4") iftaskID<>0then Response.Write("SIZE=50VALUE="""&_ rstTasks("Parameter4")&""">") else Response.Write("SIZE=50>") endif Response.Write("</B></TD></TR><TRBGCOLOR=WHITE>") Response.Write("<TDalign=rightwidth=""50%%"">") Response.Write("<B>BeeperofResponsibleperson:</B></TD>") Response.Write("<TD><B><INPUTtype=textMAXLENGTH=255") Response.Write("NAME=Beeper") iftaskID<>0then Response.Write("SIZE=50VALUE="""&_ rstTasks("Beeper")&""">") else Response.Write("SIZE=50>") endif Response.Write("</B></TD></TR><TRBGCOLOR=WHITE>") Response.Write("<TDalign=rightwidth=""50%%"">") Response.Write("<B>E-mailofResponsibleperson:</B></TD>") Response.Write("<TD><B><INPUTtype=textMAXLENGTH=255") Response.Write("NAME=email") iftaskID<>0then Response.Write("SIZE=50VALUE="""&rstTasks("email")&""">") else Response.Write("SIZE=50>") endif Response.Write("</B></TD></TR><TRBGCOLOR=WHITE>") Response.Write("</TR></TABLE><BR>") Response.Write("<INPUTtype=""submit""") Response.Write("value=""Submit""") Response.Write("id=submit1name=submit1>") Response.Write("</FORM>") endif setrstTasks=nothing setdb=nothing %> </BODY> </HTML>

EditTask.asp creates an invisible table to properly align elements on the form. If a TaskID of 0 is passed to EditTask.asp, a new record is added. Look back at Figure 15-4, which shows the screen produced by SelectTask.asp , and notice the hyperlink Add A Task at the top of the screen. When you select Add A Task, EditTask.asp is called with a TaskID of 0. When you click on the Submit button on the screen in Figure 15-5, SaveTask.asp is called. SaveTask.asp can be called with a nonzero TaskID parameter to update an existing task or with the TaskID parameter set to zero to insert a new record. Listing 15-9 shows SaveTask.asp.

Listing 15-9

SaveTask.asp

 <%@Language=VBScript%> <HTML> <HEAD> <METANAME="GENERATOR"Content="MicrosoftVisualStudio6.0"> <TITLE>SaveaTask</TITLE> </HEAD> <BODY> <% functionnullSafe(s) iflen(s)>0then nullSafe="'"&s&"'" else nullSafe="null" endif endfunction dimdb dimstrSQL dimtaskID dimcbStr dimnumRecs taskID=request("taskID") Setdb=Server.CreateObject("ADODB.Connection") db.Open(Session("SQLLOGIN")) iftaskIDthen strSQL="UPDATEtblTasksSET" strSQL=strSQL&"Name="&NullSafe(request("Name"))&"," strSQL=strSQL&"AtOrEvery='"&request("cbRunType")&"'," strSQL=strSQL&"ExecuteType="&request("cbExeType")&"," ifCLng(request("Interval"))>0then strSQL=strSQL&"RunEveryInterval="&request("Interval")&"," endif strSQL=strSQL&"Executable="&NullSafe(request("Executable"))&"," strSQL=strSQL&"Function="&NullSafe(request("Function"))&"," strSQL=strSQL&"Parameter1="&NullSafe(request("Parameter1"))&"," strSQL=strSQL&"Parameter2="&NullSafe(request("Parameter2"))&"," strSQL=strSQL&"Parameter3="&NullSafe(request("Parameter3"))&"," strSQL=strSQL&"Parameter4="&NullSafe(request("Parameter4"))&"," strSQL=strSQL&"Beeper="&NullSafe(request("beeper"))&"," strSQL=strSQL&"email="&NullSafe(request("email")) strSQL=strSQL&"WHERETaskID="&taskID else strSQL="INSERTINTOtblTasks(Name,AtOrEvery,ExecuteType," ifCLng(request("Interval"))>0then strSQL=strSQL&"RunEveryInterval," endif strSQL=strSQL&"Executable,parameter1,parameter2,parameter3," strSQL=strSQL&"parameter4,beeper,email,DateTimeNextRun)VALUES(" strSQL=strSQL&NullSafe(request("Name"))&"," strSQL=strSQL&NullSafe(request("AtOrEvery"))&"," strSQL=strSQL&request("cbExeType")&"," ifCLng(request("Interval"))>0then strSQL=strSQL&request("Interval")&"," endif strSQL=strSQL&NullSafe(request("Executable"))&"," strSQL=strSQL&NullSafe(request("Function"))&"," strSQL=strSQL&NullSafe(request("Parameter1"))&"," strSQL=strSQL&NullSafe(request("Parameter2"))&"," strSQL=strSQL&NullSafe(request("Parameter3"))&"," strSQL=strSQL&NullSafe(request("Parameter4"))&"," strSQL=strSQL&NullSafe(request("beeper"))&"," strSQL=strSQL&NullSafe(request("email"))&"," strSQL=strSQL&"'"&Now()&"')" endif numRecs=0 db.ExecutestrSQL,numRecs,adExecuteNoRecords Response.Write("<CENTER>") ifnumRecs>0then Response.Write("<H2>ChangesRecorded!</H2>") else Response.Write("<H2>Anerrorhasoccured</H2>") endif Response.Write("<FORMaction=""SelectTask.asp""") Response.Write("method=POSTid=form1name=form1>") Response.Write("<INPUTtype=""submit""value=") Response.Write("""ReturntoTaskList""id=submit1name=submit1>") Response.Write("</FORM>") Response.Write("</CENTER>") %> </BODY> </HTML>

In addition to the main line of code, Listing 15-9 contains one function, nullSafe , declared at the top of the listing. This function allows us to safely set string fields in a SQL query, even if the value we are setting is a null. The string returned by nullSafe is either the string enclosed in single quotes or the literal string null.

When someone deletes a task we have a dilemma. What should happen with the alerts out there for the deleted task? There are two possible options. The first is to cascade the deletes, deleting all alert records related to the task. In the Web monitoring example I have taken a different tack. Rather than delete the record, I set the reset type to 3 (which happens to be the ResetTypeID for "Task Deleted" in my version of the tblResetType file). I do this using a delete trigger on TblTasks , which is shown in the following code fragment:

 CREATETRIGGERDelTblTasksON[TblTasks] FORDELETE AS UPDATEtblAlertsSETResetTypeID=3 WHERETaskID=(SELECTTaskIDFROMDeleted)

Triggers have pseudotables available to them named Inserted or Deleted , depending upon the type of trigger. I use a subquery to retrieve the TaskID from the deleted record and use that in the WHERE clause of the UPDATE statement to update TblAlerts .

If you select the Run Now hyperlink for a task, the DateTimeNextRun column in the underlying table tblTasks is set to the system date and time. RunNow.asp, shown in Listing 15-10, is the ASP page called when you click on this hyperlink. There are a couple of things to remember about this function.

Setting DateTimeNextRun does not directly run the task, but rather causes MonitorService to run the task as part of its normal processing, generally within 30 seconds.
DateTimeNextRun is set using the system date and time on the server on which IIS is running. While this is often the same server on which MonitorService is running, if the service is running on another server and the system clocks on the two servers are not synchronized, setting DateTimeNextRun might not actually force the task to run immediately.

Listing 15-10

RunNow.asp

 <%@Language=VBScript%> <% Response.Buffer=true dimdb dimstrSQL dimtaskID taskID=request("taskID") Setdb=Server.CreateObject("ADODB.Connection") db.Open(Session("SQLLOGIN")) 'WetrytodeterminetheUserIntervention 'resettypeIDbutdefaultto2ifwefail. strSQL="UPDATEtblTasksSETDateTimeNextRun='"&Now()&"'" strSQL=strSQL&"WHERETaskID="&taskID db.ExecutestrSQL,,adExecuteNoRecords setdb=nothing Response.Redirect("SelectTask.asp") %>

Taken together, the eight ASP files allow virtually complete access to the tables that drive the MonitorService application. The user can, over the intranet or the Internet, monitor and reset alerts as well as modify, add, and delete tasks. This is 90 percent of what an administrator needs to know about the running monitor.

The other 10 percent that is useful if not essential is the ability to look at the activities taking place on the server. For instance, in addition to seeing that a task has succeeded or failed, you might find it useful to get to the feedback of an operation and see exactly when it was performed within the context of the application. Multiple threads run tasks and monitor tasks at any given moment. How useful is it to be able to look at what amounts to a trace of the application's progress? Answering that question is the goal of the section that follows .