Hello Marty,
We use SQL data sources and then Meta Sources to scale the raw data into meaningful data. To avoid Mango creating data points while communication was lost from the data logger we have Mango set to read on a timestamp change only.

On the SQL data source we have a data point just for the timestamp, the other data points use a time override column name and on the meta source we use the timestamp data point for the context update.

Of course this will only work if your logging device creates its own timestamp or your SQL database records the date and time of the data insertion into the table.

I hope this helps

Cheers

Brian

Hello Terry,
Thank you for you response. This will let me continue with the upgrade. I am sure we can work with the unique email address point. It will just need to make sure I get a separate contact address for each user and explain to the customer it is for password recovery.

Thank you for your help and I hope you all had a happy Thanksgiving.

Cheers

Brian

Hello Phil,
I just wanted to thank you. The point links worked great once I understood how to use them and the scripting. This is how I did it in case anyone else wants to know:

I have an SQL data source that needs to be read when the data comes in, not on a set interval and I need to use the data's own timestamp.

My data source uses this select statement:

SELECT * FROM staging_all where logger_id=1 and flag=0 order by date asc limit 1;

To trigger the reading of each row I set up a point link as follows:

I have a source data point that is set a a virtual sinusoidal that is going between -1 and +1 every 10 seconds.

I have a target data point set up as a modify table only point.

The point link uses the following one line script:

  if (source.value >0) return '(1)'; return '(0)';

I needed this line because the target data point was an alphanumeric data point.

My target point has this script:

  UPDATE staging_all SET flag=? WHERE logger_id=1 AND flag=0 ORDER BY date ASC LIMIT 1;

Now the data point that modifies the staging table gets a trigger (1) several times a minute and that causes my data rows to be read one at a time until they are all marked as (1).

Thank you for pointing me in the right direction.

Hello Shubham,
We run a system with many users and limit what the users can access on the menu. We also have to isolate the custom pages from one user to the other. Matt is correct, you will have to edit the Menu permissions and the Page permissions to limit what each user is able to see and do.

I will show you what we have set up. It might not be the best way to do it but it works for us. Any advice on a better way to do this would be greatly appreciated. :)

1. Create the user and give the user a unique role.
   

2. In the settings / permissions remove User from all items you do not want the average user to access. Everything else will still be accessible by the superadmin as default.
   

3. Use the Edit Menu function to remove set the appropriate permission for your user. Some items such as the Help menu we have left as User:
   

4. other items we remove the User account from the permissions such as the API Docs menu item:
   

5. Those items that are user specific such as the custom pages, we set the unique user role:
   

I hope this helps you get it set up the way you wish.

Remember to click Save in all the right places.

Cheers

Brian

Hello cwangv,
If you are just browsing the list from the drop down of the Point Properties - Unit values then you will not see some of the units available. I found that if you type the ones you need such as mm, cm kW and kWh and click save, the point will save with proper validation. %RH is not available for validation, but % is available. The drop down list is not the full list of units available.

To use non standard units, you would need to go to the Text Renderer Properties and untick "use unit symbol as suffix". Then you can enter in your desired text in the Suffix value.

Cheers
Brian

Hello,
Since our recent Excel Reports Module upgrades from 3.6.2 to 3.7.0, all of our scheduled Excel reports are not running automatically. They were working fine before the upgrades. I can run the reports manually but the CRON schedule is not working.

I have recreated the schedule from scratch on our test report but is is not running either. There is also nothing showing up in the ma.log file.

I have noticed that when I click the gear icon next to the report, I get the choices to copy, delete, run or schedule. When selecting schedule the only option there is to set the time period. I have always used the actual report template set up to set the schedule so am not sure if the CRON pattern choice was available from the schedule option of the gear icon.

I am wondering if the schedule option from the gear icon does not allow setting the CRON pattern for the schedule, has the scheduling been dropped by accident?

I am eagerly looking forward to your response.

Cheers,

Brian

Hello Terry,
Thank you very much, I just got a chance to update the modules and test the scheduling.
Working great now.

Cheers

Brian

@MattFox Hello Matt,
Thank you very much for spelling that out to me.
Here's the rub though.

I am trying to use a timestamp to update an integer. So on validation I got this message:

Setting point 0100raw - 0100-zflag to 2024-03-14 09:20:00.0 @ 12 Apr 2024 07:54:01 UTC

What I really need is the parameter to set a 1 when the timestamp updates.
In the old point links I had a simple line,

Return '(1)';

So I tried something actually quite simple once you know. I left the timestamp as the update context and used this script:

