Kind of a nerd thing but..... ST Data Dump?


(Monte Montemayor) #1

Does anyone know of an app that dumps ST Event or Log data to a file like XLS? If not an app, is there a way to export the event data? Ideally, I’d like to do it in time periods (like every day at a certain time for the past 24 hours).


(Tony Fleisher) #2

I don’t think there is an easy way to dump historical data, but you could use webCoRE to log event data via fifty.

A discussion was started a few days ago on the webCoRE forum about this:


#3

(Monte Montemayor) #4

Excellent - thank you both, this seems to be exactly what I’m trying to do. Time to explore a little… :star_struck:


(Kevin) #5

GoogleSheetsLogging is a lot easier to work with if you’re trying to use it for reporting, but if you’re looking for the raw data of every event from a device you’re better off using Simple Event Logger.


(Monte Montemayor) #6

Thank you Kevin -I used your app and it’s working really well. Thank you for creating it.


#7

(Kevin) #8

I’m not sure if you’re aware of this, but Google has an archive feature and you can use that to download a zip file containing all your sheets.

Are you familiar with SQL Databases?


(Monte Montemayor) #9

Yes familiar with SQL commands, DBs and Schemas. So I am wondering about something… does your app only log “up to” 200 events per device and then it stops or will it replace what’s there or simply append it to make one giant Google Sheet? I am trying to analyze 1 months worth of data from all of my devices.

PS The analytics software I am using, Tableau, can handle millions of rows of data so I am not worried about a large size. If it gets too big, I can throw it into Snowflake or Redshift or something.


#10

I use this one. Similar to the others but another option for you.


Note though, you can’t make any changes to the sheet where the data is recorded. I added another sheet with some formulas and a pivot table and it totally screwed up the logging. So, you’d have to use the sheet for the log as a data source only and not modify it except with whatever app you are logging with.


(Monte Montemayor) #11

Good to know. I am using the Sheet as a database and using Tableau for visualizations. So far so good. In my testing, I just hit F5 (in Tableau) and the data refreshes my graphs.


#12

That should be okay…you’d have to test it. I thought i was fine cause the smartapp didn’t report a problem. But then two days later i go into the sheet and there is no new data in it. So, the SmartApp might report it was successful but the data didn’t go into the sheet.


(Kevin) #13

Before the SmartApp starts retrieving events it stores the time and it includes that time in the information it sends to Google and if Google successfully logs the data it sends that time back to the SmartApp and it gets stored as the last successful log time.

The SmartApp uses that last successful log time to determine how far back in the event history it should look to get the next batch of events, but if it finds more than that “up to” limit then it only retrieves the most recent.

The limit and the “max catch up” setting ensure that if SmartThings is down for a while and the SmartApp can’t send the events to Google it won’t try to send 1000s of events for each device when SmartThings comes back up because that will cause it to time out.

That “up to” setting can also be used to fix time out errors or excessive events from noisy devices.

Long story short, if you have the logging interval set to 15 minutes or less and SmartThings, Google or your Internet doesn’t go down for a long period of time, that limit shouldn’t effect your data.

One massive sheet most likely won’t work if you’re logging a lot of events because the more rows a sheet has, the longer the logging process takes and you’ll start getting timeout errors long before the sheet is full.

I originally wrote the Simple Event Logger because I wanted to be able to analyze the data over long periods of time, but I found that it wasn’t possible with Google Sheets because the data had to be split into multiple sheets.

I ended up writing a windows application that allows you to select all the new excel files you download from Google and it imports them all into a SQL Database.

I have the Simple Event Logger set to archive the data after 5,000 records and run every 5 minutes and I download and import the files every few weeks.

I have all my events going back to December of 2016 so the table has over 2 million records.

If a user knows SQL and can figure out how to install SQL Express they can PM me their email address and I’ll send them the application and empty database, but I don’t plan on posting it to GitHub because I don’t have time to support it.


(Arne Helseth) #14

Look into Splunk. You can easily log ST data using a http listener and then create charts, graphs or other statistics to your hearts desire.


(Kevin) #15

Is Splunk free?


(Monte Montemayor) #16

I think this may work for me. Do so set this up in the app? Also, I’ll PM you for the app. :slight_smile: This stuff is super exciting to me :slight_smile:


(Kevin) #17

Those are both settings in the Simple Event Logger SmartApp.


(Arne Helseth) #18

As long as you run a local Splunk server it’s free. If you want to run against Splunk Cloud there is obviously a cost.

But if you already have a machine running 24/7,and who doesn’t, it’s a great solution. Not just for logging, but it also let’s you create graphs and whatnot out of the data without much fuss. I’ve got realtime access to energy consumption right now as well as kWh, which doors were used when, what time my Ring doorbells saw motion or actual presses etc.