Functionality for VM analysis

VMs.sql

/*
create table #counts (name sysname, n int)
go
declare @s varchar(max)
select @s = isnull(@s, '') + ' insert #counts select ''' + name + ''', count(*) from ' + name from sys.tables
exec(@s)
select * from #counts where n >= 16 order by 2
select * from vpx_object_type
select * from vpx_feature
select * from vpx_sequence order by id
select * from vpx_role
select * from vpx_host
select * from vpx_vm
select top 10 * from vpx_nic
select top 10 * from vpx_guest_disk
select is_connected, count(*) from vpx_nic group by is_connected
select * from vpx_nic where is_connected = 0
select * from vpx_entity where id in (
	select entity_id from vpx_nic where is_connected = 0
	)
*/
if object_id('VMField') is not null
	drop function VMField
go
create function VMField(@VMId int, @name nvarchar(510))
returns nvarchar(max)
as
	begin
	return (
		select	convert(nvarchar(max), v.value)
		from	vpx_field_def d
		join	vpx_field_val v on v.field_id = d.id
		join	vpx_entity e on e.id = cast(isnull(substring(v.mo_id,4,len(v.mo_id)),1) as bigint)
		join	vpx_object_type t on t.id = e.type_id	
		where	d.name = @name
		and		e.id = @VMId
		and		t.name = 'VM'
		)
	end
go
select dbo.VMField(10165, 'Owner Email')
go
if object_id('VMConfigVal') is not null
	drop function VMConfigVal
go
create function VMConfigVal(@VMId int, @path varchar(max))
returns nvarchar(max)
as
	begin
	declare	@x xml
	,		@ret nvarchar(max)

	select	@x = master.dbo.RegExReplace('xsi:type="[\w:]+"|xmlns="[\w:]+"', '', Config)
	from	vpx_vm
	where	id = @VMId
      
      -- return @x.value('(' + @path + ')[1]', 'varchar(max)')

	;with cte as
	(
	select	value = x.c.value('.', 'varchar(50)')
	,		path = cast ( null as varchar(max) )
	,		node = x.c.query('.')
	from	@x.nodes('/*') x(c)
	union all
	select	n.c.value('.', 'varchar(50)')
	,		isnull( c.path + '/', '/' ) + n.c.value('local-name(.)', 'varchar(max)')
	,		n.c.query('*')
	from	cte c
	cross	apply c.node.nodes('*') n(c)
	)
	select @ret = value from cte where path = @path
	return @ret
	end
go
select dbo.VMConfigVal(10165, '/obj/guestFullName')
go
if object_id('VMDevices') is not null
	drop function VMDevices
go
create function VMDevices(@VMId int)
returns @ret table (
	id int
,	macAddress varchar(256)
,	label varchar(256)
,	thinProvisioned varchar(5)
,	diskMode varchar(32)
,	writeThrough varchar(5)
,	split varchar(5)
,	shares varchar(32)
,	sharesLevel varchar(32)
)
as
begin
	declare @x xml
	select	@x = master.dbo.RegExReplace('xsi:type="[\w:]+"|xmlns="[\w:]+"', '', Config)
	from	vpx_vm
	where	id = @VMId
	
	insert	@ret
	select	x.value('(key)[1]', 'int')
	,		x.value('(macAddress)[1]', 'varchar(256)')
	,		x.value('(deviceInfo/label)[1]', 'varchar(256)')
	,		x.value('(backing/thinProvisioned)[1]', 'varchar(5)')
	,		x.value('(backing/diskMode)[1]', 'varchar(32)')
	,		x.value('(backing/writeThrough)[1]', 'varchar(5)')
	,		x.value('(backing/split)[1]', 'varchar(5)')
	,		x.value('(shares/shares)[1]', 'varchar(32)')
	,		x.value('(shares/level)[1]', 'varchar(32)')
	from	@x.nodes('/obj/hardware/device') t(x)
	return
end
go
select * from dbo.VMDevices(10165)
go
if object_id('DimVm') is not null
	drop view DimVm
