Find number of data points in system

JoelHaggar

you can use ```
select count(*) from pointValues


You can use this in the SQL Console in Mango.

Joel.

jeremyh

Thanks Joel, that's what I was hoping for.

It returns a 0 however, even using 'POINTVALUES' all in caps as the table name..

JoelHaggar

I'm so sorry, I gave you the wrong table to count

this is the correct one:

select count(*) from datapoints

Your using a H2 database which I don't think cares about the case. The reason the pointValues table returned 0 is because you have the NoSQL database installed so all the point values are store there.

Joel.

jeremyh

Oh, I should have been able to figure out that myself!

Anyway, that works, thanks.

Total point count: 1381 :)

JoelHaggar

Nice, on a MangoES?

jeremyh

Yes, MangoES.

There are 16 data sources.

One of those is polling every second, but the points are logged 'on changes only' and they rarely change (large motorised breakers).

Another data source is polling every 30s, and logging every value (from instrumentation in the electrical boards). There are about 100 points on this data source.

Another logs about 200 points (from four gensets) at 30s interval, but only while the equipment is running.

There are another few hundred points being logged at 2-3 minute intervals continuously (battery inverters) and at five minutes during daylight hours (solar PV inverters).

The database size, after about 40 days, is 250MB, so about 6MB/day, so recording for years will be possible.

The site has two (redundant) data links. A few dozen points are is pushed in realtime to our central Mango server, using the Persistent TCP publisher, where it is shown on DGLux dashboards.

jeremyh

Another question (this one a bit harder)-

An engineer here has asked me whether there is a way to export a complete listing of the points.
Is there a SQL statemement that will list something like:

"Data source, Data source polling interval, Point name, Unit"

With a new line/row for each data point? Then it could be pasted into Excel for the engineers to refer to.

Edit: I have just copied/pasted the data into Excel from the 'data points' tab on the data sources page. This has given me what I wanted.

JoelHaggar

That one is not so easy because some of the data you want is serialized in the database table. But there is a pretty cool tool build into H2 that makes this easy.

First make sure you are using a H2 database by looking on the top of the systems setting page.

Then in the SQL concol you can run: ```
call CSVWRITE ( 'h2export.csv', 'SELECT * FROM DATAPOINTS' )


Here is more info: http://www.h2database.com/html/functions.html#csvwrite

This will create a csv file in your root Mango folder.

If you needed more details the only practical way would be to get it from the JSON export or the API which is also JSON so you'd need to create some scripts that would turn it into a csv with the info you want.

Joel.

jeremyh

@JoelHaggar said:

Then in the SQL concol you can run: ```
call CSVWRITE ( 'h2export.csv', 'SELECT * FROM DATAPOINTS' )

Thanks Joel
This worked.

Here is a sample of the output columns for anyone who is curious.

The 'data' column contains a string of perhaps 256 or 512 characters length. It makes the file very big, a 7MB csv.
Deleting that column reduces the file size to 120KB.

JoelHaggar

good point, so you should be able to do ```
call CSVWRITE ( 'h2export.csv', 'SELECT ID, XID, NAME FROM DATAPOINTS' )


Basically rather then the * which stands for everything just put in the columns you want.  I haven't tested this but I'm also guessing you could do a more advanced join or other statement to export what you want.

Joel.