Jump to content
SDRI

Virtualization Manager Reports

Recommended Posts

I'm looking for a way to extract a Dataview or run reports to pull the data out of Virtualization Manager so that we can get a quick break-down of VMWare/HyperV hosts and guest in detail. Does anyone know the best way to do this? Running 2020.1. Any direction would be appreciated. 

Thanks!

SDRI

Share this post


Link to post
Share on other sites

did you ever find out anything? I am looking for the same.. or reports from the monitors on the vmware machines especially

Share this post


Link to post
Share on other sites

I have no report , but what kind of information do you need to report on?

I got a few SQL queries if you want them

Share this post


Link to post
Share on other sites
Posted (edited)
On 5/14/2020 at 3:07 AM, p_ern said:

I have no report , but what kind of information do you need to report on?

I got a few SQL queries if you want them

I wouldn't mine taking a look at your queries if you don't mind sharing them. Thanks!

Edited by mgragen

Share this post


Link to post
Share on other sites

Heres a few since I am not sure what you need.

ESXi-storage

SELECT
StorageLastUpdate)  ,
StorageName ,
FreeGB  ,
CapacityGB
FROM plugin_vm_esxdatastorage
WHERE StorageLastUpdat) and StorageLastUpdate > DATE_ADD(NOW(),INTERVAL -365 DAY) 
 
Group by Storagename

For percentages change (CapacityGB -FreeGB) / CapacityGB *100  as 'Capacity Used %'

Average ESXi Host CPU/RAM

SELECT 
(LastupDate) as time_sec,
DeviceName ,
(CPUGhz *1000 *NumberCores) AS TotalCPU ,
OverallCPUUsage,
(CPUGhz *1000*NumberCores -OverallCPUUsage) / (CPUGhz *1000*NumberCores) *100 AS "CPU Free %",

RAMmb AS TotalRAM,
OverallMemoryUsage,
(RAMmb - OverallMemoryUsage ) / RAMmb *100 AS "RAM Free %"
FROM 
plugin_vm_esxhosts

Group BY Devicename

Virtual Machines Count

SELECT 
Sum(VirtualmachineTemplateCount)

FROM plugin_vm_esxhosts

This one is quite large,but gives alot of information:

Number of VM on each host, vmlist, memory usage, IP and datastorage

SELECT locations.clientID,
plugin_vm_esxhosts.DeviceId,
plugin_vm_esxhosts.DeviceName,
plugin_vm_esxhosts.VirtualMachineTemplateCount AS 'VMs' , 
GROUP_CONCAT(DISTINCT plugin_vm_esxvirtualmachines.VmName) AS VMList,	
CONCAT(plugin_vm_esxhosts.overallmemoryusage DIV 1024, 'GB', '/', plugin_vm_esxhosts.RamMb DIV 1024, 'GB') AS MEMUsed, 
CONCAT(plugin_vm_esxhosts.ProcessorSockets,'x',' ',plugin_vm_esxhosts.ProcessorType, ' (',plugin_vm_esxhosts.CoresPerSocket * plugin_vm_esxhosts.ProcessorSockets, ' Total Cores)' ) AS CPU,
networkdevices.IPAddress AS hostIP,
GROUP_CONCAT(DISTINCT CONCAT(ROUND(plugin_vm_esxdatastorage.CapacityGB - plugin_vm_esxdatastorage.FreeGB,0),' GB', '/',ROUND(plugin_vm_esxdatastorage.CapacityGB,0), ' GB')) AS Datastorage
FROM plugin_vm_esxhosts
JOIN networkdevices ON networkdevices.deviceID = plugin_vm_esxhosts.deviceid 
JOIN locations ON networkdevices.locationid = locations.locationid
JOIN plugin_vm_esxvirtualmachines ON networkdevices.deviceID = plugin_vm_esxvirtualmachines.deviceID
JOIN plugin_vm_esxdatastorage ON plugin_vm_esxvirtualmachines.deviceid = plugin_vm_esxdatastorage.deviceID
GROUP BY SUBSTRING(SUBSTRING_INDEX(plugin_vm_esxhosts.OtherInformation, ';', -1 ), 13)

