This utility allows to:
1. print all states of all devices and variables into the log file in a nice format with current values. This is useful to get a quick overview of your setup
2. print device.Name and device.ID (and variables) to the log file. ==> if you have the device.ID but not the device.name
3. print # of records stored for each device and variable. Useful to find "offenders" i.e. devices that create a lot of entries and might explode your database
4. query device/ states from sqlite/postgres and print result to file or indigo logfile and indigo variables (the first record of the selection is copied to an indigo variable) - can also be done from an ACTION. Can be used to understand patterns of up/donw states and also to e.g. get the seconds last entry, i.e. what was it before the current entry though an easy python call
5. make a backup of indigo_history_sqlite. It can be called from the MENU or through an ACTION. After the copy is finished a test query is done.
--- If ok: rename backup db to indigo_history-1.sqlite and the last one to -2.sqlite
--- if not ok you get a message in the log file
--- anyone that had a bad SQLite DB will appreciate this function
--- it takes ~ 10 seconds for a 6.5 Gbyte database on a mac pro 3,1 (2008)
==> this is useful to (a) make a consistent backup at lets say 2 am for the last 2 days and (b) make sure it is not corrupt, or if it is you will get a message in your log file (this should be expanded to a email). You can also start the backup also from an action group or schedule (select "utilities Action" / "create and test backup of ..")
6. try to fix a broken indigo_history.sqlite :
--- make a copy, create a dump file,sort records to date-stamp, remove duplicates, reimport the dump file into new db and run a test query against it. This has been tested with 2 separate incidences with different issues.
--- it takes ~ 1hour minutes for a 8 Gbyte database on a mac-mini2014 16GByte Ram, 2TB fusion i7 )
--- at the end of the process you need to shut down the indigo server, rename the existing sqlite database and rename the just created database to indigo_history.sqlite, then restart the server and in many cases you will have a working data base again. You will lose ~ 30 minutes of data from the time the process started, until the new database is installed
7. print all wave devices with their neighbors. Useful if you want to debug your z-wave system and create .dot file for graphing zwave connections and if graphviz is installed it will also create a .zsv file ( ==> contributed by Frank Nicholas, see discussion @ http://forums.indigodomo.com/viewtopic.php?f=84&t=15251&p=108467&hilit=frank#p108467)
9. print Trigger info. lists available info about triggers, name,id, what triggered, and trigger parameters, no info is available on conditions, and actions
not yet implemented: prune/ compress the data base selectively i.e. records that are in the db twice and or prune all variable entries older than date xx. this will reduce your database size by ~ 20-50% depending on your sensors and settings.
10. trigger event when backup failed This is useful in case you have a corrupt DB you can set a trigger that will notify you
More detailed logging can be found in /Users/YourID/documents/indigoUtilities/backup.log
11. ping device you can enter deny ip number of dns name for a device to be pinged, result is shown in the log file
12.print Plugin names & IDs including daughter processes and consumed CPU time to log file. This is useful if you have many plugin running and would like to know which one is which as the all have the same name. The info is composed from "ps -ef" output in a terminal session.
13.print versions # of all installed active plugin and version available on the indigo store. this is throttled to 1 query /1.5sec and can only be run once a day.
14.[b]delete duplicate records in sql data base This option will go through your indigo SQL database and remove records with the same time stamp- keep only last one. there are several modes:
== test- will create the SQL statements in a file in the indigo utilities directory,
== execute - will execute these sql statements and and print progress in logfile
== execute quiet - will execute the SQL statement , but will not show progress in the log file
+++ you can do this through the menu or through an action (execute and execute quite)
+++ this can take a long time depending on the size of your database.
++ if you use SQLITE, please stop sqllogger before executing. when finished reenable sqllogger. POSTERS seems to be better for multi user / access environments.
For sqlite to actually shrink the database open a terminal and executed: :
- Code: Select all
sqlite3 '/Library/Application Support/Perceptive Automation/Indigo 6/Logs/indigo_history.sqlite' .dump > dump ; sqlite3 smaller < dump .dump > dump ; sqlite3 smaller < dump
- Code: Select all
mv '/Library/Application Support/Perceptive Automation/Indigo 6/Logs/indigo_history.sqlite' '/Library/Application Support/Perceptive Automation/Indigo 6/Logs/indigo_history.sqlite.copy'
- Code: Select all
cp smaller '/Library/Application Support/Perceptive Automation/Indigo 6/Logs/indigo_history.sqlite'
ALL THIS HAS TO BE DONE WHILE SQL-LOGGER IS OFF IN INDIGO when you use SQLITE.
14.make a backup of important indigo files i.e. config database and preferences This option can be invoked through a menu, action or python script. I am using it to make a a copy of these directories every 2 hours . This will make sure that you always have a valid backup of you r INDIGO configs (devices, variables, actions, plugin configs etc). -- I added this after I lost almost everything as my backup did not work properly.
This took
- 2 hours for the deletes
-35 minutes for a database dump and recreate of new db
starting with a 50Gbyte sqlite db ending with a 20Gbyte db
on a mac mini 2014 i7 16GB with a 2 TB fusion drive
this will retire 2 other plugins, their functionality is included in this one.
- print SQL data in log file
- Print all States of all Variables and Devices to log file
Karl
the main menu
select device states etc to be retrieved from the sql db
output into the indigo variables
output of sql very to log file
example output of #1 print devices/states ..
- Code: Select all
============== Print variables and devices to logfile =============
Variable ID -----------------------------variables: Value
1235558255 abc: 0
200232878 alarmVariable: false
657358351 alarm_any: 1
52939690 alarm_BackW_1: 0
1948752398 alarm_BackW_1_changed: 0
1279900999 alarm_BackW_2: 0
827282794 alarm_BackW_2_changed: 0
1140522547 alarm_control_arduino_reboot_email_send: 0
...
Device ID ---------------------------device Name: State(Value), State(Value), ...
145294727 Alarm-arduino: MACNumber("DE:AD:BE:EF:FE:EE"), WiFi(""), deviceInfo(""), hardwareVendor(""), ipNumber("192.168.1.152"),
: nickName("Alarm-arduino"), noOfChanges("661"), status("up"), statusDisplay("up 2015-01-1"), timeOfLastChange("2015-01-13 04:42:32"),
: usePing(""),
1759240170 apple-Leo-xx: MACNumber("7C:6D:62:8F:A3:DE"), WiFi(""), deviceInfo("leo-wachs"), hardwareVendor("Apple"), ipNumber("192.168.1.240"),
: nickName("apple-Leo-xx"), noOfChanges("7"), status("expired"), statusDisplay("expired 2015-01-01 0"), timeOfLastChange("2015-01-01 00:19:57"),
: usePing(""),
857475942 appleTV: MACNumber("58:55:CA:11:46:E9"), WiFi(""), deviceInfo(""), hardwareVendor("Apple"), ipNumber("192.168.1.212"),
: nickName("appleTV"), noOfChanges("829"), status("expired"), statusDisplay("expired 2014-10-20 1"), timeOfLastChange("2014-10-20 19:38:52"),
: usePing(""),
767493990 AsusRouter: MACNumber("D8:50:E6:CF:B4:E0"), WiFi(""), deviceInfo("router.asus.com"), hardwareVendor(""), ipNumber("192.168.1.6"),
: nickName("AsusRouter"), noOfChanges("1121"), status("up"), statusDisplay("up 2015-01-1"), timeOfLastChange("2015-01-13 04:43:41"),
: usePing(""),
273552650 backWindowSensor1: onOffState("False"),
1043783476 backWindowSensor2: onOffState("False"),
1452111289 bigMac: MACNumber("00:1F:5B:30:E9:20"), WiFi(""), deviceInfo("dad"), hardwareVendor("Apple"), ipNumber("192.168.1.52"),
: nickName("bigMac"), noOfChanges("101"), status("expired"), statusDisplay("expired 2014-11-25 2"), timeOfLastChange("2014-11-25 23:55:25"),
: usePing(""),
82928661 Cam-1: MACNumber("F0:7D:68:06:F6:87"), WiFi("2GHz"), deviceInfo(""), hardwareVendor("D-Link"), ipNumber("192.168.1.71"),
: nickName("Cam-1"), noOfChanges("520"), status("up"), statusDisplay("up 2015-01-1"), timeOfLastChange("2015-01-17 14:55:29"),
: usePing("Sig[dBm]:-44,ave:-46"),
1009180251 Cam-2: MACNumber("00:22:B0:E5:D4:C8"), WiFi(""), deviceInfo(""), hardwareVendor("D-Link"), ipNumber("192.168.1.72"),
: nickName("Cam-2"), noOfChanges("388"), status("up"), statusDisplay("up 2015-01-1"), timeOfLastChange("2015-01-13 04:42:32"),
: usePing(""),
107339729 Cam-4: MACNumber("F0:7D:68:08:5F:D0"), WiFi("2GHz"), deviceInfo(""), hardwareVendor("D-Link"), ipNumber("192.168.1.74"),
...
example python action code:
- Code: Select all
plug = indigo.server.getPlugin("com.karlwachs.utilities")
if not plug.isEnabled(): return
## print variable
plug.executeAction("printSQLaction" , props ={
"printFile": "" ##/users/karlwachs/documents/sql" ## eeither "" -> to logfile or print ot file --> /users/yourid/documents/filename
, "header": "yes" ## either "yes" or "no"
, "separator": ";" ## either "" or ";" or "\t" for tab or "tab"
, "devOrVar": "var" ## dev or var
, "variable": "EnergyToday2" ##variable name or variable-id
, "id": "0" ## first id to print
, "numberOfRecords":"2"
} )
## print device/states
plug.executeAction("printSQLaction" , props ={
"printFile": "sqlOutput" ## either "" -> to logfile or print ot file --> /users/yourid/documents/ >>filename<<<
, "header": "yes" ## either "yes" or "no"
, "separator": ""
, "devOrVar": "dev" ## dev or var
, "device": "volvo" ## device name or device-id
, "id": "" ## first id to print, if "": print the last numberOfRecords records, if =0: print first numberOfRecords
, "numberOfRecords":"10" ## # of records to be printed
, "state0": "Signal" ## can be name of state or *
, "state0Condition":"eq" ## can be "eq" or "ne" or "any" or ""
, "state0Value" :"-55" ## can be any string but not ""
, "state1": "Temperature" ## name of 2. state or ""
, "state2": "Presence" ## ...
, "state3": "MotherAssociated"
, "state4": ""
} )
## delete devices states before #of days with:
plug.executeAction("pruneDevsandVarsAction" , props ={
"devOrVar": "dev" # dev or var
, "device": "1312758245" # dev indigo id or name
, "deleteBeforeThisDays": "55" # # of days to be kept == keeps 55 days
} )
plug.executeAction("pruneDevsandVarsAction" , props ={
"devOrVar": "dev" # dev or var
, "device": "Karl FAN" # dev indigo id or name
, "deleteBeforeThisDays": "2" # # of days to be kept == keeps 2 days
} )
plug.executeAction("pruneDevsandVarsAction" , props ={
"devOrVar": "var" # dev or var
, "variable": "EnergyDay_7" #var indigo id or name
, "deleteBeforeThisDays": "2" # # of days to be kept
} )
plug.executeAction("pruneDevsandVarsAction" , props ={
"devOrVar": "var" # dev or var
, "variable": "EnergyDay_7" #var indigo id or name
, "deleteBeforeThisDays": "2" # # of days to be kept
} )
##squeeze duplicate records with
plug.executeAction("actionDatabaseSqueeze" , props ={ })
##squeeze duplicate records (quietly) with
plug.executeAction("actionDatabaseSqueezeQuiet" , props ={ })
## create a postgres dump /users/documents/yourhomeDirectory/indigouUtilities/postgresBackup.dump
plug.executeAction("executeBACKUPpostgresAction" , props ={ })
## make a backup of all important indigo files copied to /users/documents/yourhomeDirectory/indigouUtilities/....
plug.executeAction("IndigoBackup" , props ={ })
for #2 example output for # of records in sql data base:
- Code: Select all
utilities # of records devId / Name for DEVICES
utilities 0 36863338 / Z-Wave Repeater living room
utilities 0 56627415 / 014-Clamp 3
utilities 0 60819802 / example
utilities 0 146980745 / scatter1
utilities 0 193085791 / yyy
utilities 0 309074716 / wifiSignalTest
utilities 0 309579619 / EnergyUsage ü
utilities 0 310156472 / 006 - Clamp 3
utilities 0 368213950 / temp
utilities 0 396470314 / remote_8_button
utilities 0 397373477 / Z-Wave Repeater Kons
....
utilities 595278 325253257 / konstantin
utilities 600520 533133990 / master Bed
utilities 615598 46489666 / TempOutside
utilities 636725 374645596 / MeterTotalLeftBox
utilities 643801 1130448881 / Meter-RightBoxClamp1
utilities 643889 720397021 / Meter-RightBoxClamp2
utilities 712322 776367642 / MeterTotalRightBox
utilities 748960 1954322276 / blueJacket Leo II
utilities 878928 1431469100 / kitchen
utilities 5748292 375541516 / wind sensor
utilities # of records varId / Name for VARIABLES
utilities 8 1079519644 / huzzahh
utilities 8 1449882585 / alarm_control_script_new_data
utilities 10 469180806 / huzzah
utilities 12 258515116 / ESP5
....
utilities 82019 152675605 / alarm_control_script_data_count
utilities 152125 1861388192 / alarm_motion_detection
utilities 152643 970221212 / alarm_motion_detection_changed
utilities 174077 687297433 / averageWiFiSignal-5GHz
utilities 248202 1717323797 / averageWiFiSignal-2GHz
utilities 262512 535852682 / Irrigationcaddy_action
utilities 601942 1929581894 / Irrigationcaddy_Zone_SecLeft
utilities 601944 1749698837 / Irrigationcaddy_Prog_Seconds_Left
example to z-wave info print out
- Code: Select all
============== Print zwave info of devices to logfile =============
Device ID ---------------------device Name -addr: neighbors
1332829462 ThermostatGameRoom- 2: 1, 3, 4, 5, 7, 9, 12, 13, 15, 16, 17, 20
1623295497 ThermostatExerciseRoom- 3: 1, 2, 6, 7, 12, 13, 16, 17, 20
55209657 ThermostatDownstairs- 4: 1, 2, 5, 14, 15, 16, 17, 19, 20, 22, 23, 24, 25, 27, 28, 29
768183209 ThermostatUpstairs- 5: 1, 4, 15, 17, 19, 22, 23, 24, 25, 26, 27, 28, 29
776367642 MeterTotalRightBox- 6: 1, 3, 7, 12, 13, 16, 17, 20
374645596 MeterTotalLeftBox- 7: 1, 2, 3, 4, 5, 6, 9, 10, 11, 12, 13, 14, 15, 16, 17, 19, 20, 29
1223893079 Key Fob- 8: - none -
1215370021 outsideMosquito switch tree- 9: 1, 2, 12, 13, 20
282154644 downstairsHallwayoutletMiddle- 11: 1, 2, 3, 4, 5, 10, 15
115635811 Game Room TV switch- 12: 1, 2, 3, 6, 7, 9, 10, 11, 13, 14, 15, 16, 17
662003093 Excercise room TV switch- 13: 1, 2, 3, 4, 6, 7, 9, 12, 15, 16, 17, 20, 29
1766870014 poolEnergy total- 14: 1, 4, 15, 17
113937113 attic squirrel 2- 15: 1, 2, 4, 5, 7, 13, 14, 16, 17, 19, 20, 22, 23, 26, 27, 28, 29
1275294478 Z-Wave Repeater garage work bench- 16: 1, 2, 3, 4, 6, 7, 10, 12, 13, 14, 15, 17, 20, 28, 29
1769189912 Z-Wave Repeater game room- 17: 1, 2, 3, 4, 5, 6, 7, 12, 13, 14, 15, 16, 19, 20, 27, 29
397373477 Z-Wave Repeater Kons- 19: 1, 4, 5, 15, 17, 22, 24, 25, 26, 28, 29
524220718 Z-Wave Repeater exercise room- 20: 1, 2, 3, 6, 7, 9, 10, 12, 13, 15, 16, 17
36863338 Z-Wave Repeater living room- 21: 1, 4, 5, 11, 14, 15, 17, 19, 20, 22, 23, 24
652241576 outsideBalkonyLights- 22: 1, 4, 5, 15, 19, 23, 24, 25, 26, 27
1288667224 downstrairsHallwayOutletFront- 23: 1, 4, 5, 11, 14, 15, 20, 21, 22
24596767 studyOutlet- 24: 1, 4, 5, 11, 15, 19, 21, 23
1469978670 livingRoom camera switch- 25: 1, 4, 5, 11, 15, 19, 20, 22, 24, 26
1875535582 frontBalkonyCameraSwitch- 26: 1, 4, 11, 17, 19, 22, 23, 24, 25, 27
1392012060 outsideMosquito switch Balkony- 27: 1, 4, 5, 17, 19, 22, 23, 25, 26
719674580 switch for insteon modem- 28: 1, 4, 5, 15, 19, 24, 27, 29
1017625793 coffeeMachineü- 29: 1, 4, 5, 7, 15, 16, 23, 24, 28
example of output for print trigger info
- Code: Select all
============== Print for each Trigger devices/variables that trigger them =============
Trigger Name Trig.ID Trig.SourceType Dev/Var/Plugin-ID Source-D/V/P-Name Other info
Alarm_excercise 121520365 device 1413551529 exerciseWindow state: onOffState-- changeType: Changes-- compareTo:
DoorsUnlock with key-fob 141240424 plugin tomation.indigoplugin.zwave zwaveCommand z-wave "Key Fob" button 2 pressed received
Garage_Door 820257178 device 396470314 remote_8_button cmd: On-- button/Group: 5
IP number changed 395786349 plugin com.karlwachs.fingscan IPNumberChanged
PoolTargetTempChanged 1298946066 variable 1183316306 poolTargetTemp chgType: Changes-- compareTo:
alarm off home Key fob 1011481739 plugin tomation.indigoplugin.zwave zwaveCommand z-wave "Key Fob" button 4 pressed received
alarm reset 25391179 variable 15846349 alarm_reset chgType: BecomesTrue-- compareTo:
alleyWayDoorOpenClose 1829600407 device 1352702133 alleyWayOpen-close cmd: AnyCommand-- button/Group: 1
alleywayDoorChanged2 310854818 device 542224739 outsideAlleyWayDoor state: binaryInputsAll-- changeType: Changes-- compareTo:
backW1 607786589 device 273552650 backWindowSensor1 state: onOffState-- changeType: BecomesTrue-- compareTo:
bad wifi 138456350 plugin com.karlwachs.fingscan badWiFi
brightness 503061623 variable 136862429 setbrightness100 chgType: BecomesEqual-- compareTo: 10000
cat fight terminator 91094848 plugin tomation.indigoplugin.zwave zwaveCommand z-wave "Key Fob" button 1 pressed received
eTrack - ThermostatUpstairs He 27200589 device 768183209 ThermostatUpstairs state: hvacHeaterIsOn-- changeType: Changes-- compareTo:
email_away 304840394 emailIncoming MatchEmailFields from: Subj: away
enoughRain 220969120 device 57636140 RainSensor state: raintotal-- changeType: Changes-- compareTo:
enoughRainNOT 322193216 device 57636140 RainSensor state: rainrate-- changeType: Changes-- compareTo:
excWoff 1149326979 device 1413551529 exerciseWindow state: onOffState-- changeType: BecomesFalse-- compareTo:
excWon 1918014925 device 1413551529 exerciseWindow state: onOffState-- changeType: BecomesTrue-- compareTo:
gallonCounter 1432400102 device 273552650 backWindowSensor1 state: onOffState-- changeType: BecomesTrue-- compareTo:
garageDoorChanged1 662065461 device 849429887 outsideGarageDoorOpen state: onOffState-- changeType: Changes-- compareTo:
garageDoorChanged2 471251233 device 1982763902 outsideGarageDoorOpener state: binaryInputsAll-- changeType: Changes-- compareTo:
home away fingscan all 172238082 plugin com.karlwachs.fingscan EVENT-1-allAway
home away fingscan one 674235408 plugin com.karlwachs.fingscan EVENT-1-oneAway
home iphone 1074720508 plugin com.karlwachs.fingscan EVENT-1-oneHome
home open fingscan all 10719099 plugin com.karlwachs.fingscan EVENT-1-allHome
leo off 1739688393 device 503851504 Leo6Switch cmd: Off-- button/Group: 1
light living room ceiling 685848916 variable 136862429 setbrightness100 chgType: BecomesEqual-- compareTo: 100000
power failure email 762821978 interface All
smo
how to setup trigger for failed sql backup:
ping a device:
print plugin names and ids (and daughter processes to log file:
- Code: Select all
utilities ---------------------pluginName------------ ID CPU
utilities .Action Collection 1624 0:36.47
utilities Group-Trigger 1625 0:34.63
utilities INDIGOplotD 1626 2:36.35
utilities |- indigoMPplot.py 1640 9:27.17
utilities miniPLOT 1627 0:35.20
utilities Plugin Developer Documenter-3 1629 0:35.76
utilities SQL Logger 1630 0:37.31
utilities Virtual Devices 1631 0:36.03
utilities piBeacon 5395 0:00.78
utilities utilities 5558 0:00.24
old versions of plugins:
https://www.dropbox.com/s/zvqkcunjg2z5rbn/utilities-v-1.12.5.zip?dl=1
https://www.dropbox.com/s/zxf6aizlir6sg3z/utilities-v-1.12.3.zip?dl=1
https://www.dropbox.com/s/ly4xscieniw8a4b/utilities-v-1-12-1.zip?dl=1
https://www.dropbox.com/s/v94kpkythhu5x0r/utilities-v-1-11-3.zip?dl=1
https://www.dropbox.com/s/0w8aqqje0i811ok/utilities-v-1-11-2.zip?dl=0
https://www.dropbox.com/s/xermrjdxa1tgvdb/utilities-v-1-11-1.zip?dl=1
https://www.dropbox.com/s/gzsirpvo6qhe8y6/utilities-v-1-10-1.zip?dl=1
https://www.dropbox.com/s/1ajiug7x0gt0sge/utilities-v-1-9-5.zip?dl=1
https://www.dropbox.com/s/xz9umt0bmvdo1de/utilities-v-1-9-4.zip?dl=1
https://www.dropbox.com/s/9rb8md9rhiid9h5/utilities-v-1-8-1.zip?dl=1
https://www.dropbox.com/s/01ym7m2br9o7a4n/utilities-v-1-7-4.zip?dl=1
https://www.dropbox.com/s/4e1cwma20h4b6y9/utilities-v-1-7-3.zip?dl=1
https://www.dropbox.com/s/fkd15iujv2j0ffh/utilities.indigoPlugin-v-1-6-1.zip?dl=1