Custom Table using SQL Query with hyperlinks

mjlee1979

Hi,

I'm trying to create a custom table that show all my team active alerts but want to include and hyperlink to the node details page when you click on the node name. This is what I have so far but I'm struggling to work out how to achieve this.

Any help greatly appreciated!

SELECT DISTINCT
	[SolarWindsOrion].[dbo].[Containers].[Name] AS 'Site' 
	,'https://OURSOLARWINDSSERVER/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:'+CAST([SolarWindsOrion].[dbo].[NodesData].[NodeID] as varchar(256)) as 'DetailsURL'
	, [SolarWindsOrion].[dbo].[ContainerMemberSnapshots].[EntityID]
	, [SolarWindsOrion].[dbo].[Containers].[ContainerID]
	, [SolarWindsOrion].[dbo].[NodesData].[Caption] AS 'Device'
	, [SolarWindsOrion].[dbo].[AlertObjects].[RelatedNodeDetailsUrl] AS 'Device URL'
	, [SolarWindsOrion].[dbo].[AlertObjects].[RelatedNodeUri] AS 'Device URI'
	, [SolarWindsOrion].[dbo].[AlertObjects].[EntityCaption] AS 'Node Element'
	, [SolarWindsOrion].[dbo].[AlertObjects].[EntityDetailsUrl] AS 'Node Element URL'
	, [SolarWindsOrion].[dbo].[AlertObjects].[EntityUri] AS 'Node Element URI'
    , [SolarWindsOrion].[dbo].[AlertActive].[TriggeredMessage]
    , [SolarWindsOrion].[dbo].[AlertActive].[TriggeredDateTime]
	, [SolarWindsOrion].[dbo].[AlertConfigurations].[Category]
    , DATEDIFF(mi,[SolarWindsOrion].[dbo].[AlertActive].[TriggeredDateTime], GetDate()) AS 'minutes'
    , DATEDIFF(dd,[SolarWindsOrion].[dbo].[AlertActive].[TriggeredDateTime], GetDate()) AS 'days'
	, DATEDIFF(hh,[SolarWindsOrion].[dbo].[AlertActive].[TriggeredDateTime], GetDate()) AS 'hours'
	, CONVERT(varchar(3),DATEDIFF(minute,[SolarWindsOrion].[dbo].[AlertActive].[TriggeredDateTime], GetDate())/60) + ':' +
       RIGHT('0' + CONVERT(varchar(2),DATEDIFF(minute,[SolarWindsOrion].[dbo].[AlertActive].[TriggeredDateTime],GetDate())%60),2)
       AS 'Hours Elapsed'
    , [SolarWindsOrion].[dbo].[NodesData].[GroupStatus]
	,  replace(
       replace([SolarWindsOrion].[dbo].[NodesData].[StatusDescription],'Node Status is Critical,','')
       , 'Node Status is Warning,','') as StatusDescription
	, REPLACE([SolarWindsOrion].[dbo].[AlertActive].[AcknowledgedBy], 'DRL\' , '' ) AS AcknowledgedBy

FROM [SolarWindsOrion].[dbo].[AlertActive]

INNER JOIN [SolarWindsOrion].[dbo].[AlertObjects]
ON [SolarWindsOrion].[dbo].[AlertActive].[AlertObjectID] = [SolarWindsOrion].[dbo].[AlertObjects].[AlertObjectID]
INNER JOIN [SolarWindsOrion].[dbo].[NodesData]
ON [SolarWindsOrion].[dbo].[NodesData].[NodeID] = [SolarWindsOrion].[dbo].[AlertObjects].[RelatedNodeId]
INNER JOIN [SolarWindsOrion].[dbo].[AlertConfigurations]
ON [SolarWindsOrion].[dbo].[AlertConfigurations].[AlertID] = [SolarWindsOrion].[dbo].[AlertObjects].[AlertID]
INNER JOIN [SolarWindsOrion].[dbo].[ContainerMemberSnapshots]
ON [SolarWindsOrion].[dbo].[ContainerMemberSnapshots].[EntityID] = [SolarWindsOrion].[dbo].[NodesData].[NodeID]
INNER JOIN [SolarWindsOrion].[dbo].[Containers]
ON [SolarWindsOrion].[dbo].[Containers].[ContainerID] = [SolarWindsOrion].[dbo].[ContainerMemberSnapshots].[ContainerID] 

WHERE [SolarWindsOrion].[dbo].[AlertActive].[TriggeredMessage] <> 'Node is Critical.' 
AND [SolarWindsOrion].[dbo].[AlertActive].[TriggeredMessage] <> 'Node is Warning.' 
AND [SolarWindsOrion].[dbo].[AlertConfigurations].[Category] = 'Network Alert'
AND [SolarWindsOrion].[dbo].[ContainerMemberSnapshots].[EntityType] = 'Orion.Nodes'

ORDER BY 
[SolarWindsOrion].[dbo].[AlertActive].[TriggeredDateTime] DESC
, [SolarWindsOrion].[dbo].[NodesData].[Caption] ASC

stevenstadel

I use the Query widget and a SWQL query to show this type of info with hyperlinks. I recommend using SWQL so that if anything changes in the schema after an upgrade, you will be protected, and your query will still work. You are also able to use the built-in relationships and cut down on joins. 

When I build a SWQL query, I use the [_LinkFor_WHATEVER_YOUR_COLUMNNAMEIS] to add a hyperlink automatically.

For example, this query was created to show when nodes hit a CPU or Memory custom alert trigger and how long they are in that state. This won't show anything in your environment because I am using a custom alert to write the log message, but it will give you some ideas.  

SELECT TOP 1000
    AlertActive.AlertObjects.EntityCaption AS [Node],
    CONCAT('/Orion/images/StatusIcons/Small-', AlertActive.AlertObjects.Node.StatusIcon) AS [_ICONFor_Node],
    AlertActive.AlertObjects.Node.CPULoad,
    AlertActive.AlertObjects.Node.PercentMemoryUsed,
    AlertActiveID,
    AlertObjectID,
    tolocal(TriggeredDateTime) AS Date,
    MinuteDiff(TriggeredDateTime, GETUTCDATE()) AS Minutes,
    TriggeredMessage,
    AlertActive.AlertObjects.Node.DetailsURL AS [_Linkfor_Node]
FROM 
    Orion.AlertActive
WHERE 
    TriggeredMessage IN ('High CPU Utilization - Log Event', 'High Memory Utilization - Log Event')
ORDER BY 
    CPULoad DESC