Send Logs to External Database

[Just5]

So quick backstory, I am brand new to Homeseer after coming from a competitor. I am very happy so far, but have a few kinks I need to work out. The most important one to me is logging.

When using a competitors device, I simply had device status/variables being logged to SYSLOG, and then sent to a SYSLOG server. From there I was able to database and extract information for use in graphing applications (Kibana/Grafana). This was mainly for energy monitoring. I take a reading off of my Aeon Labs Energy Monitor every 30 seconds, and then it was graphed throughout the day using the log data.

I know there are several plugins for HomeSeer for graphing devices, but they are not as in-depth or graphically "pretty" as what I was using.

So my question is, is there a way to either trigger an event and log certain devices to an external database, such as SQLite, or even just send the entire log to an external database...

[NicolaiL]

I can't think of a way to do this with the native logging.

However, take a look at the UltraLog plug-in. I think that might help you with what you are trying to achieve.

[S-F]

I may be way off the mark here but I don't think that sending the logs somewhere else is a function found in UltraLog. But it is a really interesting feature so he may be willing to add it.

[sparkman]

UltraLog does create a copy of the HS log into a different database file. You may be able to access it and use it for your needs.

HS user chrisgo has posted some utilities here: https://sites.google.com/site/b123cdd/ some of which look to do what you need.

Cheers
Al

[Just5]

Thanks guys, I will look at these suggestions tonight!

[Just5]

UltraLog does create a copy of the HS log into a different database file. You may be able to access it and use it for your needs.

HS user chrisgo has posted some utilities here: https://sites.google.com/site/b123cdd/ some of which look to do what you need.

Cheers
Al

Log2Syslog looks to be exactly what I'm looking for...THANKS!

[vasrc]

Log2Syslog looks to be exactly what I'm looking for...THANKS!

Also checkout the Device_History PI. It logs every change of the devices you select, into a SQLite DB. You could do a SQL query of that DB to retrieve the data for the chart (ie Crystal reports, etc).
The Fields are:
CHANGE_DATE
DEVICE_REF
OLD_VALUE
NEW_VALUE
NEW_STRING

Z

[Just5]

Also checkout the Device_History PI. It logs every change of the devices you select, into a SQLite DB. You could do a SQL query of that DB to retrieve the data for the chart (ie Crystal reports, etc).
The Fields are:
CHANGE_DATE
DEVICE_REF
OLD_VALUE
NEW_VALUE
NEW_STRING

Z

I am actually using that Plugin on a trial basis right now. I honestly didn't know I could grab anything from the database it is using. I will give that a shot as well, thanks!

[vasrc]

I am actually using that Plugin on a trial basis right now. I honestly didn't know I could grab anything from the database it is using. I will give that a shot as well, thanks!

It should just be a quick db read (ie SELECT * from db where DEVICE_REF=xxx). I just did a quick query and it worked OK. Might drop the PI writer a note to make sure they don't do something special with locks or somesuch that cause problems, but a read should typically wait for any locks to clear.

He also has some Views setup as well to summarize the data retrieved as well. There is also an HS Event Action to create a chart. Probably for HStouch I would suspect?

I would have liked to have seen them provide a rollup database as well for Hourly, daily, etc summaries, like most historical DB systems do so you don't always have to query such large chunks of data.

Z

[waynehead99]

