Part 1:
The table in SQL 2005 - the Database is called HostMonitor:
USE [HostMonitor]
GO
/****** Object: Table [dbo].[HMLOG] Script Date: 04/24/2007 14:39:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HMLOG](
[EVENTTIME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TESTNAME] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[STATUS] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[REPLY] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TESTMETHOD] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EVENTTIMESTAMP] [datetime] NOT NULL,
[TESTID] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[INTERVAL] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ALERTTHRESHOLD] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SIMPLESTATUS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HOSTADDR] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FOLDER] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FULLPATH] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ACKNOWLEDGEDAT] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACKNOWLEDGEDBY] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACKCOMMENT] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Notice the eventtimestamp column set actual time in, it is used for sorting correctly, do make indexes!! Varchar sizes can also be improved a bit.
I have used MS SQL timestamps on an SQL server running on top of VMWare ESX - this did not work for some reason, so I use GetDate() - this is also (human)readable and easy to handle. Check out http://support.microsoft.com/kb/931279
In HostMonitor Options -> Backup Log -> Database I have this SQL Query (remember to setup ODBC also):
Insert into hmlog (eventtime, testname, status, reply, testmethod,testid,interval,alertthreshold,simplestatus,hostaddr,folder,fullpath,eventtimestamp,acknowledgedat,acknowledgedby,ackcomment) VALUES ('%DateTime%', '%TestName%', '%Status%', LEFT('%Reply%',199), '%TestMethod%','%TestID%','%Interval%','%AlertThreshold%','%SimpleStatus%','%HostAddr%','%Folder%','%FullPath%',GetDate(),'%AcknowledgedAt% ','%AcknowledgedBy% ',LEFT('%AckComment%',499))
Notice the LEFT function is inplemented, as I have seen that stuff from the "Text Log" test can be very long, and if it is longer than the column can handle, an ODBC error is raised ( an that is bad if syslog.htm is monitored, because the ODBC error text is quite long.... think it over)
I log all events in the database, logging only status changes makes it impossible to track trends/values (via the reply column)
Well, that takes care of logging to the ODBC Source.
Part 2:
To make sure that continues logging is happening, a ODBC test tells how long time from the last entry in the table:
select top 1 DATEDIFF(s, eventtimestamp,getdate()) from sla Order by eventtimestamp desc
This gives the difference in seconds from now to the last entry, must be max 30 secs in my case (full logging), if the test fails - check logging to the ODBC source. Also use IP instead of servername in the ODBC setup, to prevent logging fails if DNS does..
Also, I have various checks over the MS SQL Server, as service runnning, agent running, disks not filling up and watch out for filling the transaction log - it is a common MS SQL mistake
Part 3:
Over the table HMLOG I have some practical Views, both for the LogAnalyzer and various Web-Applications:
USE [HostMonitor]
GO
/****** Object: View [dbo].[LogAnalyzer1Day] Script Date: 04/24/2007 14:50:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[LogAnalyzer1Day]
AS
SELECT TESTNAME, STATUS, REPLY, TESTMETHOD, EVENTTIMESTAMP, EVENTTIME, ACKNOWLEDGEDAT, ACKNOWLEDGEDBY
FROM dbo.HMLOG
WHERE (EVENTTIMESTAMP > GETDATE() - 1)
GO
I have 1Day, 2Days, 3Days, 1Week, 2Weeks etc etc - all that is needed to change is the GETDATE()-Z, where Z=Number of days.
I also have some very important tests, that are used for calculating SLA (service Level Agreements) for customers, these are all in a folder called "0SLA", and similar to the above, I have some views like this:
USE [HostMonitor]
GO
/****** Object: View [dbo].[SLA] Script Date: 04/24/2007 14:52:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[SLA]
AS
SELECT EVENTTIME, TESTNAME, STATUS, REPLY, TESTMETHOD, EVENTTIMESTAMP, TESTID, INTERVAL, ALERTTHRESHOLD, SIMPLESTATUS, HOSTADDR,
FOLDER, FULLPATH
FROM dbo.HMLOG
WHERE (FULLPATH = 'Root\0SLA\')
Part 4:
A few WebApps inspirations (with a little pseudo code) in have made over the HMLOG table, the moment the test data is in a SQL database, the possibilities are endless
:Monitoring actual status for each test in the "0SLA" folder:
Get all tests:
sql="SELECT DISTINCT TestName FROM SLA ORDER BY TestName"
Run a loop with this:
sql="SELECT TOP 1 Status FROM LogAnalyzer1Day WHERE TestName='" & TestName & "' ORDER BY EVENTTIMESTAMP DESC"
This will give the current status for variable TestName
The pda version for out QTEK devices:
A detailed view over a test:
Part 5:
For most of the views, I have made an ODBC Log like this:
I prefer to have filter and stuff in the SQL database, but that of course is an option of opinions.
Part 5:
May be continued here....posting of actual asp and/or .net files etc.
