Add a custom property to a report

jamiewillis

Hi folks,

Hopefully a simple query and thereby answer!

I am trying to create a report listing name, Time it went down, how long its been down for, it IP Address and finally information held in the Custom Property "Comments",

This is what I have (and already plagiarized)

SELECT

  NodeName AS [Node Name],
  machinetype AS [Machine Type],
  IP_Address AS [IP Address],
  '/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name],
  DetailsUrl AS [_LinkFor_Node Name],
  concat(SUBSTRING(tostring(MAX(e.EVENTTIME)),1,4),SUBSTRING(tostring(MAX(e.EVENTTIME)),5,2),
        SUBSTRING(tostring(tolocal(MAX(e.EVENTTIME))),12,8)) as Downtime,
  CONCAT(HOURDIFF(tolocal(max(e.eventtime)),getdate())/24,' Day(s) ',
        HOURDIFF(tolocal(max(e.eventtime)),getdate())-(HOURDIFF(tolocal(max(e.eventtime)),getdate())/24)*24,'h ',
        MINUTEDIFF(tolocal(max(e.eventtime)),getdate())   -   (MINUTEDIFF(tolocal(max(e.eventtime)),getdate())/60)*60,'m') AS Duration
        -- ,MINUTEDIFF(tolocal(MAX(E.EventTime)),getdate()) as minutes_since
        --HOURDIFF(tolocal(max(e.eventtime)),getdate())/24 as Days,
        --HOURDIFF(tolocal(max(e.eventtime)),getdate())     -   (HOURDIFF  (tolocal(max(e.eventtime)),getdate())/24)*24 as hours,
        --MINUTEDIFF(tolocal(max(e.eventtime)),getdate())   -   --(MINUTEDIFF(tolocal(max(e.eventtime)),getdate())/60)*60 as mins
FROM ORION.NODES NODES
INNER JOIN ORION.EVENTS e ON NODES.NODEID = e.NETWORKNODE
WHERE STATUS = 2 and E.Eventtype=1 --and nodes.customproperties.SystemsGrouping Like '%CPE%'
GROUP BY NodeName, StatusIcon, DetailsUrl, machinetype, IP_Address
ORDER BY MINUTEDIFF(tolocal(MAX(E.EventTime)),getdate())  desc

which gives

So how do I add in the custom property so it displays in the above. I have no idea on SQL or SWQL - so any assistance would be awesome!

styler

I would guess there's a column in dbo.nodes for "Comments" as I'm seeing all my custom node properties getting uniquely named column headers in the same view in my system. You could probably get what you're looking for by just adding "comments," in your SELECT statement.

As an aside, I recommend using SQL Server Management Studio for your queries if you're still trying to use SWQL studio, as I've encountered syntactical issues like not supporting "select *" and requiring me to relaunch every time a query doesn't run successfully for any reason.