go
create view DimVm
as
	select	vm.id
	,		e.Name
	,		FolderName = (
			select	Name
			from	vpx_entity
			where	id = e.parent_id
			)
	,		[State] = case power_state when 0 then 'Powered Off' else 'Powered On' end
	,		[Guest OS] = guest_os
	,		[Guest Family] = guest_family
	,		[Guest State] = guest_state
	,		FQDN = dns_name
	,		[Is Template] = is_template
	,		[Host IP] = ip_address
	,		[description]
	,		annotation
	-- custom fields
	,		[Owner] = dbo.VMField(vm.id, 'Owner Email')
	,		[Department] = dbo.VMField(vm.id, 'Department')
	,		[Expiration Date] = dbo.VMField(vm.id, 'Expiration Date')
	,		[Backup Frequency] = dbo.VMField(vm.id, 'Backup Frequency')
	,		[Purpose] = dbo.VMField(vm.id, 'Purpose')
	,		[Type] = dbo.VMField(vm.id, 'Type')
	,		[Recovery Priority] = dbo.VMField(vm.id, 'Recovery Priority')
	,		[Notify Email] = dbo.VMField(vm.id, 'Notify Email')
	,		[Comments] = dbo.VMField(vm.id, 'Comments')
	,		[Status] = dbo.VMField(vm.id, 'Status')
	,		[Lease Expiration Date] = dbo.VMField(vm.id, 'Lease Expiration Date')
	,		[Last Good Backup] = dbo.VMField(vm.id, 'Last Good Backup')
	,		[WorkFlow ID] = dbo.VMField(vm.id, 'WorkFlow ID')
	,		[WorkFlow Info Populated] = dbo.VMField(vm.id, 'WorkFlow Info Populated')
	-- device-level information
	,		vmd.Label
	,		vmd.thinProvisioned
	,		vmd.diskMode
	,		vmd.writeThrough
	-- everything but the kitchen sink
	,		FileName = file_name
	,		LocalFileName = local_file_name
	,		DisabledSnapshot = disabled_snapshot
	,		UUID_BIOS
	,		ResourceGroupId = resource_group_id
	,		host_id
	,		tools_status
	,		tools_version
	,		screen_width
	,		screen_height
	,		agent_id
	,		agent_cnx_state
	,		pending_name
	,		pending_annotation
	,		pending_annot_set_flg
	,		failed_dmotion
	,		e.parent_id
	,		ConfigXML = convert(xml, master.dbo.RegExReplace('xsi:type="[\w:]+"|xmlns="[\w:]+"', '', Config))
	from	vpx_vm vm
	join	vpx_entity e on e.id = vm.id
	cross	apply dbo.VMDevices(vm.id) vmd
	where	charindex('Hard', vmd.label) + charindex('Network Adapter', vmd.label) > 0
go
select * from vpx_entity
select * from DimVM
go
if object_id('FactVm') is not null
	drop view FactVm
go
create view FactVm as
	select	id
	,		VCPUs = num_vcpu
	,		NICs = num_nic
	,		boot_time
	,		[Mem Size (MB)] = mem_size_mb
	,		[Mem Overhead (MB)] = memory_overhead / power(1024,2)
	,		[Disk Capacity (GB)] = 1.0 * convert(bigint, capacity) / power(1024,3)
	,		[Disk Free Space (GB)] = 1.0 * convert(bigint, free_space) / power(1024,3)
	-- everything else
	,		memory_reservation
	,		cpu_reservation
	,		num_disk
	,		suspend_time
	,		suspend_interval
	,		tools_mounted
	,		mks_connections
	from	vpx_vm vm
	join	vpx_guest_disk d on d.vm_id = vm.id
go
select * from FactVM
go
if object_id('XLVm') is not null
	drop view XLVm
