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 /*! \brief This method tries to connect the GOsa-stats database and
17 * then returns a database handle on success else NULL.
18 *
19 * (The GOsa-stats database has to be enabled : statsDatabaseEnabled/statsDatabaseFile)
20 *
21 * This database will then contain information about the use of GOsa,
22 * no customer data will be stored.
23 *
24 * @return handle Returns a sqlite database handle.
25 */
26 static function getDatabaseHandle()
27 {
28 // Try to return last valid handle.
29 if(stats::$lastHandle != NULL && is_resource(stats::$lastHandle)){
30 return(stats::$lastHandle);
31 }
33 // Check if Logging is enabled
34 global $config;
35 if(!is_object($config) || ! $config instanceOf config){
36 return(NULL);
37 }
39 // Get statsFile property
40 stats::$tableFile = $config->get_cfg_value('core', 'statsDatabaseFile');
41 stats::$statsEnabled = $config->boolValueIsTrue('core', 'statsDatabaseEnabled');
42 if(!stats::$statsEnabled){
43 return;
44 }
46 // Append a date suffix to the database file, to prevent huge and unusable database files.
47 stats::$tableFile.= date('-Y_m_d');
49 // Check for SQLite extension
50 if(!stats::checkSQLiteExtension()){
51 return(NULL);
52 }
54 // Check if we are able to read/write the given database file.
55 if(!is_writeable(stats::$tableFile) && !is_writeable(dirname(stats::$tableFile))){
56 return(NULL);
57 }
59 // Try to create database, if it exists just open it.
60 $handle = sqlite_popen(stats::$tableFile, 0666, $error);
61 if($handle){
62 stats::createDatabaseOnDemand($handle);
63 }
64 stats::$lastHandle = $handle;
65 return($handle);
66 }
69 /*! \brief Check whether the qlite extension is available or not.
70 * @return boolean TRUE on success else FALSE
71 */
72 static function checkSQLiteExtension()
73 {
74 return(function_exists('sqlite_popen'));
75 }
78 /*! \brief Drops the current stats table and thus enforces a recreation.
79 * @param handle The database handle to use.
80 */
81 static function dropTable($handle)
82 {
83 $TABLE_NAME = stats::$tableName;
84 $query = "DROP TABLE '{$TABLE_NAME}'";
85 $ret = sqlite_query($query, $handle);
86 stats::$lastHandle = NULL;
87 stats::getDatabaseHandle();
88 }
91 /*! \brief Returns the currently used amount of memory form the PHP process.
92 * @return int The amount of bytes used for the PHP process.
93 */
94 static function get_memory_usage()
95 {
96 return(memory_get_usage());
97 }
100 /*! \brief Returns the current CPU load.
101 * The result will be cached and one updated every 5 seconds.
102 * @return float The current 'cpu_load'.
103 */
104 static function get_cpu_load()
105 {
106 $cur = time();
107 if(empty(stats::$lastCpuLoad) || (($cur - stats::$lastCpuLoadTimestamp) >= 5 )){
108 list($one, $five, $ten) =preg_split("/ /",shell_exec('cat /proc/loadavg'));
109 stats::$lastCpuLoad = $one;
110 stats::$lastCpuLoadTimestamp = $cur;
111 }
112 return(stats::$lastCpuLoad);
113 }
116 /*! \brief This method checks if the 'stats' table is already present,
117 * if it is not then it will be created.
118 * @param handle The sqlite database handle
119 */
120 static function createDatabaseOnDemand($handle)
121 {
122 $TABLE_NAME = stats::$tableName;
124 // List Tables an check if there is already everything we need,
125 // if not create it.
126 $query = "SELECT name FROM sqlite_master WHERE type='table' and name='{$TABLE_NAME}'";
127 $ret = sqlite_query($query, $handle);
128 if(!count(sqlite_fetch_all($ret))){
129 $query = "
130 CREATE TABLE {$TABLE_NAME} (
131 ID INTEGER PRIMARY KEY,
132 ACTID INTEGER,
133 TYPE TEXT,
134 PLUGIN TEXT,
135 CATEGORY TEXT,
136 ACTION TEXT,
137 UUID TEXT,
138 TIMESTAMP INTEGER,
139 MTIMESTAMP REAL,
140 DURATION REAL,
141 RENDER_TIME REAL,
142 AMOUNT INTEGER,
143 MEMORY_USAGE INTEGER,
144 CPU_LOAD FLOAT,
145 INFO BLOB
146 )";
147 $ret = sqlite_query($query, $handle);
148 }
149 }
152 /*! \brief Creates a new 'stats' table entry.
153 * -> Logs a GOsa action/activity in the sqlite stats table.
154 * @param string type The action type, e.g. ldap/plugin/management
155 * @param string plugin The plugin name, e.g. userManagement/user/posixAccount
156 * @param string category The plugin category e.g. users/servers/groups
157 * @param string action The action done e.g. edit/view/open/move
158 * @param int amount The amount, e.g. for multiple edit
159 * @param float duration The elapsed time.
160 * @param string info Some infos form the action, e.g. the used hashing mehtod for pwd changes.
161 */
162 static function log($type, $plugin, $category, $action, $amount = 1, $duration = 0, $info ='')
163 {
164 global $config;
165 global $clicks;
166 global $overallRenderTimer;
168 // Get database handle, if it is invalid (NULL) return without creating stats
169 $res = stats::getDatabaseHandle();
170 if(!$res) return;
172 // Ensure that 'clicks' and 'overallRenderTimer' are present and set correctly,
173 // if not simply create them with dummy values...
174 // -- 'clicks' is a counter wich is set in main.php -> Number of page reloads
175 // -- 'overallRenderTimer' is set in main.php -> timestamp of rendering start.
176 if(!isset($clicks) || empty($clicks)) $clicks = 0;
177 if(!isset($overallRenderTimer)){
178 $renderTime = 0;
179 }else{
180 $renderTime = microtime(TRUE) - $overallRenderTimer;
182 // Now set the overallRenderTimer to the current timestamp - else
183 // we will not be able to sum up the render time in a single SQL statement.
184 $overallRenderTimer = microtime(TRUE);
185 }
187 $duration = stats::prepareFloatForWriting($duration);
188 $renderTime = stats::prepareFloatForWriting($renderTime);
190 // Prepare values to be useable within a database
191 $uuid = $config->getGOsaUUID();
192 $type = sqlite_escape_string($type);
193 $plugin = sqlite_escape_string($plugin);
194 $action = sqlite_escape_string($action);
195 $timestamp = time();
196 $mtimestamp = microtime(TRUE);
197 $amount = sqlite_escape_string($amount);
198 $duration = sqlite_escape_string($duration);
199 $renderTime = sqlite_escape_string($renderTime);
200 $info = sqlite_escape_string($info);
201 $clicks = sqlite_escape_string($clicks);
202 $memory_usage = sqlite_escape_string(stats::get_memory_usage());
203 $cpu_load = sqlite_escape_string(sprintf("%0.6f",stats::get_cpu_load()));
205 // Clean up category, which usally comes from acl_category and may still contain
206 // some special chars like /
207 $tmp = array();
208 foreach($category as $cat){
209 $tmp[] = trim($cat, '\/,; ');
210 }
211 $category = sqlite_escape_string(implode($tmp, ', '));
213 // Create insert statement.
214 $TABLE_NAME = stats::$tableName;
215 $query = "
216 INSERT INTO {$TABLE_NAME}
217 (ACTID, TYPE, PLUGIN, CATEGORY, ACTION, UUID, MTIMESTAMP, TIMESTAMP,
218 AMOUNT, DURATION, RENDER_TIME, MEMORY_USAGE, CPU_LOAD, INFO)
219 VALUES
220 ('{$clicks}','{$type}','{$plugin}','{$category}','{$action}','{$uuid}',
221 '{$mtimestamp}','{$timestamp}','{$amount}','{$duration}','{$renderTime}',
222 '{$memory_usage}','{$cpu_load}','{$info}')";
223 sqlite_query($query, $res);
224 }
227 /*! \brief This method returns all entries of the GOsa-stats table.
228 * You can limit the result by setting the from/to parameter (timestamp).
229 * @param int from The timestamp to start the result from.
230 * @param int to The timestamp to end the request.
231 * @return array An array containing the requested entries.
232 */
233 static function dumpTables($from = NULL, $to = NULL)
234 {
235 // Get database connection
236 $TABLE_NAME = stats::$tableName;
237 $handle = stats::getDatabaseHandle();
238 if(!$handle) return;
240 // Build up filter to limit dumped entries to the given range.
241 $tim = "";
242 if($from != NULL){
243 $from = sqlite_escape_string($from);
244 $tim.= "AND TIMESTAMP >= '{$from}' ";
245 }
246 if($to != NULL){
247 $to = sqlite_escape_string($to);
248 $tim.= "AND TIMESTAMP <= '{$to}' ";
249 }
250 $tim = preg_replace("/^AND /"," WHERE ",$tim);
252 // Create Filter and start query
253 $filter = "SELECT * FROM {$TABLE_NAME}{$tim} ORDER BY ID";
254 $ret = sqlite_array_query($filter, $handle, SQLITE_ASSOC);
255 return($ret);
256 }
259 /*! \brief This is just a dummy output/debug method
260 * It directly prints some stats and table infos on the screen.
261 */
262 static function show()
263 {
264 $res = stats::getDatabaseHandle();
265 # stats::dropTable($res);
266 if(!$res) return;
268 $TABLE_NAME = stats::$tableName;
269 $query = "SELECT * FROM {$TABLE_NAME} ORDER BY MTIMESTAMP";
270 $query = "SELECT PLUGIN, ACTION, MAX(DURATION) as 'DURATION' FROM {$TABLE_NAME} WHERE ACTION='modify' GROUP BY PLUGIN,ACTION ";
271 $query = "SELECT * FROM {$TABLE_NAME} ORDER BY ID DESC LIMIT 30";
272 $query = "SELECT * FROM {$TABLE_NAME} WHERE plugin != 'LDAP' ORDER BY ID DESC LIMIT 30";
273 $ret = sqlite_query($query, $res);
274 echo "<pre>";
276 $colSize = 16;
278 $title = FALSE;
279 foreach(sqlite_fetch_all($ret) as $entry){
280 if(!$title){
281 foreach($entry as $key => $str) {
282 if(is_numeric($key)) continue;
283 echo str_pad($key,$colSize,' ')."|";
284 }
285 echo "\n";
286 foreach($entry as $key => $str) {
287 if(is_numeric($key)) continue;
288 echo str_pad('',$colSize,'-')."-";
289 }
290 echo "\n";
291 $title = TRUE;
292 }
294 foreach($entry as $key => $str){
295 if(is_numeric($key)) continue;
296 if($key == "DURATION" || $key == "MTIMESTAMP" || $key == "CPU_LOAD"){
297 $str = sprintf("%0.4f", preg_replace("/,/",".",$str));
298 echo str_pad($str,$colSize,' ', STR_PAD_LEFT)."|";
299 }else{
300 echo str_pad($str,$colSize,' ')."|";
301 }
302 }
303 echo "\n";
304 }
305 echo sqlite_error_string($ret);
307 echo "\n------ \n";
308 echo "Time spent per plugin-category \n";
309 echo "------ \n";
311 $query = "
312 SELECT SUM(DURATION) AS DUR, CATEGORY
313 FROM {$TABLE_NAME}
314 WHERE plugin != 'LDAP'
315 GROUP BY CATEGORY
316 ORDER BY DUR DESC LIMIT 10";
317 $ret = sqlite_query($query, $res);
319 $colSize = 16;
320 $title = FALSE;
321 foreach(sqlite_fetch_all($ret) as $entry){
322 foreach($entry as $key => $str){
323 if(is_numeric($key)) continue;
324 echo str_pad($str,$colSize,' ')."|";
325 }
326 echo "\n";
327 }
330 echo sqlite_error_string($ret);
332 echo "\n------ \n";
333 echo "Time spent per plugin \n";
334 echo "------ \n";
336 $query = "
337 SELECT SUM(DURATION) AS DUR, PLUGIN, UUID
338 FROM {$TABLE_NAME}
339 WHERE plugin != 'LDAP'
340 GROUP BY PLUGIN, UUID
341 ORDER BY DUR DESC LIMIT 10";
342 $ret = sqlite_query($query, $res);
344 $colSize = 16;
345 $title = FALSE;
346 foreach(sqlite_fetch_all($ret) as $entry){
347 foreach($entry as $key => $str){
348 if(is_numeric($key)) continue;
349 echo str_pad($str,$colSize,' ')."|";
350 }
351 echo "\n";
352 }
353 echo sqlite_error_string($ret);
355 # * Anzahl von Objekttypen
356 # * Anzahl der Löschungen pro Objekttyp
357 # * Anzahl der Erzeugungen pro Objekttyp
358 # * Anzahl der Bearbeitungen pro Objekttyp
359 # * Anzahl der Verschiebungen pro Objekttyp
360 # * Anzahl der Mehrfachbearbeitungen pro Objekttyp
361 # * Antwortzeiten pro aktion
362 # * Anzahl der Passwortänderungen
363 # * Anzahl der unterschiedlichen Anmeldungen
366 echo "\n------ \n";
367 echo "Actions done per plugin \n";
368 echo "------ \n";
370 $query = "
371 SELECT COUNT(ACTION) as CNT, ACTION, PLUGIN
372 FROM {$TABLE_NAME}
373 WHERE TYPE = 'plugin'
374 AND PLUGIN != 'LDAP'
375 GROUP BY ACTION,PLUGIN
376 ORDER BY CNT DESC LIMIT 30";
377 $ret = sqlite_query($query, $res);
379 $colSize = 16;
380 $title = FALSE;
381 foreach(sqlite_fetch_all($ret) as $entry){
382 foreach($entry as $key => $str){
383 if(is_numeric($key)) continue;
384 echo str_pad($str,$colSize,' ')."|";
385 }
386 echo "\n";
387 }
388 echo sqlite_error_string($ret);
390 echo "\n------ \n";
391 echo "'create' actions done per plugin (5 most)\n";
392 echo "------ \n";
394 $query = "
395 SELECT COUNT(ACTION) as CNT, ACTION, PLUGIN
396 FROM {$TABLE_NAME}
397 WHERE TYPE = 'plugin'
398 AND PLUGIN != 'LDAP'
399 AND ACTION = 'create'
400 GROUP BY ACTION,PLUGIN
401 ORDER BY CNT DESC LIMIT 5";
402 $ret = sqlite_query($query, $res);
404 $colSize = 16;
405 $title = FALSE;
406 foreach(sqlite_fetch_all($ret) as $entry){
407 foreach($entry as $key => $str){
408 if(is_numeric($key)) continue;
409 echo str_pad($str,$colSize,' ')."|";
410 }
411 echo "\n";
412 }
413 echo sqlite_error_string($ret);
415 echo "\n------ \n";
416 echo "'move' actions done per plugin (5 most)\n";
417 echo "------ \n";
419 $query = "
420 SELECT COUNT(ACTION) as CNT, ACTION, PLUGIN
421 FROM {$TABLE_NAME}
422 WHERE TYPE = 'plugin'
423 AND PLUGIN != 'LDAP'
424 AND ACTION = 'move'
425 GROUP BY ACTION,PLUGIN
426 ORDER BY CNT DESC LIMIT 5";
427 $ret = sqlite_query($query, $res);
429 $colSize = 16;
430 $title = FALSE;
431 foreach(sqlite_fetch_all($ret) as $entry){
432 foreach($entry as $key => $str){
433 if(is_numeric($key)) continue;
434 echo str_pad($str,$colSize,' ')."|";
435 }
436 echo "\n";
437 }
438 echo sqlite_error_string($ret);
440 echo "\n------ \n";
441 echo "'view' actions done per plugin (5 most)\n";
442 echo "------ \n";
444 $query = "
445 SELECT COUNT(ACTION) as CNT, ACTION, PLUGIN
446 FROM {$TABLE_NAME}
447 WHERE TYPE = 'plugin'
448 AND PLUGIN != 'LDAP'
449 AND ACTION = 'view'
450 GROUP BY ACTION,PLUGIN
451 ORDER BY CNT DESC LIMIT 5";
452 $ret = sqlite_query($query, $res);
454 $colSize = 16;
455 $title = FALSE;
456 foreach(sqlite_fetch_all($ret) as $entry){
457 foreach($entry as $key => $str){
458 if(is_numeric($key)) continue;
459 echo str_pad($str,$colSize,' ')."|";
460 }
461 echo "\n";
462 }
463 echo sqlite_error_string($ret);
465 echo "\n------ \n";
466 echo "'open' actions done per plugin (5 most)\n";
467 echo "------ \n";
469 $query = "
470 SELECT COUNT(ACTION) as CNT, ACTION, PLUGIN, UUID
471 FROM {$TABLE_NAME}
472 WHERE TYPE = 'plugin'
473 AND PLUGIN != 'LDAP'
474 AND ACTION = 'open'
475 GROUP BY ACTION,PLUGIN, UUID
476 ORDER BY CNT DESC LIMIT 5";
477 $ret = sqlite_query($query, $res);
479 $colSize = 16;
480 $title = FALSE;
481 foreach(sqlite_fetch_all($ret) as $entry){
482 foreach($entry as $key => $str){
483 if(is_numeric($key)) continue;
484 echo str_pad($str,$colSize,' ')."|";
485 }
486 echo "\n";
487 }
488 echo sqlite_error_string($ret);
490 echo "\n------ \n";
491 echo "Time per session\n";
492 echo "------ \n";
494 $query = "
495 SELECT SUM(DURATION) as DUR, UUID
496 FROM {$TABLE_NAME}
497 GROUP BY UUID
498 ORDER BY DUR DESC LIMIT 10";
499 $ret = sqlite_query($query, $res);
501 $colSize = 16;
502 $title = FALSE;
503 foreach(sqlite_fetch_all($ret) as $entry){
504 foreach($entry as $key => $str){
505 if(is_numeric($key)) continue;
506 echo str_pad($str,$colSize,' ')."|";
507 }
508 echo "\n";
509 }
510 echo sqlite_error_string($ret);
512 echo "\n------ \n";
513 echo "Most used password hashes\n";
514 echo "------ \n";
516 $query = "
517 SELECT DISTINCT(INFO), COUNT(INFO) as CNT
518 FROM {$TABLE_NAME}
519 WHERE ACTION = 'change_password'
520 GROUP BY INFO
521 ORDER BY INFO DESC LIMIT 10";
522 $ret = sqlite_query($query, $res);
524 $colSize = 16;
525 $title = FALSE;
526 foreach(sqlite_fetch_all($ret) as $entry){
527 foreach($entry as $key => $str){
528 if(is_numeric($key)) continue;
529 echo str_pad($str,$colSize,' ')."|";
530 }
531 echo "\n";
532 }
533 echo sqlite_error_string($ret);
535 echo "\n------ \n";
536 echo "Actions at all\n";
537 echo "------ \n";
539 $query = "
540 SELECT DISTINCT(ACTION), COUNT(ACTION) AS CNT
541 FROM {$TABLE_NAME}
542 GROUP BY ACTION
543 ORDER BY CNT DESC LIMIT 10";
544 $ret = sqlite_query($query, $res);
546 $colSize = 16;
547 $title = FALSE;
548 foreach(sqlite_fetch_all($ret) as $entry){
549 foreach($entry as $key => $str){
550 if(is_numeric($key)) continue;
551 echo str_pad($str,$colSize,' ')."|";
552 }
553 echo "\n";
554 }
555 echo sqlite_error_string($ret);
557 echo "\n------ \n";
558 echo "Time spent per action\n";
559 echo "------ \n";
561 $query = "
562 SELECT DISTINCT(ACTION), SUM(DURATION) AS DUR
563 FROM {$TABLE_NAME}
564 GROUP BY ACTION
565 ORDER BY DUR DESC LIMIT 10";
566 $ret = sqlite_query($query, $res);
568 $colSize = 16;
569 $title = FALSE;
570 foreach(sqlite_fetch_all($ret) as $entry){
571 foreach($entry as $key => $str){
572 if(is_numeric($key)) continue;
573 echo str_pad($str,$colSize,' ')."|";
574 }
575 echo "\n";
576 }
577 echo sqlite_error_string($ret);
579 echo "\n------ \n";
580 echo "Average time per action\n";
581 echo "------ \n";
583 $query = "
584 SELECT DISTINCT(ACTION), AVG(DURATION) AS DUR
585 FROM {$TABLE_NAME}
586 GROUP BY ACTION
587 ORDER BY DUR DESC LIMIT 10";
588 $ret = sqlite_query($query, $res);
590 $colSize = 16;
591 $title = FALSE;
592 foreach(sqlite_fetch_all($ret) as $entry){
593 foreach($entry as $key => $str){
594 if(is_numeric($key)) continue;
595 echo str_pad($str,$colSize,' ')."|";
596 }
597 echo "\n";
598 }
599 echo sqlite_error_string($ret);
602 echo "\n------ \n";
603 echo "Rendertime per plugin\n";
604 echo "------ \n";
606 $query = "
607 SELECT PLUGIN, RENDER_TIME AS RM
608 FROM {$TABLE_NAME}
609 GROUP BY PLUGIN
610 ORDER BY RM DESC
611 LIMIT 10
612 ";
613 $ret = sqlite_query($query, $res);
615 $colSize = 16;
616 $title = FALSE;
617 foreach(sqlite_fetch_all($ret) as $entry){
618 foreach($entry as $key => $str){
619 if(is_numeric($key)) continue;
620 echo str_pad($str,$colSize,' ')."|";
621 }
622 echo "\n";
623 }
624 echo sqlite_error_string($ret);
626 echo "</pre>";
627 }
630 /*! \brief Somehow sqlite can not work with float values when it comes to AVG() SUM().
631 * We use this methods to convert float values to int and vice versa.
632 * The database will then contain 'int' intead of 'float'.
633 * prepareFloatForReading -> Used for reading 'float' values.
634 * prepareFloatForWriting -> Used for writing 'float' values.
635 * @param float The 'float' value to convert.
636 * @return int The converted float value.
637 */
638 static function prepareFloatForWriting($float)
639 {
640 return(floor($float * 1000));
641 }
645 /*! \brief Somehow sqlite can not work with float values when it comes to AVG() SUM().
646 * We use this methods to convert float values to int and vice versa.
647 * The database will then contain 'int' intead of 'float'.
648 * prepareFloatForWriting -> Used for writing 'float' values.
649 * @param float The 'int' value read from the table.
650 * @return int The converted int value.
651 */
652 static function prepareFloatForReading($int)
653 {
654 return($int / 1000);
655 }
656 }
658 ?>