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 // Check for SQLite extension
47 if(!stats::checkSQLiteExtension()){
48 return(NULL);
49 }
51 // Check if we are able to read/write the given database file.
52 if(!is_writeable(stats::$tableFile) && !is_writeable(dirname(stats::$tableFile))){
53 return(NULL);
54 }
56 // Try to create database, if it exists just open it.
57 $handle = sqlite_popen(stats::$tableFile, 0666, $error);
58 if($handle){
59 stats::createDatabaseOnDemand($handle);
60 }
61 stats::$lastHandle = $handle;
62 return($handle);
63 }
66 /*! \brief Check whether the qlite extension is available or not.
67 * @return boolean TRUE on success else FALSE
68 */
69 static function checkSQLiteExtension()
70 {
71 return(function_exists('sqlite_popen'));
72 }
75 /*! \brief Drops the current stats table and thus enforces a recreation.
76 * @param handle The database handle to use.
77 */
78 static function dropTable($handle)
79 {
80 $TABLE_NAME = stats::$tableName;
81 $query = "DROP TABLE '{$TABLE_NAME}'";
82 $ret = sqlite_query($query, $handle);
83 stats::$lastHandle = NULL;
84 stats::getDatabaseHandle();
85 }
88 /*! \brief Returns the currently used amount of memory form the PHP process.
89 * @return int The amount of bytes used for the PHP process.
90 */
91 static function get_memory_usage()
92 {
93 return(memory_get_usage());
94 }
97 /*! \brief Returns the current CPU load.
98 * The result will be cached and one updated every 5 seconds.
99 * @return float The current 'cpu_load'.
100 */
101 static function get_cpu_load()
102 {
103 $cur = time();
104 if(empty(stats::$lastCpuLoad) || (($cur - stats::$lastCpuLoadTimestamp) >= 5 )){
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 This method checks if the 'stats' table is already present,
114 * if it is not then it will be created.
115 * @param handle The sqlite database handle
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 Creates a new 'stats' table entry.
150 * -> Logs a GOsa action/activity in the sqlite stats table.
151 * @param string type The action type, e.g. ldap/plugin/management
152 * @param string plugin The plugin name, e.g. userManagement/user/posixAccount
153 * @param string category The plugin category e.g. users/servers/groups
154 * @param string action The action done e.g. edit/view/open/move
155 * @param int amount The amount, e.g. for multiple edit
156 * @param float duration The elapsed time.
157 * @param string info Some infos form the action, e.g. the used hashing mehtod for pwd changes.
158 */
159 static function log($type, $plugin, $category, $action, $amount = 1, $duration = 0, $info ='')
160 {
161 global $config;
162 global $clicks;
163 global $overallRenderTimer;
165 // Get database handle, if it is invalid (NULL) return without creating stats
166 $res = stats::getDatabaseHandle();
167 if(!$res) return;
169 // Ensure that 'clicks' and 'overallRenderTimer' are present and set correctly,
170 // if not simply create them with dummy values...
171 // -- 'clicks' is a counter wich is set in main.php -> Number of page reloads
172 // -- 'overallRenderTimer' is set in main.php -> timestamp of rendering start.
173 if(!isset($clicks) || empty($clicks)) $clicks = 0;
174 if(!isset($overallRenderTimer)){
175 $renderTime = 0;
176 }else{
177 $renderTime = microtime(TRUE) - $overallRenderTimer;
179 // Now set the overallRenderTimer to the current timestamp - else
180 // we will not be able to sum up the render time in a single SQL statement.
181 $overallRenderTimer = microtime(TRUE);
182 }
184 $duration = stats::prepareFloatForWriting($duration);
185 $renderTime = stats::prepareFloatForWriting($renderTime);
187 // Prepare values to be useable within a database
188 $uuid = $config->getGOsaUUID();
189 $type = sqlite_escape_string($type);
190 $plugin = sqlite_escape_string($plugin);
191 $action = sqlite_escape_string($action);
192 $timestamp = time();
193 $mtimestamp = microtime(TRUE);
194 $amount = sqlite_escape_string($amount);
195 $duration = sqlite_escape_string($duration);
196 $renderTime = sqlite_escape_string($renderTime);
197 $info = sqlite_escape_string($info);
198 $clicks = sqlite_escape_string($clicks);
199 $memory_usage = sqlite_escape_string(stats::get_memory_usage());
200 $cpu_load = sqlite_escape_string(sprintf("%0.6f",stats::get_cpu_load()));
202 // Clean up category, which usally comes from acl_category and may still contain
203 // some special chars like /
204 $tmp = array();
205 foreach($category as $cat){
206 $tmp[] = trim($cat, '\/,; ');
207 }
208 $category = sqlite_escape_string(implode($tmp, ', '));
210 // Create insert statement.
211 $TABLE_NAME = stats::$tableName;
212 $query = "
213 INSERT INTO {$TABLE_NAME}
214 (ACTID, TYPE, PLUGIN, CATEGORY, ACTION, UUID, MTIMESTAMP, TIMESTAMP,
215 AMOUNT, DURATION, RENDER_TIME, MEMORY_USAGE, CPU_LOAD, INFO)
216 VALUES
217 ('{$clicks}','{$type}','{$plugin}','{$category}','{$action}','{$uuid}',
218 '{$mtimestamp}','{$timestamp}','{$amount}','{$duration}','{$renderTime}',
219 '{$memory_usage}','{$cpu_load}','{$info}')";
220 sqlite_query($query, $res);
221 }
224 /*! \brief This method returns all entries of the GOsa-stats table.
225 * You can limit the result by setting the from/to parameter (timestamp).
226 * @param int from The timestamp to start the result from.
227 * @param int to The timestamp to end the request.
228 * @return array An array containing the requested entries.
229 */
230 static function dumpTables($from = NULL, $to = NULL)
231 {
232 // Get database connection
233 $TABLE_NAME = stats::$tableName;
234 $handle = stats::getDatabaseHandle();
235 if(!$handle) return;
237 // Build up filter to limit dumped entries to the given range.
238 $tim = "";
239 if($from != NULL){
240 $from = sqlite_escape_string($from);
241 $tim.= "AND TIMESTAMP >= '{$from}' ";
242 }
243 if($to != NULL){
244 $to = sqlite_escape_string($to);
245 $tim.= "AND TIMESTAMP <= '{$to}' ";
246 }
247 $tim = preg_replace("/^AND /"," WHERE ",$tim);
249 // Create Filter and start query
250 $filter = "SELECT * FROM {$TABLE_NAME}{$tim} ORDER BY ID";
251 $ret = sqlite_array_query($filter, $handle, SQLITE_ASSOC);
252 return($ret);
253 }
256 /*! \brief This is just a dummy output/debug method
257 * It directly prints some stats and table infos on the screen.
258 */
259 static function show()
260 {
261 $res = stats::getDatabaseHandle();
262 # stats::dropTable($res);
263 if(!$res) return;
265 $TABLE_NAME = stats::$tableName;
266 $query = "SELECT * FROM {$TABLE_NAME} ORDER BY MTIMESTAMP";
267 $query = "SELECT PLUGIN, ACTION, MAX(DURATION) as 'DURATION' FROM {$TABLE_NAME} WHERE ACTION='modify' GROUP BY PLUGIN,ACTION ";
268 $query = "SELECT * FROM {$TABLE_NAME} ORDER BY ID DESC LIMIT 30";
269 $query = "SELECT * FROM {$TABLE_NAME} WHERE plugin != 'LDAP' ORDER BY ID DESC LIMIT 30";
270 $ret = sqlite_query($query, $res);
271 echo "<pre>";
273 $colSize = 16;
275 $title = FALSE;
276 foreach(sqlite_fetch_all($ret) as $entry){
277 if(!$title){
278 foreach($entry as $key => $str) {
279 if(is_numeric($key)) continue;
280 echo str_pad($key,$colSize,' ')."|";
281 }
282 echo "\n";
283 foreach($entry as $key => $str) {
284 if(is_numeric($key)) continue;
285 echo str_pad('',$colSize,'-')."-";
286 }
287 echo "\n";
288 $title = TRUE;
289 }
291 foreach($entry as $key => $str){
292 if(is_numeric($key)) continue;
293 if($key == "DURATION" || $key == "MTIMESTAMP" || $key == "CPU_LOAD"){
294 $str = sprintf("%0.4f", preg_replace("/,/",".",$str));
295 echo str_pad($str,$colSize,' ', STR_PAD_LEFT)."|";
296 }else{
297 echo str_pad($str,$colSize,' ')."|";
298 }
299 }
300 echo "\n";
301 }
302 echo sqlite_error_string($ret);
304 echo "\n------ \n";
305 echo "Time spent per plugin-category \n";
306 echo "------ \n";
308 $query = "
309 SELECT SUM(DURATION) AS DUR, CATEGORY
310 FROM {$TABLE_NAME}
311 WHERE plugin != 'LDAP'
312 GROUP BY CATEGORY
313 ORDER BY DUR DESC LIMIT 10";
314 $ret = sqlite_query($query, $res);
316 $colSize = 16;
317 $title = FALSE;
318 foreach(sqlite_fetch_all($ret) as $entry){
319 foreach($entry as $key => $str){
320 if(is_numeric($key)) continue;
321 echo str_pad($str,$colSize,' ')."|";
322 }
323 echo "\n";
324 }
327 echo sqlite_error_string($ret);
329 echo "\n------ \n";
330 echo "Time spent per plugin \n";
331 echo "------ \n";
333 $query = "
334 SELECT SUM(DURATION) AS DUR, PLUGIN, UUID
335 FROM {$TABLE_NAME}
336 WHERE plugin != 'LDAP'
337 GROUP BY PLUGIN, UUID
338 ORDER BY DUR DESC LIMIT 10";
339 $ret = sqlite_query($query, $res);
341 $colSize = 16;
342 $title = FALSE;
343 foreach(sqlite_fetch_all($ret) as $entry){
344 foreach($entry as $key => $str){
345 if(is_numeric($key)) continue;
346 echo str_pad($str,$colSize,' ')."|";
347 }
348 echo "\n";
349 }
350 echo sqlite_error_string($ret);
352 # * Anzahl von Objekttypen
353 # * Anzahl der Löschungen pro Objekttyp
354 # * Anzahl der Erzeugungen pro Objekttyp
355 # * Anzahl der Bearbeitungen pro Objekttyp
356 # * Anzahl der Verschiebungen pro Objekttyp
357 # * Anzahl der Mehrfachbearbeitungen pro Objekttyp
358 # * Antwortzeiten pro aktion
359 # * Anzahl der Passwortänderungen
360 # * Anzahl der unterschiedlichen Anmeldungen
363 echo "\n------ \n";
364 echo "Actions done per plugin \n";
365 echo "------ \n";
367 $query = "
368 SELECT COUNT(ACTION) as CNT, ACTION, PLUGIN
369 FROM {$TABLE_NAME}
370 WHERE TYPE = 'plugin'
371 AND PLUGIN != 'LDAP'
372 GROUP BY ACTION,PLUGIN
373 ORDER BY CNT DESC LIMIT 30";
374 $ret = sqlite_query($query, $res);
376 $colSize = 16;
377 $title = FALSE;
378 foreach(sqlite_fetch_all($ret) as $entry){
379 foreach($entry as $key => $str){
380 if(is_numeric($key)) continue;
381 echo str_pad($str,$colSize,' ')."|";
382 }
383 echo "\n";
384 }
385 echo sqlite_error_string($ret);
387 echo "\n------ \n";
388 echo "'create' actions done per plugin (5 most)\n";
389 echo "------ \n";
391 $query = "
392 SELECT COUNT(ACTION) as CNT, ACTION, PLUGIN
393 FROM {$TABLE_NAME}
394 WHERE TYPE = 'plugin'
395 AND PLUGIN != 'LDAP'
396 AND ACTION = 'create'
397 GROUP BY ACTION,PLUGIN
398 ORDER BY CNT DESC LIMIT 5";
399 $ret = sqlite_query($query, $res);
401 $colSize = 16;
402 $title = FALSE;
403 foreach(sqlite_fetch_all($ret) as $entry){
404 foreach($entry as $key => $str){
405 if(is_numeric($key)) continue;
406 echo str_pad($str,$colSize,' ')."|";
407 }
408 echo "\n";
409 }
410 echo sqlite_error_string($ret);
412 echo "\n------ \n";
413 echo "'move' actions done per plugin (5 most)\n";
414 echo "------ \n";
416 $query = "
417 SELECT COUNT(ACTION) as CNT, ACTION, PLUGIN
418 FROM {$TABLE_NAME}
419 WHERE TYPE = 'plugin'
420 AND PLUGIN != 'LDAP'
421 AND ACTION = 'move'
422 GROUP BY ACTION,PLUGIN
423 ORDER BY CNT DESC LIMIT 5";
424 $ret = sqlite_query($query, $res);
426 $colSize = 16;
427 $title = FALSE;
428 foreach(sqlite_fetch_all($ret) as $entry){
429 foreach($entry as $key => $str){
430 if(is_numeric($key)) continue;
431 echo str_pad($str,$colSize,' ')."|";
432 }
433 echo "\n";
434 }
435 echo sqlite_error_string($ret);
437 echo "\n------ \n";
438 echo "'view' actions done per plugin (5 most)\n";
439 echo "------ \n";
441 $query = "
442 SELECT COUNT(ACTION) as CNT, ACTION, PLUGIN
443 FROM {$TABLE_NAME}
444 WHERE TYPE = 'plugin'
445 AND PLUGIN != 'LDAP'
446 AND ACTION = 'view'
447 GROUP BY ACTION,PLUGIN
448 ORDER BY CNT DESC LIMIT 5";
449 $ret = sqlite_query($query, $res);
451 $colSize = 16;
452 $title = FALSE;
453 foreach(sqlite_fetch_all($ret) as $entry){
454 foreach($entry as $key => $str){
455 if(is_numeric($key)) continue;
456 echo str_pad($str,$colSize,' ')."|";
457 }
458 echo "\n";
459 }
460 echo sqlite_error_string($ret);
462 echo "\n------ \n";
463 echo "'open' actions done per plugin (5 most)\n";
464 echo "------ \n";
466 $query = "
467 SELECT COUNT(ACTION) as CNT, ACTION, PLUGIN, UUID
468 FROM {$TABLE_NAME}
469 WHERE TYPE = 'plugin'
470 AND PLUGIN != 'LDAP'
471 AND ACTION = 'open'
472 GROUP BY ACTION,PLUGIN, UUID
473 ORDER BY CNT DESC LIMIT 5";
474 $ret = sqlite_query($query, $res);
476 $colSize = 16;
477 $title = FALSE;
478 foreach(sqlite_fetch_all($ret) as $entry){
479 foreach($entry as $key => $str){
480 if(is_numeric($key)) continue;
481 echo str_pad($str,$colSize,' ')."|";
482 }
483 echo "\n";
484 }
485 echo sqlite_error_string($ret);
487 echo "\n------ \n";
488 echo "Time per session\n";
489 echo "------ \n";
491 $query = "
492 SELECT SUM(DURATION) as DUR, UUID
493 FROM {$TABLE_NAME}
494 GROUP BY UUID
495 ORDER BY DUR DESC LIMIT 10";
496 $ret = sqlite_query($query, $res);
498 $colSize = 16;
499 $title = FALSE;
500 foreach(sqlite_fetch_all($ret) as $entry){
501 foreach($entry as $key => $str){
502 if(is_numeric($key)) continue;
503 echo str_pad($str,$colSize,' ')."|";
504 }
505 echo "\n";
506 }
507 echo sqlite_error_string($ret);
509 echo "\n------ \n";
510 echo "Most used password hashes\n";
511 echo "------ \n";
513 $query = "
514 SELECT DISTINCT(INFO), COUNT(INFO) as CNT
515 FROM {$TABLE_NAME}
516 WHERE ACTION = 'change_password'
517 GROUP BY INFO
518 ORDER BY INFO DESC LIMIT 10";
519 $ret = sqlite_query($query, $res);
521 $colSize = 16;
522 $title = FALSE;
523 foreach(sqlite_fetch_all($ret) as $entry){
524 foreach($entry as $key => $str){
525 if(is_numeric($key)) continue;
526 echo str_pad($str,$colSize,' ')."|";
527 }
528 echo "\n";
529 }
530 echo sqlite_error_string($ret);
532 echo "\n------ \n";
533 echo "Actions at all\n";
534 echo "------ \n";
536 $query = "
537 SELECT DISTINCT(ACTION), COUNT(ACTION) AS CNT
538 FROM {$TABLE_NAME}
539 GROUP BY ACTION
540 ORDER BY CNT DESC LIMIT 10";
541 $ret = sqlite_query($query, $res);
543 $colSize = 16;
544 $title = FALSE;
545 foreach(sqlite_fetch_all($ret) as $entry){
546 foreach($entry as $key => $str){
547 if(is_numeric($key)) continue;
548 echo str_pad($str,$colSize,' ')."|";
549 }
550 echo "\n";
551 }
552 echo sqlite_error_string($ret);
554 echo "\n------ \n";
555 echo "Time spent per action\n";
556 echo "------ \n";
558 $query = "
559 SELECT DISTINCT(ACTION), SUM(DURATION) AS DUR
560 FROM {$TABLE_NAME}
561 GROUP BY ACTION
562 ORDER BY DUR DESC LIMIT 10";
563 $ret = sqlite_query($query, $res);
565 $colSize = 16;
566 $title = FALSE;
567 foreach(sqlite_fetch_all($ret) as $entry){
568 foreach($entry as $key => $str){
569 if(is_numeric($key)) continue;
570 echo str_pad($str,$colSize,' ')."|";
571 }
572 echo "\n";
573 }
574 echo sqlite_error_string($ret);
576 echo "\n------ \n";
577 echo "Average time per action\n";
578 echo "------ \n";
580 $query = "
581 SELECT DISTINCT(ACTION), AVG(DURATION) AS DUR
582 FROM {$TABLE_NAME}
583 GROUP BY ACTION
584 ORDER BY DUR DESC LIMIT 10";
585 $ret = sqlite_query($query, $res);
587 $colSize = 16;
588 $title = FALSE;
589 foreach(sqlite_fetch_all($ret) as $entry){
590 foreach($entry as $key => $str){
591 if(is_numeric($key)) continue;
592 echo str_pad($str,$colSize,' ')."|";
593 }
594 echo "\n";
595 }
596 echo sqlite_error_string($ret);
599 echo "\n------ \n";
600 echo "Rendertime per plugin\n";
601 echo "------ \n";
603 $query = "
604 SELECT PLUGIN, RENDER_TIME AS RM
605 FROM {$TABLE_NAME}
606 GROUP BY PLUGIN
607 ORDER BY RM DESC
608 LIMIT 10
609 ";
610 $ret = sqlite_query($query, $res);
612 $colSize = 16;
613 $title = FALSE;
614 foreach(sqlite_fetch_all($ret) as $entry){
615 foreach($entry as $key => $str){
616 if(is_numeric($key)) continue;
617 echo str_pad($str,$colSize,' ')."|";
618 }
619 echo "\n";
620 }
621 echo sqlite_error_string($ret);
623 echo "</pre>";
624 }
627 /*! \brief Somehow sqlite can not work with float values when it comes to AVG() SUM().
628 * We use this methods to convert float values to int and vice versa.
629 * The database will then contain 'int' intead of 'float'.
630 * prepareFloatForReading -> Used for reading 'float' values.
631 * prepareFloatForWriting -> Used for writing 'float' values.
632 * @param float The 'float' value to convert.
633 * @return int The converted float value.
634 */
635 static function prepareFloatForWriting($float)
636 {
637 return(floor($float * 1000));
638 }
642 /*! \brief Somehow sqlite can not work with float values when it comes to AVG() SUM().
643 * We use this methods to convert float values to int and vice versa.
644 * The database will then contain 'int' intead of 'float'.
645 * prepareFloatForWriting -> Used for writing 'float' values.
646 * @param float The 'int' value read from the table.
647 * @return int The converted int value.
648 */
649 static function prepareFloatForReading($int)
650 {
651 return($int / 1000);
652 }
653 }
655 ?>