pull.php 10.5 KB
Newer Older
MatrixCrawler's avatar
MatrixCrawler committed
1
<?php
2
//* Copyright (c) 2011, David Morley. This file is licensed under the Affero General Public License version 3 or later. See the COPYRIGHT file. */
3

David Morley's avatar
David Morley committed
4 5 6
$debug   = isset($_GET['debug']) || (isset($argv) && in_array('debug', $argv, true));
$newline = PHP_SAPI === 'cli' ? "\n" : '<br>';

7 8
$_domain = $_GET['domain'] ?? '';

9
require_once __DIR__ . '/../config.php';
David Morley's avatar
David Morley committed
10
require_once __DIR__ . '/../vendor/autoload.php';
dmorley's avatar
dmorley committed
11

12 13
$dbh = pg_connect("dbname=$pgdb user=$pguser password=$pgpass");
$dbh || die('Error in connection: ' . pg_last_error());
MatrixCrawler's avatar
MatrixCrawler committed
14

15
if ($_domain) {
16
  $sql = 'SELECT domain,score,date_created,adminrating,weight,hidden,podmin_notify,email FROM pods WHERE domain = $1';
17 18
  $result = pg_query_params($dbh, $sql, [$_domain]);
} elseif (PHP_SAPI === 'cli') {
19
  $sql = 'SELECT domain,score,date_created,adminrating,weight,hidden,podmin_notify,email FROM pods';
dmorley's avatar
dmorley committed
20
  $result = pg_query($dbh, $sql);
21 22
} else {
  die('No valid input');
dmorley's avatar
dmorley committed
23
}
24 25
$result || die('Error in SQL query1: ' . pg_last_error());

