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


    This is not a new topic, it was discussed in several posts, such as and


    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:



    'Virtual, unknown host'


    The Function:


    CREATE  FUNCTION [dbo].[myNodeType] (@nodeId INT)
        DECLARE @machineType VARCHAR(25)
    ;WITH tbVM
      -- If 'NodeID' is not NULL, it means a VM with known host
        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)
        -- 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
        (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 =
      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'
        RETURN @machineType


    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'





    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.