Messages posted by: craig

ah yep, that is mo betta

mango can store data in mysql, derby or mssql database. derby can be setup as network instance so other programs can query.
while mango does use blobs to store some objects, you can cut and paste the code for turning the blob back into an object pretty easily.

if you want to write a JSP which displays some data for easy parsing look up the custom JSP function - I haven't done this so I don't know the specifics.

the data publisher surely must be adaptable to your needs?

wuciwug wrote:modbus slave wouldn't help.

incoming http would work and I can write the jsp, but what do I query, I thought mango had a propitiatory data base?

I am also glad that someone has picked up the torch, and indeed I was looking at scadaBR as an upgrade path from mango.

we must be looking at different logos: http://www.scadabr.com.br/
From browsing their site I expected more credit assigned where credit is due, even if the copyright notices in the code are left intact

apl wrote:Search for ScadaBr

Arne

it is unfortunate the scadaBR website gives no credit to matt or serotonin. "The ScadaBR was born in MCA Systems in Florianópolis / SC"

if you want the data to be queried, you can do this with a custom jsp and then make http requests.
look at data publisher for a push method which probably uses json.
you could also write the data to a modbus slave.
etc

You have to set up a scheduled report which send the data via email, then this script checks the e-mail and saves the attached CSV file. It has been working well for me.

#!/usr/bin/perl -w
#
# v1.0 February 2009-August 14th 2009.
#
# The purpose of this script is to save attachments from emails to an FTP server.
#
# The targeted attachments are plain text comma separated value (CSV) files. They
# are generated by  (mango). The subject of the message must match
# a format specified in the script. The messages are removed from the POP3 server.
#
# The script removes some columns and does some quoting and escaping on the data
# to make more robust CSV files (ie can handle a , in the data as well as ")
#
# The script reformats the dates as per the requested  format.
#
# The CSV files are saved locally in localDataDir as well as FTP'd to 2 servers.
# An error email is generated if there aren't the correct number and intervals
# of data. 15 minute data is expected.
################################################################################

use strict;
use Net::FTP;
use Mail::POP3Client;
use MIME::Parser;
use Time::Local;
use MIME::Lite;
use Text::CSV_XS;

# define login credentials for POP3 server to retrieve email from
my $pop3Username    = '';
my $pop3Password    = "";
my $pop3Server      = "";

# first FTP server to send CSV files to
my $ftpServer = "";
my $ftpPassword = "";
my $ftpUsername = "";
my $ftpDir       = "Data";

# second FTP server to send files to
my $ftpServer2   = "";
my $ftpPassword2 = "";
my $ftpUsername2 = "";
my $ftpDir2       = "";

# a writeable directory in which to store temporary files
my $temp_dir = "/usr/home/foo";

# a writeable directory in which to store a copy of the data that is FTPd
my $localDataDir = "/usr/home/foo";

################################################################################
################################################################################
################################################################################

my @csvFile;
my $fh;
my @tempFiles;
my @outFiles;
my @wiers;
my $time = timelocal( localtime() );

my $error      = 0;
my $error_text = "";

print "Content-type: text/html\n\n";
&log("Running Email to FTP script\n");

my $pop = new Mail::POP3Client(
	USER     => $pop3Username,
	PASSWORD => $pop3Password,
	HOST     => $pop3Server
);

my $message = $pop->Message();
if ( !$pop->Alive() ) {
	$error = 1;
	&log("Error connecting to POP server $pop3Server\n");
	&exitLog( $message . "\n" );
}

my $emails = $pop->Count();

&log("Have $emails emails to process\n");
for ( my $i = 1 ; $i <= $emails ; $i++ ) {
	foreach ( my $header = $pop->Head($i) ) {

chomp($header);
		$header =~ /Subject:\s+(.*)/;
		my $subject = $1;
		chomp($subject);
		$subject =~ s/(\r|\n)//g;

if (   # $header =~ /From:(.*)scada/ &&
			 $header =~ /Subject: (.*) Published FTP Data/ )
		{
			my $wier = $1;    
			$wier =~ s/^\s+//g;
			$wier =~ s/\/s+$//g;
			chomp($wier);

my $tempFileName = $temp_dir . $time . "-" . $i;
			open( $fh, ">$tempFileName" )
				or &errorlog("Can't open temp file $tempFileName for saving pop3 mail message body: $!\n ");

$pop->RetrieveToFile( $fh, $i );
			close $fh;
			push( @tempFiles, $tempFileName );
			push( @wiers,     "$wier" );
			&log("Matched email $i subject $subject. Saved message for $wier\n");
			
			# delete message from server since we have saved it to temp file
			$pop->Delete($i);
		}
		else {
			&log( "leaving non matching message " . $subject . "\n" );
		}
	}
}
$pop->Close();

