Oracle EM12C querie virtual machines

Oracle Enterprise Manager, partially in combination with oracle VM manager is able to monitor and manage your Oracle VM landscape and the virtual machines that are deployed on this. One of the advantages of Oracle Enterprise Manager is that all the information associated with known targets is that it is stored in a database. This means that with some simple SQL statements you are able to query information, in the below sample code we do a simple query on the Oracle Enterprise Manager database to get information about the virtual machines we have deployed on Oracle Enterprise Manager in combination with the location where they are in the cluster.

This query can be very handy in case you need to make a quick impact analysis and are in need to know in which datacenter, in which pool, on which physical server specific virtual machines are deployed.

SELECT 
   v_ovm_vm.ovm_display_name         AS VM_NAME,
   v_ovm_vm.kernel_ver               AS VM_KERNEL,
   v_ovm_serverpool.ovm_display_name AS VMSERVER_POOL,
   v_ovm_zone.ovm_display_name       AS VMSERVER_ZONE,
   v_ovm_server.OVM_DISPLAY_NAME     AS VMSERVER_SERVER
FROM 
   MGMT$VT_VM_SW_CFG v_ovm_vm,
   MGMT$VT_VSP_CONFIG v_ovm_serverpool,
   MGMT$VT_ZONE_CONFIG v_ovm_zone,
   MGMT$VT_VS_SW_CFG v_ovm_server
WHERE 
   v_ovm_vm.vsp_uuid = v_ovm_serverpool.vsp_uuid
   AND v_ovm_serverpool.zone_uuid = v_ovm_zone.zone_uuid
   AND v_ovm_vm.VS_UUID = v_ovm_server.vs_uuid
   ORDER BY 3,4,5,1

The code is also available on github where you can find a larger collection of scripts. This scripting repository will be updated continuously so everyone is able to make use of the scripts.