Export variables so I can utilize them elsewhere?

Greetings!

I've been sifting forum entries till I can't see straight, but I can't seem to find exactly what I'm looking for..

I'm using an RFXplugin to sample a pile of Oregon Scientific temp/humidity sensors and populate a similar pile of variables with their data..

The actual question:
I want to export these environmental variables on a periodic basis to a (pick one!) XML, JSON or even just an individual raw text file so I can pick them up from a DIFFERENT HOST (FreeBSD secure web server) and stash the data in a database.. Most likely RRDTOOL databases.. Bonus points for suggestions on how to push the data to a remote host as part of the export process!!

I can see that people are doing this for local API use (the inFluxDB plugin is an example) but I can't can't seem to find a simple example of how to put this into effect.. That plugin looks mighty interesting, but I don't want to implement that on my somewhat over loaded Mac host..

Warning, never written a line of python in my life, BUT I'm not entirely stupid or experience free in other languages, so given an example/pointer/RTFM, I believe I can muddle through..

Thanks so much for any thoughts on how to solve my issues!

Sincerely,

Eric

All for keeping things simple, yet might be missing the point, but what about pushing to ThingSpeak Database via the ThinkSpeak plugin?

http://www.indigodomo.com/pluginstore/42/
https://thingspeak.com/

Glenn

Hmn.. That's an interesting thought, and interestingly I've investigated ThingSpeak with the thought of setting up a LoRa IOT network up.. Someday..

But really, there should be some relatively simple method to take variable X and poop it out for easy assimilation into RRDTOOL on an alternate host..

I'll see if I can glean anything from the ThingSpeak PlugIn.. Thanks for the pointer/suggestion!

You can simply do an sql query to the sqllogger dB

Indigo utilities has an option to run sql queries against the indigo dB in actions or from a menu

If you switch on sql debug you can see the sql statement that is used

Use that sql statement and change to your liking and run in a scheduled action and pipe into a file ...

If you need help how to do that just ask.

Karl.


Sent from my iPhone using Tapatalk

Ah, Karl.... Thank you, that is EXACTLY what I desire in a form I can easily digest!!

Code: Select all

[etimberl@hallinta:~]%/usr/bin/sqlite3 '/Library/Application Support/Perceptive Automation/Indigo 7.2/logs/indigo_history.sqlite' "SELECT value from variable_history_875607185 LIMIT 1 OFFSET (SELECT COUNT(*) FROM variable_history_875607185)-1;"
59.0
[etimberl@hallinta:~]%


Pretty easy to script that up for remote access, as well...

Code: Select all

[etimberl@webserv:~/weatherdata]% ssh hallinta -t "/usr/bin/sqlite3 /Library/Application\ Support/Perceptive\ Automation/Indigo\ 7.2/logs/indigo_history.sqlite \"SELECT value from variable_history_875607185 LIMIT 1 OFFSET (SELECT COUNT(*) FROM variable_history_875607185)-1;\""
Enter passphrase for key '/home/etimberl/.ssh/id_rsa':
59.0
Connection to hallinta.mtrcycllvr.org closed.
[etimberl@webserv:~/weatherdata]%
[etimberl@webserv:~/weatherdata]% eval `ssh-agent`
Agent pid 15930
[etimberl@webserv:~/weatherdata]% ssh-add
Enter passphrase for /home/etimberl/.ssh/id_rsa:
Identity added: /home/etimberl/.ssh/id_rsa (/home/etimberl/.ssh/id_rsa)
Identity added: /home/etimberl/.ssh/id_dsa (/home/etimberl/.ssh/id_dsa)
[etimberl@webserv:~/weatherdata]% ssh hallinta -t "/usr/bin/sqlite3 /Library/Application\ Support/Perceptive\ Automation/Indigo\ 7.2/logs/indigo_history.sqlite \"SELECT value from variable_history_875607185 LIMIT 1 OFFSET (SELECT COUNT(*) FROM variable_history_875607185)-1;\""
59.0
Connection to hallinta.mtrcycllvr.org closed.
[etimberl@webserv:~/weatherdata]%

Apparently it is that cold in my living room!

Thank you so much for the pointer! I think I'm good from here!! Oh, well, I THINK..

Not knowing much about the database schema, but looking at the output of, say 10 records, I'm having a hard time figuring out what triggers a history update to the database - I'm GUESSING that if the variable changes, it is updated with a current time stamp, and then X records are retained.. so ordering by the timestamp should always get me the current record might be the correct thing to do here as well.. ie; update my query a bit to ensure I've got the actual latest record for said variable..

I think I've got a handle on this now...

Sincerely,

-ET-

Or an alternative, I've switched the SQL logging in indigo to postgres and use 3rd party app to view the data and structure, as well as clean it up, and point my graphing/ dashboard GUI direct to that postgres dB.

The sqllogger dB is very simple

For variables
id, time, value
Only when updated.

For devices
id, time, state 1 value, stare2 value,...


Check out the sqllogger settings on updates
Can be only changes and for devices write only those states that changed.

No entries for triggers, actions,...

Karl



Sent from my iPhone using Tapatalk

These are all good options. Here's a couple more.

A Python script to export variable values to a CSV file would be very simple to write and you could save directly to the remote host (obviously as long as Indigo can see it). You could then have Indigo run the script on a schedule. This would result in the data being stored on a routine interval, rather than when the values change. If having the data exported say every 10 minutes would work for you, this might be a decent option. If you wanted to log all variable changes, you could use a trigger (trigger on variable value change), but that would require a separate trigger for each variable and would likely result in a lot of I/O. These files can get very big over time, so you could add a feature to the Python script to trim the CSV as a part of the save process (limit by size or date or whatever).