ESX Host Sensors:

Insert deviceid on Where DeviceID='CHANGE THIS'

select
SensorName as metric,
SensorReading as _text,
LastUpdate as _time
from plugin_vm_esxhostsensors
Where DeviceID='CHANGE THIS' and SensorReading >'0'

 

  • Thanks 1

Share this post


Link to post
Share on other sites
On 6/10/2020 at 3:39 AM, p_ern said:

Heres a few since I am not sure what you need.

ESXi-storage


SELECT
StorageLastUpdate)  ,
StorageName ,
FreeGB  ,
CapacityGB
FROM plugin_vm_esxdatastorage
WHERE StorageLastUpdat) and StorageLastUpdate > DATE_ADD(NOW(),INTERVAL -365 DAY) 
 
Group by Storagename

For percentages change (CapacityGB -FreeGB) / CapacityGB *100  as 'Capacity Used %'

Average ESXi Host CPU/RAM


SELECT 
(LastupDate) as time_sec,
DeviceName ,
(CPUGhz *1000 *NumberCores) AS TotalCPU ,
OverallCPUUsage,
(CPUGhz *1000*NumberCores -OverallCPUUsage) / (CPUGhz *1000*NumberCores) *100 AS "CPU Free %",

RAMmb AS TotalRAM,
OverallMemoryUsage,
(RAMmb - OverallMemoryUsage ) / RAMmb *100 AS "RAM Free %"
FROM 
plugin_vm_esxhosts

Group BY Devicename

Virtual Machines Count


SELECT 
Sum(VirtualmachineTemplateCount)

FROM plugin_vm_esxhosts

This one is quite large,but gives alot of information:

Number of VM on each host, vmlist, memory usage, IP and datastorage


SELECT locations.clientID,
plugin_vm_esxhosts.DeviceId,
plugin_vm_esxhosts.DeviceName,
plugin_vm_esxhosts.VirtualMachineTemplateCount AS 'VMs' , 
GROUP_CONCAT(DISTINCT plugin_vm_esxvirtualmachines.VmName) AS VMList,	
CONCAT(plugin_vm_esxhosts.overallmemoryusage DIV 1024, 'GB', '/', plugin_vm_esxhosts.RamMb DIV 1024, 'GB') AS MEMUsed, 
CONCAT(plugin_vm_esxhosts.ProcessorSockets,'x',' ',plugin_vm_esxhosts.ProcessorType, ' (',plugin_vm_esxhosts.CoresPerSocket * plugin_vm_esxhosts.ProcessorSockets, ' Total Cores)' ) AS CPU,
networkdevices.IPAddress AS hostIP,
GROUP_CONCAT(DISTINCT CONCAT(ROUND(plugin_vm_esxdatastorage.CapacityGB - plugin_vm_esxdatastorage.FreeGB,0),' GB', '/',ROUND(plugin_vm_esxdatastorage.CapacityGB,0), ' GB')) AS Datastorage
FROM plugin_vm_esxhosts
JOIN networkdevices ON networkdevices.deviceID = plugin_vm_esxhosts.deviceid 
JOIN locations ON networkdevices.locationid = locations.locationid
JOIN plugin_vm_esxvirtualmachines ON networkdevices.deviceID = plugin_vm_esxvirtualmachines.deviceID
JOIN plugin_vm_esxdatastorage ON plugin_vm_esxvirtualmachines.deviceid = plugin_vm_esxdatastorage.deviceID
GROUP BY SUBSTRING(SUBSTRING_INDEX(plugin_vm_esxhosts.OtherInformation, ';', -1 ), 13)

ESX Host Sensors:

Insert deviceid on Where DeviceID='CHANGE THIS'


select
SensorName as metric,
SensorReading as _text,
LastUpdate as _time
from plugin_vm_esxhostsensors
Where DeviceID='CHANGE THIS' and SensorReading >'0'

 

