PowerHome Messageboard: Proposed Eventlog changes

Active Topics

Memberlist

Help

PowerHome General
PowerHome Messageboard : PowerHome General

Topic: Proposed Eventlog changes

Author

Message

<< Prev Topic | Next Topic >>

dhoward
Admin Group

Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447

Posted: January 25 2012 at 19:48 \| IP Logged

All,

Im toying with the idea of removing the eventlog from the
database. More than anything else in the database, the
eventlog just continues to grow unless you setup a timed
event or similar and trim/export the eventlog. Obviously
you don't want the eventlog to just grow forever and
create a database that is so huge you start to have
performance problems or corruption.

The idea Im working on is to save the eventlog to a CSV
file as a text based database accessible via the
Microsoft Text ODBC driver. Internally within PowerHome,
the eventlog would still be available, can be filtered,
sorted, and could even be queried using SQL. You just
wouldnt be able to write a SQL query that joins the
eventlog table to any of the other PowerHome tables.

Looking for some general feedback on what everyone thinks
about this proposed change.

Thanks,

Dave.

TonyNo
Moderator Group

Joined: December 05 2001
Location: United States
Online Status: Offline
Posts: 2889

Posted: January 26 2012 at 08:13 \| IP Logged

I like the idea.

grif091
Super User

Joined: March 26 2008
Location: United States
Online Status: Offline
Posts: 1357

Posted: January 26 2012 at 12:24 \| IP Logged

I also like the idea.

__________________
Lee G

smarty
Super User

Joined: May 21 2006
Location: United States
Online Status: Offline
Posts: 729

Posted: January 26 2012 at 13:26 \| IP Logged

Works for me.

__________________
Elk - Insteon - BlueIris - DMC1 - PowerHome - XLobby - HA_Bridge w/Dots - Brultech

nerdboytodd
Newbie
Newbie

Joined: January 19 2011
Location: United States
Online Status: Offline
Posts: 11

Posted: January 26 2012 at 13:58 \| IP Logged

The event log is great for troubleshooting and for historical purposes, but in the interest of stability/scalability, I can understand the need to change. The Event log has made my Insteon/Elk M1 Gold installation way more powerful than they were before I purchased PH.

MrGibbage
Super User

Joined: October 23 2006
Location: United States
Online Status: Offline
Posts: 513

Posted: January 26 2012 at 14:10 \| IP Logged

I already have a timed event to erase my event log each
night (actually, I just delete all events over three days
old). Anyway, I can certainly understand what you are
getting at with regards to performance. I regularly write
my own text file logs with PH, especially when I am testing
a new macro, so moving away from the database id fine with
me.

Have you considered having a formula that can write to the
windows event log? That might be useful to some people.

__________________
Skip

raven77m
Newbie
Newbie

Joined: December 31 2006
Online Status: Offline
Posts: 24

Posted: January 26 2012 at 17:59 \| IP Logged

Sounds good to me also.

patrickm
Senior Member

Joined: February 22 2007
Location: United States
Online Status: Offline
Posts: 188

Posted: January 27 2012 at 19:20 \| IP Logged

I use the event log a lot and like the idea of seperating the event log.

I would like a seperate field similar to the "Don't Display" field in the Insteon Explorer window that would allow the event log window within PH to exclude specific devices or events that are in the proposed CSV event file.

I really like the live event pane in the PH Status window. Having an entire window with live scrolling event data would be very nice while debugging.

Patrick

krommetje
Super User

Joined: December 29 2004
Location: Netherlands
Online Status: Offline
Posts: 695

Posted: February 02 2012 at 12:57 \| IP Logged

As long as the logfile is accessible through the network and readable through the network, you can do just about what you want. Reason: My internet-server(s)logs like IIS and mail and so forth are subject to my daily job for checking if they aren't subject to hacking /spam ....

Peter

GadgetGuy
Super User

Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942

Posted: February 02 2012 at 15:36 \| IP Logged

Dave - works for me too. But a humble suggestion. Instead of having to do a major rewrite, why not just set a default Trim event for every 2 days (or whatever)?

I have set my PH up that way from the get-go and have never had a problem.

To provide user flexibility, wouldn't it be easier to just provide clear documentation about the care and feeding of, or a little wizzard for "ph_trimeventlog(" ?

__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!

syonker
Senior Member

Joined: March 06 2009
Location: United States
Online Status: Offline
Posts: 212

Posted: February 04 2012 at 09:51 \| IP Logged

I kind of like GadgetGuy's proposal to periodically purge the DB automatically. Even funneling it off to a text file would still "grow disk space".

On the other hand, funneling it off to a txt file would allow one to put in an independent periodic purge (using Windows, or other utilities).

If you used a standard log structure (or even not), it could be run through an enterprise log server for analysis...which is nice too.

On a final note - options to write to either, and set purging from within the app would be the most ideal.

Regardless, you're on the right track - these need to be able to be cleaned up and pared down for agility in the app.

And yes, ph_trimeventlog(2) works too. Might be nice to have access in the aforementioned log analyzer though - especially if it could be pumped directly out to a network socket (log standard). More information here - recommend Syslog or similar, and sticking to Common Log Format if possible. http://en.wikipedia.org/wiki/Common_Log_Format

-S

Edited by syonker - February 04 2012 at 09:58

