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

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,

As requested upon further review...

When looking through the SolarWinds Platform SWIS Schema there is no reference found for the string; vm.folder.name in full or in part, despite finding some old posts within THWACK, that I believe to be from an earlier versions of VMAN.

> SolarWinds Information Service v3.0 Schema Documentation Index

> SolarWinds Information Service v3.0 Schema Documentation > Orion.VIM.VirtualMachines

Is the not another filter that you can use within the Orion.VIM.VirtualMachines Table?

Kind Regards,

eptesicus,

As requested upon further review...

When looking through the SolarWinds Platform SWIS Schema there is no reference found for the string; vm.folder.name in full or in part, despite finding some old posts within THWACK, that I believe to be from an earlier versions of VMAN.

> SolarWinds Information Service v3.0 Schema Documentation Index

> SolarWinds Information Service v3.0 Schema Documentation > Orion.VIM.VirtualMachines

Is the not another filter that you can use within the Orion.VIM.VirtualMachines Table?

Kind Regards,

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?

Yes, sir.  My apologies for the delay.

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.