remove all output without DEBUG=1 for cron use

[eg5120] / parse-node-logs.pl

diff --git a/parse-node-logs.pl b/parse-node-logs.pl
index 1e1b6e9..5b973fd 100755 (executable)
--- a/parse-node-logs.pl
+++ b/parse-node-logs.pl
@@ -8,12 +8,25 @@ use DBD::Pg;
 use Data::Dump qw(dump);
 use autodie;
 
+my $path = '/home/nodelogs/c405f97667784094bca5cfa52af0bcf1/';
+
+my $last_files = $ENV{LAST} || 3; # process last 3 logs by mtime
+
+my $debug = $ENV{DEBUG} || 0;
+
+# select _id,json->'received',time,to_timestamp((json->>'received')::int8/1000) - interval '1 hour' from nodelog ;
+
 my $dbh = DBI->connect("dbi:Pg:dbname=eg5120", "dpavlin", "", { RaiseError => 1 });
+warn "# truncate table nodelog" if $debug;
 $dbh->do( qq{ truncate table nodelog } ); # FIXME
 my $sth = $dbh->prepare(qq{insert into nodelog (time,json) values (?,?)});
 
-foreach my $filename ( sort(glob('node-logs/node-red-out__2023-07-*')), 'node-logs/node-red-out.log' ) {
-       warn "# $filename";
+
+foreach my $filename (
+               sort { -M $a <=> -M $b } (glob("$path/node-red-out*.log")),
+       ) {
+       last if $last_files-- == 0;
+       warn "# $filename" if $debug;
        open(my $log, '<', $filename);
        my $in_json = 0;
        my $json;
@@ -23,6 +36,7 @@ foreach my $filename ( sort(glob('node-logs/node-red-out__2023-07-*')), 'node-lo
        while(<$log>) {
                chomp;
                #warn "## $in_json -->$_<--\n";
+               ## XXX debug 1 in node-red with logging to system console
                if ( m/(\d+ \w+)\s+(\d\d:\d\d:\d\d) - \Q[info] [debug:debug 1]\E/ ) {
                        $time = $1 . ' ' . $year . ' ' . $2;
                        #warn "# time $time\n";
@@ -39,7 +53,7 @@ foreach my $filename ( sort(glob('node-logs/node-red-out__2023-07-*')), 'node-lo
                        if ( $_ eq '}' ) {
                                $in_json = 0;
                                #warn "- in_json";
-                               warn ">>> $time [[[[ $json ]]]]";
+                               warn ">>> $time [[[[ $json ]]]]" if $debug;
                                write_file '/dev/shm/json', $json;
                                $sth->execute( $time, $json );
                                $json = '';
@@ -48,3 +62,18 @@ foreach my $filename ( sort(glob('node-logs/node-red-out__2023-07-*')), 'node-lo
 
        }
 }
+
+# insert missing sensor readings
+$dbh->do( qq{
+
+create temporary table nl_received as select _id,json->'received' as received from nodelog where json->'received' is not null;
+
+create temporary table eg_received as select _id,json->'received' as received from eg5120 where json->'received' is not null;
+
+create temporary table nl_new as select * from nl_received where received not in (select received from eg_received) ;
+
+select to_timestamp((json->>'received')::int8/1000), time,mac,addr,"nodeId",json,_id,sensor_type from nodelog where _id in (select _id from nl_new);
+
+insert into eg5120 select to_timestamp((json->>'received')::int8/1000) as time,mac,addr,"nodeId",json,sensor_type from nodelog where _id in (select _id from nl_new);
+
+} );