The following are some useful commands when searching the MYSQL DB:

Useful commands for MySQL

Get variable id# from the data property flat for a specific metric: select id,device,part,parttype,name from data_property_flat where device =” ” and name =” ”;

Get all the metric values from a given cache group table: select * from cache_group_ _ where variable = ;

Get the variable’s name: select * from data_variable where id= ;

Get the full size of the database: select concat (format(sum(data_length + index_length)/ power(1024,2),2), ' Mb') as`Size` from information_schema.tables where tables.table_type = 'base table';

Get the metric count: select count(*) from data_property where name=” ”;

Get the metric count by device: select distinct source, devtype, count(*) as ct_metrics from data_property_flat group by source, devtype order by source, devtype;

Get a count of inactive variables: select count(*) from data_variable where id in (select id from data_property_flat where vstatus='inactive');

Get a count of inactive properties: select count(*) from data_property where variableid in (select id from data_property_flat where vstatus='inactive');

Find the last X variables that where created: select * from data_variable order by id desc limit ;

Get a count of metrics per data group: select datagrp,count(*) as total from data_property_flat group by datagrp order by total desc;

Getting a list of inactive device in a text file for review: select data_variable.name,data_property_flat.device from data_property_flat left join data_variable on data_variable.id = data_property_flat.id and data_property_flat.vstatus=”inactive” into outfile “/”;

Manually (re)creating the admin account in the master db: insert into users.master (id, username, password, rolename, disabled) values (“1”, “admin”, “changeme”, “admin”, “0”);

Manually changing the admin password: update master.users set password = “ ” where username=“admin”;