A second approach would be to use the Matplotlib plugin and its CSV engine device (you could skip the plotting aspects of the plugin). This would save the data to a CSV file, which could also be the remote host. This would save each variable to its own CSV file with a timestamp and--if you create a separate CSV Engine device for each variable--each could be saved on its own schedule, independent of the others. Again, not logging each variable value change but rather on a fixed schedule.

Not saying these are better options than those already suggested, just a couple more to chew on.

[jay (support)]

The script is easy:

Code: Select all

import json

# IDs of the variables whose value you want to export
VARS_TO_EXPORT = [
   1916392948,
   760419202,
]

# Loop through the list getting the variable values and add
# them to the dictionary. I'm making the varible name as the
# key, but you could just as easily make it the var.id
export_dict = {}
for id in VARS_TO_EXPORT:
   var = indigo.variables[id]
   export_dict[var.name] = var.value

# Convert the dictionary to JSON and write it to a file.
# If you can mount the remote machine then you
# can write to the network volume.
with open("/path/to/file.json", "w") as file:
   json.dump(export_dict, file)

How about:

[url]Indigo.server:8176/variables.json[/url]

Or

[url]Indigo.server:8176/variables.xml[/url]

Terry




Sent from my iPhone using Tapatalk

[jay (support)]

Good point, if the destination app can perform a digest authenticated URL call. That will return all variables, but you can get a single variable:

Code: Select all

FatBook:www jay$ curl -u username:password --digest http://127.0.0.1:8176/variables/isDaylight.json
{
    "displayInUI": true,
    "name": "isDaylight",
    "value": "true",
    "restParent": "variables",
    "readOnly": true,
    "isFalse": false,
    "id": 1815507596
}

I put it in a curl call so it's a bit more clear what's happening.

Thanks for all the responses.. I'm not smart enough to figure out json or xml files at the remote end, thus my "stupid simple" approach..

I have a new problem.

I migrated my indigo pro 7.2 instance to a newer Mac mini with enough horse power to ALSO run security spy without choking..

Since that migration, the variable history database is not being updated, though it APPEARS that all else within the indigo app is working fine..

I'm not clear on how to repair and/or diagnose the issue.

I followed the migration instructions posted in this forum that pretty much stated all you need is the app and the databases in /Library/Application Support/Perceptive Automation/Indigo 7.2/..

Code: Select all

[etimberl:/Library/Application Support/Perceptive Automation/Indigo 7.2/Logs] % ssh florenceha -t "/usr/bin/sqlite3 /Library/Application\ Support/Perceptive\ Automation/Indigo\ 7.2/logs/indigo_history.sqlite \"SELECT * from variable_history_875607185 ORDER BY ts ;\""| tail -10
94008|2019-01-25 21:21:00|67.5
94009|2019-01-25 21:23:00|67.3
94010|2019-01-25 21:26:00|67.1
94011|2019-01-25 21:30:00|66.9
94012|2019-01-25 21:35:01|66.7
94013|2019-01-25 21:58:48|66.7
94014|2019-01-25 22:00:00|66.2
94015|2019-01-25 22:04:00|66.0
94016|2019-01-25 22:15:00|65.8
94017|2019-01-25 22:23:00|65.7
Connection to florenceha closed.
[etimberl:/Library/Application Support/Perceptive Automation/Indigo 7.2/Logs] %

I am entirely confused.. No errors in the log that I can see... That 1-25 "last update" is right when I made the hardware swap..

Anyone know why my variable history isn't being updated anymore?

Thanks so much for your help and suggestions!! (this is probably a bit of a miss-post..)

[jay (support)]

Edit the SQLLogger prefs and make sure that it's pointing to the right location for the database.

Ah, thanks, Jay!

I had to do little hunting to figure out that the SQLlogger function is a plugin, but the problem was obvious once I found it!!

PLLUGINS -> SQLlogger -> ENABLE!! (as opposed to disabled, which is was for some reason..)

Thanks so much for your time!

Code: Select all

[etimberl:/Library/Application Support/Perceptive Automation/Indigo 7.2/Logs] % ssh florenceha -t "/usr/bin/sqlite3 /Library/Application\ Support/Perceptive\ Automation/Indigo\ 7.2/logs/indigo_history.sqlite \"SELECT * from variable_history_875607185 ORDER BY ts ;\""| tail -10
Host key fingerprint is SHA256:IUjmRgP0gW1HdLJibNCqk7urkqOi7xnBNwkGnOqFWBI
+---[ECDSA 256]---+
|Eo==*o+ .        |
|.+oO++.+         |
|oo+oX.o .        |
|o+.* o . .       |
|.o+ +   S        |
|+. o .           |
| +.              |
|*  o             |
|%*+              |
+----[SHA256]-----+
94010|2019-01-25 21:26:00|67.1
94011|2019-01-25 21:30:00|66.9
94012|2019-01-25 21:35:01|66.7
94013|2019-01-25 21:58:48|66.7
94014|2019-01-25 22:00:00|66.2
94015|2019-01-25 22:04:00|66.0
94016|2019-01-25 22:15:00|65.8
94017|2019-01-25 22:23:00|65.7
94018|2019-02-05 02:40:12|47.8
94019|2019-02-05 02:40:38|47.8
Connection to florenceha closed.
[etimberl:/Library/Application Support/Perceptive Automation/Indigo 7.2/Logs] %

Next time I'm bored and feeling adventurous I'll see if I can figure how to leverage the built in JSON and XML bundles that have the data I want in them..

Thanks again!

-ET-