This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

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

pastedImage_0.png

  • 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

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

    Thanks

  • 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
  • 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"

    pastedImage_0.png

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

    pastedImage_1.png

    Pretty sure what you want to do is something similar?

  • 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.

    pastedImage_0.png

    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

    pastedImage_1.png

    The report

    pastedImage_2.png

  • No i use Orion Web reporter. But if some one have the SWQL query i need i be very happy to use that emoticons_happy.png

    I need the node name, interface name, Interface status whit LED, IP address that is assigned to the interface.

  • Sorry, report writer / web reporter, that's what I meant.

    What I posted should help you out.  

    What way you want to do it depends on how you want the info.   A resource on a page would be more of an on-demand type thing that shows up on a summary page or something.   A web-reporter can be on demand, or you can schedule it, plus you can easily export to excel or print to PDF nicely and such.

    For what you're doing, I think the report engine is the right thing...

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

  • Of course why I didn't think about that emoticons_happy.png It working now emoticons_happy.png 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 emoticons_happy.png

  • Your query looks rather complex to translate into SWQL, for me at least.  

    If you have the query working, stick with Orion Web Reporter and look at my first message to help figure out how to get the icons showing, should be simple.    My first message was referring to the web reporter...