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:

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

Data preparation

Connect to the server with DCImanager 5 via SSH.
Open the MySQL console:
```
mysql dcimgr
```

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

CREATE TABLE history_ispare_imported LIKE history_ispare;

INSERT INTO history_ispare_imported SELECT * FROM history_ispare;

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);

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;

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;

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;

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

CREATE TABLE history_servers_imported LIKE history_servers;

INSERT INTO history_servers_imported SELECT * FROM history_servers;

Add the server_name column into the history_servers_imported table:

ALTER TABLE history_servers_imported ADD COLUMN server_name VARCHAR(64);

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;

Exit the MySQL console:
```
exit
```

Create a dump of the new tables:

mysqldump dcimgr history_ispare_imported history_servers_imported > imported_history.txt

Create an archive with the dump file:

tar cfz imported_history.txt.tgz imported_history.txt

Copy the archive to the server with DCImanager 6:
```
scp imported_history.txt.tgz root@<domain>:
```
Comments to the command
<domain> — domain name or IP address of the server with DCImanager 6

Data import

Connect to the server with DCImanager 6 via SSH.
Unarchive the dump file:
```
tar xfz imported_history.txt.tgz
```

Create the database dci5_imported:

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

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

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

Create the user dci5_hist_viewer with permissions to view the data being imported:
```
CREATE USER 'dci5_hist_viewer'@'%' IDENTIFIED BY '<some_pass>';
```
Comments to the command
<some_pass> — user password
```
GRANT SELECT on dci5_imported.* TO 'dci5_hist_viewer'@'%';
```
Exit the MySQL console:
```
exit
```

Check the data import and user permissions:

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

Comments to the command

SELECT * FROM history_ispare_imported LIMIT 10 \G;

exit

Configuring Grafana

Connect to the server with DCImanager 6 via SSH.
Enter the docker container with Grafana:
```
docker exec -it dci_grafana_1 bash
```
Enter the directory /grafana_extra_files/:
```
cd /grafana_extra_files
```

In the file dci5_history_datasource.yml, specify the user settings to connect to the database:

user — dci5_hist_viewer.

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

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

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

cp dci5_servers_dashboard.json /etc/grafana/dashboards_files/

cp dci5_spares_dashboard.json /etc/grafana/dashboards_files/

Restart Grafana:
```
supervisorctl restart grafana_server
```
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:

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

Dashboard interface

You can filter the data according to a specified filter:

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