Cool thanks man I appreciate it!

Share this post


Link to post
Share on other sites

I added VMWare information into my Asset Summary report with the following Query:

SELECT
  `locations`.`ClientID`                    AS `clientID`,
  `locations`.`LocationID`                  AS `LocationID`,
  `plugin_vm_esxhosts`.`DeviceId`           AS `DeviceId`,
  `plugin_vm_esxhosts`.`DeviceName`         AS `DeviceName`,
  GROUP_CONCAT(DISTINCT `plugin_vm_esxvirtualmachines`.`VmName` SEPARATOR ',') AS `VMList`,
  `plugin_vm_esxhosts`.`ProductName`        AS `ProductName`,
  SUBSTRING_INDEX(SUBSTRING_INDEX(`plugin_vm_esxhosts`.`OtherInformation`,'Service tag=',-(1)),';',1) AS `SERIAL`,
  `plugin_vm_esxhosts`.`ProductFullVersion` AS `ProductFullVersion`,
  `plugin_vm_esxhosts`.`Vendor`             AS `Vendor`,
  CONCAT(`plugin_vm_esxhosts`.`Vendor`,' ',`plugin_vm_esxhosts`.`Model`) AS `ModelString`,
  `plugin_vm_esxhosts`.`BiosVersion`        AS `BiosVersion`,
  `plugin_vm_esxhosts`.`License`            AS `License`,
  `plugin_vm_esxhosts`.`CpuGhz`             AS `CpuGhz`,
  `plugin_vm_esxhosts`.`NumberCores`        AS `NumberCores`,
  CONCAT((`plugin_vm_esxhosts`.`OverallMemoryUsage` DIV 1024),'GB','/',(`plugin_vm_esxhosts`.`RamMb` DIV 1024),'GB') AS `MEMUsed`,
  CONCAT(`plugin_vm_esxhosts`.`ProcessorSockets`,'x',' ',`plugin_vm_esxhosts`.`ProcessorType`,' (',(`plugin_vm_esxhosts`.`CoresPerSocket` * `plugin_vm_esxhosts`.`ProcessorSockets`),' Total Cores)') AS `CPU`,
  CONCAT(`plugin_vm_esxhosts`.`HyperThreadActive`,'/',`plugin_vm_esxhosts`.`HyperThreadAvailable`) AS `HTActiveAvail`,
  `plugin_vm_esxhosts`.`NumberNics`         AS `NumberNics`,
  `plugin_vm_esxhosts`.`CoresPerSocket`     AS `CoresPerSocket`,
  `locations`.`Name`                        AS `LocationName`,
  `networkdevices`.`IPAddress`              AS `hostIP`,
  `plugin_vm_esxhosts`.`LastUpdate`         AS `Last Updated`,
  GROUP_CONCAT(DISTINCT CONCAT(ROUND((`plugin_vm_esxdatastorage`.`CapacityGB` - `plugin_vm_esxdatastorage`.`FreeGB`),0),' GB','/',ROUND(`plugin_vm_esxdatastorage`.`CapacityGB`,0),' GB') SEPARATOR ',') AS `Datastorage`
FROM ((((`plugin_vm_esxhosts`
      JOIN `networkdevices`
        ON ((`networkdevices`.`DeviceID` = `plugin_vm_esxhosts`.`DeviceId`)))
     JOIN `locations`
       ON ((`networkdevices`.`LocationID` = `locations`.`LocationID`)))
    LEFT JOIN `plugin_vm_esxvirtualmachines`
      ON ((`networkdevices`.`DeviceID` = `plugin_vm_esxvirtualmachines`.`DeviceId`)))
   LEFT JOIN `plugin_vm_esxdatastorage`
     ON ((`plugin_vm_esxvirtualmachines`.`DeviceId` = `plugin_vm_esxdatastorage`.`DeviceId`)))
GROUP BY `plugin_vm_esxhosts`.`DeviceId`

Because Report Center does not like RAW SQL query's (Or didn't when I created the report). I created a custom view and added it to the report.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...