David Morley's avatar
David Morley committed
26 27 28 29 30 31
while ($row = pg_fetch_assoc($result)) {
  $domain    = $row['domain'];
  $score     = (int) $row['score'];
  $dateadded = $row['date_created'];
  $admindb   = (int) $row['adminrating'];
  $weight    = $row['weight'];
32 33 34
  $hiddennow = $row['hidden'];
  $email     = $row['email'];
  $notify    = $row['podmin_notify'];
David Morley's avatar
David Morley committed
35 36 37
  $sqlforr   = 'SELECT admin,rating FROM rating_comments WHERE domain = $1';
  $ratings   = pg_query_params($dbh, $sqlforr, [$domain]);
  $ratings || die('Error in SQL query2: ' . pg_last_error());
38

David Morley's avatar
David Morley committed
39
  _debug('Domain', $domain);
noplanman's avatar
noplanman committed
40

David Morley's avatar
David Morley committed
41 42 43 44 45 46 47
  $user_ratings  = [];
  $admin_ratings = [];
  while ($rating = pg_fetch_assoc($ratings)) {
    if ($rating['admin'] == 0) {
      $user_ratings[] = $rating['rating'];
    } elseif ($rating['admin'] == 1) {
      $admin_ratings[] = $rating['rating'];
48
    }
David Morley's avatar
David Morley committed
49 50 51
  }
  $user_rating  = empty($user_ratings) ? 0 : max(10, round(array_sum($user_ratings) / count($user_ratings), 2));
  $admin_rating = empty($admin_ratings) ? 0 : max(10, round(array_sum($admin_ratings) / count($admin_ratings), 2));
noplanman's avatar
noplanman committed
52

David Morley's avatar
David Morley committed
53 54 55
  if ($admindb == -1) {
    $admin_rating = -1;
  }
56

David Morley's avatar
David Morley committed
57 58
  $chss = curl_init();
  curl_setopt($chss, CURLOPT_URL, 'https://' . $domain . '/nodeinfo/1.0');
59 60
  curl_setopt($chss, CURLOPT_CONNECTTIMEOUT, 10);
  curl_setopt($chss, CURLOPT_TIMEOUT, 30);
David Morley's avatar
David Morley committed
61 62
  curl_setopt($chss, CURLOPT_RETURNTRANSFER, 1);
  curl_setopt($chss, CURLOPT_CERTINFO, 1);
dmorley's avatar
dmorley committed
63
  curl_setopt($chss, CURLOPT_CAINFO, $cafullpath);
David Morley's avatar
David Morley committed
64 65 66
  $outputssl      = curl_exec($chss);
  $outputsslerror = curl_error($chss);
  $info           = curl_getinfo($chss, CURLINFO_CERTINFO);
67 68 69
  $conntime       = curl_getinfo($chss, CURLINFO_CONNECT_TIME);
  $nstime         = curl_getinfo($chss, CURLINFO_NAMELOOKUP_TIME);
  $latency        = $conntime - $nstime;
dmorley's avatar
dmorley committed
70
  $sslexpire      = $info[0]['Expire date'] ?? null;
David Morley's avatar
David Morley committed
71
  curl_close($chss);
72

David Morley's avatar
David Morley committed
73 74
  _debug('Nodeinfo output', $outputssl, true);
  _debug('Nodeinfo output error', $outputsslerror, true);
75
  _debug('Cert expire date', $sslexpire);
76 77
  _debug('Conntime', $conntime);
  _debug('NStime', $nstime);
dmorley's avatar
dmorley committed
78
  _debug('Latency', $latency);
79
  
David Morley's avatar
David Morley committed
80
  $jsonssl = json_decode($outputssl);
noplanman's avatar
noplanman committed
81

82 83 84 85 86
  $xdver                 = $jsonssl->software->version ?? 0;
  $dverr                 = explode('-', trim($xdver));
  $shortversion          = $dverr[0];
  $signup                = ($jsonssl->openRegistrations ?? false) === true;
  $softwarename          = $jsonssl->software->name ?? 'unknown';
David Morley's avatar
David Morley committed
87
  $name                  = $jsonssl->metadata->nodeName ?? $softwarename;
88 89 90 91 92 93
  $total_users           = $jsonssl->usage->users->total ?? 0;
  $active_users_halfyear = $jsonssl->usage->users->activeHalfyear ?? 0;
  $active_users_monthly  = $jsonssl->usage->users->activeMonth ?? 0;
  $local_posts           = $jsonssl->usage->localPosts ?? 0;
  $comment_counts        = $jsonssl->usage->localComments ?? 0;
  $service_xmpp          = ($jsonssl->metadata->xmppChat ?? false) === true;
dmorley's avatar
dmorley committed
94 95 96 97
  $service_facebook      = false;
  $service_twitter       = false;
  $service_tumblr        = false;
  $service_wordpress     = false;
98
  if (json_last_error() === 0) {
dmorley's avatar
dmorley committed
99
    (!$jsonssl->software->version) || $score += 1;
David Morley's avatar
David Morley committed
100 101 102 103
    $service_facebook      = in_array('facebook', $jsonssl->services->outbound, true);
    $service_twitter       = in_array('twitter', $jsonssl->services->outbound, true);
    $service_tumblr        = in_array('tumblr', $jsonssl->services->outbound, true);
    $service_wordpress     = in_array('wordpress', $jsonssl->services->outbound, true);
104
  }
David Morley's avatar
David Morley committed
105
    
106 107
  if ($jsonssl !== null) {
    $status        = 'Up';
dmorley's avatar
dmorley committed
108 109
    $sql_checks    = 'INSERT INTO checks (domain, online, latency, total_users, local_posts, comment_counts, shortversion) VALUES ($1, $2, $3, $4, $5, $6, $7)';
    $result_checks = pg_query_params($dbh, $sql_checks, [$domain, 1, $latency, $total_users, $local_posts, $comment_counts, $shortversion]);
David Morley's avatar
David Morley committed
110
    $result_checks || die('Error in SQL query: ' . pg_last_error());
David Morley's avatar
David Morley committed
111
  }
112 113 114
  
  if (!$jsonssl) {    
    _debug('Connection', 'Can not connect to pod');
dmorley's avatar
dmorley committed
115 116
    $sql_errors    = 'INSERT INTO checks (domain, online, error, latency) VALUES ($1, $2, $3, $4)';
    $result_errors = pg_query_params($dbh, $sql_errors, [$domain, 0, $outputsslerror, $latency]);
117 118 119 120 121 122 123
    $result_errors || die('Error in SQL query: ' . pg_last_error());
    $score         -= 1;
    $shortversion  = '0.error';
    $status        = 'Down';
  }
  
  _debug('Version code', $shortversion);
David Morley's avatar
David Morley committed
124
  _debug('Signup Open', $signup);
David Morley's avatar
David Morley committed
125

David Morley's avatar
David Morley committed
126 127 128
  $delv = new NPM\Xec\Command("delv @{$dnsserver} {$domain}");
  $delv->throwExceptionOnError(false);

129
  $ip         = '';
David Morley's avatar
David Morley committed
130 131 132
  $iplookupv4 = explode(PHP_EOL, trim($delv->execute([], null, 15)->stdout));
  $dnssec     = in_array('; fully validated', $iplookupv4, true) ?? false;
  $getaonly   = array_values(preg_grep('/\s+IN\s+A\s+.*/', $iplookupv4));
133
  if ($getaonly) {
134
    preg_match('/A\s(.*)/', $getaonly[0], $aversion);
135
    $ip = trim($aversion[1]) ?? '';
136
  }
David Morley's avatar
David Morley committed
137 138 139

  $ipv6        = false;
  $iplookupv6  = explode(PHP_EOL, trim($delv->execute(['AAAA'], null, 15)->stdout));
David Morley's avatar
David Morley committed
140 141 142
  $getaaaaonly = array_values(preg_grep('/\s+IN\s+AAAA\s+.*/', $iplookupv6));
  if ($getaaaaonly) {
    preg_match('/AAAA\s(.*)/', $getaaaaonly[0], $aaaaversion);
David Morley's avatar
David Morley committed
143
    $ipv6 = trim($aaaaversion[1]) ?? '';
dmorley's avatar
dmorley committed
144
  }
dmorley's avatar
dmorley committed
145
  $ip || $score -= 2;
David Morley's avatar
David Morley committed
146 147

  _debug('IPv4', $ip);
148
  _debug('Iplookupv4', $iplookupv4, true);
David Morley's avatar
David Morley committed
149
  _debug('IPv6', $ipv6);
150
  _debug('Iplookupv6', $iplookupv6, true);
David Morley's avatar
David Morley committed
151 152 153

  $location = geoip_record_by_name($ip);
  _debug('Location', $location, true);
154 155 156 157 158
  $country  = !empty($location['country_code']) ? iconv('UTF-8', 'UTF-8//IGNORE', $location['country_code']) : null;
  $city     = !empty($location['city']) ? iconv('UTF-8', 'UTF-8//IGNORE', $location['city']) : null;
  $state    = !empty($location['region']) ? iconv('UTF-8', 'UTF-8//IGNORE', $location['region']) : null;
  $lat      = !empty($location['latitude']) ? $location['latitude'] : 0;
  $long     = !empty($location['longitude']) ? $location['longitude'] : 0;
dmorley's avatar
dmorley committed
159
  
David Morley's avatar
David Morley committed
160 161
  echo $newline;
  $statslastdate = date('Y-m-d H:i:s');
David Morley's avatar
David Morley committed
162

163 164
  $diff         = (new DateTime())->diff(new DateTime($dateadded));
  $months       = $diff->m + ($diff->y * 12);
David Morley's avatar
David Morley committed
165
    
dmorley's avatar
dmorley committed
166 167 168 169 170
  $avglatency     = 0;
  $sqllatency     = 'SELECT round(avg(latency) * 1000) AS latency FROM checks WHERE domain = $1';
  $resultlatency  = pg_query_params($dbh, $sqllatency, [$domain]);
  $resultlatency  || die('Error in SQL query resultchecks: ' . pg_last_error());
  $avglatency     = pg_fetch_result($resultlatency, 0);
David Morley's avatar
David Morley committed
171 172

  $uptime       = 0;
173
  $sqlonline    = 'SELECT avg(online::int) * 100 AS online FROM checks WHERE domain = $1';
David Morley's avatar
David Morley committed
174 175
  $resultonline = pg_query_params($dbh, $sqlonline, [$domain]);
  $resultonline || die('Error in SQL query resultchecks: ' . pg_last_error());
176 177 178
  $uptime       = round(pg_fetch_result($resultonline, 0),2);
  
  _debug('Uptime', $uptime);
David Morley's avatar
David Morley committed
179 180 181 182 183 184 185 186

  $sqlmasters    = 'SELECT version FROM masterversions WHERE software = $1 ORDER BY date_checked LIMIT 1';
  $resultmasters = pg_query_params($dbh, $sqlmasters, [$softwarename]);
  $resultmasters || die('Error in SQL query: ' . pg_last_error());
  $masterversion = pg_fetch_result($resultmasters, 0);

  _debug('Masterversion', $masterversion);
  
David Morley's avatar
David Morley committed
187 188
  $hidden = $score <= 70;
  _debug('Hidden', $hidden ? 'yes' : 'no');
189 190 191 192 193 194 195 196 197

  if ($hiddennow === 'f' && $hidden && $notify === 't') {
    $to      = $email;
    $headers = ['From: ' . $adminemail, 'Bcc: ' . $adminemail];
    $subject = 'Monitoring notice from poduptime';
    $message = 'Notice for ' . $domain . '. Your score fell to ' . $score . ' and your pod is now marked as hidden.';
    @mail($to, $subject, $message, implode("\r\n", $headers));
    _debug('Mail Notice', 'sent to '.$email);
  }
David Morley's avatar
David Morley committed
198 199 200 201 202
  if ($score > 100) {
    $score = 100;
  } elseif ($score < 0) {
    $score = 0;
  }
203 204
  $weightedscore = ($uptime + $score - (10 - $weight)) / 2;
  _debug('Weighted Score', $weightedscore);
noplanman's avatar
noplanman committed
205

David Morley's avatar
David Morley committed
206
  $timenow    = date('Y-m-d H:i:s');
dmorley's avatar
dmorley committed
207 208
  $sql_set    = 'UPDATE pods SET secure = $2, hidden = $3, ip = $4, ipv6 = $5, monthsmonitored = $6, uptime_alltime = $7, status = $8, date_laststats = $9, date_updated = $10, latency = $11, score = $12, adminrating = $13, country = $14, city = $15, state = $16, lat = $17, long = $18, userrating = $19, shortversion = $20, masterversion = $21, signup = $22, total_users = $23, active_users_halfyear = $24, active_users_monthly = $25, local_posts = $26, name = $27, comment_counts = $28, service_facebook = $29, service_tumblr = $30, service_twitter = $31, service_wordpress = $32, weightedscore = $33, service_xmpp = $34, softwarename = $35, sslvalid = $36, dnssec = $37, sslexpire = $38 WHERE domain = $1';
  $result_set = pg_query_params($dbh, $sql_set, [$domain, 1, (int) $hidden, $ip, (int) ($ipv6 !== null), $months, $uptime, $status, $statslastdate, $timenow, $avglatency, $score, $admin_rating, $country, $city, $state, $lat, $long, $user_rating, $shortversion, $masterversion, (int) $signup, $total_users, $active_users_halfyear, $active_users_monthly, $local_posts, $name, $comment_counts, (int) $service_facebook, (int) $service_tumblr, (int) $service_twitter, (int) $service_wordpress, $weightedscore, (int) $service_xmpp, $softwarename, $outputsslerror, (int) $dnssec, $sslexpire]);
David Morley's avatar
David Morley committed
209 210 211
  $result_set || die('Error in SQL query3: ' . pg_last_error());

  _debug('Score out of 100', $score);
MatrixCrawler's avatar
MatrixCrawler committed
212

213
  echo 'Success '.$domain;
dmorley's avatar
dmorley committed
214
  
David Morley's avatar
David Morley committed
215 216 217
  echo $newline;
  echo $newline;
}
218

David Morley's avatar
David Morley committed
219 220 221 222 223 224 225 226 227
/**
 * Output a debug message and variable value
 * 
 * @param string $label
 * @param mixed  $var
 * @param bool   $dump
 */
function _debug($label, $var = null, $dump = false) {
  global $debug, $newline;
228

David Morley's avatar
David Morley committed
229 230
  if (!$debug) {
    return;
dmorley's avatar
dmorley committed
231
  }
David Morley's avatar
David Morley committed
232 233 234 235 236 237 238 239 240 241
  
  if ($dump || is_array($var)) {
    $output = print_r($var, true); 
  } elseif (is_bool($var)) {
    $output = $var ? 'true' : 'false';
  } else {
    $output = (string) $var;
  }
  
  printf('%s: %s%s', $label, $output, $newline);
242
}