Anyone running VC SQL database queries?

[lldmka]

.... to extract data for reporting and capacity planning (eg. number of hosts, number of VMs, total disk space, free disk space, etc)?

[JohnGibson · Accepted solution]

I have a colelction of custom views in my sql database which i use to generate custom daily extracts to a http page... some of these rely upon custom fields so may not work.

you can either extract them using a sql query using osql, i.e.

EXECUTE sp_makewebtask @outputfile = 'E:\Inetpub\wwwroot\ESXHostDensity.html',

@query = 'SELECT * FROM VirtualCenter.dbo.RPT_HOST_DENSITY_REPORT',

@dbname = 'VirtualCenter', @rowcnt = 0, @whentype = 1, @webpagetitle = 'ESX Host Density Report',

@resultstitle = 'ESX Host Density Report', @HTMLheader = 2, @tabborder = 1

GO

or bcp to csv file.

CREATE VIEW dbo.RPT_ESX_COUNT_BY_SITE

AS

SELECT TOP 100 PERCENT VPX_ENTITY_4.NAME AS SITE, COUNT(e.DNS_NAME) AS HOSTS,

CASE e.VMOTION_ENABLED WHEN 0 THEN 'Tier 2' WHEN 1 THEN 'Tier 1' END AS TIER

FROM dbo.VPX_HOST e INNER JOIN

dbo.VPX_ENTITY ON e.ID = dbo.VPX_ENTITY.ID INNER JOIN

dbo.VPX_ENTITY VPX_ENTITY_1 ON dbo.VPX_ENTITY.PARENT_ID = VPX_ENTITY_1.ID INNER JOIN

dbo.VPX_ENTITY VPX_ENTITY_2 ON VPX_ENTITY_1.PARENT_ID = VPX_ENTITY_2.ID INNER JOIN

dbo.VPX_ENTITY VPX_ENTITY_3 ON VPX_ENTITY_2.PARENT_ID = VPX_ENTITY_3.ID INNER JOIN

dbo.VPX_ENTITY VPX_ENTITY_4 ON VPX_ENTITY_3.PARENT_ID = VPX_ENTITY_4.ID

GROUP BY VPX_ENTITY_4.NAME, e.VMOTION_ENABLED

ORDER BY VPX_ENTITY_4.NAME, CASE e.VMOTION_ENABLED WHEN 0 THEN 'Tier 2' WHEN 1 THEN 'Tier 1' END

CREATE VIEW dbo.RPT_HOST_DENSITY_REPORT

AS

SELECT TOP 100 PERCENT dbo.VPX_HOST.DNS_NAME AS , dbo.VPX_HOST.HOST_MODEL AS ,

dbo.VPX_HOST.CPU_COUNT AS , dbo.VPX_HOST.CPU_CORE_COUNT AS , COUNT(dbo.VPX_VM.ID) AS ,

SUM(dbo.VPX_VM.NUM_VCPU) AS

FROM dbo.VPX_VM RIGHT OUTER JOIN

dbo.VPX_HOST ON dbo.VPX_VM.HOST_ID = dbo.VPX_HOST.ID

GROUP BY dbo.VPX_HOST.DNS_NAME, dbo.VPX_HOST.HOST_MODEL, dbo.VPX_HOST.CPU_COUNT, dbo.VPX_HOST.CPU_CORE_COUNT,

dbo.VPX_VM.IS_TEMPLATE

HAVING (dbo.VPX_VM.IS_TEMPLATE = 0) OR

(dbo.VPX_VM.IS_TEMPLATE IS NULL)

ORDER BY dbo.VPX_HOST.DNS_NAME

CREATE VIEW dbo.RPT_HOST_REPORT

AS

SELECT TOP 100 PERCENT DNS_NAME, PRODUCT_FULLNAME, PRODUCT_VERSION, PRODUCT_BUILD, IP_ADDRESS, VMOTION_ENABLED, HOST_VENDOR,