target.set('(1)')

I assumed that the example Phil put on the forum all those years ago required me to assign the target and source point in the script code.

Sometime I can't see the forest for the trees.

So HUGE THANKS Matt. I can now get my system running and I now know how to take it into Mango 4 hopefully.

Cheers
Brian

@terrypacker I know this thread is a year old but we are now trying to set up a display to capture this event and I have been searching the forum when I saw your entry. I am not sure if you are still looking for cases but I have one.

We have set up a monitor and display that is checking rainfall levels, reservoir levels, and discharge flow rates into rivers and drainage systems for a flood prevention service. Obviously this is a hot topic these days and companies are spending a lot of time to gather, monitor and log this information. Our system is just the online portion of this system and the company will use the information as a kiosk style display so that the main office can see what is happening across the various points at a glance.

We have set up 2 sets of high alarms, the first an amber warning and the second a red critical warning for the levels and flow rates. We have also set up the Rate Of Change event detector for the flow rates that will alert on an increasing rate of change and a decreasing rate of change so the users will alerted to the flood potentials.

On test the system works well and thankfully since the system went live we have not had an event yet. I like the Rate of Change Event Detector for this type of application as it can give an early indication of the flood potential before the actual reservoir levels have reached the warning levels.

Cheers
Brian

@jcaballeroa
I am sorry I can't help with your error, but I am very interested to know how you get on with your tests with mango 5. We are also considering the move from 3 to 5 ourselves and I haven't had the time to test 5 yet.

Cheers
Brian

@MattFox Hello Matt,
Thank you very much for spelling that out to me.
Here's the rub though.

I am trying to use a timestamp to update an integer. So on validation I got this message:

Setting point 0100raw - 0100-zflag to 2024-03-14 09:20:00.0 @ 12 Apr 2024 07:54:01 UTC

What I really need is the parameter to set a 1 when the timestamp updates.
In the old point links I had a simple line,

Return '(1)';

So I tried something actually quite simple once you know. I left the timestamp as the update context and used this script:

target.set('(1)')

I assumed that the example Phil put on the forum all those years ago required me to assign the target and source point in the script code.

Sometime I can't see the forest for the trees.

So HUGE THANKS Matt. I can now get my system running and I now know how to take it into Mango 4 hopefully.

Cheers
Brian

Hello all,
In Mango 3.7.12, Can anyone tell me how to write a script in the Scripting Data Source that would create a parameter that can then be picked up by a SQL Data Source script to use for setting a value in the MySQL database table?

This to replace the Point Links feature where the source and target were from drop down lists.

Cheers

Brian

@MattFox Hello Matt,
Sorry for my ignorance, but I ran your code in my SQL console and I got an error.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'var dbProxy,JDBC,sqlClassInstance; var DatabaseProxy=com.serotonin.m2m2.db...' at line 1

I am not sure if I was supposed to change any of the the values.

Cheers
Brian

@MattFox Hello Matt,
Yes they are on the same server just different databases. I hadn't thought about copying the data to a table in the Mango MySQL Db.

Would that solve anything?

Cheers
Brian

@MattFox Instead of starting a new thread I will explain what we are doing here. I don't think too many people are searching for Mango 3 solutions anymore.

Our dataloggers have the ability to store its data locally during a mobile data connection loss. Once the data connection is restored the logger could potentially upload 100K rows of data to the MySQL database in a very short period of time. The other eccentricity of the loggers is that the digital channels only send data on a state change. we do not get an the current digital state value on every record that is sent.

So we need to be able to read the data from earliest to latest, and if there is no new data, mango needs to be able to go back to the last non null digital point found in the already read rows of data.

So to make sure Mango reads the data into the NoSQL database sequentially in timestamp order, we use the flag method to mark which rows have been read and identify the next row to be read.

We use a SQL data source to bring the data into mango, then use a Meta data source to scale the data for display on the dashboards, trigger alarms etc, as you know.

In the SQL data source we have a series of coalesce select statements as shown:

Select date, flag,
coalesce(
         	(SELECT an1 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT an1 FROM staging_all where grd_id = 0100 and flag = 1 and an1 IS NOT NULL order by date desc limit 1)
                     ) as an1,
coalesce(
         	(SELECT an2 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT an2 FROM staging_all where grd_id = 0100 and flag = 1 and an2 IS NOT NULL order by date desc limit 1)
                     ) as an2
coalesce(
         	(SELECT i1 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT i1 FROM staging_all where grd_id = 0100 and flag = 1 and i1 IS NOT NULL order by date desc limit 1)
                     ) as i1,
