SQL data source to query MangoES H2

[jeremyh]

I thought I would create a SQL datasource on a MangoES so that I could keep tabs on the number of points in the system.

I configured my SQL data source like this and when I run the statement test I get the correct value of 535:

But when I go to configure an actual point for that column it never updates:

Any idea what I'm doing wrong?

[JoelHaggar]

I was also able to duplicate this and not sure why it didn't work. I was able to get it to work as a row based query. Check the box for row based query and then use this statement"

select 'count' as count, count(*) from datapoints

You'll probably need to delete your data point and then create a new one with count as the row identifier.

[jeremyh]

Thanks Joel, that works.

Here is the JSON if anyone would like it (sorry, can't figure out how to make all this code):

{
"dataSources":[
{
"xid":"DS_652957",
"name":"SQL",
"enabled":true,
"type":"SQL",
"alarmLevels":{
"POLL_ABORTED":"NONE",
"STATEMENT_EXCEPTION":"NONE",
"DATA_SOURCE_EXCEPTION":"NONE"
},
"purgeType":"YEARS",
"updatePeriodType":"MINUTES",
"connectionUrl":"jdbc:h2:/opt/mango/databases/mah2",
"driverClassname":"org.h2.Driver",
"password":"password-here",
"rowBasedQuery":true,
"selectStatement":"select 'count' as count, count(*) from datapoints",
"updatePeriods":5,
"username":"mango",
"purgeOverride":false,
"purgePeriod":1
}
],
"dataPoints":[
{
"xid":"DP_262803",
"name":"Point Count",
"enabled":true,
"loggingType":"ON_CHANGE",
"intervalLoggingPeriodType":"MINUTES",
"intervalLoggingType":"INSTANT",
"purgeType":"YEARS",
"pointLocator":{
"dataType":"NUMERIC",
"dateParameterFormat":"yyyy-MM-dd'T'HH:mm:ss",
"fieldName":"Count",
"parameters":[
],
"tableModifier":false,
"timeOverrideName":"",
"updateStatement":""
},
"eventDetectors":[
],
"plotType":"STEP",
"unit":"",
"chartColour":"",
"chartRenderer":null,
"dataSourceXid":"DS_652957",
"defaultCacheSize":1,
"deviceName":"MangoES",
"discardExtremeValues":false,
"discardHighLimit":0.0,
"discardLowLimit":0.0,
"intervalLoggingPeriod":15,
"intervalLoggingSampleWindowSize":0,
"overrideIntervalLoggingSamples":false,
"purgeOverride":false,
"purgePeriod":1,
"textRenderer":{
"type":"ANALOG",
"useUnitAsSuffix":false,
"unit":"",
"renderedUnit":"",
"suffix":" points",
"format":"####"
},
"tolerance":0.0
}
]
}

[jeremyh]

Hey Joel,

For bonus points - what SQL statement could I use to return values for number of data sources, number of events, number of event handlers, number of users as well as number of data points?

Then in that data source I'd like to have data points to log each of the above.

This way I can set up easy alerts in case anyone adds or deletes a data source, point, user, etc.

[terrypacker]

I was just playing around with this and found that if you want to use the query 'select count() from datapoints' you need to use the data source as a 'non row based query' and then set the column name to 'Count()'. You can see this in the test utility if you look at the Column name, its shown as COUNT(*) when you test the query.

You could also use this query if you want to have a specific column name:

select count(*) as pointcount from datapoints;

As for your last question you can put this all together like this:

SELECT  (
    SELECT COUNT(*)
    FROM   datapoints
    ) AS pointCount,
    (
    SELECT COUNT(*)
    FROM   datasources
    ) AS datasourceCount,
    (
    SELECT COUNT(*)
    FROM   events
    ) AS eventCount,
    (
    SELECT COUNT(*)
    FROM   eventHandlers
    ) AS eventHandlerCount,
    (
    SELECT COUNT(*)
    FROM   users
    ) AS userCount
FROM    dual

The thing to notice here is the keyword 'dual' at the end, this is required as you cannot perform a select without a from clause and the dual table that all accounts can access for common needs and it has one record.

Just make sure you change the data source back to not use the row based query.

FYI To format as code just make sure your lines are all indented by a tab or 4 spaces and has whitespace lines above and below.