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:
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:
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