coalesce(
         	(SELECT i2 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT i2 FROM staging_all where grd_id = 0100 and flag = 1 and i2 IS NOT NULL order by date desc limit 1)
                     ) as i2
from staging_all 
where grd_id = 0100
and flag = 0
order by date asc 
limit 1;

This works well for us and as you can see gives us the choice of picking the earliest unread or the latest read row of data.

I then have a data point in the SQL data source called zflag that had this statement:

UPDATE GRDXF.staging_all SET flag= ?  WHERE grd_id=0100 AND flag=0 ORDER BY date ASC LIMIT 1;

The question mark is the integer parameter assigned via the point link which just says "Return 1;"

I tried putting that line in the SQL data source code as below without the parameter ?:

Select date, flag,
coalesce(
         	(SELECT an1 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT an1 FROM staging_all where grd_id = 0100 and flag = 1 and an1 IS NOT NULL order by date desc limit 1)
                     ) as an1,
coalesce(
         	(SELECT an2 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT an2 FROM staging_all where grd_id = 0100 and flag = 1 and an2 IS NOT NULL order by date desc limit 1)
                     ) as an2,
coalesce(
         	(SELECT i1 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT i1 FROM staging_all where grd_id = 0100 and flag = 1 and i1 IS NOT NULL order by date desc limit 1)
                     ) as i1,
coalesce(
         	(SELECT i2 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT i2 FROM staging_all where grd_id = 0100 and flag = 1 and i2 IS NOT NULL order by date desc limit 1)
                     ) as i2

from staging_all 
where grd_id = 0100
and flag = 0
order by date asc 
limit 1;

UPDATE GRDXF.staging_all SET flag= 1  WHERE grd_id=0100 AND flag=0 ORDER BY date ASC LIMIT 1;

This works manually in the MySQL workbench but this failed in Mango. I think the reason it fails in Mango is that the SQL data source is a read only unless the data point has the Modify Table attribute ticked. So I can't add the update statement to the data source SQL code.

I did some digging in the forum from the time when IAS was going to remove the point links after version 3.7.7 and the only comment I could find was that point links could be accomplished in the Scripting data source but didn't give much more detail on how. I apologise if the "How" was in the early form but i didn't find it.

So I started to look there and first of all I can't see how to create a connection to the MySQL database as in the SQL data source.

Then as I am writing this I started to realise that I think I am supposed to use the Scripting data source to recreate the Source Point and the Target point that was chosen as drop downs in the Point Link. And if that IS the case, that is where I am stuck.
I am researching about using Ecmascript to use target and source points to provide a parameterised integer to my SQL data point that can modify the table.

Once I have that figured out I think our system will be back to normal.

Thank you for your time in reading this lengthy post. :)

@MattFox Thank you for your offer of help Matt.

I think I will stick with version 3.7.12 for now an figure out how to get the system running without the point links. After the recent crash it has highlighted the need to stay more relevant in regards to database versions and Linux versions which also mean we should think about moving towards Mango 5 with a stop over in Mango 4 just to be sure everything is still working. And in no future that we see for the Mango service will point links return. A shame because I found them an easy and simple way to achieve what we needed.

Hello All,
The restore process was almost a success.

The process went ok. I managed to get the NoSQL back up restored via the legacy pages, the MySQL restored via the new system settings pages and the json configuration imported via the new configuration import/export page.

The automatic back ups did not back up the filestore but I happened to have a recent copy of that folder so I was able to restore all of the images I created for my dashboards as well as the excel report templates in use,

The only problem I encountered and it is a major problem for us, is that we had to install 3.7.12 as it is the only Mango 3 option available and that version does not have the Point Links feature.

All of our loggers used the Point Link Feature to change a flag data cell on each row of data in the MySQL table after the row was read into Mango.

I am now left with trying to figure out how to get the MySQL data row read into Mango using the Scripting Data Source. And I am struggling but I will put that into the forum under a new thread with more details.

Cheers
Brian

@lgorence Hello Liv, and anyone else who comes across this thread.

I discovered that the AlmaLinux 9 was not allowing the Mango 3 install to complete due to permissions. Even after making the mango user the owner of the /opt/mango folder
the install still failed at the last module and kept trying to restart itself over and over again.

I recursively set the execute permission on the mango folder for the mango user and the install completed and we were able to get to the log in screen and pick up the correct license file.

That seems to have been the crux of the whole install problem.

Cheers
Brian