wreedctd
Enthusiast
Enthusiast

SQL Statement to get VMs built in last 30 days

Hello,

I have a 5000 VM Vcenter. PowerCLI does not display the expected results.

Has anyone written a SQL statement to produce a report of all VMs created in the last 30 days? If so could you share it with me?

0 Kudos
2 Replies
wreedctd
Enthusiast
Enthusiast

Anyone ever done this?

0 Kudos
wreedctd
Enthusiast
Enthusiast

Here is a statement that works we came up with in a hour. We will tweak tomorrow. For the curious minds.

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

0 Kudos