Not sure if this is what you are looking for, but here is a script to pull any device change and put it into a sql database. I pulled this from another post in this board somewhere (long time ago so don't remember), and modified it slightly to send data to 2 tables. One table gets overwritten with current data. The other table is updated and maintained for history.

It has been working well for me. Change from .txt to .vb. You also need to add this to the startup.vb script to utilize this script:

hs.RegisterStatusChangeCB("L2DB.vb","Main")

[mrhappy]

This is a script I wrote for HS2, it will need a couple of amendments for HS3 but what it did was register for a callback with HS and then when a device changes status then it will pass in the status/device ref/date/time and put those in an SQLite DB. I used it but never actually did anything with the data so stopped it...

Imports System.Data.SQLite
Imports System.Text

Const DataPath As String = "\data\generaldata.db3"
Const L As String = "DevLogging"

Sub Main(ByVal Parms As Object)

Try

Dim SQLconnect As New System.Data.SQLite.SQLiteConnection()
Dim SQLcommand As System.Data.SQLite.SQLiteCommand

SQLconnect.ConnectionString = "Data Source=" & hs.getapppath & datapath & ";"
SQLconnect.Open()
SQLcommand = SQLconnect.CreateCommand

Dim DSt As String = Nothing

If Parms(2) <> 17 Then

Select Case Parms(0)
Case "]", "`", "Y"
'hs.writelog(L, "Ignore - " & Parms(0))

Case Else

DSt = "INSERT INTO DevStatusData (DateAdded, UC, Status) VALUES ('" & DateTime.Now.ToString("yyyy-MM-dd HH:mm") & "','" & Parms(0) & Parms(1) & "','" & Parms(2) & "');"

'hs.writelog(L,DSt)

SQLcommand.CommandText = DSt
SQLcommand.ExecuteNonQuery()

End Select

SQLcommand.Dispose()
SQLconnect.Close()

End If

Catch ex as Exception

hs.writelog(L, "Error: " & ex.Message)

End Try

End Sub

Sub CallBack(ByVal Parms As Object)

hs.RegisterStatusChangeCB("logdevices.vb","Main")

End Sub

Sub CDB(ByVal Parms As Object)

Try

Dim SQLconnect As New System.Data.SQLite.SQLiteConnection()
Dim SQLcommand As System.Data.SQLite.SQLiteCommand

SQLconnect.ConnectionString = "Data Source=" & hs.getapppath & datapath & ";"
SQLconnect.Open()
SQLcommand = SQLconnect.CreateCommand

SQLcommand.CommandText = "CREATE TABLE IF NOT EXISTS DevStatusData (DateAdded DATE, UC TEXT, Status INTEGER);"
SQLcommand.ExecuteNonQuery()

SQLcommand.Dispose()
SQLconnect.Close()

hs.writelog(L, "Database Created")

Catch ex as Exception

hs.writelog(L, "Error: " & ex.Message)

    End Try


End Sub

Sub WeedDB(ByVal Parms As Object)

    hs.writelog(L, "Weeding Old DB Records")

    Dim SQLconnect As New System.Data.SQLite.SQLiteConnection()
    Dim SQLcommand As System.Data.SQLite.SQLiteCommand

    SQLconnect.ConnectionString = "Data Source=" & hs.getapppath & "\data\generaldata.db3;"
    SQLconnect.Open()
    SQLcommand = SQLconnect.CreateCommand

    SQLcommand.CommandText = "DELETE FROM DevStatusData WHERE DateAdded BETWEEN '" & DateTime.Now().AddMonths(-1).ToString("yyyy-MM-dd") & "' AND '" & DateTime.Now().AddMonths(-6).ToString("yyyy-MM-dd") & "'"

    hs.writelog(L, SQLCommand.CommandText)
    SQLcommand.ExecuteNonQuery()

End Sub

[lveatch]

So quick backstory, I am brand new to Homeseer after coming from a competitor. I am very happy so far, but have a few kinks I need to work out. The most important one to me is logging.

When using a competitors device, I simply had device status/variables being logged to SYSLOG, and then sent to a SYSLOG server. From there I was able to database and extract information for use in graphing applications (Kibana/Grafana). This was mainly for energy monitoring. I take a reading off of my Aeon Labs Energy Monitor every 30 seconds, and then it was graphed throughout the day using the log data.

Do you want to keep your SYSLOG integration method? Depending on your scripting / coding skills ... how about using the ASCII control interface to watch for device changes, filter on your energy devices and send those changes to syslog.

ASCII control interface documentation is at http://homeseer.com/support/homeseer...nk/default.htm .

An untested Perl version follows.

#!/usr/bin/perl

use IO::Socket::INET;
use Net::Syslog;
#use Data::Dumper;

# flush after every write
$| = 1;

my ($socket,$client_socket);

my $now = localtime;
$socket = new IO::Socket::INET (
        PeerHost => 'hs_server',
        PeerPort => '11000',
        Proto => 'tcp',
) or die "ERROR in Socket Creation : $!\n";

print "TCP Connection Success. $now\n";

$data = 'au,<id>,<passwd>';
print $socket "$data\r\n\n";
$data = <$socket>;
$now = localtime;
print "$now Received from Server : $data\n";

$data = 'vr';
print $socket "$data\r\n\n";
$data = <$socket>;
$now = localtime;
print "$now Received from Server : $data\n";


while (my $data = <$socket>) {
    $now = localtime;
    chomp $data;
    #print "$now: $data\n";
    my ($action, $ref, $newValue, $oldValue) = split (/,/, $data);
    print "$now --> $action:$ref:$newValue:$oldValue\n";

    # write to remote syslog.
    my $syslog = new Net::Syslog(Facility => 'local4',
                                 SyslogHost => '192.168.1.36',
                                );
    $syslog->send("$ref,$newValue", Priority => 'informational');

}

$socket->close();

Output:
Wed Feb 3 22:54:01 2016 Received from Server : ok

Wed Feb 3 22:54:01 2016 Received from Server : 3.0.0.208

Wed Feb 3 22:54:13 2016 --> DC:234:0:255
Wed Feb 3 22:54:15 2016 --> DC:480:51.992:51.991
Wed Feb 3 22:54:15 2016 --> DC:538:96.542:96.383
Wed Feb 3 22:54:16 2016 --> DC:539:96.542:96.383
Wed Feb 3 22:54:30 2016 --> DC:538:96.464:96.542
Wed Feb 3 22:54:31 2016 --> DC:539:96.464:96.542

[Just5]

Thanks everyone, all of this worked, now I just have to choose one

[greg631]

UltraLog does create a copy of the HS log into a different database file. You may be able to access it and use it for your needs.

HS user chrisgo has posted some utilities here: https://sites.google.com/site/b123cdd/ some of which look to do what you need.

Cheers
Al

This is exactly what I'm looking for, but after enabling it, I get an error:
"CRITICAL: Plugin has disconnected"

Anyone else have this issue?

Thanks.

[BigBadBuzz]

Greg:

I just tried it a few minutes ago - I get the same thing. By any chance are you running HomeSeer on Linux?

BBB