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.

eptesicus,

Upon further review I have found that all being well you will be able to alter Greater Than >, Less Than <, Equals = along with the Positive or - Negative number of days entered.

If I understand correctly in your script it states Grater Than > 15 Days.

Kind Regards.

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,

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?

Lotta comments on this already, but seems like the main error is "vm" as in "vm.folder.name" is an alias that's not defined in the FROM statement, so it's invalid

Usually engineers will do stuff like "Select x from orion.virtualmachines vm" at which point you can use vm.x to talk to stuff in the virtualmachines table.

You've copypasted that in from somewhere it works i'd guess. Swap vm.for "virtualmachines" before anything else if you've not already