HOST_MODEL, CPU_MODEL, CPU_COUNT, CPU_CORE_COUNT, CPU_HZ, MEM_SIZE, NIC_COUNT, HBA_COUNT, BOOT_TIME, ID

FROM dbo.VPX_HOST e

ORDER BY DNS_NAME

CREATE VIEW dbo.RPT_HOST_STORAGE_CAPACITY

AS

SELECT TOP 100 PERCENT NAME AS , CONVERT(bigint, CAPACITY) / 1073741824 AS 'Total Gb', CONVERT(BIGINT, FREE_SPACE)

/ 1073741824 AS 'Free Gb'

FROM dbo.VPX_DATASTORE

ORDER BY NAME

CREATE VIEW dbo.RPT_HOST_TIER_DENSITY_REPORT

AS

SELECT TOP 100 PERCENT CASE dbo.VPX_HOST.VMOTION_ENABLED WHEN 0 THEN 'Tier 2' WHEN 1 THEN 'Tier 1' END AS ,

COUNT(dbo.VPX_VM.ID) AS , SUM(dbo.VPX_VM.NUM_VCPU) AS

FROM dbo.VPX_VM RIGHT OUTER JOIN

dbo.VPX_HOST ON dbo.VPX_VM.HOST_ID = dbo.VPX_HOST.ID

GROUP BY dbo.VPX_HOST.VMOTION_ENABLED, dbo.VPX_VM.IS_TEMPLATE

HAVING (dbo.VPX_VM.IS_TEMPLATE = 0)

ORDER BY CASE dbo.VPX_HOST.VMOTION_ENABLED WHEN 0 THEN 'Tier 2' WHEN 1 THEN 'Tier 1' END

CREATE VIEW dbo.RPT_VM_COUNT_BY_SITE

AS

SELECT TOP 100 PERCENT VPX_ENTITY_4.NAME AS SITE, COUNT(dbo.VPX_VM.DATACENTER_ID) AS VM

FROM dbo.VPX_HOST e INNER JOIN

dbo.VPX_VM ON e.ID = dbo.VPX_VM.HOST_ID INNER JOIN

dbo.VPX_ENTITY ON e.ID = dbo.VPX_ENTITY.ID INNER JOIN

dbo.VPX_ENTITY VPX_ENTITY_1 ON dbo.VPX_ENTITY.PARENT_ID = VPX_ENTITY_1.ID INNER JOIN

dbo.VPX_ENTITY VPX_ENTITY_2 ON VPX_ENTITY_1.PARENT_ID = VPX_ENTITY_2.ID INNER JOIN

dbo.VPX_ENTITY VPX_ENTITY_3 ON VPX_ENTITY_2.PARENT_ID = VPX_ENTITY_3.ID INNER JOIN

dbo.VPX_ENTITY VPX_ENTITY_4 ON VPX_ENTITY_3.PARENT_ID = VPX_ENTITY_4.ID

GROUP BY dbo.VPX_VM.IS_TEMPLATE, VPX_ENTITY_4.NAME

HAVING (dbo.VPX_VM.IS_TEMPLATE = 0)

CREATE VIEW dbo.RPT_VM_VM_by_vCPU

AS

SELECT NUM_VCPU AS vCPU, COUNT(ID) AS

FROM dbo.VPX_VM

GROUP BY IS_TEMPLATE, NUM_VCPU

HAVING (IS_TEMPLATE = 0)

CREATE VIEW dbo.RPT_VM_DISK_CAPACITY

AS

SELECT dbo.VPXV_VMS.NAME, dbo.VPX_GUEST_DISK.PATH, CONVERT(bigint, dbo.VPX_GUEST_DISK.CAPACITY) / 1048576 AS 'Capacity Mb', CONVERT(bigint,

dbo.VPX_GUEST_DISK.FREE_SPACE) / 1048576 AS 'Free Mb'

FROM dbo.VPX_GUEST_DISK INNER JOIN

dbo.VPXV_VMS ON dbo.VPX_GUEST_DISK.VM_ID = dbo.VPXV_VMS.VMID

