Adding Custom Properties to a SWQL query

Hi 

So I'm creating a dashboard and want to add a table that shows node that are down and how long they have been 

i achieved that by the below SWQL 

SELECT TOUPPER(SUBSTRING (n.Caption,1, CASE WHEN CHARINDEX('.',n.Caption,1) <=4 THEN LENGTH(n.Caption) ELSE (CHARINDEX('.',n.Caption,1)-1) END)) AS [IP]
,case when min(rt.DateTime) is NULL
then 'More than a year'
else concat(substring(tostring(min(rt.DateTime)),6,2),'-',substring(tostring(min(rt.DateTime)),9,2),'-',substring(tostring(min(rt.DateTime)),1,4),' ',substring(tostring(min(rt.DateTime)),12,2),':',substring(tostring(min(rt.DateTime)),15,2))
end as [Date]
,case when min(rt.DateTime) is NULL
then ' More than a year'
else concat((hourDIFF(tolocal(min(rt.DateTime)),getdate())/24), ' Days '
,(Ceiling((HourDiff(tolocal(min(rt.DateTime)),getdate())/24.0-Floor(HourDiff(tolocal(min(rt.DateTime)),getdate())/24.0))*24)),' h '
,(Ceiling((MinuteDiff(tolocal(min(rt.DateTime)),getdate())/60.0-Floor(MinuteDiff(tolocal(min(rt.DateTime)),getdate())/60.0))*60),' m'))
end AS Duration

FROM Orion.Nodes n
left join orion.responsetime rt on rt.nodeid=n.nodeid
--LEFT JOIN (SELECT nn.nodeid, count(*) AS drops FROM Orion.nodes nn WHERE (nn.events.NetObjectType='N') and (nn.Events.EventType=1) and (daydiff(nn.Events.EventTime,tolocal(getdate()))=0) group by nn.nodeid) drops on drops.nodeid=n.NodeID
WHERE n.STATUS = 2 and rt.AvgResponseTime is null

--and n.caption like '%${SEARCH_STRING}%' or n.IP_Address like '%${SEARCH_STRING}%'
GROUP BY n.caption, n.StatusIcon, n.DetailsUrl, n.nodeid, n.ip_address, n.VendorIcon
ORDER BY MinuteDiff(tolocal(min(rt.DateTime)),getdate()) desc, n.caption asc

