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…
|
1 |
<!-- Code highlighting produced by Actipro SyntaxEditor http://www.ActiproSoftware.com/Products/DotNet/ --><span style="color: rgb(0, 128, 0);">#</span><span style="color: rgb(0, 128, 0);"> Fill in the following information:</span><span style="color: rgb(0, 128, 0);"><br /></span><span style="color: rgb(128, 0, 128);">$SqlServer</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 0);">"</span><span style="color: rgb(128, 0, 0);">MYDBSERVER</span><span style="color: rgb(128, 0, 0);">"</span><span style="color: rgb(0, 0, 0);">;<br /></span><span style="color: rgb(128, 0, 128);">$SqlDB</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 0);">"</span><span style="color: rgb(128, 0, 0);">VMwareDataBase</span><span style="color: rgb(128, 0, 0);">"</span><span style="color: rgb(0, 0, 0);">;<br /></span><span style="color: rgb(128, 0, 128);">$MYVM</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 0);">"</span><span style="color: rgb(128, 0, 0);">TESTSERVER1</span><span style="color: rgb(128, 0, 0);">"</span><span style="color: rgb(0, 0, 0);"><br /></span><span style="color: rgb(128, 0, 128);">$TypeofEvent</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 0);">"</span><span style="color: rgb(128, 0, 0);">vim.event.VmRemovedEvent</span><span style="color: rgb(128, 0, 0);">"</span><span style="color: rgb(0, 0, 0);"><br /></span><span style="color: rgb(0, 128, 0);">#</span><span style="color: rgb(0, 128, 0);"> The vim.event.VmRemovedEvent is a Removed action from VC you can also use :</span><span style="color: rgb(0, 128, 0);"><br />#</span><span style="color: rgb(0, 128, 0);"> vim.event.VmGuestShutdownEvent</span><span style="color: rgb(0, 128, 0);"><br />#</span><span style="color: rgb(0, 128, 0);"> vim.event.VmPoweredOffEvent</span><span style="color: rgb(0, 128, 0);"><br />#</span><span style="color: rgb(0, 128, 0);"> vim.event.VmConnectedEvent</span><span style="color: rgb(0, 128, 0);"><br /></span><span style="color: rgb(0, 0, 0);"><br /></span><span style="color: rgb(0, 0, 255);">Function</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(95, 158, 160);">Read-VIDB</span><span style="color: rgb(0, 0, 0);"> (</span><span style="color: rgb(128, 0, 128);">$SqlQuery</span><span style="color: rgb(0, 0, 0);">)<br />{<br /> </span><span style="color: rgb(0, 128, 0);">#</span><span style="color: rgb(0, 128, 0);"> Setup SQL Connection</span><span style="color: rgb(0, 128, 0);"><br /></span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 128);">$SqlConnection</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(95, 158, 160); font-weight: bold;">New-Object</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 0);">System.Data.SqlClient.SqlConnection</span><span style="color: rgb(0, 0, 0);"><br /> </span><span style="color: rgb(128, 0, 128);">$SqlConnection</span><span style="color: rgb(0, 0, 0);">.ConnectionString </span><span style="color: rgb(255, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 0);">"</span><span style="color: rgb(128, 0, 0);">Server = $SqlServer; Database = $SqlDB; Integrated Security = True</span><span style="color: rgb(128, 0, 0);">"</span><span style="color: rgb(0, 0, 0);"><br /><br /> </span><span style="color: rgb(0, 128, 0);">#</span><span style="color: rgb(0, 128, 0);"> Setup SQL Command</span><span style="color: rgb(0, 128, 0);"><br /></span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 128);">$SqlCmd</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(95, 158, 160); font-weight: bold;">New-Object</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 0);">System.Data.SqlClient.SqlCommand</span><span style="color: rgb(0, 0, 0);"><br /> </span><span style="color: rgb(128, 0, 128);">$SqlCmd</span><span style="color: rgb(0, 0, 0);">.CommandText </span><span style="color: rgb(255, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 128);">$SqlQuery</span><span style="color: rgb(0, 0, 0);"><br /> </span><span style="color: rgb(128, 0, 128);">$SqlCmd</span><span style="color: rgb(0, 0, 0);">.Connection </span><span style="color: rgb(255, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 128);">$SqlConnection</span><span style="color: rgb(0, 0, 0);"><br /><br /> </span><span style="color: rgb(0, 128, 0);">#</span><span style="color: rgb(0, 128, 0);"> Setup .NET SQLAdapter to execute and fill .NET Dataset</span><span style="color: rgb(0, 128, 0);"><br /></span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 128);">$SqlAdapter</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(95, 158, 160); font-weight: bold;">New-Object</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 0);">System.Data.SqlClient.SqlDataAdapter</span><span style="color: rgb(0, 0, 0);"><br /> </span><span style="color: rgb(128, 0, 128);">$SqlAdapter</span><span style="color: rgb(0, 0, 0);">.SelectCommand </span><span style="color: rgb(255, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 128);">$SqlCmd</span><span style="color: rgb(0, 0, 0);"><br /> </span><span style="color: rgb(128, 0, 128);">$DataSet</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(95, 158, 160); font-weight: bold;">New-Object</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 0);">System.Data.DataSet</span><span style="color: rgb(0, 0, 0);"><br /><br /> </span><span style="color: rgb(0, 128, 0);">#</span><span style="color: rgb(0, 128, 0);">Execute and Get Row Count</span><span style="color: rgb(0, 128, 0);"><br /></span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 128);">$nRecs</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 128);">$SqlAdapter</span><span style="color: rgb(0, 0, 0);">.Fill(</span><span style="color: rgb(128, 0, 128);">$DataSet</span><span style="color: rgb(0, 0, 0);">)<br /><br /> </span><span style="color: rgb(0, 0, 255);">if</span><span style="color: rgb(0, 0, 0);"> (</span><span style="color: rgb(128, 0, 128);">$nRecs</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">-gt</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 0);">0</span><span style="color: rgb(0, 0, 0);">)<br /> {<br /> </span><span style="color: rgb(0, 128, 0);">#</span><span style="color: rgb(0, 128, 0);"> Do Stuff</span><span style="color: rgb(0, 128, 0);"><br /></span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 128);">$dataSet</span><span style="color: rgb(0, 0, 0);">.Tables | </span><span style="color: rgb(95, 158, 160); font-weight: bold;">Select-Object</span><span style="color: rgb(0, 0, 0);"> -Expand </span><span style="color: rgb(128, 0, 0);">Rows</span><span style="color: rgb(0, 0, 0);"><br /> }<br />}<br /><br /></span><span style="color: rgb(128, 0, 128);">$SqlQuery</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 0);">"</span><span style="color: rgb(128, 0, 0);">SELECT CREATE_TIME, USERNAME, VM_NAME, HOST_NAME, EVENT_TYPE FROM VMWareDS.VPX_EVENT WHERE (VM_NAME = N'$MYVM') AND (EVENT_TYPE = '$TypeofEvent')</span><span style="color: rgb(128, 0, 0);">"</span><span style="color: rgb(0, 0, 0);"><br /></span><span style="color: rgb(128, 0, 128);">$MyResults</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">=</span><span style="color: rgb(0, 0, 0);"> Read-VIDB </span><span style="color: rgb(128, 0, 128);">$SqlQuery</span><span style="color: rgb(0, 0, 0);"><br /></span><span style="color: rgb(128, 0, 128);">$MyResults</span><span style="color: rgb(0, 0, 0);"><br /></span> |
how to add sql login in this script
thanks
Alan, one of our customer VMs were destroyed one day without any idea. We suspect someone involved. it is running on ESX 5. We got the logs from ESX. We are looking into hostd log. How can I deter mine which user / IP connected, powered off, dismounted and destroyed VMs? Do we have nay tool that analyze the Logs
Thanks in advance.
use below in line37 $SqlQuery = “SELECT CREATE_TIME, USERNAME, VM_NAME, HOST_NAME, EVENT_TYPE FROM dbo.VPX_EVENT WHERE (VM_NAME = N’$MYVM’) AND (EVENT_TYPE = ‘$TypeofEvent’)” to track the delete VM details
I get an error on line 28 when I run this….”Exception calling “fill” with “1” argument”
Any ideas?
Andy,
Did you find any answer for this, im getting a similar error.
-Sai
Sorry Sai… I never found a fix to this error.
Andy
Got a tricky question about something similar:
I want to find out, which user moved a folder to another folder. (even if the event is out of the max 1000 events in the client view)
The problem is, that i see in events and tasks: “user moved to target” without “what” he moved…
With the get-vievent cmdlet i only receive a “Task: move entity”, but no folder or something and also no target 🙁
How can i achive this ?
I found that when i migrate a VM guest from a host to another host a event is logged saying : Migrated from host ESX1 to ESX2
When i do datastore migration and i leave the host the same I get the following log:
Migrated from host esx1 to esx1 but i cant find to which datastore it was moved?
is there a way to get that info?
Thanks for the quick answer…
This script like posted before did not run.
Have it done now with an direct script into excel..
Is it possible to read the information from the vc directly ? Like:
$results = Get-VM | Get-VIEvent -maxsamples 10000 -Start (Get-Date).AddDays(-1) | where {$_.fullformattedmessage -eq “Aufgabe: Virtuelle Maschine ausschalten” -or $_.fullformattedmessage -eq “Aufgabe: Herunterfahren des Gastbetriebssystems initiiert.”} | Sort CreatedTime -Descending | select createdtime, username, vm, fullformattedmessage
foreach ($result in $Results) {
$Details=”” | select createdtime, username, vm, fullformattedmessage
$Details.createdtime = $result.createdtime
$Details.username = $result.username
$Details.vm = $result.vm.name
$Details.FullFormattedMessage = $result.fullformattedMessage
}
$details | Export-csv -NoTypeInformation ‘C:\TEMP\shutdown.csv’
I want to read out which user shutdown which vm at which time
Yes it would