Adding link to a hostname in a result of an UnDP, in custom table?

planglois

Hello!

I currently have a custom table that returns different values polled via SNMP. Basically, I'm asking an application who host it. Values returned are the same as caption of another monitored node. (Think of it as if you were asking a VM who's the active and standby host).

Polled Node it the node where the UnDP result is coming from, and Active_Host & Standby_host are text values returned from polls.

I would like to enable link to the page of the active_host and standby_host.

My data source is defined this way:

SELECT         N.NodeID,        '<img src="'+'/Orion/Images/StatusIcons/'+N.StatusLED+'" width="16" height="16">'  as Status,          '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:'+CAST(N.NodeID as varchar(256)) as 'DetailsURL' ,        N.Caption as 'Polled Node',        N.System,        N.SysName,        Active_Host = ( SELECT TOP 1                            CPSD.Status                    FROM    CustomPollerAssignment CPA                            INNER JOIN CustomPollers CP                                ON CPA.CustomPollerID = CP.CustomPollerID                                   AND CP.UniqueName = 'eadsP25ActiveHostName'                            INNER JOIN CustomPollerStatistics_Detail CPSD                                ON CPA.CustomPollerAssignmentID = CPSD.CustomPollerAssignmentID                    WHERE   CPA.NodeID = N.NodeID                    ORDER BY CPSD.DATETIME DESC                  ),        Standby_Host = ( SELECT TOP 1                            CPSD.Status                     FROM   CustomPollerAssignment CPA                            INNER JOIN CustomPollers CP                                ON CPA.CustomPollerID = CP.CustomPollerID                                   AND CP.UniqueName = 'eadsP25StandByHostName'                            INNER JOIN CustomPollerStatistics_Detail CPSD                                ON CPA.CustomPollerAssignmentID = CPSD.CustomPollerAssignmentID                     WHERE  CPA.NodeID = N.NodeID                     ORDER BY CPSD.DATETIME DESC                   )FROM    Nodes NWHERE   NodeID IN ( SELECT  NodeID                    FROM    CustomPollerAssignment CPA                            INNER JOIN CustomPollers CP                                ON CPA.CustomPollerID = CP.CustomPollerID                                   AND CP.UniqueName IN ( 'eadsP25ActiveHostName', 'eadsP25StandByHostName') AND N.System = 'XXXXX')ORDER BY N.Caption ASC

How could I approach the issue and provide the links (and ideally the tooltips) in the active_host & standby_host column?

planglois

I auto answered myself and posting the solution hoping that could help somebody down the road.

By building the HTML link in the query, I was happily surprised that the tooltip was also enabled for those links, which I expected to be only on the DetailURL column.

New Data Source query:

SELECT
    a.NodeID,
    a.Status,
    a.DetailsURL,
    a.caption as 'Polled Cluster',
    a.Sysname,
    a.Active_Hostname,
    CASE
        WHEN Active_Hostname IS NOT NULL 
          AND (SELECT TOP 1 Caption FROM [SolarWindsOrion].[dbo].[Nodes] n WHERE a.Active_Hostname = n.Caption) IS NOT NULL 
          --If a hostname is not matched against the Nodes table, value will be returned without URL
        THEN (
            SELECT TOP 1
                ''+a.Active_Hostname+''
            FROM [SolarWindsOrion].[dbo].[Nodes] n
            WHERE a.Active_Hostname = n.Caption 
            )
        ELSE Active_Hostname 
        END AS Active_Host,
    a.Standby_Hostname,
    CASE
        WHEN Standby_Hostname IS NOT NULL 
          AND (SELECT TOP 1 Caption FROM [SolarWindsOrion].[dbo].[Nodes] n WHERE a.Standby_Hostname = n.Caption) IS NOT NULL
           --If a hostname is not matched against the Nodes table, value will be returned without URL
        THEN (
            SELECT TOP 1
                ''+a.Standby_Hostname+''
            FROM [SolarWindsOrion].[dbo].[Nodes] n
            WHERE a.Standby_Hostname = n.Caption 
            )
        ELSE Standby_Hostname
        END AS Standby_Host
FROM (
    SELECT   
        n.NodeID,  
        '

'  as Status,    
        '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:'+CAST(N.NodeID as varchar(256)) as 'DetailsURL' ,  
        n.Caption,  
        n.System,  
        n.SysName,  
        Active_Hostname = ( 
                SELECT TOP 1  
                    cpsd.Status  
                FROM    CustomPollerAssignment cpa
                INNER JOIN CustomPollers cp
                    ON cpa.CustomPollerID = cp.CustomPollerID  
                    AND cp.UniqueName = 'eadsP25ActiveHostName'  
                INNER JOIN CustomPollerStatistics_Detail cpsd
                    ON cpa.CustomPollerAssignmentID = cpsd.CustomPollerAssignmentID  
                WHERE   cpa.NodeID = N.NodeID  
                ORDER BY cpsd.DATETIME DESC  
                ),  
        Standby_Hostname = ( 
                SELECT TOP 1  
                    cpsd.Status  
                FROM   CustomPollerAssignment cpa
                INNER JOIN CustomPollers cp  
                    ON cpa.CustomPollerID = cp.CustomPollerID  
                    AND cp.UniqueName = 'eadsP25StandByHostName'  
                INNER JOIN CustomPollerStatistics_Detail cpsd  
                    ON cpa.CustomPollerAssignmentID = cpsd.CustomPollerAssignmentID  
                WHERE  cpa.NodeID = N.NodeID  
                ORDER BY cpsd.DATETIME DESC  
                )
    FROM    Nodes N  
    WHERE   NodeID IN ( 
                    SELECT  
                        NodeID  
                    FROM    CustomPollerAssignment CPA  
                    INNER JOIN CustomPollers CP  
                        ON CPA.CustomPollerID = CP.CustomPollerID  
                        AND CP.UniqueName IN ( 'eadsP25ActiveHostName', 'eadsP25StandByHostName')   
                        AND N.System = 'XXXXX'  
                    ) 
    ) AS a
ORDER BY a.Caption ASC  

On the "Polled Cluster" column, Display settings are "Details page link" + Enable tooltips = Checked

Then Active_Host & Standby_Host column have only Allow HTML Tags = Checked

Result: