This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> select id,inverter_id,timestamp_poll from inverter_monitor_polls order by id desc limit 100; | |
+------------+-------------+---------------------+ | |
| id | inverter_id | timestamp_poll | | |
+------------+-------------+---------------------+ | |
| 2150109370 | 2090 | 2012-12-06 06:18:00 | | |
| 2150109369 | 2701 | 2012-12-06 06:18:00 | | |
| 2150109368 | 1248 | 2012-12-06 06:17:00 | | |
| 2150109367 | 2090 | 2012-12-06 06:17:00 | | |
| 2150109366 | 322694 | 2012-12-06 06:17:00 | | |
| 2150109365 | 1248 | 2012-12-06 06:16:00 | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 3398201 | helios | 10.180.148.162:35374 | device_hub | Query | 5 | Copying to tmp table | |
| SELECT `devices`.`id` AS `id`, `serial_numbers`.`serial` AS `serial_number`, `mac_address`, CAST(group_concat(CAS | |
E WHEN lookup_metrics.ui_metric_name = 'version_firmware_dsp' THEN inverter_properties.property_value ELSE null END) AS decimal(30,2)) AS `version | |
_firmware_dsp`, CAST(group_concat(CASE WHEN lookup_metrics.ui_metric_name = 'version_firmware_pvm' THEN inverter_properties.property_value ELSE nu | |
ll END) AS decimal(30,2)) AS `version_firmware_pvm`, CAST(group_concat(CASE WHEN lookup_metrics.ui_metric_name = 'pvm_status' THEN inverter_proper | |
ties.property_value ELSE null END) AS decimal(30,2)) AS `pvm_status`, `model_name`, `inverter_connection_history`.`state` AS `location_state`, `fi | |
rst_reported`, `last_reported`, group_concat(DISTINCT inverter_lookup_status.name ORDER BY inverter_lookup_status.type SEPARATOR ',') AS `stat |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
mysql> SHOW INDEXES FROM inverter_status_history \G | |
*************************** 1. row *************************** | |
Table: inverter_status_history | |
Non_unique: 0 | |
Key_name: PRIMARY | |
Seq_in_index: 1 | |
Column_name: id | |
Collation: A | |
Cardinality: 8032570 | |
Sub_part: NULL |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
+----+-------------+-----------------------------+--------+--------------------------------------------------+------------------+---------+----------------------------------------------------------+------+----------------------------------------------+ | |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | |
+----+-------------+-----------------------------+--------+--------------------------------------------------+------------------+---------+----------------------------------------------------------+------+----------------------------------------------+ | |
| 1 | SIMPLE | devices | index | NULL | PRIMARY | 4 | NULL | 4962 | Using where; Using temporary; Using filesort | | |
| 1 | SIMPL |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
+----+-------------+-----------------------------+--------+--------------------------------------------------+------------------+---------+----------------------------------------------------------+------+----------------------------------------------+ | |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | |
+----+-------------+-----------------------------+--------+--------------------------------------------------+------------------+---------+----------------------------------------------------------+------+----------------------------------------------+ | |
| 1 | SIMPLE | devices | index | NULL | PRIMARY | 4 | NULL | 4962 | Using where; Using temporary; Using filesort | | |
| 1 | SIMPL |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
EXPLAIN | |
SELECT `devices`.`id` AS `id`, `serial_numbers`.`serial` AS `serial_number`, `mac_address`, | |
CAST(group_concat(CASE WHEN lookup_metrics.ui_metric_name = 'version_firmware_dsp' | |
THEN inverter_properties.property_value | |
ELSE null END) AS decimal(30,2)) AS `version_firmware_dsp`, | |
CAST(group_concat(CASE WHEN lookup_metrics.ui_metric_name = 'version_firmware_pvm' | |
THEN inverter_properties.property_value ELSE null END) AS decimal(30,2)) AS `version_firmware_pvm`, | |
CAST(group_concat(CASE WHEN lookup_metrics.ui_metric_name = 'pvm_status' | |
THEN inverter_properties.property_value |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
# | |
# Author: Josh Miller | |
# Date: 11/06/2012 | |
# | |
# ensure username was entered | |
user=$1 | |
if [ -z ${user} ] |