Add a MSSQL function to Solarwinds database to identify a node hardware type (Physical or Virtual)

hcy01uk

In node details page we can see hardware type information, but we cannot find a dedicated colume in Solarwinds database
tables to populate the value.

Solarwinds uses several figures to decide the hardware type. Details can be found at  https://support.solarwinds.com/Success_Center/Network_Performance_Monitor_(NPM)/Hardware_field_values_in_NPM_Node_Details

This is not a new topic, it was discussed in several posts, such as https://thwack.solarwinds.com/message/145687#145687 and https://thwack.solarwinds.com/message/154895#154895.

But the solutions are not convenient to be reused when you create various reports and alerts. Based on bshopp​ solution (Thanks!) I created a simple scalar valued SQL function to return the node hardware type when the NodeID is given:

FUNCTION [dbo].[myNodeType] (@nodeId INT)

Return:  VARCHAR(25),
Three possible value:

'Physical’

'Virtual'

'Virtual, unknown host'

The Function:

CREATE  FUNCTION [dbo].[myNodeType] (@nodeId INT)
RETURNS VARCHAR(25)
AS
Begin
    DECLARE @machineType VARCHAR(25)
;WITH tbVM
AS
(
  -- If 'NodeID' is not NULL, it means a VM with known host
  SELECT
    t2.NodeID AS NodeID,
    N'Host' AS NodeType
  FROM dbo.VIM_VirtualMachines AS t1
  LEFT JOIN dbo.VIM_Hosts AS t2
  ON (t1.HostID = t2.HostID)
  WHERE (t1.NodeID = @nodeId)
  UNION
  (
  SELECT
    -- If 'NodeID' is NULL and 'ThisNodeID' is not NULL, it means a VM with unknown host
    t3.NodeID AS ThisNodeID,                                    
    N'VM' AS NodeType
  FROM dbo.NodeMACAddresses AS t3
  WHERE                                               
    (
    (t3.MAC LIKE N'0003FF%') OR
    (t3.MAC LIKE N'000C29%') OR
    (t3.MAC LIKE N'005056%') OR
    (t3.MAC LIKE N'000569%') OR
    (t3.MAC LIKE N'001C14%')
    )
    AND (t3.NodeID = @nodeId)
    )
)
SELECT @machineType =
(
   CASE
  WHEN NOT EXISTS(SELECT * FROM tbVM) THEN 'Physical'  --- enpty table
  WHEN EXISTS(SELECT * FROM tbVM WHERE NodeType = 'Host') THEN 'Virtual'   --- table with Host info                                            
  WHEN EXISTS(SELECT * FROM tbVM WHERE NodeType = 'VM') THEN 'Virtual, unknown host'
  ELSE 'Physical'
  END
    )
   
    RETURN @machineType
End 

Import and use the function:

• Import the function to Solarwinds database “NetPerfMon”
  • Download the attached file: SQL-function-node-vm-or-physical-thwack.sql
  • Run “SQL Server Management Studio”  and connect to Solarwinds Database engine
  • Create a New Query, copy the file text and paste as the query
  • Run the query. Function is added
• Use the function:
  • Use the function same way as native functions. Here is an
    example - List all physical windows servers

SELECT Nodes.NodeID,Nodes.ObjectSubType, Nodes.Caption,

       Nodes.MachineType, Nodes.IP_Address,

       dbo.myNodeType(Nodes.NodeID) AS Hardware

FROM Nodes

Where Nodes.Vendor = 'Windows' AND dbo.myNodeType(Nodes.NodeID) = 'Physical'

Limitation:

This function is tested in NPM 12.1 and SAM 6.4. It should work well with nodes polled by SNMP, WMI, Agent, API, but if a node is polled by
ICMP only, it may return false value.

Thanks

SQL-function-node-vm-or-physical-thwack.sql