The web based syslog https://lggr.io
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

744 lines
19KB

  1. <?php
  2. class Lggr {
  3. const LASTSTAT = 5000;
  4. const ARCHIVEDSIZE = 'archivedSize';
  5. const INNERAND = ' AND ';
  6. private $config = null;
  7. private $db = null;
  8. private $state = null;
  9. private $cache = null;
  10. private $aPerf = null;
  11. function __construct(LggrState $state, AbstractConfig $config) {
  12. $this->config = $config;
  13. $this->state = $state;
  14. $this->cache = new LggrCacheFile(); // or use LggrCacheRedis instead
  15. $this->aPerf = array(); // of type LggrPerf objects
  16. if (! $this->state->isLocalCall()) {
  17. $this->checkSecurity();
  18. }
  19. $this->db = new mysqli('localhost', $this->config->getDbUSer(),
  20. $this->config->getDbPwd(), $this->config->getDbName());
  21. $this->db->set_charset('utf8');
  22. }
  23. // constructor
  24. function __destruct() {
  25. if (null != $this->db) {
  26. $this->db->close();
  27. } // if
  28. }
  29. // destructor
  30. private function checkSecurity() {
  31. // local access allowed without login data
  32. if ($_SERVER["REMOTE_ADDR"] === "::1") {
  33. return;
  34. }
  35. if ($_SERVER["REMOTE_ADDR"] === "127.0.0.1") {
  36. return;
  37. }
  38. if (! isset($_SERVER['REMOTE_USER'])) {
  39. throw new LggrException('You must enable basic authentication');
  40. } // if
  41. }
  42. // function
  43. private function getViewName() {
  44. $rcView = '';
  45. switch ($this->state->getRange()) {
  46. case 1:
  47. $rcView = 'lasthour';
  48. break;
  49. case 24:
  50. $rcView = 'today';
  51. break;
  52. case 168:
  53. $rcView = 'week';
  54. break;
  55. case 8760:
  56. $rcView = 'year';
  57. break;
  58. default:
  59. $rcView = 'today';
  60. break;
  61. }
  62. return $rcView;
  63. }
  64. function getLevels() {
  65. $perf = new LggrPerf();
  66. $v = $this->getViewName();
  67. $sql = "
  68. SELECT level, COUNT(*) AS c
  69. FROM (SELECT level FROM $v ORDER BY `date` DESC LIMIT " .
  70. self::LASTSTAT . ") AS sub
  71. GROUP BY level
  72. ORDER BY c DESC
  73. ";
  74. $a = $this->cache->retrieve("levels$v");
  75. if (null != $a) {
  76. return $a;
  77. } // if
  78. $a = array();
  79. $perf->start($sql);
  80. $res = $this->db->query($sql);
  81. if (false === $res) {
  82. throw new LggrException($this->db->error);
  83. } // if
  84. while ($row = $res->fetch_object()) {
  85. $a[] = $row;
  86. } // while
  87. $res->close();
  88. $sum = 0;
  89. foreach ($a as $level) {
  90. $sum += $level->c;
  91. } // foreach
  92. foreach ($a as $level) {
  93. $f = $level->c / $sum * 100;
  94. $level->f = round($f, 2);
  95. } // foreach
  96. $perf->stop();
  97. $this->aPerf[] = $perf;
  98. $this->cache->store("levels$v", $a);
  99. return $a;
  100. }
  101. // function
  102. function getAllServers() {
  103. $perf = new LggrPerf();
  104. $sql = "
  105. SELECT id,name AS host
  106. FROM servers";
  107. $perf->start($sql);
  108. $res = $this->db->query($sql);
  109. if (false === $res) {
  110. throw new LggrException($this->db->error);
  111. } // if
  112. while ($row = $res->fetch_object()) {
  113. $a[] = $row;
  114. } // while
  115. $res->close();
  116. $perf->stop();
  117. $this->aPerf[] = $perf;
  118. return $a;
  119. }
  120. // function
  121. function getServersName($id) {
  122. $perf = new LggrPerf();
  123. $sql = "
  124. SELECT name
  125. FROM servers
  126. WHERE id=$id";
  127. $perf->start($sql);
  128. $a = null;
  129. $res = $this->db->query($sql);
  130. if (false === $res) {
  131. throw new LggrException($this->db->error);
  132. } // if
  133. if ($row = $res->fetch_object()) {
  134. $a = $row;
  135. } // while
  136. $res->close();
  137. $perf->stop();
  138. $this->aPerf[] = $perf;
  139. return $a->name;
  140. }
  141. // function
  142. function getServers() {
  143. $perf = new LggrPerf();
  144. $v = $this->getViewName();
  145. $sql = "
  146. SELECT host, COUNT(*) AS c
  147. FROM (SELECT host FROM $v ORDER BY `date` DESC LIMIT " .
  148. self::LASTSTAT . ") AS sub
  149. GROUP BY host
  150. ORDER BY c DESC";
  151. $a = $this->cache->retrieve("servers$v");
  152. if (null != $a) {
  153. return $a;
  154. } // if
  155. $a = array();
  156. $perf->start($sql);
  157. $res = $this->db->query($sql);
  158. if (false === $res) {
  159. throw new LggrException($this->db->error);
  160. } // if
  161. while ($row = $res->fetch_object()) {
  162. $a[] = $row;
  163. } // while
  164. $res->close();
  165. $sum = 0;
  166. foreach ($a as $host) {
  167. $sum += $host->c;
  168. } // foreach
  169. foreach ($a as $host) {
  170. $f = $host->c / $sum * 100;
  171. $host->f = round($f, 2);
  172. } // foreach
  173. $perf->stop();
  174. $this->aPerf[] = $perf;
  175. $this->cache->store("servers$v", $a);
  176. return $a;
  177. }
  178. // function
  179. function getArchived($from = 0, $count = LggrState::PAGELEN) {
  180. $iArchivedSize = $this->cache->retrieve(ARCHIVEDSIZE);
  181. $aArchivedData = $this->cache->retrieve(ARCHIVEDSIZE . intval($from));
  182. if ((null != $iArchivedSize) && (null != $aArchivedData)) {
  183. $this->state->setResultSize($iArchivedSize);
  184. return $aArchivedData;
  185. } // if
  186. $perfSize = new LggrPerf();
  187. $perfData = new LggrPerf();
  188. $sqlSize = "SELECT COUNT(*) AS c FROM archived";
  189. $sqlData = "
  190. SELECT * FROM archived
  191. ORDER BY `date` DESC
  192. LIMIT $from,$count";
  193. $perfSize->start($sqlSize);
  194. $this->getResultSize($sqlSize);
  195. $perfSize->stop();
  196. $perfData->start($sqlData);
  197. $a = $this->sendResult($sqlData);
  198. $perfData->stop();
  199. $this->aPerf[] = $perfSize;
  200. $this->aPerf[] = $perfData;
  201. $this->cache->store("archivedSize", $this->state->getResultSize());
  202. $this->cache->store("archivedData" . intval($from), $a);
  203. return $a;
  204. }
  205. // function
  206. function getLatest($from = 0, $count = LggrState::PAGELEN) {
  207. $perfSize = new LggrPerf();
  208. $perfData = new LggrPerf();
  209. $v = $this->getViewName();
  210. $sqlSize = "SELECT COUNT(*) AS c FROM $v";
  211. $sqlData = "
  212. SELECT * FROM $v
  213. ORDER BY `date` DESC
  214. LIMIT $from,$count";
  215. $perfSize->start($sqlSize);
  216. $this->getResultSize($sqlSize);
  217. $perfSize->stop();
  218. $perfData->start($sqlData);
  219. $a = $this->sendResult($sqlData);
  220. $perfData->stop();
  221. $this->aPerf[] = $perfSize;
  222. $this->aPerf[] = $perfData;
  223. return $a;
  224. }
  225. // function
  226. function getCloud() {
  227. $perf = new LggrPerf();
  228. $v = $this->getViewName();
  229. $a = $this->cache->retrieve("cloud$v");
  230. if (null != $a) {
  231. return $a;
  232. } // if
  233. $sql = "SELECT COUNT(*) AS c, program FROM $v GROUP BY program HAVING CHAR_LENGTH(program)>2 ORDER BY c DESC";
  234. $perf->start($sql);
  235. $a = $this->sendResult($sql);
  236. $perf->stop();
  237. $this->aPerf[] = $perf;
  238. $this->cache->store("cloud$v", $a);
  239. return $a;
  240. }
  241. // function
  242. function getNewer($id) {
  243. $perf = new LggrPerf();
  244. $sqlData = "
  245. SELECT * FROM lasthour
  246. WHERE id>$id
  247. ORDER BY `date` DESC
  248. LIMIT " . LggrState::PAGELEN;
  249. $perf->start($sqlData);
  250. $a = $this->sendResult($sqlData);
  251. $perf->stop();
  252. $this->aPerf[] = $perf;
  253. return $a;
  254. }
  255. // function
  256. function getEntry($id) {
  257. $perf = new LggrPerf();
  258. $sqlData = "
  259. SELECT * FROM lasthour
  260. WHERE id=$id";
  261. $perf->start($sqlData);
  262. $a = $this->sendResult($sqlData);
  263. $perf->stop();
  264. $this->aPerf[] = $perf;
  265. return $a;
  266. }
  267. // function
  268. function getFromTo($from = 0, $count = LggrState::PAGELEN) {
  269. $perfSize = new LggrPerf();
  270. $perfData = new LggrPerf();
  271. $sFrom = $this->db->escape_string($this->state->getFrom());
  272. $sTo = $this->db->escape_string($this->state->getTo());
  273. $sqlSize = "
  274. SELECT COUNT(*) AS c FROM newlogs
  275. WHERE `date` BETWEEN '$sFrom' AND '$sTo'";
  276. $sqlData = "
  277. SELECT * FROM newlogs
  278. WHERE `date` BETWEEN '$sFrom' AND '$sTo'
  279. ORDER BY `date` DESC
  280. LIMIT $from,$count";
  281. $perfSize->start($sqlSize);
  282. $this->getResultSize($sqlSize);
  283. $perfSize->stop();
  284. $perfData->start($sqlData);
  285. $a = $this->sendResult($sqlData);
  286. $perfData->stop();
  287. $this->aPerf[] = $perfSize;
  288. $this->aPerf[] = $perfData;
  289. return $a;
  290. }
  291. // function
  292. function getHostFromTo($from = 0, $count = LggrState::PAGELEN) {
  293. $perfSize = new LggrPerf();
  294. $perfData = new LggrPerf();
  295. $iHost = $this->state->getHostId();
  296. $sFrom = $this->db->escape_string($this->state->getFrom());
  297. $sTo = $this->db->escape_string($this->state->getTo());
  298. $sqlSize = "
  299. SELECT COUNT(*) AS c FROM newlogs
  300. WHERE `date` BETWEEN '$sFrom' AND '$sTo'
  301. AND idhost=$iHost";
  302. $sqlData = "
  303. SELECT * FROM newlogs
  304. WHERE `date` BETWEEN '$sFrom' AND '$sTo'
  305. AND idhost=$iHost
  306. ORDER BY `date` DESC
  307. LIMIT $from,$count";
  308. $perfSize->start($sqlSize);
  309. $this->getResultSize($sqlSize);
  310. $perfSize->stop();
  311. $perfData->start($sqlData);
  312. $a = $this->sendResult($sqlData);
  313. $perfData->stop();
  314. $this->aPerf[] = $perfSize;
  315. $this->aPerf[] = $perfData;
  316. return $a;
  317. }
  318. // function
  319. function getFiltered($host = null, $level = null, $from = 0,
  320. $count = LggrState::PAGELEN) {
  321. $perfSize = new LggrPerf();
  322. $perfData = new LggrPerf();
  323. $v = $this->getViewName();
  324. $sqlSize = "SELECT COUNT(*) AS c FROM $v";
  325. $sqlData = "SELECT * FROM $v";
  326. $aWhere = array();
  327. if (null != $host) {
  328. $sTmp = $this->db->escape_string($host);
  329. $aWhere[] = "host='$sTmp'";
  330. } // if
  331. if (null != $level) {
  332. $sTmp = $this->db->escape_string($level);
  333. $aWhere[] = "level='$sTmp'";
  334. } // if
  335. if (count($aWhere) > 0) {
  336. $sqlSize .= " WHERE " . implode(Lggr::INNERAND, $aWhere);
  337. $sqlData .= " WHERE " . implode(Lggr::INNERAND, $aWhere);
  338. } // if
  339. $sqlData .= " ORDER BY `date` DESC LIMIT $from,$count";
  340. $perfSize->start($sqlSize);
  341. $this->getResultSize($sqlSize);
  342. $perfSize->stop();
  343. $perfData->start($sqlData);
  344. $a = $this->sendResult($sqlData);
  345. $perfData->stop();
  346. $this->aPerf[] = $perfSize;
  347. $this->aPerf[] = $perfData;
  348. return $a;
  349. }
  350. // function
  351. function getText($msg = '', $prog = '', $from = 0, $count = LggrState::PAGELEN) {
  352. $perf = new LggrPerf();
  353. $v = $this->getViewName();
  354. $sTmpMsg = $this->db->escape_string($msg);
  355. $sTmpProg = $this->db->escape_string($prog);
  356. $aWhere = array();
  357. if ('' != $msg) {
  358. $aWhere[] = "message LIKE '%{$sTmpMsg}%'";
  359. } // if
  360. if ('' != $prog) {
  361. $aWhere[] = "program LIKE '%{$sTmpProg}%'";
  362. } // if
  363. $sWhere = implode(' AND ', $aWhere);
  364. $sql = "
  365. SELECT * FROM $v
  366. WHERE $sWhere
  367. ORDER BY `date` DESC
  368. LIMIT $from,$count";
  369. $perf->start($sql);
  370. $a = $this->sendResult($sql);
  371. $perf->stop();
  372. $this->aPerf[] = $perf;
  373. return $a;
  374. }
  375. // function
  376. function getMessagesPerHour() {
  377. $perf = new LggrPerf();
  378. $sql = "
  379. SELECT HOUR(TIME(`date`)) AS h, COUNT(*) AS c
  380. FROM today
  381. GROUP BY h";
  382. $a = $this->cache->retrieve('mph');
  383. if (null != $a) {
  384. return $a;
  385. } // if
  386. $perf->start($sql);
  387. $a = $this->sendResult($sql);
  388. $perf->stop();
  389. $this->aPerf[] = $perf;
  390. $this->cache->store('mph', $a);
  391. return $a;
  392. }
  393. // function
  394. function getArchivedStatistic() {
  395. $perf = new LggrPerf();
  396. $sql = "
  397. SELECT COUNT(*) AS cnt
  398. FROM archived
  399. ";
  400. $a = $this->cache->retrieve('archivedstats');
  401. if (null != $a) {
  402. return $a;
  403. } // if
  404. $perf->start($sql);
  405. $a = $this->sendResult($sql);
  406. $perf->stop();
  407. $this->aPerf[] = $perf;
  408. $this->cache->store('archivedstats', $a);
  409. return $a;
  410. }
  411. // function
  412. function getStatistic() {
  413. $perf = new LggrPerf();
  414. $sql = "
  415. SELECT COUNT(*) AS cnt, MIN(`date`) AS oldest
  416. FROM newlogs
  417. ";
  418. $a = $this->cache->retrieve('stats');
  419. if (null != $a) {
  420. return $a;
  421. } // if
  422. $perf->start($sql);
  423. $a = $this->sendResult($sql);
  424. $perf->stop();
  425. $this->aPerf[] = $perf;
  426. $this->cache->store('stats', $a);
  427. return $a;
  428. }
  429. // function
  430. /* delete anything older than maxage hours, or 4 weeks */
  431. function purgeOldMessages($maxage = 672) {
  432. $perf = new LggrPerf();
  433. $sql = "
  434. DELETE FROM newlogs
  435. WHERE `date` < (NOW() - INTERVAL $maxage hour)
  436. AND archived='N'
  437. ";
  438. $perf->start($sql);
  439. $res = $this->db->query($sql);
  440. if (false === $res) {
  441. throw new LggrException($this->db->error);
  442. } // if
  443. $perf->stop();
  444. $this->aPerf[] = $perf;
  445. return $this->db->affected_rows;
  446. }
  447. // function
  448. function updateServers() {
  449. $perf = new LggrPerf();
  450. $iCount = 0;
  451. // First, get list of all known servers
  452. $sql = "SELECT id,name FROM servers";
  453. $perf->start($sql);
  454. $aServersKnownObj = $this->sendResult($sql);
  455. $perf->stop();
  456. $this->aPerf[] = $perf;
  457. $aServersKnown = array();
  458. foreach ($aServersKnownObj as $obj) {
  459. $aServersKnown[$obj->name] = $obj->id;
  460. } // foreach
  461. // Second, get list of all used servers in logs
  462. $sql = "SELECT DISTINCT host FROM newlogs";
  463. $perf->start($sql);
  464. $aServersUsed = $this->sendResult($sql);
  465. $perf->stop();
  466. $this->aPerf[] = $perf;
  467. // Third, Look for servers not yet in the known servers list
  468. foreach ($aServersUsed as $obj) {
  469. $sName = $obj->host;
  470. if (array_key_exists($sName, $aServersKnown)) {
  471. // already existing
  472. } else {
  473. $sName = $this->db->escape_string($sName);
  474. $sql = "INSERT INTO servers SET name='$sName'";
  475. $res = $this->db->query($sql);
  476. if (false === $res) {
  477. throw new LggrException($this->db->error);
  478. } // if
  479. $iCount ++;
  480. } // if
  481. } // foreach
  482. // Fourth, add foreign key of server to new entries with null foreign key
  483. foreach ($aServersKnown as $sName => $sID) {
  484. $sName = $this->db->escape_string($sName);
  485. $sql = "UPDATE newlogs SET idhost=$sID WHERE host='$sName' AND idhost IS NULL";
  486. $res = $this->db->query($sql);
  487. if (false === $res) {
  488. throw new LggrException($this->db->error);
  489. } // if
  490. $iCount += $this->db->affected_rows;
  491. } // foreach
  492. // return to caller
  493. return $iCount;
  494. }
  495. function setArchive($iID, $bIsArchived) {
  496. $iID = intval($iID);
  497. if ($bIsArchived) {
  498. $sArchive = 'Y';
  499. } else {
  500. $sArchive = 'N';
  501. } // if
  502. $sql = "UPDATE newlogs SET archived='$sArchive' WHERE id=$iID LIMIT 1";
  503. $res = $this->db->query($sql);
  504. if (false === $res) {
  505. throw new LggrException($this->db->error);
  506. } // if
  507. $this->cache->purge(ARCHIVEDSIZE);
  508. $this->cache->purge("archivedData0");
  509. }
  510. // function
  511. function normalizeHosts() {
  512. // Find any new hostnames
  513. $sql = "
  514. SELECT newlogs.host
  515. FROM newlogs
  516. LEFT JOIN hosts ON hosts.name=newlogs.host
  517. WHERE hosts.id IS NULL
  518. GROUP BY newlogs.host";
  519. $aEmpty = $this->sendResult($sql);
  520. foreach ($aEmpty as $o) {
  521. $host = $o->host;
  522. $host = $this->db->escape_string($host);
  523. $sql = "INSERT INTO hosts (name) VALUES ('$host')";
  524. $res = $this->db->query($sql);
  525. if (false === $res) {
  526. throw new LggrException($this->db->error);
  527. } // if
  528. $id = $this->db->insert_id;
  529. $sql = "UPDATE newlogs SET idhost=$id WHERE host='$host'";
  530. $res = $this->db->query($sql);
  531. if (false === $res) {
  532. throw new LggrException($this->db->error);
  533. } // if
  534. } // foreach
  535. // read current list of hostnames and ids
  536. $sql = "
  537. SELECT *
  538. FROM hosts";
  539. $aTmp = $this->sendResult($sql);
  540. $aHosts = array();
  541. foreach ($aTmp as $o) {
  542. $hostId = $o->id;
  543. $hostName = $o->name;
  544. $aHosts[$hostName] = $hostId;
  545. } // foreach
  546. // search any new entry without hostid and update it
  547. foreach ($aHosts as $hostName => $hostId) {
  548. $hostName = $this->db->escape_string($hostName);
  549. $sql = "
  550. UPDATE newlogs
  551. SET idhost=$hostId
  552. WHERE idhost IS NULL
  553. AND host='$hostName'
  554. ";
  555. $res = $this->db->query($sql);
  556. if (false === $res) {
  557. throw new LggrException($this->db->error);
  558. } // if
  559. } // foreach
  560. }
  561. // function
  562. private function getResultSize($sql) {
  563. $res = $this->db->query($sql);
  564. if (false === $res) {
  565. throw new LggrException($this->db->error);
  566. } // if
  567. if ($row = $res->fetch_object()) {
  568. $i = $row->c;
  569. $this->state->setResultSize($i);
  570. } // if
  571. $res->close();
  572. }
  573. // function
  574. private function sendResult($sql) {
  575. $a = array();
  576. $res = $this->db->query($sql);
  577. if (false === $res) {
  578. throw new LggrException($this->db->error);
  579. } // if
  580. while ($row = $res->fetch_object()) {
  581. $a[] = $row;
  582. } // while
  583. $res->close();
  584. return $a;
  585. }
  586. // function
  587. public function getPerf() {
  588. return $this->aPerf;
  589. } // function
  590. } // class
Social stuff:
Mastodon