10 Replies Latest reply on Jan 28, 2020 3:36 PM by Craig Norborg

    How to get Interface status and LED icon on this SQL script

    chasmann

      I need some help whit this SQL script i have used in the Report. Need to get the Status of the interface if its up or down and whit LED icon. Can anyone help whit this?

       

      select

      Interfaces.nodeID AS NodeID,

      Nodes.Caption AS NodeName

      interfaces.caption AS InterfaceName,

      interfaces.interfaceIndex,

      NodeIPAddresses.IPAddress,

      NodeIPAddresses.InterfaceIndex

       

      from

      Interfaces

       

      Right join nodes on (nodes.nodeID = interfaces.NodeID)

       

      Left join NodeIPAddresses on (NodeIPAddresses.nodeid = interfaces.nodeid AND NodeIPAddresses.InterfaceIndex = interfaces.interfaceindex)

      WHERE (NodeIPAddresses.IPAddress IS NOT NULL AND Nodes.Caption like 'SB1-%')

      ORDER BY Nodes.Caption, interfaces.caption

       

        • Re: How to get Interface status and LED icon on this SQL script
          krishna mishra

          Use the status object in your query-

           

          1. ,'/Orion/images/StatusIcons/small-' + ToString(N.StatusIcon) AS [_IconFor_Node Name
          2. ,'/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(N.NodeID) AS [_LinkFor_Node Name

           

          try to add them and check the result.

           

          always run the query with top 2 object, which will give a you good results in term of testing.

           

          thanks

           

            • Re: How to get Interface status and LED icon on this SQL script
              chasmann

              Hmm not working. I use regular SQL query. And i need the status of the interface not the node.

               

               

              Thanks

                • Re: How to get Interface status and LED icon on this SQL script
                  MathieuJM

                  Hi,

                   

                  you can try something like

                   

                  select interfaces.nodeid as [nodeid], nodes.caption as [node name], interfaces.caption as [interface name],interfaces.interfaceindex as [interface index],interfaces.status as [status id],statusinfo.statusname as [status],
                  concat('<img src="/Orion/images/StatusIcons/small-',interfaces.statusled,'"/>')
                  from interfaces
                  join nodes on nodes.nodeid = interfaces.nodeid
                  join statusinfo on statusinfo.statusid = interfaces.status
                    • Re: How to get Interface status and LED icon on this SQL script
                      chasmann

                      I have modifyed the query you have made and merged whit mine like this

                       

                      select

                      Interfaces.nodeID AS NodeID,

                      Nodes.Caption AS NodeName,

                      interfaces.caption AS InterfaceName,

                      interfaces.interfaceIndex,

                      NodeIPAddresses.IPAddress,

                      NodeIPAddresses.InterfaceIndex,

                      interfaces.status as [status id],

                      statusinfo.statusname as [status]

                      from

                      Interfaces

                       

                      join statusinfo on statusinfo.statusid = interfaces.status 

                      Right join nodes on (nodes.nodeID = interfaces.NodeID)

                       

                      Left join NodeIPAddresses on (NodeIPAddresses.nodeid = interfaces.nodeid AND NodeIPAddresses.InterfaceIndex = interfaces.interfaceindex)

                      WHERE (NodeIPAddresses.IPAddress IS NOT NULL AND Nodes.Caption like 'SB1-%')

                      ORDER BY Nodes.Caption, interfaces.caption

                       

                      Getting this result, but it will be nice to have the LED color status beside the status info.

                      I tried the concat('<img src="/Orion/images/StatusIcons/small-',interfaces.statusled,'"/>')  but that did not work. I can see the color on the preview but not inn the ac

                       

                      Preview

                       

                      The report

                        • Re: How to get Interface status and LED icon on this SQL script
                          MathieuJM

                          Have you check the "Allow HTML tags" in order to have the html code from the query interpreted as HTML and not simply text ?

                            • Re: How to get Interface status and LED icon on this SQL script
                              chasmann

                              Of course why I didn't think about that It working now Using this SQL query

                               

                              select

                              Interfaces.nodeID AS NodeID,

                              Nodes.Caption AS NodeName,

                              interfaces.caption AS InterfaceName,

                              interfaces.interfaceIndex,

                              NodeIPAddresses.IPAddress,

                              NodeIPAddresses.InterfaceIndex,

                              interfaces.status as [status id],

                              statusinfo.statusname as [status text],

                              concat('<img src="/Orion/images/StatusIcons/small-',interfaces.statusled,'"/>')  AS [Status]

                              from

                              Interfaces

                               

                              join statusinfo on statusinfo.statusid = interfaces.status 

                              Right join nodes on (nodes.nodeID = interfaces.NodeID)

                              Left join NodeIPAddresses on (NodeIPAddresses.nodeid = interfaces.nodeid AND NodeIPAddresses.InterfaceIndex = interfaces.interfaceindex)

                              WHERE (NodeIPAddresses.IPAddress IS NOT NULL AND Nodes.Caption like 'SB1-%')

                              ORDER BY Nodes.Caption, interfaces.caption


                              Thanks all of you for the help

                    • Re: How to get Interface status and LED icon on this SQL script
                      Craig Norborg

                      I'm guessing that your working in the "Report Writer", since you're using SQL rather than SWQL.  I think they were thinking you were writing a custom resource in SWQL.  One thing to keep in mind is that there are a lot of examples included with the product or on line here. 

                       

                      If you go into an existing report, like "Current status of all nodes" for instance.  Then go in to "Edit Table"

                       

                       

                      You can see how they put the icon in their report by choosing the "Generic Icon" display setting.

                       

                       

                      Pretty sure what you want to do is something similar?

                      1 of 1 people found this helpful