MySQL dynamic pivot table

I’m using Redmine to manage projects and bug reports, I was needing a daily report with spent time by user on each project, nothing complicated, but I wanted a cross-reference result like Excel dynamic tables. As we know, MySQL doesn’t support dynamic cross-reference queries but I make an approach:

1. Write the main query with rows: project identifier, task subject and total hours in a date range.

SELECT p.identifier, 
	e.issue_id, i.subject, round(SUM(e.hours),2) AS total_hours
	FROM redmine_nsp.time_entries e
	INNER JOIN redmine_nsp.issues i ON e.issue_id = i.id
	INNER JOIN redmine_nsp.projects p ON e.project_id = p.id
	WHERE e.spent_on BETWEEN '2014-07-01' AND '2014-07-07'
 GROUP BY p.identifier,e.issue_id;
+------------+----------+----------------------------+-------------+
| identifier | issue_id | subject                    | total_hours |
+------------+----------+----------------------------+-------------+
| bg02       |     3223 | Gestion de proyecto        |        0.25 |
| bg04       |     3256 | 1.1 Preparación del entor  |        0.63 |
| emision    |     3251 | Desarrollar la aplicación  |        3.97 |
| nsp00      |     3236 | Preparar propuesta para G  |        2.02 |
| nsp02      |     3234 | Subida al servidor de pro  |        0.52 |
| nsp02      |     3240 | Agregar funcionalidad de   |        0.55 |
| nsp02      |     3241 | Revertir el Documento      |        2.80 |
| nsp02      |     3242 | Agregar Filtros en las vi  |        0.72 |
| nsp02      |     3243 | Reportes de Cargo de entr  |        2.35 |
| nsp02      |     3254 | Control de sesiones        |        3.23 |
| nsp05      |     3252 | Mantenimiento del servido  |        0.18 |
| nsp05      |     3253 | Mantenimiento a redmine    |        0.53 |
| nsp06      |     3203 | Elaborar el visualzador d  |        0.23 |
| nsp06      |     3228 | Evitar que se tome mas de  |        0.25 |
| nsp06      |     3255 | Actualizar el porcentaje   |        1.50 |
| nsp08      |     3239 | Asistencia remota          |        1.38 |
+------------+----------+----------------------------+-------------+

2. Then list the active users between dates:

SELECT e.user_id, u.login
FROM redmine_nsp.time_entries e
	INNER JOIN redmine_nsp.users u ON e.user_id = u.id 
WHERE e.spent_on BETWEEN '2014-07-01' AND '2014-07-07'
GROUP BY e.user_id;
+---------+----------+
| user_id | login    |
+---------+----------+
|       1 | user1    |
|       4 | user2    |
|       5 | user3    |
|       6 | user4    |
+---------+----------+

3. Now, create a stored procedure to create a dynamic SQL command with the second query as column names and values with each Redmine issue. The second query is used as cursor.

DELIMITER $$
 
CREATE PROCEDURE `get_range_summary`(dDate1 DATE,dDate2 DATE)
BEGIN
 
DECLARE done INT DEFAULT 0;
DECLARE p_user_id INT;
DECLARE p_sql text;
DECLARE p_login VARCHAR(255);
DECLARE c_users cursor FOR
	SELECT e.user_id, u.login
	FROM redmine_nsp.time_entries e
		INNER JOIN redmine_nsp.users u ON e.user_id = u.id 
	WHERE e.spent_on BETWEEN  dDate1 AND dDate2 
	GROUP BY e.user_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 
SET p_sql = 'select s.codigo,s.issue_id,s.subject ';
OPEN c_users;
 
read_loop: LOOP
	FETCH c_users INTO p_user_id, p_login;
    IF done THEN
      LEAVE read_loop;
    END IF;
	SET p_sql = concat(p_sql,
      ', (select round(sum(t.hours),2) as total
		from redmine_nsp.time_entries t
		where t.spent_on between \'',dDate1,'\' and \'',dDate2,'\'  
        and t.issue_id = s.issue_id
		and t.user_id = ',p_user_id,') as `',p_login,'` ');
 
END LOOP;
 
