Migrating warehouse history from DCImanager 5

You can migrate the history of deliveries and movements of equipment in the warehouse from DCImanager 5. The history will be available for viewing in the Grafana monitoring system.

Migration steps:

1. Data preparation.
2. Importing tables from the DCImanager 5 database to the DCImanager 6 database.
3. Creating a dashboard in Grafana with a source in the form of DCImanager 6 database.

Data preparation

1. Connect to the server with DCImanager 5 via SSH.

2. Open the MySQL console:

   mysql dcimgr

   BASH

3. Copy the contents of table history_ispare into the new table history_ispare_imported:

   CREATE TABLE history_ispare_imported LIKE history_ispare;

   SQL

   INSERT INTO history_ispare_imported SELECT * FROM history_ispare;

   SQL

4. Add to the table history_ispare_imported columns barcode, name, idelivery_name, placement_new_s, and placement_s:

   ALTER TABLE history_ispare_imported ADD COLUMN barcode VARCHAR(255), ADD COLUMN name VARCHAR(255), ADD COLUMN idelivery_name VARCHAR(255), ADD COLUMN placement_new_s VARCHAR(16), ADD COLUMN placement_s VARCHAR(16);

   SQL

5. Populate in the columns barcode, name, idelivery_name with values from other tables:

   UPDATE history_ispare_imported as h LEFT JOIN ispare AS s ON s.id = h.reference LEFT JOIN idelivery AS d ON d.id = s.idelivery SET h.barcode = s.barcode, h.name = s.name, h.idelivery_name = d.name;

   SQL

6. Populate the new columns placement_new_s and placement_s with value descriptions from columns placement_new and placement:

   UPDATE history_ispare_imported SET
     placement_new_s = CASE
     WHEN placement_new = 0 THEN 'in_warehouse'
     WHEN placement_new = 1 THEN 'in_server'
     WHEN placement_new = 2 THEN 'in_equipment'
     WHEN placement_new = 3 THEN 'written_off'
     WHEN placement_new = 4 THEN 'broken'
     WHEN placement_new = 5 THEN 'under_repair'
     WHEN placement_new = 6 THEN 'bad_input'
     WHEN placement_new = 7 THEN 'reserved'
     WHEN placement_new = 8 THEN 'in_delivery'
     WHEN placement_new = 9 THEN 'in_rack'
     ELSE ''
     END,
     placement_s = CASE
     WHEN placement = 0 THEN 'in_warehouse'
     WHEN placement = 1 THEN 'in_server'
     WHEN placement = 2 THEN 'in_equipment'
     WHEN placement = 3 THEN 'written_off'
     WHEN placement = 4 THEN 'broken'
     WHEN placement = 5 THEN 'under_repair'
     WHEN placement = 6 THEN 'bad_input'
     WHEN placement = 7 THEN 'reserved'
     WHEN placement = 8 THEN 'in_delivery'
     WHEN placement = 9 THEN 'in_rack'
     ELSE ''
     END;

   SQL
7. Add to the table history_ispare_imported columns servers_name, servers_new_name, rack_name, rack_new_name and populate them: 

   ALTER TABLE history_ispare_imported ADD COLUMN servers_name VARCHAR(64);
   ALTER TABLE history_ispare_imported ADD COLUMN servers_new_name VARCHAR(64);
   ALTER TABLE history_ispare_imported ADD COLUMN rack_name VARCHAR(64);
   ALTER TABLE history_ispare_imported ADD COLUMN rack_new_name VARCHAR(64);
   UPDATE history_ispare_imported as h LEFT JOIN servers AS s ON h.servers = s.id SET h.servers_name = s.name;
   UPDATE history_ispare_imported as h LEFT JOIN servers AS s ON h.servers_new = s.id SET h.servers_new_name = s.name;
   UPDATE history_ispare_imported as h LEFT JOIN racks AS r ON h.rack = r.id SET h.rack_name = r.name;
   UPDATE history_ispare_imported as h LEFT JOIN racks AS r ON h.rack_new = r.id SET h.rack_new_name = r.name;

   SQL

8. Copy the contents of table history_server into the new table history_servers_imported:

   CREATE TABLE history_servers_imported LIKE history_servers;

   SQL

   INSERT INTO history_servers_imported SELECT * FROM history_servers;

   SQL

9. Add the server_name column into the history_servers_imported table: 

   ALTER TABLE history_servers_imported ADD COLUMN server_name VARCHAR(64);

   SQL

10. Fill in the column server_name in the table history_servers_imported with values from other tables:

    UPDATE history_servers_imported as h LEFT JOIN servers AS s ON h.reference = s.id SET h.server_name = s.name;

    SQL

11. Exit the MySQL console:
    

    exit

    SQL

