1 <?php
3 class stats
4 {
6 static protected $lastCpuLoad = "";
7 static protected $lastCpuLoadTimestamp = 0;
9 static protected $tableName = "stats";
10 static protected $tableFile = "/var/spool/gosa/stats";
12 static protected $lastHandle = NULL;
13 static protected $statsEnabled = FALSE;
16 static function prepareFloatForWriting($float){
17 return(floor($float * 1000));
18 }
20 static function prepareFloatForReading($int){
21 return($int / 1000);
22 }
24 static function getDatabaseHandle()
25 {
26 // Try to return last valid handle.
27 if(stats::$lastHandle != NULL && is_resource(stats::$lastHandle)){
28 return(stats::$lastHandle);
29 }
31 // Check if Logging is enabled
32 global $config;
33 if(!is_object($config) || ! $config instanceOf config){
34 return(NULL);
35 }
37 // Get statsFile property
38 stats::$tableFile = $config->get_cfg_value('core', 'statsDatabaseFile');
39 stats::$statsEnabled = $config->boolValueIsTrue('core', 'statsDatabaseEnabled');
40 if(!stats::$statsEnabled){
41 return;
42 }
44 // Check for SQLite extension
45 if(!stats::checkSQLiteExtension()){
46 return(NULL);
47 }
49 // Check if we are able to read/write the given database file.
50 if(!is_writeable(stats::$tableFile) && !is_writeable(dirname(stats::$tableFile))){
51 return(NULL);
52 }
54 // Try to create database, if it exists just open it.
55 $handle = sqlite_popen(stats::$tableFile, 0666, $error);
56 if($handle){
57 stats::createDatabaseOnDemand($handle);
58 }
59 stats::$lastHandle = $handle;
60 return($handle);
61 }
63 /*! \brief |
64 * @param |
65 * @return |
66 */
67 static function checkSQLiteExtension()
68 {
69 return(function_exists('sqlite_popen'));
70 }
73 /*! \brief |
74 * @param |
75 * @return |
76 */
77 static function dropTable($handle)
78 {
79 $TABLE_NAME = stats::$tableName;
80 $query = "DROP TABLE '{$TABLE_NAME}'";
81 $ret = sqlite_query($query, $handle);
82 stats::$lastHandle = NULL;
83 stats::getDatabaseHandle();
84 }
87 /*! \brief |
88 * @param |
89 * @return |
90 */
91 static function get_memory_usage()
92 {
93 return(memory_get_usage());
94 }
97 /*! \brief |
98 * @param |
99 * @return |
100 */
101 static function get_cpu_load()
102 {
103 $cur = time();
104 if(empty(stats::$lastCpuLoad) || (($cur - stats::$lastCpuLoadTimestamp) >=2 )){
105 list($one, $five, $ten) =preg_split("/ /",shell_exec('cat /proc/loadavg'));
106 stats::$lastCpuLoad = $one;
107 stats::$lastCpuLoadTimestamp = $cur;
108 }
109 return(stats::$lastCpuLoad);
110 }
113 /*! \brief |
114 * @param |
115 * @return |
116 */
117 static function createDatabaseOnDemand($handle)
118 {
119 $TABLE_NAME = stats::$tableName;
121 // List Tables an check if there is already everything we need,
122 // if not create it.
123 $query = "SELECT name FROM sqlite_master WHERE type='table' and name='{$TABLE_NAME}'";
124 $ret = sqlite_query($query, $handle);
125 if(!count(sqlite_fetch_all($ret))){
126 $query = "
127 CREATE TABLE {$TABLE_NAME} (
128 ID INTEGER PRIMARY KEY,
129 ACTID INTEGER,
130 TYPE TEXT,
131 PLUGIN TEXT,
132 CATEGORY TEXT,
133 ACTION TEXT,
134 UUID TEXT,
135 TIMESTAMP INTEGER,
136 MTIMESTAMP REAL,
137 DURATION REAL,
138 RENDER_TIME REAL,
139 AMOUNT INTEGER,
140 MEMORY_USAGE INTEGER,
141 CPU_LOAD FLOAT,
142 INFO BLOB
143 )";
144 $ret = sqlite_query($query, $handle);
145 }
146 }
149 /*! \brief |
150 * @param |
151 * @return |
152 */
153 static function log($type, $plugin, $category, $action, $amount = 1, $duration = 0, $info ='')
154 {
155 global $config;
156 global $clicks;
157 global $overallRenderTimer;
159 // Get database handle, if it is invalid (NULL) return without creating stats
160 $res = stats::getDatabaseHandle();
161 # stats::dropTable($res);
162 if(!$res) return;
164 // Ensure that 'clicks' and 'overallRenderTimer' are present and set correctly,
165 // if not simply create them with dummy values...
166 // -- 'clicks' is a counter wich is set in main.php -> Number of page reloads
167 // -- 'overallRenderTimer' is set in main.php -> timestamp of rendering start.
168 if(!isset($clicks) || empty($clicks)) $clicks = 0;
169 if(!isset($overallRenderTimer)){
170 $renderTime = 0;
171 }else{
172 $renderTime = microtime(TRUE) - $overallRenderTimer;
174 // Now set the overallRenderTimer to the current timestamp - else
175 // we will not be able to sum up the render time in a single SQL statement.
176 $overallRenderTimer = microtime(TRUE);
177 }
179 $duration = stats::prepareFloatForWriting($duration);
180 $renderTime = stats::prepareFloatForWriting($renderTime);
182 // Prepare values to be useable within a database
183 $uuid = $config->getGOsaUUID();
184 $type = sqlite_escape_string($type);
185 $plugin = sqlite_escape_string($plugin);
186 $action = sqlite_escape_string($action);
187 $timestamp = time();
188 $mtimestamp = microtime(TRUE);
189 $amount = sqlite_escape_string($amount);
190 $duration = sqlite_escape_string($duration);
191 $renderTime = sqlite_escape_string($renderTime);
192 $info = sqlite_escape_string($info);
193 $clicks = sqlite_escape_string($clicks);
194 $memory_usage = sqlite_escape_string(stats::get_memory_usage());
195 $cpu_load = sqlite_escape_string(sprintf("%0.6f",stats::get_cpu_load()));
197 // Clean up category, which usally comes from acl_category and may still contain
198 // some special chars like /
199 $tmp = array();
200 foreach($category as $cat){
201 $tmp[] = trim($cat, '\/,; ');
202 }
203 $category = sqlite_escape_string(implode($tmp, ', '));
205 // Create insert statement.
206 $TABLE_NAME = stats::$tableName;
207 $query = "
208 INSERT INTO {$TABLE_NAME}
209 (ACTID, TYPE, PLUGIN, CATEGORY, ACTION, UUID, MTIMESTAMP, TIMESTAMP,
210 AMOUNT, DURATION, RENDER_TIME, MEMORY_USAGE, CPU_LOAD, INFO)
211 VALUES
212 ('{$clicks}','{$type}','{$plugin}','{$category}','{$action}','{$uuid}',
213 '{$mtimestamp}','{$timestamp}','{$amount}','{$duration}','{$renderTime}',
214 '{$memory_usage}','{$cpu_load}','{$info}')";
215 sqlite_query($query, $res);
216 }
219 /*! \brief |
220 * @param |
221 * @return |
222 */
223 static function dumpTables($from = NULL, $to = NULL)
224 {
225 // Get database connection
226 $TABLE_NAME = stats::$tableName;
227 $handle = stats::getDatabaseHandle();
228 if(!$handle) return;
230 // Build up filter to limit dumped entries to the given range.
231 $tim = "";
232 if($from != NULL){
233 $from = sqlite_escape_string($from);
234 $tim.= "AND TIMESTAMP >= '{$from}' ";
235 }
236 if($to != NULL){
237 $to = sqlite_escape_string($to);
238 $tim.= "AND TIMESTAMP <= '{$to}' ";
239 }
240 $tim = preg_replace("/^AND /"," WHERE ",$tim);
242 // Create Filter and start query
243 $filter = "SELECT * FROM {$TABLE_NAME}{$tim} ORDER BY ID";
244 $ret = sqlite_array_query($filter, $handle, SQLITE_ASSOC);
245 return($ret);
246 }
249 /*! \brief |
250 * @param |
251 * @return |
252 */
253 static function show()
254 {
256 stats::dumpTables();
258 return;
260 $res = stats::getDatabaseHandle();
261 # stats::dropTable($res);
262 if(!$res) return;
264 $TABLE_NAME = stats::$tableName;
265 $query = "SELECT * FROM {$TABLE_NAME} ORDER BY MTIMESTAMP";
266 $query = "SELECT PLUGIN, ACTION, MAX(DURATION) as 'DURATION' FROM {$TABLE_NAME} WHERE ACTION='modify' GROUP BY PLUGIN,ACTION ";
267 $query = "SELECT * FROM {$TABLE_NAME} ORDER BY ID DESC LIMIT 30";
268 $query = "SELECT * FROM {$TABLE_NAME} WHERE plugin != 'LDAP' ORDER BY ID DESC LIMIT 30";
269 $ret = sqlite_query($query, $res);
270 echo "<pre>";
272 $colSize = 16;
274 $title = FALSE;
275 foreach(sqlite_fetch_all($ret) as $entry){
276 if(!$title){
277 foreach($entry as $key => $str) {
278 if(is_numeric($key)) continue;
279 echo str_pad($key,$colSize,' ')."|";
280 }
281 echo "\n";
282 foreach($entry as $key => $str) {
283 if(is_numeric($key)) continue;
284 echo str_pad('',$colSize,'-')."-";
285 }
286 echo "\n";
287 $title = TRUE;
288 }
290 foreach($entry as $key => $str){
291 if(is_numeric($key)) continue;
292 if($key == "DURATION" || $key == "MTIMESTAMP" || $key == "CPU_LOAD"){
293 $str = sprintf("%0.4f", preg_replace("/,/",".",$str));
294 echo str_pad($str,$colSize,' ', STR_PAD_LEFT)."|";
295 }else{
296 echo str_pad($str,$colSize,' ')."|";
297 }
298 }
299 echo "\n";
300 }
301 echo sqlite_error_string($ret);
303 echo "\n------ \n";
304 echo "Time spent per plugin-category \n";
305 echo "------ \n";
307 $query = "
308 SELECT SUM(DURATION) AS DUR, CATEGORY
309 FROM {$TABLE_NAME}
310 WHERE plugin != 'LDAP'
311 GROUP BY CATEGORY
312 ORDER BY DUR DESC LIMIT 10";
313 $ret = sqlite_query($query, $res);
315 $colSize = 16;
316 $title = FALSE;
317 foreach(sqlite_fetch_all($ret) as $entry){
318 foreach($entry as $key => $str){
319 if(is_numeric($key)) continue;
320 echo str_pad($str,$colSize,' ')."|";
321 }
322 echo "\n";
323 }
326 echo sqlite_error_string($ret);
328 echo "\n------ \n";
329 echo "Time spent per plugin \n";
330 echo "------ \n";
332 $query = "
333 SELECT SUM(DURATION) AS DUR, PLUGIN, UUID
334 FROM {$TABLE_NAME}
335 WHERE plugin != 'LDAP'
336 GROUP BY PLUGIN, UUID
337 ORDER BY DUR DESC LIMIT 10";
338 $ret = sqlite_query($query, $res);
340 $colSize = 16;
341 $title = FALSE;
342 foreach(sqlite_fetch_all($ret) as $entry){
343 foreach($entry as $key => $str){
344 if(is_numeric($key)) continue;
345 echo str_pad($str,$colSize,' ')."|";
346 }
347 echo "\n";
348 }
349 echo sqlite_error_string($ret);
351 # * Anzahl von Objekttypen
352 # * Anzahl der Löschungen pro Objekttyp
353 # * Anzahl der Erzeugungen pro Objekttyp
354 # * Anzahl der Bearbeitungen pro Objekttyp
355 # * Anzahl der Verschiebungen pro Objekttyp
356 # * Anzahl der Mehrfachbearbeitungen pro Objekttyp
357 # * Antwortzeiten pro aktion
358 # * Anzahl der Passwortänderungen
359 # * Anzahl der unterschiedlichen Anmeldungen
362 echo "\n------ \n";
363 echo "Actions done per plugin \n";
364 echo "------ \n";
366 $query = "
367 SELECT COUNT(ACTION) as CNT, ACTION, PLUGIN
368 FROM {$TABLE_NAME}
369 WHERE TYPE = 'plugin'
370 AND PLUGIN != 'LDAP'
371 GROUP BY ACTION,PLUGIN
372 ORDER BY CNT DESC LIMIT 30";
373 $ret = sqlite_query($query, $res);
375 $colSize = 16;
376 $title = FALSE;
377 foreach(sqlite_fetch_all($ret) as $entry){
378 foreach($entry as $key => $str){
379 if(is_numeric($key)) continue;
380 echo str_pad($str,$colSize,' ')."|";
381 }
382 echo "\n";
383 }
384 echo sqlite_error_string($ret);
386 echo "\n------ \n";
387 echo "'create' actions done per plugin (5 most)\n";
388 echo "------ \n";
390 $query = "
391 SELECT COUNT(ACTION) as CNT, ACTION, PLUGIN
392 FROM {$TABLE_NAME}
393 WHERE TYPE = 'plugin'
394 AND PLUGIN != 'LDAP'
395 AND ACTION = 'create'
396 GROUP BY ACTION,PLUGIN
397 ORDER BY CNT DESC LIMIT 5";
398 $ret = sqlite_query($query, $res);
400 $colSize = 16;
401 $title = FALSE;
402 foreach(sqlite_fetch_all($ret) as $entry){
403 foreach($entry as $key => $str){
404 if(is_numeric($key)) continue;
405 echo str_pad($str,$colSize,' ')."|";
406 }
407 echo "\n";
408 }
409 echo sqlite_error_string($ret);
411 echo "\n------ \n";
412 echo "'move' actions done per plugin (5 most)\n";
413 echo "------ \n";
415 $query = "
416 SELECT COUNT(ACTION) as CNT, ACTION, PLUGIN
417 FROM {$TABLE_NAME}
418 WHERE TYPE = 'plugin'
419 AND PLUGIN != 'LDAP'
420 AND ACTION = 'move'
421 GROUP BY ACTION,PLUGIN
422 ORDER BY CNT DESC LIMIT 5";
423 $ret = sqlite_query($query, $res);
425 $colSize = 16;
426 $title = FALSE;
427 foreach(sqlite_fetch_all($ret) as $entry){
428 foreach($entry as $key => $str){
429 if(is_numeric($key)) continue;
430 echo str_pad($str,$colSize,' ')."|";
431 }
432 echo "\n";
433 }
434 echo sqlite_error_string($ret);
436 echo "\n------ \n";
437 echo "'view' actions done per plugin (5 most)\n";
438 echo "------ \n";
440 $query = "
441 SELECT COUNT(ACTION) as CNT, ACTION, PLUGIN
442 FROM {$TABLE_NAME}
443 WHERE TYPE = 'plugin'
444 AND PLUGIN != 'LDAP'
445 AND ACTION = 'view'
446 GROUP BY ACTION,PLUGIN
447 ORDER BY CNT DESC LIMIT 5";
448 $ret = sqlite_query($query, $res);
450 $colSize = 16;
451 $title = FALSE;
452 foreach(sqlite_fetch_all($ret) as $entry){
453 foreach($entry as $key => $str){
454 if(is_numeric($key)) continue;
455 echo str_pad($str,$colSize,' ')."|";
456 }
457 echo "\n";
458 }
459 echo sqlite_error_string($ret);
461 echo "\n------ \n";
462 echo "'open' actions done per plugin (5 most)\n";
463 echo "------ \n";
465 $query = "
466 SELECT COUNT(ACTION) as CNT, ACTION, PLUGIN, UUID
467 FROM {$TABLE_NAME}
468 WHERE TYPE = 'plugin'
469 AND PLUGIN != 'LDAP'
470 AND ACTION = 'open'
471 GROUP BY ACTION,PLUGIN, UUID
472 ORDER BY CNT DESC LIMIT 5";
473 $ret = sqlite_query($query, $res);
475 $colSize = 16;
476 $title = FALSE;
477 foreach(sqlite_fetch_all($ret) as $entry){
478 foreach($entry as $key => $str){
479 if(is_numeric($key)) continue;
480 echo str_pad($str,$colSize,' ')."|";
481 }
482 echo "\n";
483 }
484 echo sqlite_error_string($ret);
486 echo "\n------ \n";
487 echo "Time per session\n";
488 echo "------ \n";
490 $query = "
491 SELECT SUM(DURATION) as DUR, UUID
492 FROM {$TABLE_NAME}
493 GROUP BY UUID
494 ORDER BY DUR DESC LIMIT 10";
495 $ret = sqlite_query($query, $res);
497 $colSize = 16;
498 $title = FALSE;
499 foreach(sqlite_fetch_all($ret) as $entry){
500 foreach($entry as $key => $str){
501 if(is_numeric($key)) continue;
502 echo str_pad($str,$colSize,' ')."|";
503 }
504 echo "\n";
505 }
506 echo sqlite_error_string($ret);
508 echo "\n------ \n";
509 echo "Most used password hashes\n";
510 echo "------ \n";
512 $query = "
513 SELECT DISTINCT(INFO), COUNT(INFO) as CNT
514 FROM {$TABLE_NAME}
515 WHERE ACTION = 'change_password'
516 GROUP BY INFO
517 ORDER BY INFO DESC LIMIT 10";
518 $ret = sqlite_query($query, $res);
520 $colSize = 16;
521 $title = FALSE;
522 foreach(sqlite_fetch_all($ret) as $entry){
523 foreach($entry as $key => $str){
524 if(is_numeric($key)) continue;
525 echo str_pad($str,$colSize,' ')."|";
526 }
527 echo "\n";
528 }
529 echo sqlite_error_string($ret);
531 echo "\n------ \n";
532 echo "Actions at all\n";
533 echo "------ \n";
535 $query = "
536 SELECT DISTINCT(ACTION), COUNT(ACTION) AS CNT
537 FROM {$TABLE_NAME}
538 GROUP BY ACTION
539 ORDER BY CNT DESC LIMIT 10";
540 $ret = sqlite_query($query, $res);
542 $colSize = 16;
543 $title = FALSE;
544 foreach(sqlite_fetch_all($ret) as $entry){
545 foreach($entry as $key => $str){
546 if(is_numeric($key)) continue;
547 echo str_pad($str,$colSize,' ')."|";
548 }
549 echo "\n";
550 }
551 echo sqlite_error_string($ret);
553 echo "\n------ \n";
554 echo "Time spent per action\n";
555 echo "------ \n";
557 $query = "
558 SELECT DISTINCT(ACTION), SUM(DURATION) AS DUR
559 FROM {$TABLE_NAME}
560 GROUP BY ACTION
561 ORDER BY DUR DESC LIMIT 10";
562 $ret = sqlite_query($query, $res);
564 $colSize = 16;
565 $title = FALSE;
566 foreach(sqlite_fetch_all($ret) as $entry){
567 foreach($entry as $key => $str){
568 if(is_numeric($key)) continue;
569 echo str_pad($str,$colSize,' ')."|";
570 }
571 echo "\n";
572 }
573 echo sqlite_error_string($ret);
575 echo "\n------ \n";
576 echo "Average time per action\n";
577 echo "------ \n";
579 $query = "
580 SELECT DISTINCT(ACTION), AVG(DURATION) AS DUR
581 FROM {$TABLE_NAME}
582 GROUP BY ACTION
583 ORDER BY DUR DESC LIMIT 10";
584 $ret = sqlite_query($query, $res);
586 $colSize = 16;
587 $title = FALSE;
588 foreach(sqlite_fetch_all($ret) as $entry){
589 foreach($entry as $key => $str){
590 if(is_numeric($key)) continue;
591 echo str_pad($str,$colSize,' ')."|";
592 }
593 echo "\n";
594 }
595 echo sqlite_error_string($ret);
598 echo "\n------ \n";
599 echo "Rendertime per plugin\n";
600 echo "------ \n";
602 $query = "
603 SELECT PLUGIN, RENDER_TIME AS RM
604 FROM {$TABLE_NAME}
605 GROUP BY PLUGIN
606 ORDER BY RM DESC
607 LIMIT 10
608 ";
609 $ret = sqlite_query($query, $res);
611 $colSize = 16;
612 $title = FALSE;
613 foreach(sqlite_fetch_all($ret) as $entry){
614 foreach($entry as $key => $str){
615 if(is_numeric($key)) continue;
616 echo str_pad($str,$colSize,' ')."|";
617 }
618 echo "\n";
619 }
620 echo sqlite_error_string($ret);
622 echo "</pre>";
623 }
624 }
627 ?>