But now i want to add custom Properties to it but all attempts failed. 

  • Hi  you can simply add n.CustomProperties.SiteName as [Site Name] at the start of the select, but make sure that you add it to the Group By as well... 

    SELECT n.CustomProperties.SiteName as [Site Name], TOUPPER(SUBSTRING (n.Caption,1, CASE WHEN CHARINDEX('.',n.Caption,1) <=4 THEN LENGTH(n.Caption) ELSE (CHARINDEX('.',n.Caption,1)-1) END)) AS [IP]
    ,case when min(rt.DateTime) is NULL
    then 'More than a year'
    else concat(substring(tostring(min(rt.DateTime)),6,2),'-',substring(tostring(min(rt.DateTime)),9,2),'-',substring(tostring(min(rt.DateTime)),1,4),' ',substring(tostring(min(rt.DateTime)),12,2),':',substring(tostring(min(rt.DateTime)),15,2))
    end as [Date]
    ,case when min(rt.DateTime) is NULL
    then ' More than a year'
    else concat((hourDIFF(tolocal(min(rt.DateTime)),getdate())/24), ' Days '
    ,(Ceiling((HourDiff(tolocal(min(rt.DateTime)),getdate())/24.0-Floor(HourDiff(tolocal(min(rt.DateTime)),getdate())/24.0))*24)),' h '
    ,(Ceiling((MinuteDiff(tolocal(min(rt.DateTime)),getdate())/60.0-Floor(MinuteDiff(tolocal(min(rt.DateTime)),getdate())/60.0))*60),' m'))
    end AS Duration
    
    
    FROM Orion.Nodes n
    left join orion.responsetime rt on rt.nodeid=n.nodeid
    --LEFT JOIN (SELECT nn.nodeid, count(*) AS drops FROM Orion.nodes nn WHERE (nn.events.NetObjectType='N') and (nn.Events.EventType=1) and (daydiff(nn.Events.EventTime,tolocal(getdate()))=0) group by nn.nodeid) drops on drops.nodeid=n.NodeID
    WHERE n.STATUS = 2 and rt.AvgResponseTime is null
    
    --and n.caption like '%${SEARCH_STRING}%' or n.IP_Address like '%${SEARCH_STRING}%'
    GROUP BY n.caption, n.StatusIcon, n.DetailsUrl, n.nodeid, n.ip_address, n.VendorIcon, n.CustomProperties.SiteName
    ORDER BY MinuteDiff(tolocal(min(rt.DateTime)),getdate()) desc, n.caption asc
    

  • Hi LebeauUK 

    Thank you for responding, when i click on show records it shows to me what i just added 

    However, when i save the model it doesn't reflect and only shows the below 

  • Ok I have just moved the custom field to after the Duration so:

    SELECT TOUPPER(SUBSTRING (n.Caption,1, CASE WHEN CHARINDEX('.',n.Caption,1) <=4 THEN LENGTH(n.Caption) ELSE (CHARINDEX('.',n.Caption,1)-1) END)) AS [IP]
    ,case when min(rt.DateTime) is NULL
    then 'More than a year'
    else concat(substring(tostring(min(rt.DateTime)),6,2),'-',substring(tostring(min(rt.DateTime)),9,2),'-',substring(tostring(min(rt.DateTime)),1,4),' ',substring(tostring(min(rt.DateTime)),12,2),':',substring(tostring(min(rt.DateTime)),15,2))
    end as [Date]
    ,case when min(rt.DateTime) is NULL
    then ' More than a year'
    else concat((hourDIFF(tolocal(min(rt.DateTime)),getdate())/24), ' Days '
    ,(Ceiling((HourDiff(tolocal(min(rt.DateTime)),getdate())/24.0-Floor(HourDiff(tolocal(min(rt.DateTime)),getdate())/24.0))*24)),' h '
    ,(Ceiling((MinuteDiff(tolocal(min(rt.DateTime)),getdate())/60.0-Floor(MinuteDiff(tolocal(min(rt.DateTime)),getdate())/60.0))*60),' m'))
    end AS Duration
    , n.CustomProperties.SiteName as [Site Name]

    And it showed up in the table columns then you can change the order where it appears. 

  • Hi LebeauUK 

    Also the same, please check the below SWQL 

    SELECT TOUPPER(SUBSTRING (n.Caption,1, CASE WHEN CHARINDEX('.',n.Caption,1) <=4 THEN LENGTH(n.Caption) ELSE (CHARINDEX('.',n.Caption,1)-1) END)) AS [IP]
    ,case when min(rt.DateTime) is NULL
    then 'More than a year'
    else concat(substring(tostring(min(rt.DateTime)),6,2),'-',substring(tostring(min(rt.DateTime)),9,2),'-',substring(tostring(min(rt.DateTime)),1,4),' ',substring(tostring(min(rt.DateTime)),12,2),':',substring(tostring(min(rt.DateTime)),15,2))
    end as [Date]
    ,case when min(rt.DateTime) is NULL
    then ' More than a year'
    else concat((hourDIFF(tolocal(min(rt.DateTime)),getdate())/24), ' Days '
    ,(Ceiling((HourDiff(tolocal(min(rt.DateTime)),getdate())/24.0-Floor(HourDiff(tolocal(min(rt.DateTime)),getdate())/24.0))*24)),' h '
    ,(Ceiling((MinuteDiff(tolocal(min(rt.DateTime)),getdate())/60.0-Floor(MinuteDiff(tolocal(min(rt.DateTime)),getdate())/60.0))*60),' m'))
    end AS Duration
    
    , n.CustomProperties.info as [Infomration]
    , n.CustomProperties.Owner as [Team]
    
    FROM Orion.Nodes n
    left join orion.responsetime rt on rt.nodeid=n.nodeid
    --LEFT JOIN (SELECT nn.nodeid, count(*) AS drops FROM Orion.nodes nn WHERE (nn.events.NetObjectType='N') and (nn.Events.EventType=1) and (daydiff(nn.Events.EventTime,tolocal(getdate()))=0) group by nn.nodeid) drops on drops.nodeid=n.NodeID
    WHERE n.STATUS = 2 and rt.AvgResponseTime is null
    
    --and n.caption like '%${SEARCH_STRING}%' or n.IP_Address like '%${SEARCH_STRING}%'
    GROUP BY n.caption, n.StatusIcon, n.DetailsUrl, n.nodeid, n.ip_address, n.VendorIcon, n.CustomProperties.info, n.CustomProperties.Owner
    ORDER BY MinuteDiff(tolocal(min(rt.DateTime)),getdate()) desc, n.caption asc

  • Hi  

    Very Strange, as I have put that SWQL into test pad I have got and it is working as it should do.   When you put the SWQL into the table and click on Show Records is it showing all of the columns that you expect to see? Have you puit the code in to a Classic Dashboard on a Custom Query to see if it works on there? 

    I guess the final thing to check is what version of SolarWinds are you running? I am running 2023.4.1 here.. 

  • Hi LebeauUK 

    Thank you, it works now after I remove the table and add it again

  • If you add new elements to a Custom Table widget's Definition in Modern Dashboards, you'll need to click on the "Plus" to add a new field to the output. (down at the bottom)