[MySQL] Random NetApp DWH SQL Queries: Where are our VDIs? Finding VMs and vCenters?

########################
## WHERE ARE OUR VDIS ##
########################
 
# dwh_inventory
describe dwh_inventory.acq_data_source;
select * from dwh_inventory.acq_data_source where vendor like '%VMware%';
select os,count(*) from dwh_inventory.hv_virtual_machine group by os;
select * from dwh_inventory.hv_virtual_machine where os = 'Microsoft Windows 10 (64-bit)'; # For Example
select * from dwh_inventory.hv_virtual_machine where dnsName like '%workstations%'; # Or search by dns FQDN
 
# dwh_capacity
describe vm_capacity_fact;
describe vm_dimension;
select count(*) from vm_dimension; # Might have millions
select * from vm_dimension where name = 'VM-NAME'; # Use to get the TK of your VM
select count(*) from vm_capacity_fact; # Even more than from vm_dimension
select * from vm_capacity_fact where vmTk = 'TK-OF-VM-FROM-ABOVE';
 
###############
# FINDING VMS #
###############
 
select * from hv_virtual_machine where name = 'VM-NAME'; # GET: DATASTOREID, vCenter IP
select * from hv_data_store_to_volume where dataStoreId = DATASTOREID; # GET: STORAGEID
select * from storage where id = STORAGEID; # GET: Storage Name
select * from storage where name like '%NAME%'; # GET: STORAGEID
select count(*) from hv_data_store_to_volume where storageId = STORAGEID; # COUNT
select name from acq_data_source where name like 'STRING%' and model = 'vSphere (Web Services)';
 
#########################
# VCENTERs PER OCI SITE #
#########################
 
select distinct(siteName) from acq_acquisition_unit; # GET: SITENAME
 
select ads.name,aau.siteName from acq_data_source AS ads
join acq_acquisition_unit AS aau ON aau.id = ads.acquisitionUnitId
where aau.siteName = 'SITENAME1' and ads.model = 'vSphere (Web Services)' order by ads.name;
 
select ads.name,aau.siteName from acq_data_source AS ads
join acq_acquisition_unit AS aau ON aau.id = ads.acquisitionUnitId
where aau.siteName = 'SITENAME2' and ads.model = 'vSphere (Web Services)' order by ads.name;
 
# FINDING vCENTERS in 2 SITES
SELECT * FROM
(select ads.name,aau.siteName from acq_data_source AS ads
join acq_acquisition_unit AS aau ON aau.id = ads.acquisitionUnitId
where aau.siteName = 'SITENAME1' and ads.model = 'vSphere (Web Services)' order by ads.name) as t0
JOIN
(select substring_index(ads.name,"_",-1) as 'name',aau.siteName from acq_data_source AS ads
join acq_acquisition_unit AS aau ON aau.id = ads.acquisitionUnitId
where aau.siteName = 'SITENAME2' and ads.model = 'vSphere (Web Services)' order by ads.name) as t1
ON t0.name = t1.name;