Skip to content
Tags

VMware: finding VM snapshots via SQL query

16.06.2015

Snapshots are reasonable good. Just click «Snapshot» and you’re saved. Once then you forgotten to remove snapshot. What happens next? It grows. No. It Grows. And also makes datastore response time grow. If snapshot was created on heavy loaded VM which is database server for example, snapshot removal can take up to several hours to complete. This can bring additional problems. The best way to avoid them is to prevent them.

There are two ways to find VMs with snapshots. First one is using Powershell PowerCLI script. It well described on the internets:

Add-PSSnapin VMware.VimAutomation.Core
Connect-VIServer vcenter.domain.local
Get-VM | Get-Snapshot

Scripting is flexible and easy. You can generate report and send it via e-mail. No matter if script runs for several minutes. But as for me this matters if it runs longer. This made me to do some research on vCenter database. Resulting query made me wonder. SQL executed in less than one second and returned the same results as Powershell script, which was running for 10 minutes! Query returns table with the following columns: VM Name, Snapshot name, Description, Snapshot Size, Creation time and Username.

So this is the query:

SELECT DISTINCT
VPX_ENTITY.NAME,
VPX_SNAPSHOT.SNAPSHOT_NAME,
CAST(VPX_SNAPSHOT.SNAPSHOT_DESC AS NVARCHAR(MAX)) AS Description,
SUM(CAST(CAST(VPX_VM_FLE_FILE_INFO.FILE_SIZE AS DECIMAL(18, 0)) / 1073741824 AS DECIMAL(9, 2))) AS FileSize,
VPX_SNAPSHOT.CREATE_TIME,
VPX_TASK.USERNAME
FROM dbo.VPX_SNAPSHOT
INNER JOIN dbo.VPX_ENTITY
ON VPX_SNAPSHOT.VM_ID = VPX_ENTITY.ID
INNER JOIN dbo.VPX_VM_FLE_FILE_INFO
ON VPX_SNAPSHOT.VM_ID = VPX_VM_FLE_FILE_INFO.VM_ID
INNER JOIN dbo.VPX_TASK
ON VPX_SNAPSHOT.VM_ID = VPX_TASK.VM_ID
WHERE VPX_VM_FLE_FILE_INFO.TYPE = ‘diskExtent’
AND VPX_VM_FLE_FILE_INFO.NAME LIKE ‘%-delta.vmdk’
AND VPX_TASK.COMPLETE_TIME BETWEEN DATEADD(mi, -1, VPX_SNAPSHOT.CREATE_TIME) AND DATEADD(mi, 1, VPX_SNAPSHOT.CREATE_TIME)
AND VPX_TASK.NAME = ‘vim.VirtualMachine.createSnapshot’
GROUP BY VPX_SNAPSHOT.SNAPSHOT_NAME,
VPX_ENTITY.NAME,
CAST(VPX_SNAPSHOT.SNAPSHOT_DESC AS NVARCHAR(MAX)),
VPX_SNAPSHOT.CREATE_TIME,
VPX_TASK.USERNAME
ORDER BY VPX_ENTITY.NAME

Реклама

From → VMware

Добавить комментарий

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

%d такие блоггеры, как: