SQL Logger Help

Posted on
Sun Jul 26, 2020 7:31 am
HEGarrard offline
Posts: 21
Joined: Sep 09, 2014

SQL Logger Help

I am trying to create a trigger to capture errors occurring with a Z-Wave Device. For some reason the error does not seem to be recorded in the database.

Here is information from the Event Log - reloading the SQL Logger PlugIn

Jul 26, 2020 at 8:13:56 AM
Reloading plugin "SQL Logger 7.4.1"
Stopping plugin "SQL Logger 7.4.1" (pid 34769)
SQL Logger closing SQL connection
Stopped plugin "SQL Logger 7.4.1"
Starting plugin "SQL Logger 7.4.1" (pid 46355)
Started plugin "SQL Logger 7.4.1"
SQL Logger connected to /Library/Application Support/Perceptive Automation/Indigo 7.4/Logs/indigo_history.sqlite
SQL Logger searching for unused device and variable tables
SQL Logger search complete (none found)
SQL Logger updating all device tables
SQL Logger updating all variable tables
SQL Logger update complete

So I sent a Status Update request to my FRONT DOOR z-Wave Lock ... and you can see that there is an error.

Jul 26, 2020 at 8:15:00 AM
Z-Wave Error send "FRONT DOOR LOCK (Schlage 469)" status request failed
Z-Wave received "STUDY - AIR PURIFIER" energy total to 150.741 kWh
Z-Wave received "STUDY - AIR PURIFIER" power load to 26.210 W

When I run "Debug" in the SQL Logger - the error does not get added to the database.

Z-Wave Error send "FRONT DOOR LOCK (Schlage 469)" status request failed
SQL Logger Debug INSERT INTO device_history_623522302 ("longstatusstring", "timeleftseconds") VALUES (?, ?);
SQL Logger Debug (u'13:37:46', u'49066')
SQL Logger Debug INSERT INTO device_history_1741244237 ("time_24hmmssampm", "time_24hmmss", "time_24hhmmss", "time_12hmmssampm", "time_12hhmmss", "time_12hhmmssampm", "time_12hmmss", "time_24hhmmssampm", "time_12hmmssampml", "time_12hhmmssampml", "localtime") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
SQL Logger Debug (u'8:09:39AM', u'8:09:39', u'08:09:39', u'8:09:39AM', u'08:09:39', u'08:09:39AM', u'8:09:39', u'08:09:39AM', u'8:09:39am', u'08:09:39am', u'08:09:39')
SQL Logger Debug INSERT INTO device_history_623522302 ("longstatusstring", "timeleftseconds") VALUES (?, ?);

I have my Trigger setup as follows:

TYPE: SQL Logger Event
Event: Error in Event Log

Errors to monitor for: All (I have tried Specific Type as "Z-Wave Error")
String to match: "FRONT DOOR LOCK (Schlage 469)" ... with and without " "


Thanks in advance for any assistance!

Harold

Posted on
Sun Jul 26, 2020 9:05 am
HEGarrard offline
Posts: 21
Joined: Sep 09, 2014

Re: SQL Logger Help

This is a screenshot of the SQL Logging configuration.

I have also tried it with Changed states only checked.
Attachments
SQL Logger Settings.jpg
SQL Logger Settings.jpg (126.79 KiB) Viewed 220 times

Posted on
Sun Jul 26, 2020 2:12 pm
matt (support) offline
Site Admin
User avatar
Posts: 20080
Joined: Jan 27, 2003
Location: Texas

Re: SQL Logger Help

The SQL Logger Debug output, unfortunately, isn't going to help in this case. It does't log the logging commands, because those would then themselves get debug logged causing infinite logging recursion. :-)

For the string to match you need to enter a regular expression. Try using this:

(.*)FRONT DOOR LOCK(.*)status request failed

Image

Posted on
Sun Jul 26, 2020 2:57 pm
HEGarrard offline
Posts: 21
Joined: Sep 09, 2014

Re: SQL Logger Help

Matt,

Thanks for the reply. I am assuming that the error message should be in the SQL Logging file.

These are the lines in the "eventlog_history" at the time I ran the status request of the z-wave lock.

1660,2020-07-26 08:04:54,False,5,Z-Wave,"received ""STUDY - AIR PURIFIER"" power load to 25.896 W"
1661,2020-07-26 08:14:53,False,5,Z-Wave,"received ""STUDY - AIR PURIFIER"" energy total to 150.608 kWh"
1662,2020-07-26 08:14:54,False,5,Z-Wave,"received ""STUDY - AIR PURIFIER"" power load to 26.765 W"
1663,2020-07-26 08:15:00,False,18,Schedule,GARAGE DOOR UP AFTER 10 PM
1664,2020-07-26 08:24:53,False,5,Z-Wave,"received ""STUDY - AIR PURIFIER"" energy total to 150.613 kWh"
1665,2020-07-26 08:24:54,False,5,Z-Wave,"received ""STUDY - AIR PURIFIER"" power load to 26.639 W"

It does not appear that the SQL Logger is picking up the Z-Wave Error.


Harold

Posted on
Sun Jul 26, 2020 3:04 pm
HEGarrard offline
Posts: 21
Joined: Sep 09, 2014

Re: SQL Logger Help

Matt -

Sorry! Rookie mistake.
When searching the database I was not searching the "message" section.

Here are the results ...

2050,2020-07-26 12:51:03,True,1,Z-Wave Error,"send ""FRONT DOOR LOCK (Schlage 469)"" status request failed"
2058,2020-07-26 12:53:36,True,1,Z-Wave Error,"send ""FRONT DOOR LOCK (Schlage 469)"" status request failed"
2061,2020-07-26 12:56:56,True,1,Z-Wave Error,"send ""FRONT DOOR LOCK (Schlage 469)"" status request failed"
2062,2020-07-26 12:58:29,True,1,Z-Wave Error,"send ""FRONT DOOR LOCK (Schlage 469)"" status request failed"
2073,2020-07-26 13:04:19,True,1,Z-Wave Error,"send ""FRONT DOOR LOCK (Schlage 469)"" status request failed"
2076,2020-07-26 13:05:52,True,1,Z-Wave Error,"send ""FRONT DOOR LOCK (Schlage 469)"" status request failed"
2077,2020-07-26 13:07:43,True,1,Z-Wave Error,"send ""FRONT DOOR LOCK (Schlage 469)"" status request failed"
3807,2020-07-26 13:09:38,True,1,Z-Wave Error,"send ""FRONT DOOR LOCK (Schlage 469)"" status request failed"
4413,2020-07-26 13:13:07,True,1,Z-Wave Error,"send ""FRONT DOOR LOCK (Schlage 469)"" status request failed"
4417,2020-07-26 13:15:00,True,1,Z-Wave Error,"send ""FRONT DOOR LOCK (Schlage 469)"" status request failed"
4420,2020-07-26 13:16:43,True,1,Z-Wave Error,"send ""FRONT DOOR LOCK (Schlage 469)"" status request failed"

Now I am going to have to figure out why the TIMESTAMPS are off in the SQL LOGGING file.
All of the above "tries" were in the morning - not the afternoon.

Posted on
Sun Jul 26, 2020 3:23 pm
HEGarrard offline
Posts: 21
Joined: Sep 09, 2014

Re: SQL Logger Help

MATT,

Thank you for the excellent assistance!

Using (.*)FRONT DOOR LOCK(.*)status request failed worked as you expected.

Harold

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 1 guest