PowerCLI script to find VMs built in the last 30 days?

[wreedctd]

How can I write a PowerCLI script to find VMs built in the last 30 days? Can anyone help me or point me in the right direction?

[wreedctd]

So I got this to work by using the following command:

Get-VIEvent -maxsamples 10000 -Start (Get-Date).AddDays(–14) | where {$_.Gettype().Name-eq "VmCreatedEvent" -or $_.Gettype().Name-eq "VmBeingClonedEvent" -or $_.Gettype().Name-eq "VmBeingDeployedEvent"} |Sort CreatedTime -Descending |Select CreatedTime, UserName,FullformattedMessage

Now if I could just get this in a pretty Excel sheet, I would be golden! Any ideas?

[kunaludapi]

Get-VIEvent -maxsamples 10000 -Start (Get-Date).AddDays(–14) | where {$_.Gettype().Name-eq "VmCreatedEvent" -or $_.Gettype().Name-eq "VmBeingClonedEvent" -or $_.Gettype().Name-eq "VmBeingDeployedEvent"} |Sort CreatedTime -Descending |Select CreatedTime, UserName,FullformattedMessage | export-csv c:\temp\file.csv

[wreedctd]

Thank you I will try that!

[wreedctd]

OK next question,

How do I make have the CSV automatically emailed to me every month?

[wreedctd]

Problem I have now is my Vcenter is set to keep events forever, yet when I look in Event view of Vcenter I only have events for the last few hours.  Any ideas?

[kunaludapi]

here you go

Send-MailMessage -To to@example.com -From no-reply@example.com -SmtpServer mail.example.com -Attachments c:\temp\file.csv -Body "This is report for vms built in last 30 days" -Subject "new vm report"

[wreedctd]

Anyone?

Problem I have now is my Vcenter is set to keep events forever, yet when I look in Event view of Vcenter I only have events for the last few hours.  Any ideas?

Can I query the SQL DB directly?

[wreedctd]

Anyone?

[wreedctd]

SQL Query...

WITH T as

(

SELECT

            ROW_NUMBER() OVER (PARTITION By VM_Name ORDER BY Create_Time ASC ) as Num,

            Create_Time,

            VM_NAME

FROM

            VPX_ENTITY EN

            INNER JOIN VPX_VM V on EN.ID = V.ID

            INNER JOIN VPX_EVENT E on E.VM_Name = EN.Name         

)

SELECT DISTINCT

            T.VM_NAME,

            T.CREATE_TIME,

            E.USERNAME,

            DateDiff(D, T.Create_Time, Getdate()) as DaysSince

FROM

            T

            INNER JOIN VPX_EVENT E on T.VM_Name = E.VM_Name and E.Create_Time = T.Create_Time

WHERE

            T.num = 1

            and      DateDiff(D, T.Create_Time, Getdate()) < 600

[Craig_Baltzer]

http://www.virtuallyghetto.com/2014/03/a-kitten-dies-every-time-you-query-the-vcdb.html

The VI client restricts the number of entries in a list to 100 by default, so if you're looking for events using it then that's why you're cut off. Edit, Client Settings, Lists, Page size will allow you to up that to 1000

Rather than querying the database directly Get-VIEvent can be used to gather up the events. You can set -MaxSamples to something huge (like [Int32]::MaxValue) and -Start to control the range. If you want a GUI view you can scroll through without writing a CSV and starting Excel use Out-Gridview

[wreedctd]

Yeah A kitten def does not die every time you query the DB. We are using a user with read only access.

We have the info we need, thanks to my DBAs!

I have 100 million events. power CLI is not powerful enough to do what I need for my size environment.

[Craig_Baltzer]

I think the point William was making was less around "breaking" the database and more about the fragility of queries that access it directly as there is no published/supported schema. Good that your DBAs have reverse engineered things into a solution that works for you.

How many days of events is 100M records in your environment just out of curiosity? We keep ours outside of vCenter for analysis type queries as we can better optimize the table structure and query performance...

[wreedctd]

About 1 year ago and we have 60 million events, not 100 million.