# ok now we have retrieved file which is a multipart mime mess.

foreach my $tempFile (@tempFiles) {
	my $wier = shift @wiers;
	if ( $wier !~ /\w+/ ) {
		$wier  = "defaultSource";
		$error = 1;
		&log("ERROR: unable to parse report name from subject. Using default\n"	);
	}
	my $mp = new MIME::Parser;
	$mp->tmp_to_core(1);
	$mp->output_to_core(1);
	my $fh2;
	open( $fh2, "$tempFile" ) 
		or &errorLog("Couldn't open saved email message $tempFile , $!\n");
	$mp->output_dir($temp_dir);
	my $entity = $mp->parse($fh2);

#$entity->dump_skeleton();

foreach my $part ( $entity->parts ) {
		my $head     = $part->head;
		my $type     = $head->mime_type;
		my $filename = $head->recommended_filename;

if ( $type eq "text/csv" ) {   
			if ( !$part->bodyhandle ) {
				&errorLog("ERROR: unable to get MIME bodyhandle for CSV content-part\n");
				next;
			}

if ( $filename =~ /Events\.csv$/ ) {
				&log("Skipping events file $filename\n");
				next;
			}
			@csvFile = $part->bodyhandle->as_lines;
			&log(   "processing file \"$filename\", has "
				  . ( $#csvFile + 1)
				  . " lines.\n" );

# now have an array of arrays(which are the lines in the csv file)

# want to leave some columns out, which have the heading "annotation"
			my @keepFields;
			if (defined($csvFile[0])) {
				# this is the first line in the csv file which is the heading.
				my @fields = split( ",", $csvFile[0] );
				my $fieldNo = 0;
				foreach my $field (@fields) {
					if ($field !~ /Annotation/) {
						#this is a column we want to keep
						push(@keepFields, $fieldNo);
						#&log("keeping column $fieldNo $field\n");
					}
					$fieldNo++;
				} 
			}

#my $csv = Text::CSV_XS->new ();          # create a new object
			my $csv = Text::CSV_XS->new ({allow_loose_quotes  => 1, escape_char => "\\"});          # create a new object

my @newCsvFile;
			my $lastMinute="";
			my $thisMinute="";
			my $lastInterval="";
			my $lineNo = 0;
			foreach my $line (@csvFile) {
				$line =~ s/(\r|\n)//g; 			# strip new lines
				my $status  = $csv->parse($line);       # parse a CSV string into fields
 				my @fields = $csv->fields();           # get the parsed fields

# files come with wrong dateformat. change it to as requested.
				# comes as "yyyy/MM/dd HH:mm:ss"
				# change to "yyyyMMdd HHmmss"
				# just parse out / and :

if (!defined($fields[0])) {
					&errorLog("undefined date field. csv parse status was $status. skipping rest of file.");
					&errorLog("data: parsed $fields[0] from $line\n");
					last; # skip the rest of this file.
					
				}
				my $newdate = $fields[0];
				
				if ($lineNo > 0 && $filename !~ /UserComments\.csv$/ ) {
					# on rows other than the header, make sure that this interval is 15 minutes ahead of the last one
					$newdate =~ /\d+\/\d+\/\d+\s+\d+:(\d+):\d+/;
					$thisMinute = $1;
					my $dateOK = 0;
					if ($lastMinute ne "" ) {
						# not the first time through the loop.
						if ($lastMinute eq "00" && $thisMinute eq "15") {
							$dateOK = 1;
						}
						if ($lastMinute eq "15" && $thisMinute eq "30") {
							$dateOK = 1;
						}
						if ($lastMinute eq "30" && $thisMinute eq "45") {
							$dateOK = 1;
						}
						if ($lastMinute eq "45" && $thisMinute eq "00") {
							$dateOK =1;
						}
						if ($dateOK != 1) {
							&missingInterval($lastInterval, $newdate, $lineNo);
						}
					}
					$lastMinute = "$thisMinute";
					$lastInterval = "$newdate";
				}

$newdate =~ s/\///g;
				$newdate =~ s/://g;
				$fields[0] = $newdate;
				
				my @columns;
				push(@columns, $fields[0]);
				for (my $x = 1; $x <= $#keepFields; $x++) {
					push(@columns, $fields[$keepFields[$x]]);
				}
 				$status  = $csv->combine (@columns);  # combine columns into a string
 				my $csvLine    = $csv->string ();           # get the combined string				
				push(@newCsvFile, $csvLine . "\n");
				$lineNo++;
			}

if ( defined($newCsvFile[1]) ) {
				my @fields        = split( ",", $newCsvFile[1] );
				my $firstFileDate = $fields[0];
				#&log("Have $firstFileDate as date from $newCsvFile[1]\n");
				# remove leading spaces just in case, then remove all numbers (the time) after a space
				$firstFileDate =~ s/\"//g;
				$firstFileDate =~ s/^\s+//g;
				$firstFileDate =~ s/ \d+//g;

&validate(@newCsvFile);

my $outFileName;
				if ( $filename =~ /UserComments\.csv$/ ) {
					#this is a user comments file
					$outFileName = $wier . "-UserComments-$firstFileDate.csv";
				}
				else {
		 			 # it isn't an events file as we skipped those, so it must be wier data
					$outFileName = $wier . "-" . $firstFileDate . ".csv";
					if ( $#newCsvFile != 96 ) { 
						&errorLog("$outFileName had $#newCsvFile rows instead of 96.\n");
					}
				}
				$outFileName = $localDataDir . $outFileName;
				my $ofh;
				open( $ofh, ">" . $outFileName )
				  or &exitLog("Couln't open $localDataDir/$outFileName for writing: $!\n" );
				print $ofh @newCsvFile;
				close $ofh;
				push( @outFiles, $outFileName );
				&log("wrote csv file $outFileName successfully.\n");
			}
			else {
				if ( $filename !~ /UserComments\.csv$/ ) {
					&errorLog("skipping csv file with less than 2 lines ($#newCsvFile): $filename\n");
				}
			} # end if more than 1 lines in CSV file

} # end if type was csv

} # end for mime part

unlink($tempFile);
} # next tempfile (email message)

if (@outFiles > 0) {
if ($error == 0) {
# ok now ftp the files away.
my $ftp = Net::FTP->new( $ftpServer, Debug => 0 )
  or &exitLog("Cannot connect to $ftpServer: $@");

$ftp->login( $ftpUsername, $ftpPassword )
  or &exitLog( "Cannot login to FTP " . $ftp->message );

$ftp->cwd($ftpDir);

foreach my $outFile (@outFiles) {
	my $remoteFileSize = $ftp->size($outFile);

if ( defined($remoteFileSize) ) {
		if ( $remoteFileSize > 0 ) {

# file exists, let's assume we are replacing it and delete the old one
			$ftp->delete($outFile);
			&log("file exists on FTP, deleting the old $outFile\n");
		}
	}

if ( $ftp->put("$outFile") ) {
		&log("uploaded $outFile to $ftpServer.\n");
	}
}
$ftp->quit();
}

# do for  mirror regardless of error.
# ok now ftp the files away.
my $ftp2 = Net::FTP->new( $ftpServer2, Debug => 0 )
  or &exitLog("Cannot connect to $ftpServer2: $@");

$ftp2->login( $ftpUsername2, $ftpPassword2 )
  or &exitLog( "Cannot login to FTP " . $ftp2->message );

$ftp2->cwd($ftpDir2);

foreach my $outFile (@outFiles) {
	my $remoteFileSize = $ftp2->size($outFile);

if ( defined($remoteFileSize) ) {
		if ( $remoteFileSize > 0 ) {

# file exists, let's assume we are replacing it and delete the old one
			$ftp2->delete($outFile);
			&log("file exists on FTP, deleting the old $outFile\n");
		}
	}
	if ( $ftp2->put("$outFile") ) {
		&log("uploaded $outFile to $ftpServer2.\n");
	}
}
$ftp2->quit();
}
if ($error) { &exitLog("Script completed but with errors. Data NOT uploaded FTP2.\n"); }

sub log {
	my $logText = shift;
	my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) = localtime(time);
	$year += 1900;
	$mon++;
	if ( $sec < 10) { $sec = "0" . $sec; }
	if ( $min < 10) { $min = "0" . $min; }
	if ( $hour < 10) { $hour = "0" . $hour; }
	if ( $mday < 10) { $mday = "0" . $mday; }
	if ( $mon < 10) { $mon = "0" . $mon; }
	
	my $logTime = $year . "-" .  $mon  . "-$mday $hour:$min:$sec ";

#	open(FH, ">>" . $submissionPath . "submissionLog.txt") or warn "couldn't open logfile in $submissionPath: $!\n" ;#
	print $logTime . $logText ;
	$error_text .= $logTime . $logText ;

#	close FH;

}

