Version 1

    This custom report can be used to show all events for a specific VM by name. Events include powering on/off a VM, a VM moved from one ESX host to another, etc. before running this script, modify the value of @VMName and the @BeginDate and @EndDate variables.

     

    DECLARE @VMID varchar(5), @SQL nvarchar(max), @SQLParms nvarchar(max);

    DECLARE @BeginDate datetime = '1/1/2018 00:00', @EndDate datetime = '10/10/2018 00:00';

     

    -- need to get a VM name to show vMotions over time

    -- the list of names that DPA has can be found running this query

    -- SELECT * FROM ignite.CONV_VM

    DECLARE @VMName varchar(400) = '<VM name>'

     

    -- the Ignite metric data is stored in VM specific tables, and we need the VMID to get there

    SELECT @VMID=ID FROM ignite.CONV_VM WHERE NAME = @VMName;

     

    SELECT vm.NAME as "VM Name", e.EVENT_DATE, e.EVENT_DESCRIPTION

    FROM ignite.CONV_EVENT e

    INNER JOIN ignite.CONV_VM vm ON vm.ID = e.ENTITY_ID

    WHERE e.ENTITY_ID = @VMID

    AND EVENT_DATE BETWEEN @BeginDate AND @EndDate

    ORDER BY EVENT_DATE DESC;