Who deleted my VM ? at 15:04

Today I had a colleague come to me and say someone had deleted his VM, he didn't know when and thinks it may have been a couple of months ago, he didn't know which host or which datastore it was in. could I tell him when and who. Hmmmm, time to start trawling through the logs I thought.

A quick Twit from @stahler told me that this would be in the Virtual Center Database so, here is a quick powershell which I used to find the culprit...

# Fill in the following information:
$SqlServer = "MYDBSERVER";
$SqlDB = "VMwareDataBase";
$MYVM = "TESTSERVER1"
$TypeofEvent = "vim.event.VmRemovedEvent"
# The vim.event.VmRemovedEvent is a Removed action from VC you can also use :
# vim.event.VmGuestShutdownEvent
# vim.event.VmPoweredOffEvent
# vim.event.VmConnectedEvent

Function Read-VIDB ($SqlQuery)
{
  # Setup SQL Connection
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlDB; Integrated Security = True"

  # Setup SQL Command
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $SqlCmd.CommandText = $SqlQuery
  $SqlCmd.Connection = $SqlConnection

  # Setup .NET SQLAdapter to execute and fill .NET Dataset
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  $SqlAdapter.SelectCommand = $SqlCmd
  $DataSet = New-Object System.Data.DataSet

  #Execute and Get Row Count
    $nRecs = $SqlAdapter.Fill($DataSet)

  if ($nRecs -gt 0)
  {
      # Do Stuff
        $dataSet.Tables | Select-Object -Expand Rows
  }
}

$SqlQuery = "SELECT CREATE_TIME, USERNAME, VM_NAME, HOST_NAME, EVENT_TYPE FROM VMWareDS.VPX_EVENT WHERE (VM_NAME = N'$MYVM') AND (EVENT_TYPE = '$TypeofEvent')"
$MyResults = Read-VIDB $SqlQuery
$MyResults