How To Use SAM to Monitor VMWare For Old/Rogue Snapshots

Version 1

    After following the below step-by-step guide you will be able to:

     

    1. Use SAM to receive an alert when you have any rogue/old snapshots in your VMWare environment older than N days

    2. Ability to easily bypass check for certain snapshots

     

    Component monitor variables:

     

    MESSAGE = List of comma separated affected server names with old/rogue snapshots

    STATISTIC = Number of servers with old/rogue snapshots

     

    Capture.PNG

     

    ==========================================

     

    (1)

    In your SQL server for SolarWinds create a new Database, call it same as SolarWind's database and append "Aux" (stands for Auxilary. This can later be used for various other little projects to monitor different things. Example: "NetPerfMonAux")

    Capture5.PNG

    (2)

    Create table to store snapshots info:

    Capture1.PNG

    (2.1)

    Create first dummy record (this is because SQL script below will use "UPDATE" function)

     

    (3)

    On the Solarwinds server (or a polling engine), Install VMware vSphere PowerCLI (You can download it from https://my.vmware.com/web/vmware/details?productId=285&downloadGroup=VSP510-PCLI-510)


    (4)

    Create a PowerShell script to get Snapshots Information from a vCenter (not from VM host) and save this info into SolarWinds Aux Database


    if(-not (Get-PSSnapin VMware.VimAutomation.Core -ErrorAction SilentlyContinue))

    {

       Add-PSSnapin VMware.VimAutomation.Core

    }

     

     

    ##### Get VC connected ######################

     

     

    $VCServer = "<vCenter_Server_Name>"

    $VCUserName = "<Domain\User>"

    $VCPassword = "<Password>"

    $VCconnection = Connect-VIServer $VCServer -Protocol https -User $VCUserName -Password $VCPassword -ErrorAction SilentlyContinue

     

     

    $a = (Get-VM | where-object {$_.Folder -notmatch "Replica Servers"} | Get-Snapshot | Where-object { $_.Created -lt (Get-Date).AddDays(-2)} | Where-object { $_.Description -notlike "*skipMonitoring*"} | select VM, Name, Created, Description | Measure-Object)

    $b = (Get-VM | where-object {$_.Folder -notmatch "Replica Servers"} | Get-Snapshot | Where-object { $_.Created -lt (Get-Date).AddDays(-2)} | Where-object { $_.Description -notlike "*skipMonitoring*"} | select -expand VM) -join ', '

    $c = [System.DateTime]::Now

     

     

    #'Statistic: ' + $a.Count

    #'Message: ' + $b 

    #'DateTimeStamp: ' + $c

     

     

    Disconnect-VIServer $VCServer -Confirm:$False -ErrorAction SilentlyContinue

     

     

     

    ##### Write to Database #########################

     

     

    ### open database connection

    $SQLServer = "<SAM_SQL_Server_Name>" #use Server\Instance for named SQL instances!

    $SQLDBName = "<Aux_DB_Name>"

    $SQLDBUser = "<SQL_User_Name>"

    $SQLDBPwd = "<Password>"

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; user id=$SQLDBUser;password=$SQLDBPwd"

    $SqlConnection.Open()

     

     

     

    ### Update [dbo.VIM_OldSnapShots] table

    $Sqlcmd = $SqlConnection.CreateCommand()

     

     

    $Update = "UPDATE dbo.VIM_OldSnapshots SET CountOldSnapshots = '" + $a.Count + "',"

    $Update = $Update + "AffectedVMs = '" + $b + "',"

    $Update = $Update + "DateTimeStamp = '" + $c + "'"

     

     

    $Sqlcmd.CommandText = $Update

    $Sqlcmd.ExecuteNonQuery()

     

     

    $SqlConnection.Close()

     

     

     

    ###########################################

     


    (5)

    Run the PowerShell script as a schedule task as often as you wish. I have configured it to run every 5 minutes so that we always have up to date record in this table

    Capture6.PNG


    (6)

    Create new SQL User Experience app template in SolarWinds and attach it to SolarWinds SQL server:


    SELECT

     

     

    CASE

      WHEN (DATEDIFF(MINUTE, [DateTimeStamp], GETDATE()) < 60 )

       THEN

        [CountOldSnapshots]

       ELSE

        999

    END AS 'Statistic: ',

     

     

    CASE

      WHEN (DATEDIFF(MINUTE, [DateTimeStamp], GETDATE()) < 60 )

       THEN

        [AffectedVMs]

       ELSE

        'Monitoring is broken due to the data record in [dbo.VIM_OldSnapshots] table being older than 1 hour. Please check scheduled PS script, which updates this record'

      END AS 'Message: '

     

     

    FROM [Your_Aux_Database_Name].[dbo].[VIM_OldSnapshots]


    (7)

    Configure email alert:

    Capture7.PNG

    Capture8.PNG


    --

    Thank you,

    Alex