KS-Soft. Network Management Solutions
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister    ProfileProfile    Log inLog in 

My ODBC Logging Setup

 
Post new topic   Reply to topic    KS-Soft Forum Index -> Library
View previous topic :: View next topic  
Author Message
mos-eisley



Joined: 21 Mar 2007
Posts: 76
Location: Klarup (AAlborg), Demark

PostPosted: Tue Apr 24, 2007 6:47 am    Post subject: My ODBC Logging Setup Reply with quote

Just wanted to share my ODBC logging setup - Please notice this is Work In Progress and data are from a development/test system:

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.
Back to top
View user's profile Send private message MSN Messenger
mos-eisley



Joined: 21 Mar 2007
Posts: 76
Location: Klarup (AAlborg), Demark

PostPosted: Tue May 01, 2007 2:18 am    Post subject: Reply with quote

Well, the Story continues, since my upgrade to 6.80 yesterday, my database looks like this:

USE [HostMonitor]
GO
/****** Object: Table [dbo].[HMLOG] Script Date: 05/01/2007 10:16:18 ******/
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](8000) 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 NOT NULL,
[ACKNOWLEDGEDBY] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ACKCOMMENT] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HTTPPAGE] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HTTPCODE] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HTTPHEADER] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ROWID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


Most URL tests are changed to HTTP tests, as I want to capture the HttpHeader, HttpCode and HttpPage into the table. Also an RowID column is created, so each test has an unique identifier. This make the SQL Query in Hostmonitor look like this:

Insert into hmlog (rowid,eventtime, testname, status, reply, testmethod,testid,interval,alertthreshold,simplestatus,hostaddr,folder,fullpath,eventtimestamp,acknowledgedat,acknowledgedby,ackcomment,httppage,httpcode,httpheader) VALUES (NEWID(),'%DateTime%', '%TestName%', '%Status%', LEFT('%Reply%',7999), '%TestMethod%','%TestID%','%Interval%','%AlertThreshold%','%SimpleStatus%','%HostAddr%','%Folder%','%FullPath%',GetDate(),'%AcknowledgedAt% ','%AcknowledgedBy% ',LEFT('%AckComment%',499),LEFT('%HttpPage%',7999),LEFT('%HttpCode%',19),LEFT('%HttpHeader%',299))

To utilize this and find problems, I have created a drill-down system as a web app:

Step 1 - Select a Folder
Step 2 - Select a Test
Step 3 - Select a specific test-event in the test
Step 4 - Display all gathered info on the test:



A nice feature is, that be rightclicking any test - via an "Custom Menu profile", a web page appears with the last 1000 test-event for the test:

Back to top
View user's profile Send private message MSN Messenger
KS-Soft Europe



Joined: 16 May 2006
Posts: 2832

PostPosted: Tue May 01, 2007 4:03 am    Post subject: Reply with quote

Great job! I am impressed.
Probably, we should use some of your ideas to improve HostMonitor and Log Analyzer.
Keep on!

Regards,
Max
Back to top
View user's profile Send private message Send e-mail Visit poster's website
mos-eisley



Joined: 21 Mar 2007
Posts: 76
Location: Klarup (AAlborg), Demark

PostPosted: Tue May 01, 2007 4:30 am    Post subject: Reply with quote

Thanks - the project it well under way, I will keep posting updates, feel free to use

Actually, I am replacing a old HP Opeview system running on HP-UX and Oracle, wich was/is very very expensive to maintain
Back to top
View user's profile Send private message MSN Messenger
savenije



Joined: 26 Jun 2007
Posts: 1

PostPosted: Tue Jun 26, 2007 9:28 pm    Post subject: Thanks Reply with quote

Just wanted to feedback that this really helped me get started with logging to SQL. The biggest challenge I was facing was data types for the Macros but I can see that you just dumped it all as a text and then di any numerical manipulation within SQL which has worked out all the issues I had been facing.

Thanks again.

Paul
Back to top
View user's profile Send private message
mos-eisley



Joined: 21 Mar 2007
Posts: 76
Location: Klarup (AAlborg), Demark

PostPosted: Wed Jun 27, 2007 1:30 am    Post subject: Reply with quote

Glad to hear that, at the moment I am actually running on a MySQL database, due to different circumstances But there is not much difference.

I had yo adjust logging levels though, my initial ambition was full logging on all tests, but that produced 500.000 rows every day, wich killed my databases after a week, where there could be +4 millions rows....

So my default logging level is now "write a record when test status changes + midnight logging" - and for some tests I run the "reply" logging level.

Please also notice, that using the HTTP test instead of the URL test gives more feedback in the macro's, lige header and httpbody.
Back to top
View user's profile Send private message MSN Messenger
mos-eisley



Joined: 21 Mar 2007
Posts: 76
Location: Klarup (AAlborg), Demark

PostPosted: Thu Dec 13, 2007 7:14 am    Post subject: Reply with quote

Well, You wont belive it - now we are on Oracle

So from MS SQL -> MySQL -> Oracle - been there, done that

A few weeks ago, we switched to Oracle, mainly to the fact that we are using oracle in the production, and I dont have to maintain the database ( as I would with MS SQL and MySQL)

So now we are on a Virual Oracle (VMWare ESX underneath, Red Hat Enterprise 4 and Oracle 10G) - and the performance is pretty good.