sub errorLog {
	my $errText = shift;
	
	# set global error flag.
	$error = 1;
	&log("ERROR: " . $errText);
}
sub exitLog {
	my $reason = shift;
	&log($reason);

# send an email!

MIME::Lite->send(
		'smtp', $pop3Server,
		Timeout  => 60,
		AuthUser => $pop3Username,
		AuthPass => $pop3Password
	);
	my $msg = MIME::Lite->new(
		From => 'foo@bar.com',
		To   => 'foo@bar.com',

# Cc       =>'foo@bar.com',
		Subject => ' data FTP script error',
		Data    =>
"This email is automatically generated by the script which uploads the  data to the  FTP server daily. It is only meant to be sent if there was a problem.  You may have to manually upload the data!\n\n\n$error_text"
	);
	$msg->send();

print $error_text;
	
	exit;

}

sub missingInterval {
	my $lastInterval = shift;
	my $thisInterval = shift;
	my $lineNo = shift;
	&log("Missing or incorrect interval at line # $lineNo : $lastInterval, $thisInterval\n");
	$error =1;
}

Victorino wrote:I'm trying to figure out how download a csv file of some data points with curl or wget to be presented on a different system. The customViewExample.jsp only provides four examples and I can't figure out how to retrieve historical data.

A csv file can be manually downloaded from the watch list or reports but I need to automate the download process.