CREATE VIEW dbo.RPT_VM_VM_by_Memory

AS

SELECT TOP 100 PERCENT MEM_SIZE_MB AS , COUNT(ID) AS

FROM dbo.VPX_VM

GROUP BY IS_TEMPLATE, MEM_SIZE_MB

HAVING (IS_TEMPLATE = 0)

ORDER BY MEM_SIZE_MB

CREATE VIEW dbo.RPT_VM_REPORT

AS

SELECT TOP 100 PERCENT e.NAME AS , v.GUEST_OS AS , v.NUM_VCPU AS , v.MEM_SIZE_MB AS ,

v.GUEST_STATE AS Status, v.POWER_STATE AS , v.TOOLS_STATUS AS , v.IP_ADDRESS AS ,

v.DNS_NAME AS FQDN, v.BOOT_TIME, dbo.VPX_FIELD_VAL.[VALUE] AS Application/Service, VPX_FIELD_VAL_1.[VALUE] AS Category,

dbo.VPX_HOST.DNS_NAME AS , dbo.VPX_HOST.PRODUCT_FULLNAME AS ,

dbo.VPX_HOST.CPU_COUNT AS , dbo.VPX_HOST.CPU_CORE_COUNT AS

FROM dbo.VPX_ENTITY e INNER JOIN

dbo.VPX_VM v ON e.ID = v.ID INNER JOIN

dbo.VPX_FIELD_VAL ON e.ID = dbo.VPX_FIELD_VAL.ENTITY_ID INNER JOIN

dbo.VPX_FIELD_VAL VPX_FIELD_VAL_1 ON e.ID = VPX_FIELD_VAL_1.ENTITY_ID INNER JOIN

dbo.VPX_HOST ON v.HOST_ID = dbo.VPX_HOST.ID

WHERE (dbo.VPX_FIELD_VAL.FIELD_ID = 1) AND (VPX_FIELD_VAL_1.FIELD_ID = 2) AND (v.IS_TEMPLATE = 0)

ORDER BY e.NAME

CREATE VIEW dbo.RPT_VM_REPORT_CUSTOM

AS

SELECT TOP 100 PERCENT e.NAME AS , v.GUEST_OS AS , v.NUM_VCPU AS , v.MEM_SIZE_MB AS ,

v.IP_ADDRESS AS , dbo.VPX_FIELD_VAL.[VALUE] AS Application/Service, VPX_FIELD_VAL_1.[VALUE] AS Category,

VPX_FIELD_VAL_2.[VALUE] AS , VPX_FIELD_VAL_3.[VALUE] AS , dbo.VPX_HOST.DNS_NAME AS

FROM dbo.VPX_ENTITY e INNER JOIN

dbo.VPX_VM v ON e.ID = v.ID INNER JOIN

dbo.VPX_FIELD_VAL ON e.ID = dbo.VPX_FIELD_VAL.ENTITY_ID INNER JOIN

dbo.VPX_FIELD_VAL VPX_FIELD_VAL_1 ON e.ID = VPX_FIELD_VAL_1.ENTITY_ID INNER JOIN

dbo.VPX_HOST ON v.HOST_ID = dbo.VPX_HOST.ID INNER JOIN

dbo.VPX_FIELD_VAL VPX_FIELD_VAL_2 ON e.ID = VPX_FIELD_VAL_2.ENTITY_ID INNER JOIN

dbo.VPX_FIELD_VAL VPX_FIELD_VAL_3 ON e.ID = VPX_FIELD_VAL_3.ENTITY_ID

WHERE (dbo.VPX_FIELD_VAL.FIELD_ID = 1) AND (VPX_FIELD_VAL_1.FIELD_ID = 2) AND (v.IS_TEMPLATE = 0) AND (VPX_FIELD_VAL_2.FIELD_ID = 5) AND

(VPX_FIELD_VAL_3.FIELD_ID = 4)

ORDER BY dbo.VPX_HOST.DNS_NAME

[lldmka]

That is great, thanks John!

Mark