VMAN - Using SWQL to exclude VMs residing in a folder in vCenter from the results.

I've tried this with SWQL Studio with no success, but I'm wanting to build an alert to delete snapshots older than X days, but only apply to VMs that don't reside in a particular folder in vCenter so that we don't delete any snapshots on our Citrix MCS templates.

Googling around led me to the attribute, vm.folder.name, but it doesn't appear to be valid.

Parents
  • eptesicus,

    WHERE VirtualMachines.OldestSnapshotDate <= AddDay(-15, GETDATE())

    -- AND vm.folder.name NOT LIKE 'MCS Templates'
    -- This line is bespoke to your environment.

    ORDER BY VirtualMachines.OldestSnapshotDate DESC 

    -- ORDER BY VirtualMachines.OldestSnapshotDate ASC

    -- The ORDER BY being an optional addition to your original Script.

    I will look forward to your feedback, to confirm or otherwise that this will work ok within your environment.

  • Thanks for the reply.  Although the WHERE query you posted validates successfully, adding the AND query to it makes it invalid.  I am unsure if vm.folder.name is correct or not with VMAN.  Any guidance there? Even if I did "WHERE vm.folder.name NOT LIKE 'MCS Templates'" as the query, it's invalid.

  • eptesicus,

    Again upon review of your post have you thought about writing a scheduled report to notify you of snapshots older than X days, that you may want to delete to make space for new Snapshots.

    The SWQL Report script as follows:

    SELECT VirtualMachines.Uri
         , VirtualMachines.DisplayName
         , VirtualMachines.OldestSnapshotDate
    FROM Orion.VIM.VirtualMachines AS VirtualMachines
    WHERE VirtualMachines.OldestSnapshotDate <= AddDay(- 15, GETDATE())
    -- AND vm.folder.name NOT LIKE 'MCS Templates'
    -- This line is bespoke to your environment and or applicable to an older version (TBC?) of VMAN.
    ORDER BY VirtualMachines.OldestSnapshotDate DESC
         -- ORDER BY VirtualMachines.OldestSnapshotDate ASC
         -- The ORDER BY being an optional addition to your original Script.
    

    You may wish to add more columns from same or another Table for the new report for the potential deleting of Snapshots.

    The Snapshot size for instance?

    Again I will look forward to your feedback.

    Kind Regards,

  • Thanks for the assistance here.  I see to have figured it out with the following:

    SELECT VirtualMachines.Uri, VirtualMachines.DisplayName
    FROM Orion.VIM.VirtualMachines AS VirtualMachines
    
    WHERE VirtualMachines.OldestSnapshotDate <= AddDay(-15, GETDATE())
    AND VirtualMachines.ResourcePool.DisplayName NOT LIKE '%MCS-VM%'
    AND VirtualMachines.ResourcePool.DisplayName NOT LIKE '%SnapshotHold%'
    AND VirtualMachines.GuestDnsName NOT LIKE '%gfcdomain%'
    AND VirtualMachines.GuestState = 'running'

    I was not able to get any folder properties, so I instead targeted the resource group.  That said, I'm now trying to target the same as above but targeting specific datacenter clusters.  I've attempted the below without success ("Entity Orion.VIM.VirtualMachine does not contain requested property ClusterUri"):

    SELECT
        VirtualMachines.Uri,
        VirtualMachines.DisplayName,
        Clusters.DisplayName
    FROM
        Orion.VIM.VirtualMachines AS VirtualMachines
        INNER JOIN Orion.VIM.Clusters AS Clusters ON VirtualMachines.ClusterUri = Clusters.Uri
    
    WHERE
        VirtualMachines.OldestSnapshotDate <= AddDay(-15, GETDATE())
        AND VirtualMachines.ResourcePool.DisplayName NOT LIKE '%MCS-VM%'
        AND VirtualMachines.ResourcePool.DisplayName NOT LIKE '%SnapshotHold%'
        AND VirtualMachines.GuestDnsName NOT LIKE '%gfcdomain%'
        AND VirtualMachines.GuestState = 'running'
        AND Clusters.DisplayName != 'CITRIX'

    Any thoughts on this, good sir?

  • Hello eptesicus,

    I will review and send you my feedback as requested later on this week.

    In the meantime are you testing your SWQL code within SWQL Studio or the Orion SWIS Query via the GUI?

    Can you also advise are you now targeting the notification of the Snapshots to be deleted with a scheduled Report?

    Kind Regards.

  • Hello eptesicus,

    Can you please advise why you want or need the 'ClusterUri'?

    Am I correct to assume it is for the Tables Inner Join?

    Is there not another common entity that can be used?

    This may be found by using the SWQL Studio. 

    There maybe a common entity to  both Tables within a third Table to establish a The Table Join that you would like or want. 

    Kind Regards. 

  • eptesicus,

    Are you ok to Private Message me about your script?

  • eptesicus,

    No worries with the delay.

    With the follow up feedback from Adam and I, I don't believe that we need to have a Private Message about the script now.

    We trust that you have all that you need for the script bespoke to your environment that you would like to run.

    Thanks,

    Daniel

  • Yep.  Thanks, Daniel!  Good to go on my end.  I appreciate the help.

  • You can get from Virtual Machines to Clusters using a Navigation Property double-hop via Hosts.

    SELECT
        VirtualMachines.Uri,
        VirtualMachines.DisplayName
    FROM
        Orion.VIM.VirtualMachines AS VirtualMachines
    WHERE
        VirtualMachines.OldestSnapshotDate <= AddDay(-15, GETUTCDATE())
        AND VirtualMachines.ResourcePool.DisplayName NOT LIKE '%MCS-VM%'
        AND VirtualMachines.ResourcePool.DisplayName NOT LIKE '%SnapshotHold%'
        AND VirtualMachines.GuestDnsName NOT LIKE '%gfcdomain%'
        AND VirtualMachines.GuestState = 'running'
        ----------------------+
        --                    | VM's are connected to hosts
        --                    v
        --                    ------+
        --                          | Those hosts are connected to clusters
        --                          v
        AND VirtualMachines.Host.Cluster.DisplayName != 'CITRIX'

    I provided a much more detailed explanation in Decreasing JOINs - the Power of Navigation Properties.

    I'd offer to help more, but I don't have a vCenter in my lab environment, so I don't know where or how the Folder Name would be stored.

Reply
  • You can get from Virtual Machines to Clusters using a Navigation Property double-hop via Hosts.

    SELECT
        VirtualMachines.Uri,
        VirtualMachines.DisplayName
    FROM
        Orion.VIM.VirtualMachines AS VirtualMachines
    WHERE
        VirtualMachines.OldestSnapshotDate <= AddDay(-15, GETUTCDATE())
        AND VirtualMachines.ResourcePool.DisplayName NOT LIKE '%MCS-VM%'
        AND VirtualMachines.ResourcePool.DisplayName NOT LIKE '%SnapshotHold%'
        AND VirtualMachines.GuestDnsName NOT LIKE '%gfcdomain%'
        AND VirtualMachines.GuestState = 'running'
        ----------------------+
        --                    | VM's are connected to hosts
        --                    v
        --                    ------+
        --                          | Those hosts are connected to clusters
        --                          v
        AND VirtualMachines.Host.Cluster.DisplayName != 'CITRIX'

    I provided a much more detailed explanation in Decreasing JOINs - the Power of Navigation Properties.

    I'd offer to help more, but I don't have a vCenter in my lab environment, so I don't know where or how the Folder Name would be stored.

Children
No Data