cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Highlighted
Level 8

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

Jump to solution

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

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
Highlighted
Level 12

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

Jump to solution

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

View solution in original post

0 Kudos
10 Replies
Highlighted

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

Jump to solution

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

0 Kudos
Highlighted
Level 8

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

Jump to solution

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

Thanks

0 Kudos
Highlighted
Level 12

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

Jump to solution

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

View solution in original post

0 Kudos
Highlighted
Level 8

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

Jump to solution

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

0 Kudos
Highlighted
Level 12

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

Jump to solution

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

0 Kudos
Highlighted
Level 8

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

Jump to solution

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

0 Kudos
Highlighted

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

Jump to solution

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?

Highlighted
Level 8

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

Jump to solution

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

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

0 Kudos

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

Jump to solution

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

0 Kudos