Reports for added / removed VMware VMs within Last xx days in Solarwinds SAM

Version 1

    In our Solarwinds environments we only have NPM & SAM, but not Virtualization Manager. Neither VMware nor SAM can tell what VMs have been added / removed with in last xx days directly.  Here is a workaround for this:

     

    On Solarwinds MSSQL server:

     

    Created a new table in Solarwinds NPM DB --- called “VirtualMachinesList”, the columns are:

     

    vm001.jpg

     

    Create a MS SQL query to find out newly added VMs and insert data to the table “VirtualMachinesList”.

     

    The query is:

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

    INSERT INTO [NetPerfMon].[dbo].[VirtualMachinesList]

    SELECT Tb01.*, CAST(GETDATE() AS DATE) AS DiscoveredDate,  0 AS Removed, NULL AS rmovedDate FROM

    (SELECT [VirtualMachineID],[UUID],[Name] FROM [NetPerfMon].[dbo].[VirtualMachines]

    ) AS Tb01

    LEFT JOIN [NetPerfMon].[dbo].[VirtualMachinesList]

              ON Tb01.[UUID] =  [NetPerfMon].[dbo].[VirtualMachinesList].[UUID]

    WHERE [NetPerfMon].[dbo].[VirtualMachinesList].[UUID] IS NULL

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

     

    When you run the query first time, all current VMs will be added to the table, the “DiscoveredDate” will be date you run the query not actually the date which VM was created. Thereafter, when run the query, only new VMs will add to the table.

     

    You can setup up a scheduled SQL job to run the query once a day to find out the new VMs. If you do not have the admin right on the SQL server, alternatively you can create a PowerShell script to run the query using NPM DB credential, and  setup a schedule task on the server to run the script once a day.

     

    Now you can create the second query to find out newly removed VMs and update the table “VirtualMachinesList”.

     

    The query is

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

    UPDATE [NetPerfMon].[dbo].[VirtualMachinesList]

    SET [NetPerfMon].[dbo].[VirtualMachinesList].[Removed] = Tb03.Removed,

        [NetPerfMon].[dbo].[VirtualMachinesList].[RemovedDate] = Tb03.RemovedDate

    FROM [NetPerfMon].[dbo].[VirtualMachinesList] INNER JOIN

        (SELECT Tb01.[VirtualMachineID] AS VirtualMachineID,

               Tb01.[UUID] AS UUID,

               Tb01.[Name] AS Name,

               Tb01.[DiscoveredDate] AS DiscoveredDate,

               1 AS Removed,

               CAST(GETDATE() AS DATE) AS RemovedDate

        FROM   [NetPerfMon].[dbo].[VirtualMachinesList] AS Tb01

          LEFT JOIN 

            (SELECT [VirtualMachineID],[UUID],[Name] FROM [NetPerfMon].[dbo].[VirtualMachines]

             ) AS Tb02

          ON Tb01.[UUID] =  Tb02.[UUID]

        WHERE (Tb02.[UUID] IS NULL) AND (Tb01.[Removed] = 0)

        ) AS Tb03

    ON [NetPerfMon].[dbo].[VirtualMachinesList].[UUID] = Tb03.[UUID]

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

     

    Same way, run this query via SQL job or PowerShell script as a scheduled task one a day to find out removed VMs.

     

    Now on Solarwinds server, run Report Write to create a couple advanced SQL reports:

     

    vm002.jpg

    Report - Removed VMs Last 7 Days

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

    SELECT [Name], [DiscoveredDate], [RemovedDate] ,DATEDIFF(dd,[RemovedDate], GetDate())

    FROM [NetPerfMon].[dbo].[VirtualMachinesList]

    WHERE  [Removed] = 1 AND DATEDIFF(dd,[RemovedDate], GetDate()) < 8

    ORDER BY [Name]

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

     

    Report – Newly Added VMs Last 7 Days

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

    SELECT   Tb01.[UUID],   Tb01.[Name],    Tb02.[IPAddress],   Tb02.[OperatingSystem],

                   Tb02.[GuestState],   Nodes.Caption,   Nodes.NodeID,   Tb01.[DiscoveredDate],

                   (CASE WHEN Tb02.NodeID > 0 THEN 'Yes' ELSE 'No' END) AS Monitored

    FROM [NetPerfMon].[dbo].[VirtualMachinesList] AS Tb01

         LEFT JOIN [NetPerfMon].[dbo].[VirtualMachines]  AS Tb02 ON Tb01.[UUID] = Tb02.[UUID]

         LEFT JOIN Nodes ON Tb02.HostNodeID = Nodes.NodeID

    WHERE  (Tb01.[Removed]) = 0 AND (DATEDIFF(dd,Tb01.[DiscoveredDate], GetDate()) < 8)

    ORDER BY  Tb01.[DiscoveredDate] DESC, Nodes.Caption ASC, Tb01.[Name] ASC

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

     

    Thanks