Auto-Dependency Mapping For ESXI Hosts > Dependent VMs

rionoskae

So inspired by this post, I decided to try and leverage the dbo.VirtualMachines "NodeHostID" column to automatically (via scheduled SQL job on the NetPerfMon Primary SQL Server) to create dependency mapping for each VM and it's corresponding ESXI Host. 

Please keep in mind that this is still very early in it's implementation and testing so I encourage you to test this in your lab environment & create DB backups prior to running this command. I should also point out that I suspect the DBO.VirtualMachines table is generated from the Virtualization Module and I'm currently unsure if this is available to all customers as part of the base NPM SolarWinds product. So I encourage you to verify this table exists in your environment as well. 

This took me all day to adapt from the above post - So I sincerely hope it helps somebody else out there. I'm not a SQL wizard and I don't frequent Thwack all too often, but I will try to keep an eye on this thread and assist as able. 

MERGE NetPerfMon.dbo.Dependencies AS Target  
USING  
  (  
    SELECT  
      n.NodeID  
     ,n.Caption  
     ,s.HostNodeID  
     ,'AutoDep'+'--p'+CONVERT(NVARCHAR(250),s.HostNodeID)+'--c'+CONVERT(NVARCHAR(250),s.NodeID) AS 'DependencyName'  
     ,'swis://mlwslrprdapp01.corp.footlocker.net/Orion/Orion.Nodes/NodeID='+CONVERT(NVARCHAR(250),s.HostNodeID) AS 'ParentUri'  
     ,'swis://mlwslrprdapp01.corp.footlocker.net/Orion/Orion.Nodes/NodeID='+CONVERT(NVARCHAR(250),n.NodeID) AS 'ChildUri'  
    FROM NetPerfMon.dbo.Nodes n 
    LEFT JOIN NetPerfMon.dbo.VirtualMachines s ON n.NodeID=s.NodeID
	LEFT JOIN NetPerfMon.dbo.NodesCustomProperties c ON c.NodeID=n.NodeID
    LEFT JOIN NetPerfMon.dbo.Dependencies d ON (d.ChildUri LIKE '%='+CONVERT(NVARCHAR(250),n.NodeID) AND d.Name LIKE 'auto_g%')
	Where s.HostNodeID IS NOT Null
  ) AS Source (nNodeID, nCaption, sHostNodeID, gDependencyName, nParentUri, nChildUri) ON (Target.ChildUri = Source.nChildUri AND Target.Name = Source.gDependencyName)  
-- DELETE RECORD...  
WHEN NOT MATCHED BY source --if node no longer exist in Nodes table...  
  AND Target.Name LIKE 'auto_g%' --and providing that record has been created by this automation  
THEN DELETE  
-- DELETE RECORD...  
WHEN MATCHED --if node exist...  
  AND Target.Name LIKE 'auto_g%' --and providing that record has been created by this automation  
  AND (
	Source.nNodeID IN --or node is a member of the edge group itself  
    (SELECT n_excl.ParentNetObjectID 
     FROM NetPerfMon.dbo.Dependencies n_excl  
     WHERE
       n_excl.ParentNetObjectID = Source.sHostNodeID))  
THEN DELETE  
-- UPDATE RECORD...  
WHEN MATCHED --when it is already there...  
  AND Target.Name LIKE 'auto_g%' --and providing that record has been created by this automation...  
  AND Source.nParentUri <> Target.ParentUri --and edge group has been changed (this can be due to site for the node has been changed or edge group has been changed for the entire site)  
THEN UPDATE  
  SET  
   Name = gDependencyName,  
   ParentUri = nParentUri,  
   LastUpdateUtc = GetUtcDate()
-- INSERT NEW RECORD...  
WHEN NOT MATCHED BY target --when it does not already exist...   
  AND Source.nNodeID NOT IN --and node is not a member of the Edge group itself  
    (SELECT n_excl.ParentNetObjectID  
     FROM NetPerfMon.dbo.Dependencies n_excl  
     WHERE  
       n_excl.ParentNetObjectID = Source.sHostNodeID)  
THEN 
  INSERT (Name, ParentUri, ChildUri, LastUpdateUtc, ParentNetObjectID, ChildNetObjectID, ParentEntityType, ChildEntityType) 
  VALUES (gDependencyName, nParentUri, nChildUri, GetUtcDate(), sHostNodeID, nNodeID, 'Orion.Nodes', 'Orion.Nodes') 
;