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

Version 4

    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_%28NPM%29/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