go
create view XLVm
as
	select	Name
	,		FolderName
	,		[Owner]
	,		[Department]
	,		[State]
	,		FQDN
	-- config data
	-- config data
	,		OS = ConfigXML.value('(/obj/guestFullName)[1]', 'varchar(255)')
	,		changeVersion = ConfigXML.value('(/obj/changeVersion)[1]', 'varchar(255)')
	,		modified = ConfigXML.value('(/obj/modified)[1]', 'varchar(255)')
	,		[version] = ConfigXML.value('(/obj/version)[1]', 'varchar(255)')
	,		alternateGuestName = ConfigXML.value('(/obj/alternateGuestName)[1]', 'varchar(255)')
	,		CPUAllocReservation = ConfigXML.value('(/obj/cpuAllocation/reservation)[1]', 'varchar(255)')
	,		CPUAllocLimit = ConfigXML.value('(/obj/cpuAllocation/limit)[1]', 'varchar(255)')
	,		CPUAllocShares = ConfigXML.value('(/obj/cpuAllocation/shares/shares)[1]', 'varchar(255)')
	,		CPUAllocSharesLevel = ConfigXML.value('(/obj/cpuAllocation/shares/level)[1]', 'varchar(255)')
	,		MemAllocReservation = ConfigXML.value('(/obj/memoryAllocation/reservation)[1]', 'varchar(255)')
	,		MemAllocLimit = ConfigXML.value('(/obj/memoryAllocation/limit)[1]', 'varchar(255)')
	,		MemAllocShares = ConfigXML.value('(/obj/memoryAllocation/shares/shares)[1]', 'varchar(255)')
	,		MemAllocSharesLevel = ConfigXML.value('(/obj/memoryAllocation/shares/level)[1]', 'varchar(255)')
	-- custom field data
	,		[Expiration Date]
	,		[Backup Frequency]
	,		[Purpose]
	,		[Type]
	,		[Recovery Priority]
	,		[Notify Email]
	,		[Comments]
	,		[Status]
	,		[Lease Expiration Date]
	,		[Last Good Backup]
	,		[WorkFlow ID]
	,		[WorkFlow Info Populated]
	-- everything else
	,		[Guest OS]
	,		[Guest Family]
	,		[Guest State]
	,		[Is Template]
	,		[Host IP]
	,		[description]
	,		annotation
	,		Label
	,		thinProvisioned
	,		diskMode
	,		writeThrough
	,		FileName
	,		LocalFileName
	,		DisabledSnapshot
	,		UUID_BIOS
	,		ResourceGroupId
	,		host_id
	,		tools_status
	,		tools_version
	,		screen_width
	,		screen_height
	,		agent_id
	,		agent_cnx_state
	,		pending_name
	,		pending_annotation
	,		pending_annot_set_flg
	,		failed_dmotion
	--		fact data
	,		[Disk Capacity (GB)]
	,		[Disk Free Space (GB)]
	,		[Disk Free (%)] = 100.0 * [Disk Free Space (GB)] / [Disk Capacity (GB)]
	--		everything else
	,		VCPUs
	,		NICs
	,		boot_time
	,		[Mem Size (MB)]
	,		[Mem Overhead (MB)]
	,		memory_reservation
	,		cpu_reservation
	,		num_disk
	,		suspend_time
	,		suspend_interval
	,		tools_mounted
	,		mks_connections
	from	DimVM 
	join	FactVM on FactVM.id = DimVM.id
go
select top 10 * from XLVm
go

select top 10 * from VPXV_HIST_STAT_MONTHLY
select distinct stat_rollup_type from VPXV_HIST_STAT_MONTHLY
select distinct stat_name from VPXV_HIST_STAT_MONTHLY
select distinct stat_group from VPXV_HIST_STAT_MONTHLY
select device_name, count(*) from vpxv_hist_stat_monthly group by device_name
select	top 10 *
from	dbo.vpxv_hist_stat_monthly s with (nolock)
where	s.stat_rollup_type = 'average'
and		s.stat_name = 'usage'
and		s.stat_group in ('cpu','disk','mem','net')

group by s.entity

select	*
from	dbo.vpxv_hist_stat_monthly s with (nolock)
where	s.stat_rollup_type = 'average'
and		s.stat_name = 'usage'
and		s.stat_group in ('disk')
and		entity = 'vm-65'
order by 1