Categories
Virtualization

eSXi update storage drivers

1. Power down all running VMs on host (or migrate to another eSXi host if using vCenter)

2. Enable SSH on the eSXi host

3. SSH to the host

4. Enter maintenance mode via:

esxcli system maintenanceMode set --enable true

5. Uninstall the previous storage driver via:

esxcli software vib remove -n scsi-hpvsa -f

6. Reboot the eSXi host via:

esxcli system shutdown reboot --reason "your reason here" 

7. When the eSXi host is back SSH back in and ensure Maintenance Mode is enabled through the same command in step 4.

8. SCP / SFTP copy the new storage driver to /tmp/ on the eSXi host

9. Install the new storage drive via:

esxcli software vib install -v file:/tmp/name-of-driver-file-here.vib --force --no-sig-check --maintenance-mode

10. Reboot

11. Disable SSH

12. Exit Maintenance Mode

13. Power on / migrate back VMs

Categories
MySQL

Maintenance MySQL – JobSheets Android: Jobsheet_History

CREATE VIEW jobsheet_history AS
SELECT x.jr_id, x.jr_jobid, x.jr_userid, x.jr_user_assigned, x.jr_pdaid, x.jr_datedue, x.jr_scandt, x.jr_priloc, x.jr_secloc, x.jr_terloc, x.jr_workrqd, x.jr_workcomp, x.jr_workleft, x.jr_jobcomp, x.jr_timetaken,
x.jr_pu1barcode, p1.il_description pu1desc, x.jr_pu1qty,
x.jr_pu2barcode, p2.il_description pu2desc, x.jr_pu2qty,
x.jr_pu3barcode, p3.il_description pu3desc, x.jr_pu3qty,
x.jr_pu4barcode, p4.il_description pu4desc, x.jr_pu4qty,
x.jr_pu5barcode, p5.il_description pu5desc, x.jr_pu5qty,
x.jr_pu6barcode, p6.il_description pu6desc, x.jr_pu6qty,
x.jr_pu7barcode, p7.il_description pu7desc, x.jr_pu7qty,
x.jr_pu8barcode, p8.il_description pu8desc, x.jr_pu8qty,
x.jr_pu9barcode, p9.il_description pu9desc, x.jr_pu9qty,
x.jr_pu10barcode, p10.il_description pu10desc, x.jr_pu10qty, x.jr_ts
FROM (((((((((jobsheet_report x
LEFT JOIN item_list as p1 on x.jr_pu1barcode=p1.il_barcode)
LEFT JOIN item_list as p2 on x.jr_pu2barcode=p2.il_barcode)
LEFT JOIN item_list as p3 on x.jr_pu3barcode=p3.il_barcode)
LEFT JOIN item_list as p4 on x.jr_pu4barcode=p4.il_barcode)
LEFT JOIN item_list as p5 on x.jr_pu5barcode=p5.il_barcode)
LEFT JOIN item_list as p6 on x.jr_pu6barcode=p6.il_barcode)
LEFT JOIN item_list as p7 on x.jr_pu7barcode=p7.il_barcode)
LEFT JOIN item_list as p8 on x.jr_pu8barcode=p8.il_barcode)
LEFT JOIN item_list as p9 on x.jr_pu9barcode=p9.il_barcode)
LEFT JOIN item_list as p10 on x.jr_pu10barcode=p10.il_barcode;

Categories
Uncategorized

Maintenance MySQL – Timesheets Android: time_sheet_we_next

CREATE VIEW time_sheet_we_next AS
SELECT x.tsw_we from time_sheet_we x
WHERE x.tsw_we >=DATE_SUB(now(), INTERVAL 21 DAY)
AND x.tsw_we

Categories
Uncategorized

Maintenance MySQL – Timesheets Android: time_sheet_last_month

CREATE VIEW time_sheet_last_month AS
SELECT * from time_sheet_last t
WHERE t.ts_WE >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY);

Categories
Uncategorized

Maintenance MySQL – Timesheets Android: time_sheet_last

1. time_sheet_last_sub
CREATE VIEW time_sheet_last_sub AS
SELECT DISTINCT MAX(ts_id) ts_id, ts_userid, ts_WE, MAX(ts_scandt) ts_last_synch
FROM time_sheet GROUP BY ts_userid, ts_WE;

2. time_sheet_last
CREATE VIEW time_sheet_last AS
SELECT time_sheet.* from time_sheet, time_sheet_last_sub
WHERE time_sheet.ts_id=time_sheet_last_sub.ts_id
ORDER BY time_sheet.ts_id DESC;

Categories
Uncategorized

Maintenance MySQL – Job Sheets Android: locations_unique

CREATE VIEW locations_distinctpri AS
SELECT DISTINCT l_priloc from locations;

Categories
Uncategorized

Maintenance MySQL – Job Sheets Android: jobsheet_report_closed

CREATE VIEW jobsheet_report_closed AS
SELECT * FROM jobsheet_report_last_entry

WHERE jr_jobcomp=-1;

Categories
Uncategorized

Maintenance MySQL – Job Sheets Android: jobsheet_report_open

CREATE VIEW jobsheet_report_open AS
SELECT * FROM jobsheet_report_last_entry
WHERE jr_jobcomp=0
OR jr_jobcomp IS NULL ;

Categories
Uncategorized

Maintenance MySQL – Job Sheets Android: jobsheet_report_last_entry

1. jobsheet_report_last_entry_sub
CREATE VIEW jobsheet_report_last_entry_sub AS
SELECT MAX(jr_id) id, jr_jobid, MAX(jr_scandt) scandt
FROM jobsheet_report
GROUP BY jr_jobid;

2. jobsheet_report_last_entry
CREATE VIEW jobsheet_report_last_entry AS
SELECT x.*
FROM jobsheet_report x, jobsheet_report_last_entry_sub y
WHERE x.jr_id=y.id;

Categories
Uncategorized

Maintenance MySQL – Job Sheets Android: jobsheet_report_total

1. jobsheet_report_total_sub
CREATE VIEW jobsheet_report_total_sub(jobid, barcode, qty) AS
(SELECT jr_jobid jobid, jr_pu1barcode Barcode, jr_pu1qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu1barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu2barcode Barcode, jr_pu2qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu2barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu3barcode Barcode, jr_pu3qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu3barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu4barcode Barcode, jr_pu4qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu4barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu5barcode Barcode, jr_pu5qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu5barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu6barcode Barcode, jr_pu6qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu6barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu7barcode Barcode, jr_pu7qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu7barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu8barcode Barcode, jr_pu8qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu8barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu9barcode Barcode, jr_pu9qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu9barcode <> “”)
union all
(SELECT jr_jobid jobid, jr_pu10barcode Barcode, jr_pu10qty qty FROM jobsheet_report_last_entry WHERE jobsheet_report_last_entry.jr_pu10barcode <> “”);

2. jobsheet_report_total
CREATE VIEW jobsheet_report_total AS
SELECT barcode, SUM(qty) qtyused
FROM jobsheet_report_total_sub
GROUP BY Barcode;