database logging and reporting SOLVED

All questions related to installations, configurations and maintenance of Advanced Host Monitor (including additional tools such as RMA for Windows, RMA Manager, Web Servie, RCC).
Post Reply
ericm
Posts: 40
Joined: Tue Feb 10, 2004 6:29 am

database logging and reporting SOLVED

Post by ericm »

I am logginf to a odbc msaccess database. I want to strip the % off the end of the replies for Alive and Dead because when I try to run a query to give me the average of a certain test I get a error that says mismatched data. I have been doing this through a query in access but over time the log has grown and it takes to long to complete the query now. Is there a way to strip that % sign off the end of the reply before it gets posted to the database?
Last edited by ericm on Mon May 22, 2006 12:03 pm, edited 1 time in total.
Yoorix
Posts: 177
Joined: Wed Dec 14, 2005 8:28 am

Post by Yoorix »

For Reply field you may use Reply_Number or Reply_Integer variables, but for AliveRatio and DeadRatio you should try MSAccess string function to strip ' %'. I know nothing about MSAccess functions, but in MySQL it looks like Replace('%AliveRatio%',' %','')
http://www.ks-soft.net/hostmon.eng/mfra ... .htm#macro

E.g. INSERT INTO hmlog (DATE1, TIME1, TEST_NAME, TestID, STATUS, REPLY,ALIVERATIO) VALUES (%Date%, '%Time%', '%TestName%', %TestID%, '%Status%', '%Reply%',Replace('%AliveRatio%',' %',''))

It works.

Regards,
Yoorix
ericm
Posts: 40
Joined: Tue Feb 10, 2004 6:29 am

Post by ericm »

Thank You
I will try today.
ericm
Posts: 40
Joined: Tue Feb 10, 2004 6:29 am

Post by ericm »

maybe it is my setup but the replace function does not work with a odbc connection. :-(
Yoorix
Posts: 177
Joined: Wed Dec 14, 2005 8:28 am

Post by Yoorix »

ericm wrote:maybe it is my setup but the replace function does not work with a odbc connection. :-(
Did you try to execute such clause in Access worksheet directly? What SQL Error do you see? Could it be syntax error?
Maybe, you should perform additional data convertion, like Cast(Replace('%AliveRatio%',' %','') as double) or something like that. You should read Access manual about conversion functions.

Regards,
Yoorix
ericm
Posts: 40
Joined: Tue Feb 10, 2004 6:29 am

Post by ericm »

I tried it in the odbc string in advanced log settings
Undefined function 'Replace' in expression
Yoorix
Posts: 177
Joined: Wed Dec 14, 2005 8:28 am

Post by Yoorix »

Instead of replace you should use combination of string functions, like
left('%AliveRatio%',instr('%AliveRatio%',' %')-1)

I have checked it with Access 2002. It works. But, I think, it is not the best solution ;-)

Regards,
Yoorix
ericm
Posts: 40
Joined: Tue Feb 10, 2004 6:29 am

Post by ericm »

Thank You
Maybe not the best but the working solution
It works for what I need it for.
KS-Soft
Posts: 13012
Joined: Wed Apr 03, 2002 6:00 pm
Location: USA
Contact:

Post by KS-Soft »

Probably we can add new option, something like "ODBCLog_NoPercentSign" :roll:

Regards
Alex
KS-Soft
Posts: 13012
Joined: Wed Apr 03, 2002 6:00 pm
Location: USA
Contact:

Post by KS-Soft »

Done. Version 6.00 available at http://www.ks-soft.net/hostmon.eng/downpage.htm

Use ODBCLog_NoPercentSign option
http://www.ks-soft.net/hostmon.eng/news.htm

Regards
Alex
Post Reply