The HM setup now covers 4 physical locations and aprox 1500. Tests

So I just wanted to show my updated Webinterface, using data from HM stored in the Oracle Database:







Back to top
View user's profile Send private message MSN Messenger
KS-Soft Europe



Joined: 16 May 2006
Posts: 2832

PostPosted: Fri Dec 14, 2007 5:14 am    Post subject: Reply with quote

mos-eisley wrote:
So now we are on a Virual Oracle (VMWare ESX underneath, Red Hat Enterprise 4 and Oracle 10G) - and the performance is pretty good.
The HM setup now covers 4 physical locations and aprox 1500. Tests
Thank you for feedback! This information is important for us!

mos-eisley wrote:
So I just wanted to show my updated Webinterface, using data from HM stored in the Oracle Database
Wow! Looks awesome! Thank you, your work is a good advertising of HostMonitor's power.
BTW. We are going to release new command line utility: LogVisualizer. LogVisualizer may use HTML, text, dbf or ODBC log to build chart and save this chart into specified file (.gif). Chart is built for for the certain period of time for the particular test. So, you will be able to integrate LogVisualizer into your system to show the graphical representation of logged data.

Regards,
Max
Back to top
View user's profile Send private message Send e-mail Visit poster's website
violant



Joined: 25 Apr 2009
Posts: 51
Location: Ukraine

PostPosted: Sun Feb 14, 2010 9:42 am    Post subject: Reply with quote

Please help to write SQL query to create report like you have. Raw SLA Measument. I want to get in result table this row:
1)start datetime
2)end datetime
3)difference
4)from status
5)to status
6)translated (UP or DOWN)

No metter which timeperiod and testname. Thats I create myself. In DB I write standart row:
1)testname
2)status
3)eventtime
4)reply
5)eventid

What you doing with status 'Checking' and 'Disable'/'Enable'?
Back to top
View user's profile Send private message
violant



Joined: 25 Apr 2009
Posts: 51
Location: Ukraine

PostPosted: Mon Apr 26, 2010 12:20 am    Post subject: Reply with quote

Please help with SELECT
Back to top
View user's profile Send private message
violant



Joined: 25 Apr 2009
Posts: 51
Location: Ukraine

PostPosted: Mon Nov 22, 2010 1:57 am    Post subject: Reply with quote

MSSQL 2005| Working variant



Drop Table #101;
Select EventTime,Status,Simple_Status,Reply,TestId
Into #101
From HMLog
--Where Convert(Char(10),EventTime,104)='17.11.2010' And TestId=2113;
Where TestId=2313;
--Where Convert(Char(10),EventTime,104)='17.11.2010';
--Select * From #101 Order by 1;

Drop Table #102;
Select EventTimeBeg,StatusBeg,EventTimeEnd,
(Select Status From #101 Where TestId=sel.TestId And EventTime=sel.EventTimeEnd And Status<>sel.StatusBeg) StatusEnd,
Simple_Status,TestId
Into #102
From (
Select a.EventTime EventTimeBeg,a.Status StatusBeg,Min(b.EventTime) EventTimeEnd,a.Simple_Status,a.TestId
From #101 a,#101 b
Where Substring(a.Reply,1,6)<>'ACK BY'
And Substring(b.Reply,1,6)<>'ACK BY'
And a.TestId=b.TestId
And a.Status<>b.Status
And a.EventTime<=b.EventTime
Group by a.EventTime,a.Status,a.Simple_Status,a.TestId
) Sel;
--Select * From #102 Order by 1;

Drop Table #103;
Select v1.EventTimeBeg,v1.EventTimeEnd,DATEDIFF(s,v1.EventTimeBeg,v1.EventTimeEnd) Duration,
v1.TestId,v1.StatusBeg,v1.StatusEnd,v1.Simple_Status,sel.EventTimeReply,sel.Reply,
DATEDIFF(s,v1.EventTimeBeg,sel.EventTimeReply) DurationACK
Into #103
From #102 v1 Left join (
--Select Max(v.EventTimeEnd) EventTimeEnd,r.EventTimeReply,r.Reply,r.TestId
Select Min(v.EventTimeEnd) EventTimeEnd,r.EventTimeReply,r.Reply,r.TestId
From (
Select EventTime EventTimeReply,LTrim(Substring(Reply,7,Len(Reply)-6)) Reply,Status,TestId From #101 Where Substring(Reply,1,6)='ACK BY'
) r,#102 v
--Where r.EventTimeReply>=v.EventTimeEnd
--And r.Status=v.StatusEnd
Where r.EventTimeReply<=v.EventTimeEnd
And r.Status=v.StatusBeg
Group by r.EventTimeReply,r.Reply,r.TestId
) sel On v1.TestId=sel.TestId And v1.EventTimeEnd=sel.EventTimeEnd;

Select EventTimeBeg,EventTimeEnd,Duration "Duration,s",TestId,StatusBeg,StatusEnd,Simple_Status,EventTimeReply "EventTimeACK",Reply "ACK by",DurationACK From #103
--where Simple_Status = 'DOWN'
Order by 1;
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    KS-Soft Forum Index -> Library All times are GMT - 6 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group

KS-Soft Forum Index