Incorrect 0 point value when importing point definitions

[PCM]

In M2M version 1.12.3 I could export point definitions, edit them, and then import them. This is useful for duplicating or changing multiple existing points. However, in MA version 2.0.3, whenever I import point definitions, erroneous 0 point values are stored in the datapoint value tables. This is of course a big problem for data integrity. Perhaps the RELEASE-NOTES file indicates when this problem crept in:

Version 1.3.1
Enhancements:

• Don't disable data sources when importing data points
  Can you either roll back this feature, or at least make it optional on the import page so zeroes are not inserted into the point values tables?
  How can I search and purge points that meet a certain value? I purchased the SQL module.

Thanks

[mlohbihler]

Can you provide more detailed steps to reproduce this problem? Is it only with the SQL data source? There's no obvious reason why that should be happening.

[PCM]

The invalid 0 point values are not coming from an SQL data source (I have no SQL data sources; mostly modbus data sources). The zero values are occurring in metadata points, and based on the graphs they have occurred during datapoint definition imports.

[mlohbihler]

When i import a new enabled point into a running meta data source, i do not have any 0 values created. The point happily sits there with no history values.

[PCM]

You write: > When i import a new enabled point into a running meta data source
The zero insertions occur when importing an existing datapoint definition into a running metadata source.

Aside from troubleshooting this problem, how do you recommend I purge point values that meet certain criteria?
The datapoint details purge section purges all points, and the "Discard extreme values" feature only works for new values, not for values already in the database. e.g. What SQL query can I use to delete points where a < x < b?

Can you add a routine to the datapoint "purge now" section that allows us to delete stored point values that fall between certain specified numbers within a specified date range, or older or newer than a specified age?

Thanks.

[JoelHaggar]

PCM: By "datapoint definition" you mean "Point History" ? If this is the case then I don't think your prior messages were understood correctly.

Joel.

[PCM]

Before import, here is the point history. Note the 10 minute intervals.

History 	(as of 2013/01/14 19:10:25) 	Show most recent records Get data
Value 	Time 	Annotation
59 %V	19:01:13	
58 %V	18:51:13	
57 %V	18:41:13	
57 %V	18:31:13	
56 %V	18:21:13	
54 %V	18:11:13	
55 %V	18:01:13	
55 %V	17:51:13	
55 %V	17:41:13	
57 %V	17:31:13	

The data point definition is then exported from MA 2.0.3, pasted below, and re-imported into the same Mango installation without modification:

{
   "dataPoints":[
      {
         "loggingType":"INTERVAL",
         "intervalLoggingPeriodType":"MINUTES",
         "intervalLoggingType":"AVERAGE",
         "purgeType":"YEARS",
         "pointLocator":{
            "dataType":"NUMERIC",
            "updateEvent":"MINUTES",
            "context":[
               {
                  "dataPointXid":"DP_VPC1",
                  "varName":"VPC1"
               }
            ],
            "executionDelaySeconds":0,
            "script":"return (((VPC1.value) - 1.75) \/ (2.25 -1.7)) * 100;",
            "settable":false,
            "updateCronPattern":""
         },
         "eventDetectors":[
         ],
         "engineeringUnits":"Percent",
         "plotType":"LINE",
         "chartColour":"",
         "chartRenderer":{
            "type":"IMAGE",
            "timePeriodType":"WEEKS",
            "numberOfPeriods":1
         },
         "dataSourceXid":"DS_Battery",
         "defaultCacheSize":1,
         "deviceName":"Battery",
         "discardExtremeValues":false,
         "discardHighLimit":1.7976931348623158E30,
         "discardLowLimit":-1.7976931348623158E30,
         "enabled":true,
         "intervalLoggingPeriod":10,
         "name":"Battery % Voltage 1.75V-2.25V (HPC1)",
         "purgeOverride":true,
         "purgePeriod":1,
         "textRenderer":{
            "type":"ANALOG",
            "format":"##0",
            "suffix":" %V"
         },
         "tolerance":1.0,
         "xid":"CDP_HPC1Batt%volt"
      }
   ]
}

Import
Paste import data into the field below and click
Data point 'CDP_HPC1Batt%volt': saved
Import complete

Note the history after import, and the irregular time stamp:

History (as of 2013/01/14 19:14:27) Show most recent records Get data
Value Time Annotation
0 %V 19:13:49
63 %V 19:11:13
59 %V 19:01:13
58 %V 18:51:13
57 %V 18:41:13
57 %V 18:31:13
56 %V 18:21:13
54 %V 18:11:13
55 %V 18:01:13
55 %V 17:51:13

This is occurring with at least the meta-data points. It impacts a lot of points. Since it has occurred many times with many different datapoints, in addition to fixing the problem, I need to know how to delete the data from the point history.

Thanks.

[mlohbihler]

The problem is more likely to be due to the interval logging than being a meta point. Can you confirm?

You can use the sql console to delete point values. If you're unfamiliar with SQL i can provide some hints.

[PCM]

The problem is more likely to be due to the interval logging than being a meta point. Can you confirm?
That datapoint is set to log the average on a 10 minute interval. However, it was set this way with M2M 1.12.3, and I don't recall this being a problem with that version. I can change to log all changes and test, but that would gather data very quickly, and I don't want to add that to the dataset, especially since they'll be hard to delete with my poor SQL skills.

You can use the sql console to delete point values. If you're unfamiliar with SQL i can provide some hints.
The scariest part of using SQL on a Mango database is not knowing whether making changes in one table must be accompanied by making changes to another table to avoid breaking things. I have little SQL experience, so it would help immensely if you could provide a specific example that deletes points that fall within a specific min-max value range, (1) for the entire dataset, (2) over a specified date range, and (3) over a specified age range. Getting that syntax just right in a Mango context would take me a long time.

It would also be helpful if you could give me an example that shows how to modify all points in a dataset (e.g. x=x+80, or y=y*10). This has come up when, for instance, I have modified a modbus additive or a modbus multiplier. I need to be able to make the datapoint history consistent with the change. I have scoured the forums looking for mango-specific SQL syntax in the past, but not found anything. It would be so handy if there were a mango wiki, or if SQL commands could be saved and applied to any point, like the report page. In the latter case, you could have some pre-saved example SQL commands on the SQL page that serve as examples of likely operations.

[mlohbihler]

A fix for the 0-value thing has been committed for version 2.0.5.

[PCM]

Thanks! Are you referring to preventing the zero insertion during import, or to adding an option to the datapoint's "purge now" box to purge points meeting specified value and date criteria?

The latter would be nice, especially since the "Discard extreme values" option is often not enabled until after invalid data has been stored in the point history. Additionally, enabling the discard function will prevent data problems from being detected and solved.

Either way, I need to find out what SQL queries to use to purge or to modify points that fall meet value and date/age criteria.

Thanks,
PM