Any help would be greatly appreciated.

I have a script which can save the .csv file attached to an e-mail. then you can set up a report to be e-mailed and then retrieve the data using POP3.

I was asking mwil7034.

do you have a github repo?

why will it be a nightmare?
create a repository containing the released source, make your branches off of that, make your changes in your branches, when new releases occur update the released source trunk and then merge the changes in to your branches.

I was doing this with bazaar and am now using github but haven't been through a new source release on that yet.

mango pace of development will probably slow down with new focus being m2m2, so probably there won't be many big changes to serotonin source coming down the pipe anyway.

That being said I haven't updated my branches since 1.1.6

For years Serotonin has been doing a great job of adding new features, fixing bugs, and providing amazing support via this forum and somehow they are providing this service to us free of charge.

The new features scada-br has added, such as OPC, have so far appeared in the official Serotonin releases, which is of great benefit to everybody.

I'm sure scada-br will continue to integrate the new features and bug fixes from the serotonin releases and hopefully serotonin will be able to integrate more of the new scada-br features as they mature. This makes scada-br more of a 'branch' than a fork. I realize english isn't everyone's first language but it is an important differentiation.

what kind of meter is it? see if the manufacturer has software meant to work with the meter, and if you can get it to work then you know the comms are good, and then proceed to getting it to work with mango

ICMP ping from Java is not a straightforward thing to do.

There are lots of programs that are designed to track uptime of hosts like cacti, nagios and open nms that might be more suited than mango for tracking uptime.

Or as you say get a slightly less barebones switch which has SNMP.

http://mango.serotoninsoftware.com/downloads/mango-source-1.11.0.zip

amoulart wrote:

if (j1.value == 2) {return o1.value;} else return currentvalue;

j1 is the index value
o1 is modbus register

have you tried:

if (j1.value == 2) {return o1.value;} else return m1.value;

j1 is the index value
o1 is modbus register
m1 is the meta point

I know that this could potentially lead to recursion on the meta point update event, but I think I have seen that topic come up before in this forum and mango is smart enough not to trigger a meta point update event when the meta point itself is updated.

Give it a shot...

Yes, all the meta point scripts will run every time the index is updated, but that doesn't really matter.