3 Replies Latest reply on Aug 30, 2018 1:04 PM by jamiewillis

    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!

        • Re: Add a custom property to a report
          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.

          • Re: Add a custom property to a report
            David Smith

            Hey jamiewillis try this:

             

            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,
            Nodes.CustomProperties.Comments AS Comments
            FROM ORION.NODES Nodes
            INNER JOIN ORION.EVENTS e ON NODES.NODEID = e.NETWORKNODE
            WHERE STATUS = 2 and E.Eventtype=1
            GROUP BY NodeName, StatusIcon, DetailsUrl, MachineType, IP_Address, Nodes.CustomProperties.Comments
            ORDER BY MINUTEDIFF(tolocal(MAX(E.EventTime)),getdate())  desc
            
            2 of 2 people found this helpful