Setting Up ODBC Logging

[johnm]

I thought I might try logging to a SQL table instead of txt or html file.

When creating the table and columns, how do I know which datatype and column length to set to for each macro variable I plan to insert?

Thanks...

[johnm]

One other thing...

Is there a way to manipulate a macro var with a substring of its value.

For example:
%fullpath% returns 'Root\NJOffice\Servers\WebServer1\'

Is there a way to get subset of this string like 'NJOffice' or 'Servers'

Thanks again in advance.
John

[KS-Soft]

When creating the table and columns, how do I know which datatype and column length to set to for each macro variable I plan to insert?

Common sense. E.g. field that represents %TestName% should be char field. %HostID% should be integer, %TestMethod% represents text. If you use %Status% variable, setup char field. If you use %StatusID% variable, use integer field. The same about Reply. If you use %Reply% macro variable in the query, setup char field. If you use %Reply_Number% variable, use numeric field.

Column length? %TestName% depends on names that you are using for your tests. I don't know you like short or long names. 40 characters should be enough for %TestMethod% field. 15 characters is enough for %Status% field.

For example:
%fullpath% returns 'Root\NJOffice\Servers\WebServer1\'
Is there a way to get subset of this string like 'NJOffice' or 'Servers'

HostMonitor does not provide such option.

Regards
Alex

[johnm]

Ok thanks Alex. I kinda figured there was no way to return a substring of the macro vars so I went ahead and built a SQL stored procedure to accept the values passed from host monitor. Than the SP manipulates the values as needed.

However, I have a problem with ODBC logging. It only inserts entries in the table when I save Options. This is very strange. It does not insert an entry for each occurence of a test.

Any reason why this is happening?

John

[KS-Soft]

However, I have a problem with ODBC logging. It only inserts entries in the table when I save Options. This is very strange. It does not insert an entry for each occurence of a test.
Any reason why this is happening?

Change logging option from "Write a record when test status changes" to "Save information about every performed test". See Log Settings page in the Options dialog

Regards
Alex

[johnm]

Alex, this logging option was already set.

[KS-Soft]

Are you sure monitoring is started? If yes, could you check system log? Any error?

Regards
Alex

[johnm]

Thanks for the idea of checking the log Alex, I always forget that. Anyway, it did have an ODBC error contained within which led me to the problem. I had left a number of print statements in my stored procedure which was causing an excessive number of ODBC errors in the log which forced HM to pause logging for a period of time.

I do have one more question regarding this issue.

When I try to open the SQL table thru the ODBC option in Log Analyzer, I am receiving an error as follows:

Source: servername - tablename. Too many invalid records.

I am also receiving one more error it the system log:

"ODBC log error. Error code: 1 ([Microsoft][ODBC SQL Server Driver][SQL Server]Invalid length parameter passed to the substring function) Logging initiated by "Ping" test. "

My table designed is as follows:

location Char(50), Null (populated with substring of %fullpath%)

devicetype Char(50), Null (populated with substring of %fullpath%)

devicename Char(50), Null (populated with substring of %fullpath%)

testname Char(50), Null (populated with %testname%)

testmethod Char(50), Null (populated with %testmethod%)

date1 DateTime, Allow Null (populated with %date%)

eventtime DateTime, Null (populated with %datetime%)

status Char(50), Null (populated with %status%)

reply Char(50), Null (populated with %reply%)

In LA ODBC setup I specified the following:

Date = date1
Time = eventtime
Test name = testname
Status = status
Reply = reply
Test method = testmethod

No check on "Date" field is text field..."
Analyze all data
No filters set

When I click on [Test] the result is:
"Query Executed. 135 record(s) retrieved"
Not sure where to start looking..

Thanks,
John

[johnm]

Alex, I know what is causing the ODBC log error.

However, I am still having the same error when trying to open the ODBC table in Log Analyzer.

[KS-Soft]

What ODBC driver do you use? Microsoft MS SQL? What version?
We are investigating some strange behavior of MS SQL and Access drivers...
Could you use some other driver/database, not Microsoft. E.g. MySQL, Oracle...

Regards
Alex

[johnm]

Using SQL Server driver version 2000.86.1830.00.

I could use a WIn2k Professional machine to run Log Analyzer. Would that be helpful since it will have a different driver version?

have a good weekend...

[KS-Soft]

Would that be helpful since it will have a different driver version?

Yes, lets try it.
Also, could you try this update: www.ks-soft.net/download/la504.zip

Regards
Alex

[johnm]

Sorry Alex, no luck.

I ran both versions of Log Analyzer(5.03 and 5.04) on a Win2k Prof and W2k3 Server machine, but same error.

The SQL Server ODBC driver version on Win2K Prof is: 2000.81.9042.00

Maybe my table structure or perhaps my ODBC config?

Did you see any problem with my table structure?

Are there any non-default settings on the ODBC config I need to be aware of?

[KS-Soft]

date1 DateTime, Allow Null (populated with %date%)
eventtime DateTime, Null (populated with %datetime%)
...
Date = date1
Time = eventtime

This should not be a big problem but we never tested such configuration.
If you use %datetime% variable, you don't have to use 2 different fields for date and time. Set "eventtime" for both fields: date, time. In this case Log Analyzer will retrieve date and time from single field.

Regards
Alex

[johnm]

Any other ideas or will I have to wait on your sql driver investigation?

Thanks...