git.rot13.org Git - eg5120/blob - parse-node-logs.pl

   1 #!/usr/bin/perl
   2 use strict;
   3 use warnings;
   4
   5 use POSIX qw(strftime);
   6 use File::Slurp;
   7 use DBD::Pg;
   8 use Data::Dump qw(dump);
   9 use autodie;
  10
  11 my $path = '/home/nodelogs/c405f97667784094bca5cfa52af0bcf1/';
  12
  13 my $last_files = $ENV{LAST} || 3; # process last 3 logs by mtime
  14
  15 my $debug = $ENV{DEBUG} || 0;
  16
  17 # select _id,json->'received',time,to_timestamp((json->>'received')::int8/1000) - interval '1 hour' from nodelog ;
  18
  19 my $dbh = DBI->connect("dbi:Pg:dbname=eg5120", "dpavlin", "", { RaiseError => 1 });
  20 warn "# truncate table nodelog" if $debug;
  21 $dbh->do( qq{ truncate table nodelog } ); # FIXME
  22 my $sth = $dbh->prepare(qq{insert into nodelog (time,json) values (?,?)});
  23
  24
  25 foreach my $filename (
  26                 sort { -M $a <=> -M $b } (glob("$path/node-red-out*.log")),
  27         ) {
  28         last if $last_files-- == 0;
  29         warn "# $filename" if $debug;
  30         open(my $log, '<', $filename);
  31         my $in_json = 0;
  32         my $json;
  33         my $year = $1 if $filename =~ m/__(20\d\d)-/;
  34         $year ||= (localtime)[5] + 1900;
  35         my $time;
  36         while(<$log>) {
  37                 chomp;
  38                 #warn "## $in_json -->$_<--\n";
  39                 ## XXX debug 1 in node-red with logging to system console
  40                 if ( m/(\d+ \w+)\s+(\d\d:\d\d:\d\d) - \Q[info] [debug:debug 1]\E/ ) {
  41                         $time = $1 . ' ' . $year . ' ' . $2;
  42                         #warn "# time $time\n";
  43                 } elsif ( $_ eq '{' ) {
  44                         $json .= $_;
  45                         $in_json = 1;
  46                         #warn "+ in_json";
  47                 } elsif ( $in_json ) {
  48
  49                         s/\s(\S+):\s/"$1":/g;   # fix names quoting
  50                         s/'/"/g;                # replace ' with "
  51                         $json .= $_;
  52
  53                         if ( $_ eq '}' ) {
  54                                 $in_json = 0;
  55                                 #warn "- in_json";
  56                                 warn ">>> $time [[[[ $json ]]]]" if $debug;
  57                                 write_file '/dev/shm/json', $json;
  58                                 $sth->execute( $time, $json );
  59                                 $json = '';
  60                         }
  61                 }
  62
  63         }
  64 }
  65
  66 # insert missing sensor readings
  67 $dbh->do( qq{
  68
  69 create temporary table nl_received as select _id,json->'received' as received from nodelog where json->'received' is not null;
  70
  71 create temporary table eg_received as select _id,json->'received' as received from eg5120 where json->'received' is not null;
  72
  73 create temporary table nl_new as select * from nl_received where received not in (select received from eg_received) ;
  74
  75 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);
  76
  77 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);
  78
  79 } );