SET @SQL = concat(p_sql,' ,s.total_hours from 
	(select p.identifier, e.issue_id, i.subject, 
         round(sum(e.hours),2) as total_hours
	from redmine_nsp.time_entries e
	inner join redmine_nsp.issues i on e.issue_id = i.id
	inner join redmine_nsp.projects p on e.project_id = p.id
	where e.spent_on between \'',dDate1,'\' and \'',dDate2,'\' group by p.identifier,e.issue_id) as s');
 
 
close c_users;
 
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1; 
 
END

The result of call get_range_summary(‘2014-07-01′,’2014-07-07’):

+---------+----------+----------------------------+-------+--------+---------+----------+-------------+
| identif | issue_id | subject                    | user1 | user2  |  user3  |   user4  | total_hours |
+---------+----------+----------------------------+-------+--------+---------+----------+-------------+
| bg02    |       23 | Gestion de proyecto        |  NULL |   0.25 |    NULL |     NULL |        0.25 |
| bg04    |       56 | 1.1 Preparación del entor  |  NULL |   0.63 |    NULL |     NULL |        0.63 |
| emision |       51 | Desarrollar la aplicación  |  NULL |   NULL |    3.97 |     NULL |        3.97 |
| nsp00   |       36 | Preparar propuesta para G  |  NULL |   2.02 |    NULL |     NULL |        2.02 |
| nsp02   |       34 | Subida al servidor de pro  |  NULL |   0.52 |    NULL |     NULL |        0.52 |
| nsp02   |       40 | Agregar funcionalidad de   |  NULL |   NULL |    0.55 |     NULL |        0.55 |
| nsp02   |       41 | Revertir el Documento      |  1.40 |   NULL |    1.40 |     NULL |        2.80 |
| nsp02   |       42 | Agregar Filtros en las vi  |  NULL |   NULL |    0.72 |     NULL |        0.72 |
| nsp02   |       43 | Reportes de Cargo de entr  |  NULL |   NULL |    NULL |     2.35 |        2.35 |
| nsp02   |       54 | Control de sesiones        |  NULL |   NULL |    NULL |     3.23 |        3.23 |
| nsp05   |       52 | Mantenimiento del servidor |  NULL |   0.18 |    NULL |     NULL |        0.18 |
| nsp05   |       53 | Mantenimiento a redmine    |  NULL |   0.53 |    NULL |     NULL |        0.53 |
| nsp06   |        3 | Elaborar el visualzador de |  NULL |   0.23 |    NULL |     NULL |        0.23 |
| nsp06   |       28 | Evitar que se tome mas de  |  NULL |   0.25 |    NULL |     NULL |        0.25 |
| nsp06   |       55 | Actualizar el porcentaje d |  NULL |   1.50 |    NULL |     NULL |        1.50 |
| nsp08   |       39 | Asistencia remota          |  NULL |   1.38 |    NULL |     NULL |        1.38 |
+---------+----------+----------------------------+-------+--------+---------+----------+-------------+

That’s it. I put the result easily in a php based report and added it to cron task.

MySQL, the strange case of a timestamp field

I discovered a feature (or bug) of MySQL timestamp fields. Maybe is documented in some place what I not read yet:

When I add a new timestamp field to a table, MySQL magically adds some features to new timestamp field like a “trigger” and a default value to CURRENT_TIMESTAMP.

There is test-case script:

-- CREATING TABLE AND INSERT SOME DUMMY DATA
mysql> CREATE TABLE t(
    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> val VARCHAR(50)
    -> );
Query OK, 0 ROWS affected (0.15 sec)
 
mysql> INSERT INTO t (val) VALUES ("foo") ,("var");
Query OK, 2 ROWS affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM t;
+----+------+
| id | val  |
+----+------+
|  1 | foo  |
|  2 | var  |
+----+------+
2 ROWS IN SET (0.00 sec)
 
 
-- ADDING NEW TIMESTAMP FIELD AND ADD MORE DUMMY DATA
mysql> ALTER TABLE t ADD ts_field TIMESTAMP;
Query OK, 2 ROWS affected (0.35 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO t (val) VALUES ("foo 2") ,("var 2");
Query OK, 2 ROWS affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
-- THERE IS THE MAGIC:
mysql> SELECT * FROM t;
+----+-------+---------------------+
| id | val   | ts_field            |
+----+-------+---------------------+
|  1 | foo   | 0000-00-00 00:00:00 |
|  2 | var   | 0000-00-00 00:00:00 |
|  3 | foo 2 | 2013-01-09 23:20:01 |    <---
|  4 | var 2 | 2013-01-09 23:20:01 |    <---
+----+-------+---------------------+
4 ROWS IN SET (0.00 sec)

What happened? I dont know.

The new table structure is:

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(50) DEFAULT NULL,
  `ts_field` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

It only happens if the table doesn’t have another timestamp field yet.

Now, this is useful? maybe. This is a bug? maybe.

Update: This is a feature documented in https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html, sorry about my lapsus-post.

MySQL get disk usage of all databases

I was tired to get manually disk space used for all MySQL databases, I just created a stored procedure to get an overview of the database sizes in our MySQL server.

MySQL don’t have a command that allows us an overall summary of the databases, something like SHOW TABLE STATUS for databases. The “SHOW DATABASES” command lists only the current databases without any other information like how many space are using or how many tables there are, etc.

The procedure that I wrote this based on INFORMATION_SCHEMA database, which contains rich information of all existing databases.

I suggest, in a previous post, place all these procedures in a database called tools, but you can still choose different location.

So when call the procedure will have a global view of information that will be useful for maintenance tasks.

 

mysql> call tools.sp_overview();
+------------------------------+---------+--------+----------+---------+
| Database                     | Charset | Tables | Routines | Size Mb |
+------------------------------+---------+--------+----------+---------+
| database1                    | utf8    |     43 |       28 |     7.0 |
| database2                    | latin1  |     43 |       28 |   205.0 |
| database3                    | utf8    |    116 |        0 |   126.2 |
| database4                    | utf8    |     99 |        0 |     0.3 |
| database5                    | utf8    |    165 |        0 |    77.4 |
| database6                    | utf8    |    121 |        2 |   719.4 |
| database7                    | utf8    |    122 |        0 |    91.3 |
| database8                    | utf8    |    116 |        0 |    89.7 |
| database9                    | utf8    |    124 |        0 |     4.5 |
| database10                   | utf8    |    113 |        0 |   147.7 |
| database11                   | latin1  |    119 |        3 |   436.4 |
| database12                   | latin1  |    122 |        0 |   439.1 |
| database13                   | latin1  |    122 |        4 |   452.7 |
| database14                   | utf8    |    115 |        0 |   273.0 |
| database15                   | utf8    |    122 |        0 |   265.5 |
| database16                   | utf8    |      0 |        0 |    NULL |
| database17                   | latin1  |     26 |        0 |     0.9 |
| database18                   | latin1  |      9 |        0 |     0.0 |
| database19                   | latin1  |     22 |        0 |     0.3 |
+------------------------------+---------+--------+----------+---------+
19 rows in set (3.01 sec)

 

The first time execution maybe you will get results in a few seconds.

There is the source code:

 

DELIMITER $$
 
DROP PROCEDURE IF EXISTS tools.sp_overview$$
CREATE PROCEDURE tools.sp_overview()
BEGIN
 
    SELECT s.SCHEMA_NAME AS `Database`, s.DEFAULT_CHARACTER_SET_NAME AS `Charset`,
        COUNT(t.TABLE_NAME) AS `Tables`,
 
        (SELECT COUNT(*) FROM information_schema.ROUTINES AS r
            WHERE r.routine_schema = s.SCHEMA_NAME) AS `Routines`,
 
         round(SUM(t.DATA_LENGTH + t.INDEX_LENGTH) / 1048576 ,1) AS `Size Mb`
 
        FROM information_schema.SCHEMATA AS s
            LEFT JOIN information_schema.TABLES t ON s.schema_name = t.table_schema
        WHERE s.SCHEMA_NAME NOT IN ('information_schema', 'performance_schema')
 
    GROUP BY s.SCHEMA_NAME;
END$$
DELIMITER ;

Converting MS Access to MySQL with relationships

I have used tools like MySQL Migration Toolkit (*) and Bullzip’s Access To MySQL utility, both do a excellent job but without relationships. We can spend a lot of hours to identifying and creating relationships until now:

I have write a VBA script to identify MS-Access relationships and create MySQL code with SQL-CREATE sentences, it will be useful after migration process using any free tool:

'Put this function in new/existing MS-Access module.
'
' Version History:
'
' 2014-02-09 - Seamus Casey
' a modification to Ivan's handy Access to MySQL relationship/constraint generator
'
' changes include:
'  1) skip Access system tables (TableDefAttributeEnum.dbSystemObjec)
'  2) add support for cascading updates/deletes
'
 
Public Sub printRelations()
    Dim sql, fk As String
    Dim I, J As Integer
    Dim db As Database
    Dim Table As TableDef
    Dim TableName As String
 
    ' grab a reference to this once, otherwise when we retrieve a table below,
    ' we will get an 'Object Invalid or No Longer Set' error.
    Set db = CurrentDb
 
    For I = 0 To db.Relations.Count - 1
 
        Set Table = db.TableDefs.Item(db.Relations(I).Table)
 
        If ((Table.Attributes And TableDefAttributeEnum.dbSystemObject) = 0) Then
 
           sql = "ALTER TABLE `" & db.Relations(I).ForeignTable & _
               "` ADD CONSTRAINT `" & db.Relations(I).Name & "` FOREIGN KEY ("
           fk = "("
           For J = 0 To db.Relations(I).Fields.Count - 1
               sql = sql & "`" & db.Relations(I).Fields(J).ForeignName & "` ,"
               fk = fk & "`" & db.Relations(I).Fields(J).Name & "` ,"
           Next J
 
           sql = Left(sql, Len(sql) - 1)
           fk = Left(fk, Len(fk) - 1)
           fk = fk & ")"
           sql = sql & ") REFERENCES `" & db.Relations(I).Table & "`" & fk
 
           If (db.Relations(I).Attributes And RelationAttributeEnum.dbRelationUpdateCascade) Then
               sql = sql & " ON UPDATE CASCADE"
           End If
 
           If (db.Relations(I).Attributes And RelationAttributeEnum.dbRelationDeleteCascade) Then
               sql = sql & " ON DELETE CASCADE"
           End If
 
           sql = sql & ";"
 
           Debug.Print sql
        End If
    Next I
End Sub

Gist Source

To run the adobe code go to intermediate windows (Ctrl+G) and execute: printRelations and copy the generated SQL code.

Enjoy!

(*)MySQL Migration Toolkit is discontinued but is still available from mirrors like:
http://mirrors.dotsrc.org/mysql/Downloads/MySQLGUITools/

MySQL must improve error messages

I just finished a database modification, a new foreign key creation  shouldn’t be take more than 5 mins, but I spent 2 hours because MySQL still have some useless error messages.

There is a way to create a new foreign key:

-- Create two tables foo and bar
CREATE TABLE foo (
	id INTEGER NOT NULL PRIMARY KEY,
	bar_id INT NOT NULL    -- foreign key
);
CREATE TABLE bar (
	id INTEGER NOT NULL PRIMARY KEY
);
-- Try to create a foreign key on `foo`
ALTER TABLE foo
	ADD FOREIGN KEY(bar_id) REFERENCES bar(SOME_FIELD) ;

The last sentence returns a generic error message:

Error Code: 1005. Can't create table 'temp.#sql-4bd7_11' (errno: 150)

Everything would have been easier if I had noticed that wrong field name bar(SOME_FIELD), sometimes happens,  but if MySQL would have shown a different message like "field bar.SOME_FIELD don't exists" I would not be awake until 2 am.

I’m using MySQL 5.5.21 community edition.

if I had noticed that wrong field name

Twitter bug found!

Some days ago while I’m looking for what are saying about a mysql.com server down I found a twitter bug:

Is not a big deal, to repeat this bug you must follow these steps:

1. Find any term, in this case “mysql.com” then in results looking for a word that have the search term as a part of them (ex dev.mysql.com) and select the a part or entire word:

twitter bug 01

2. Press Ctrl + C,  some HTML codes appear from nowhere:

twitter bug 02

3. Do it again and again, you will see an strange twit like this:

twitter bug 03

I’m using Firefox 8.0  under Ubuntu, but you will get the same result using Chrome and (maybe) other web clients… you can try with other searches like twitter.com and select pic.twitter.com results.

UPDATE:

@jeremycole  says: “Your URL-selection Twitter bug was fixed and shipped yesterday afternoon! Thanks!” on Mar 14 2012

Alias shortcuts to MySQL CLI

Do you get write laziness in the command line everything what you need to connect to a MySQL server every time?

It may take less than minute, but sometimes one minute is vital (especially if we’re near the end of the world):

ivancp@ubuntu$ mysql -u root -p -h mysqlhost database

When we are hurry, these commands often fail several times per minute.

The solution: we can create shortcuts with bash alias commands in file ~/.bashrc :

# File ~ /. Bashrc
 
# Command "my" to connect to a local server
alias my='mysql -u root -p'
 
# Command "my2" to connect to a remote server
alias my2='mysql -u root -h 192.168.1.56 -p'

Next time if you want to access the local server just type the command my [database name] , there only ask for database password. You can use any command aliases, I prefer  “my” and “my2” they are short and useful.

But if you have several servers comes another problem, how to know in which server I’m?

Open a mysql-cli can be super fast with alias shortcuts, but all terminals have the same default prompt: mysql>  To avoid disasters (ex. run DROP in wrong place) you can change mysql-cli prompt with option --prompt , then finally our .bashrc look like this:

#improved ~/.bashrc 
 
# Command "my" to connect to a local server
alias my='mysql -u root --password=secret --prompt="local> "'
 
# Command "my2" to connect to a remote server
alias my2='mysql -u root --password=secret  -h 192.168.1.56 --prompt="server 1> "'

Note I added --password parameter, it can be dangerous, use it under your own risk.

Enjoy!

Custom auto increment values

The auto_increment for MySQL fields are very useful but what about if I need a custom auto-increment like a custom format like 001-000033,

001-000034
001-000035
001-000036
...

To make it possible we have an option based on past article MySQL Sequences:

  1. Create the table to store the current sequence values:
    CREATE TABLE _sequence
    (
        seq_name VARCHAR(50) NOT NULL PRIMARY KEY,
        seq_group VARCHAR(10) NOT NULL,
        seq_val INT UNSIGNED NOT NULL
    );
  2. Create a function to get and increment the current value:
    delimiter //
    DROP FUNCTION IF EXISTS getNextCustomSeq//
    CREATE FUNCTION getNextCustomSeq
    (
        sSeqName VARCHAR(50),
        sSeqGroup VARCHAR(10)
    ) RETURNS VARCHAR(20)
    BEGIN
        DECLARE nLast_val INT; 
     
        SET nLast_val =  (SELECT seq_val
                              FROM _sequence
                              WHERE seq_name = sSeqName
                                    AND seq_group = sSeqGroup);
        IF nLast_val IS NULL THEN
            SET nLast_val = 1;
            INSERT INTO _sequence (seq_name,seq_group,seq_val)
            VALUES (sSeqName,sSeqGroup,nLast_Val);
        ELSE
            SET nLast_val = nLast_val + 1;
            UPDATE _sequence SET seq_val = nLast_val
            WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
        END IF; 
     
        SET @ret = (SELECT concat(sSeqGroup,'-',lpad(nLast_val,6,'0')));
        RETURN @ret;
    END// 
     
    delimiter ;
  3. Create a stored procedure to modify a current sequence value:
    delimiter //
    DROP PROCEDURE IF EXISTS sp_setSeqCustomVal//
    CREATE PROCEDURE sp_setCustomVal(sSeqName VARCHAR(50),  
                  sSeqGroup VARCHAR(10), nVal INT UNSIGNED)
    BEGIN
        IF (SELECT COUNT(*) FROM _sequence  
                WHERE seq_name = sSeqName  
                    AND seq_group = sSeqGroup) = 0 THEN
            INSERT INTO _sequence (seq_name,seq_group,seq_val)
            VALUES (sSeqName,sSeqGroup,nVal);
        ELSE
            UPDATE _sequence SET seq_val = nVal
            WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
        END IF;
    END//
    delimiter ;

Testing the new functions:

  1. Create a table:
    CREATE TABLE custom_autonums
    (
       id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       seq_1 VARCHAR(20), -- custom sequence 1
       seq_2 VARCHAR(20), -- custom sequence 2
       UNIQUE(seq_1),
       UNIQUE(seq_2)
    );
  2. Create trigger:
    delimiter //
    DROP TRIGGER IF EXISTS custom_autonums_bi//
     
    CREATE TRIGGER custom_autonums_bi BEFORE INSERT ON custom_autonums
    FOR each ROW
    BEGIN
       SET NEW.seq_1 = getNextCustomSeq("seq_1","001");
       SET NEW.seq_2 = getNextCustomSeq("seq_2","DBA");
    END//
     
    delimiter ;
  3. Insert some values:
    INSERT INTO custom_autonums (id) VALUES (NULL),(NULL),(NULL);
    SELECT * FROM custom_autonums;
    +----+------------+------------+
    | id | seq_1      | seq_2      |
    +----+------------+------------+
    |  4 | 001-000001 | DBA-000001 |
    |  5 | 001-000002 | DBA-000002 |
    |  6 | 001-000003 | DBA-000003 |
    +----+------------+------------+
    3 ROWS IN SET (0.00 sec)
  4. Altering current values:
    CALL sp_setCustomVal('seq_1','001',675);
     
    INSERT INTO custom_autonums (id) VALUES (NULL),(NULL),(NULL);
    SELECT * FROM custom_autonums;
    +----+------------+------------+
    | id | seq_1      | seq_2      |
    +----+------------+------------+
    |  4 | 001-000001 | DBA-000001 |
    |  5 | 001-000002 | DBA-000002 |
    |  6 | 001-000003 | DBA-000003 |
    |  7 | 001-000676 | DBA-000004 |
    |  8 | 001-000677 | DBA-000005 |
    |  9 | 001-000678 | DBA-000006 |
    +----+------------+------------+
    6 ROWS IN SET (0.00 sec)

Enjoy!

CMS based on phpBB posts

phpBBIf you are using phpBB maybe you need to show a individual phpBB post on an external page (outside of our phpBB forum) but it can be frustrating because phpBB uses a key in each bbcode (bitfield), but when I explore the source code I found the solution to extract parsed HTML text from phpBB posts.

Also I try to use a phpBB CMS Mod but I leave it because is still in development and now I’m trying to make my own CMS from phpBB posts, I use some code from phpBB tutorials and now I have a very basic CMS using phpBB Posts.

This is an overview:

  • I use apache mod_rewrite to make search engine friendly URL’s. (like this post)
  • The phpBB posts are located in a hide moderated forum, it also can use as a section to allow phpBB user post articles.
  • The media content (images, attachments, etc) is stored in phpBB files section.
  • I make custom .tpl files to sections: frontpage, article listings, articles, etc.
  • Also I defined custom bbcodes to show: head sections (h1,h2,h3…), highlighted code, HTML  tables, notes, comments, etc.

But, is still in development I need to finish the resources section and multi-language content, from now all content will be based on phpBB.

You can view the CMS running on Latindevelopers.com (spanish only) when I finish this custom phpBB CMS I will test the security, and maybe publish under GPL.

Install bmon as service

bmon is a useful network monitoring tool, the most useful feature is that bmon store historical data from las 60 seconds, minutes, hours and days, the “problem” is that bmon needs to be running all time.

If you have a network server with Linux as proxy, you will probably need to see the current bandwidth usage or see what happens 60 minutes ago.

We can use screen with a running bmon process to generate HTML result of current bandwidth usage, and we will see generated HTML pages from Apache.

Steps to install and configure

Pasos para instalar y ejecutar:

  1. Install bmon and screen:

    sudo apt-get install screen bmon
     
  2. Create the directory: (you can change this option)/var/www/bmon-html
  3. Create the file /etc/init/bmon.conf  with follow content:
    description "bmon bandwidth monitoring process"
    
    start on (local-filesystems and net-device-up and runlevel [2345])
    stop on runlevel [016]
    
    exec screen -d -m -S bmon bmon -O html:path=/var/www/bmon-html
  4. Start the new service:sudo service bmon start

     

  5. Then the web page  http://localhost/bmon-html/ will be availbale and updated constantly.

Enjoy!

 

Go back to top