12. Create a dump of the new tables:
    

    mysqldump dcimgr history_ispare_imported history_servers_imported > imported_history.txt

    BASH

13. Create an archive with the dump file:
    

    tar cfz imported_history.txt.tgz imported_history.txt

    BASH

14. Copy the archive to the server with DCImanager 6:

    scp imported_history.txt.tgz root@<domain>:

    BASH
     Comments to the command

    <domain> — domain name or IP address of the server with DCImanager 6

Data import

1. Connect to the server with DCImanager 6 via SSH.

2. Unarchive the dump file:
   

   tar xfz imported_history.txt.tgz

   BASH

3. Create the database dci5_imported:

   docker exec mysql mysql -u root -p`docker exec mysql printenv MYSQL_ROOT_PASSWORD` -e'CREATE DATABASE dci5_imported'

   BASH

4. Import the tables from the dump file to the database dci5_imported:
   

   docker exec -i mysql mysql -u root -p`docker exec mysql printenv MYSQL_ROOT_PASSWORD` dci5_imported < imported_history.txt

   BASH

5. Open the database dci5_imported in the MySQL console:
   

   docker exec -it mysql mysql -u root -p`docker exec mysql printenv MYSQL_ROOT_PASSWORD` dci5_imported

   BASH

6. Create the user dci5_hist_viewer with permissions to view the data being imported:
   

   CREATE USER 'dci5_hist_viewer'@'%' IDENTIFIED BY '<some_pass>';

   SQL
    Comments to the command

   <some_pass> — user password

   GRANT SELECT on dci5_imported.* TO 'dci5_hist_viewer'@'%';

   SQL

7. Exit the MySQL console:
   

   exit

   SQL

8. Check the data import and user permissions:

   docker exec -it mysql mysql -u dci5_hist_viewer -p<some_pass> dci5_imported

   BASH
    Comments to the command

   <some_pass> — user password

   SELECT * FROM history_ispare_imported LIMIT 10 \G; 

   SQL

   exit

   SQL

Configuring Grafana

1. Connect to the server with DCImanager 6 via SSH.

2. Enter the docker container with Grafana:

   docker exec -it dci_grafana_1 bash

   BASH

3. Enter the directory /grafana_extra_files/:

   cd /grafana_extra_files

   BASH
4. In the file dci5_history_datasource.yml, specify the user settings to connect to the database:
   
   1. user — dci5_hist_viewer.

   2. password — user password of dci5_hist_viewer.
      

      Example of file

      apiVersion: 1
      
      datasources:
        - name: dci5_hist
          type: mysql
          url: mysql:3306
          user: dci5_hist_viewer
          jsonData:
            database: dci5_imported
            maxOpenConns: 100 # Grafana v5.4+
            maxIdleConns: 100 # Grafana v5.4+
            maxIdleConnsAuto: true # Grafana v9.5.1+
            connMaxLifetime: 14400 # Grafana v5.4+
          secureJsonData:
            password: secret

      CODE

5. Copy the dashboard and data source files into the Grafana working directory:
   

   cp dci5_history_datasource.yml /etc/grafana/provisioning/datasources/

   BASH

   cp dci5_servers_dashboard.json /etc/grafana/dashboards_files/

   BASH

   cp dci5_spares_dashboard.json /etc/grafana/dashboards_files/

   BASH

6. Restart Grafana:

   supervisorctl restart grafana_server

   BASH

7. Check that the dci5_hist data source has been added:

   1. Open the Grafana interface: click in the right-hand menu of the platform interface → Grafana.
   2. In the left-hand Grafana menu, click the icon.
   3. Make sure that the list of sources on the Data sources tab contains dci5_hist.
      
      
   4. To check the connection to the source:
      
      1. Click on its name.
      2. In the window that opens, click Test. If the connection is successful, the following message will appear: Database Connection OK.

Working with data

Imported data is available in dashboards:

• dci5_ispares — history of components;
• dci5_servers — history of servers.

To open the dashboard:

1. Open the Grafana interface: click in the right-hand menu of the platform interface → Grafana.
2. Enter the required dashboard: click  in the left-hand Grafana menu → Browse → General → select the dashboard dci5_ispares or dci5_servers.

Dashboard interface

• barcode — device barcode;
• reference — entry id;
• name — device name;
• from — start date of the time interval;
• till — end date of the time interval.

In the barcode, reference and name fields you can enter:

• ALL — to display all devices;
• % — to display non-empty values.

To change the SQL query that added the data to Grafana, click on the dashboard header dci5 servers (dci5 spares) → Edit → enter the new query → click Apply in the top right corner.

Useful tips

Related topics:

• Migration from DCImanager 5 to DCImanager 6
• Importing equipment into the platform