Vyššie na [Platon] / scripts / perl / accounting

MySQL selects for process accounting statistics.

/* global */
SELECT
    COUNT(*) AS 'Total number of commands',
    SUM(user_time) / 50.0        AS 'Total user time (sec.)',
    SUM(system_time) / 50.0        AS 'Total system time (sec.)',
    SUM(effective_time) / 50.0    AS 'Total effective time (sec.)'
FROM log_accounting_process \G

/* by command count */
SELECT
    command AS 'Command',
    COUNT(*) AS 'Number of commands',
    SUM(user_time) / 50.0        AS 'Total user time (sec.)',
    SUM(system_time) / 50.0        AS 'Total system time (sec.)',
    SUM(effective_time) / 50.0    AS 'Total effective time (sec.)'
FROM log_accounting_process
GROUP BY command
ORDER BY 'Number of commands' DESC
LIMIT 100;

/* by command user time */
SELECT
    command AS 'Command',
    COUNT(*) AS 'Number of commands',
    SUM(user_time) / 50.0        AS 'Total user time (sec.)',
    MIN(user_time) / 50.0        AS 'Minimal user time (sec.)',
    AVG(user_time) / 50.0        AS 'Average user time (sec.)',
    MAX(user_time) / 50.0        AS 'Maximal user time (sec.)'
FROM log_accounting_process
GROUP BY command
ORDER BY 'Total user time (sec.)' DESC
LIMIT 100;

/* by command system time */
SELECT
    command AS 'Command',
    COUNT(*) AS 'Number of commands',
    SUM(system_time) / 50.0        AS 'Total system time (sec.)',
    MIN(system_time) / 50.0        AS 'Minimal system time (sec.)',
    AVG(system_time) / 50.0        AS 'Average system time (sec.)',
    MAX(system_time) / 50.0        AS 'Maximal system time (sec.)'
FROM log_accounting_process
GROUP BY command
ORDER BY 'Total system time (sec.)' DESC
LIMIT 100;

/* by command effective time */
SELECT
    command AS 'Command',
    COUNT(*) AS 'Number of commands',
    SUM(effective_time) / 50.0        AS 'Total effective time (sec.)',
    MIN(effective_time) / 50.0        AS 'Minimal effective time (sec.)',
    AVG(effective_time) / 50.0        AS 'Average effective time (sec.)',
    MAX(effective_time) / 50.0        AS 'Maximal effective time (sec.)'
FROM log_accounting_process
GROUP BY command
ORDER BY 'Total effective time (sec.)' DESC
LIMIT 100;

--
-- User stats:
--

/* by command count */
SELECT
    p.username AS 'Username',
    COUNT(*) AS 'Number of commands',
    SUM(user_time) / 50.0        AS 'Total user time (sec.)',
    SUM(system_time) / 50.0        AS 'Total system time (sec.)',
    SUM(effective_time) / 50.0    AS 'Total effective time (sec.)'
FROM log_accounting_process AS l
INNER JOIN passwd AS p USING (uid)
GROUP BY l.uid 
ORDER BY 'Number of commands' DESC
LIMIT 100;

/* by command user time */
SELECT
    p.username AS 'Username',
    COUNT(*) AS 'Number of commands',
    SUM(user_time) / 50.0        AS 'Total user time (sec.)',
    MIN(user_time) / 50.0        AS 'Minimal user time (sec.)',
    AVG(user_time) / 50.0        AS 'Average user time (sec.)',
    MAX(user_time) / 50.0        AS 'Maximal user time (sec.)'
FROM log_accounting_process AS l
INNER JOIN passwd AS p USING (uid)
GROUP BY l.uid 
ORDER BY 'Total user time (sec.)' DESC
LIMIT 100;

/* by command system time */
SELECT
    p.username AS 'Username',
    COUNT(*) AS 'Number of commands',
    SUM(system_time) / 50.0        AS 'Total system time (sec.)',
    MIN(system_time) / 50.0        AS 'Minimal system time (sec.)',
    AVG(system_time) / 50.0        AS 'Average system time (sec.)',
    MAX(system_time) / 50.0        AS 'Maximal system time (sec.)'
FROM log_accounting_process AS l
INNER JOIN passwd AS p USING (uid)
GROUP BY l.uid 
ORDER BY 'Total system time (sec.)' DESC
LIMIT 100;

/* by command effective time */
SELECT
    p.username AS 'Username',
    COUNT(*) AS 'Number of commands',
    SUM(effective_time) / 50.0        AS 'Total effective time (sec.)',
    MIN(effective_time) / 50.0        AS 'Minimal effective time (sec.)',
    AVG(effective_time) / 50.0        AS 'Average effective time (sec.)',
    MAX(effective_time) / 50.0        AS 'Maximal effective time (sec.)'
FROM log_accounting_process AS l
INNER JOIN passwd AS p USING (uid)
GROUP BY l.uid 
ORDER BY 'Total effective time (sec.)' DESC
LIMIT 100;