__________________
"I will consider myself having succeeded when my house becomes sentient and attempts to kill me."

><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>¸.

ˇ´Ż`ˇ.¸. , . ><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>

GadgetGuy
Super User

Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942

Posted: February 04 2012 at 10:17 \| IP Logged

syonker wrote:

On the other hand, funneling it off to a txt file would allow one to put in an independent periodic purge (using Windows, or other utilities).

Syonker. Sorry I wasn't very clear in my comment. PowerHome currently has a function ""ph_trimeventlog(n)" where "n" is the number of days you want the log trimmed to.

That is "2" would trim everything beyond two days of log data.

You just set it up as a Timed Event to be run periodically.

I set the Timed Event to run every day at 5AM in the morning, so I always have two days of data to review.

There is ALSO an Export Event Log function that lets you do exactly what you suggested . . .

ph_exporteventlog ( start, end, type, filename, colheading, trim )

All of this can be done from within PH.

Ain't it a great product!!!!?

__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!

syonker
Senior Member

Joined: March 06 2009
Location: United States
Online Status: Offline
Posts: 212

Posted: February 04 2012 at 10:45 \| IP Logged

Yeah I actually had that setup too; however.....

So let's say that you're a semi-moron like me and in the process of doing an upgrade, you realize that your ph_trimeventlog(1) event is actually turned off....and has been for well over a year....and now you have a 1.09GB database, so you manually go in and run the function......how long should it take? I've been waiting for a half hour for the function to come back from "not responding". Should I keep waiting or cut bait, or will doing so cause some sort of DB corruption that's insurmountable...?

AUGH!

-S

__________________
"I will consider myself having succeeded when my house becomes sentient and attempts to kill me."

><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>¸.

ˇ´Ż`ˇ.¸. , . ><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>

syonker
Senior Member

Joined: March 06 2009
Location: United States
Online Status: Offline
Posts: 212

Posted: February 04 2012 at 12:15 \| IP Logged

Been a couple hours now and still not responding. Thoughts?

__________________
"I will consider myself having succeeded when my house becomes sentient and attempts to kill me."

><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>¸.

ˇ´Ż`ˇ.¸. , . ><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>

syonker
Senior Member

Joined: March 06 2009
Location: United States
Online Status: Offline
Posts: 212

Posted: February 04 2012 at 14:05 \| IP Logged

Ok, this is a major fail. What would be the SQL I coud run to purge this table using a query? Everything else is crashing the PH exe...can't even bring up the event log. Function isn't working.

__________________
"I will consider myself having succeeded when my house becomes sentient and attempts to kill me."

><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>¸.

ˇ´Ż`ˇ.¸. , . ><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>

grif091
Super User

Joined: March 26 2008
Location: United States
Online Status: Offline
Posts: 1357

Posted: February 04 2012 at 14:20 \| IP Logged

Try running the database update utility. If the trim had of worked it is what would have been run next to actually reduce the physical size of the database. The utility creates a new database and often resolves database problems.

__________________
Lee G

syonker
Senior Member

Joined: March 06 2009
Location: United States
Online Status: Offline
Posts: 212

Posted: February 04 2012 at 15:44 \| IP Logged

Trying "delete from eventlog" via SQL script (raw). worked for "weblog" after a few minutes...but eventlog is taking quite some time. Gonna just let it run all day and see what happens.

Can't run the update - the DB is too large and it gives up. I created my own problem here....fortunately backups in place, so can always revert if I need to, but really want to correct the problem rather than gloss over it.

-S

__________________
"I will consider myself having succeeded when my house becomes sentient and attempts to kill me."

><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>¸.

ˇ´Ż`ˇ.¸. , . ><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>

syonker
Senior Member

Joined: March 06 2009
Location: United States
Online Status: Offline
Posts: 212

Posted: February 04 2012 at 17:50 \| IP Logged

No good. Failed with "Another User has the table locked". Wondering, if I drop the EventLog table, then run update util, will it create a new table? Otherwise I need the table structure and syntax to create it anew.

-S

__________________
"I will consider myself having succeeded when my house becomes sentient and attempts to kill me."

><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>¸.

ˇ´Ż`ˇ.¸. , . ><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>

syonker
Senior Member

Joined: March 06 2009
Location: United States
Online Status: Offline
Posts: 212

Posted: February 04 2012 at 17:51 \| IP Logged

So ran the update a couple of times. Now, dialogue "An error occurred while trying to retrieve registration information". Egad.

__________________
"I will consider myself having succeeded when my house becomes sentient and attempts to kill me."

><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>¸.

ˇ´Ż`ˇ.¸. , . ><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>

syonker
Senior Member

Joined: March 06 2009
Location: United States
Online Status: Offline
Posts: 212

Posted: February 05 2012 at 07:43 \| IP Logged

Hi Gang,

If anyone else is having this problem, it was solved with the information in the following thread: http://www.power-home.com/forum/forum_posts.asp?TID=2714&TPN =1

-S

__________________
"I will consider myself having succeeded when my house becomes sentient and attempts to kill me."

><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>¸.

ˇ´Ż`ˇ.¸. , . ><(((ş>`ˇ.¸¸.ˇ´Ż`ˇ.¸><(((ş>

If you wish to post a reply to this topic you must first login
If you are not already registered you must first register

Printable version

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