Tweaking the Codes in One of the Pre-Built Orion Reports in Report Writer.

Hi Guys,

Trust you all are doing great.

Kindly assist me; I want to be able to generate reports that would consists of historical node availability, historical interface percentage utilization, and historical node response time (Latency) for all the network nodes (Cisco devices) under my work environment. I want to be able to generate it via one report file.

I observed that one of the pre-built Orion reports: Average and Peak Traffic Rates - WAN Interfaces Last 7 Days, has the ability to generate historical interface traffic information.

How to tweak the codes to fit information on Historical Node Response Time and Historical Node availability is the issue that I have.

Please help me.

Regards,

David.

Find more posts tagged with

network_monitoring

network_management

Accepted answers

All comments

borgan

If you have NPM version 10.7 you can use the web-based report tool - Manage Reports, to do this. You would simply add three separate content sections to the report - one for each data type that you want. In fact, you can even use any reports you find in Report Writer as a template for the web version of the report you are building. Simply expose the SQL for a report in Report Writer and then paste it in as a custom SQL query in the web report.

Let me know if you need more specifics.

sgenius

Hi Borgan,

Many thanks for the information. Actually, it's NPM 10.6 in my work environment.

All I really want to do is ensure that this code runs. For now, I really don't know where I'm wrong. See the code below.

SELECT TOP 10000 Nodes.NodeID AS NodeID,
Nodes.Caption AS NodeName, Nodes.Location AS Location, AVG(ResponseTime.Availability) AS AVERAGE_of_Availability, Interfaces.InterfaceID AS InterfaceID,
Interfaces.Caption AS Interface_Caption,
AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,
MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps

FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID) INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)) INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)

WHERE

( DateTime BETWEEN 41762 AND 41769.4166666667 )

AND

(

(Nodes.Caption LIKE '%%router%%') AND

(Interfaces.Caption LIKE '%%Tunnel%%') AND

(
   (DatePart(Hour,DateTime) >= 8) AND
   (DatePart(Hour,DateTime) <= 18) AND
   (
    (DATEPART(weekday, DateTime) <> 1) AND
    (DATEPART(weekday, DateTime) <> 7)))
)

GROUP BY Nodes.NodeID, Nodes.Caption, Nodes.Location, Interfaces.InterfaceID, Interfaces.Caption

ORDER BY 2 ASC, 5 ASC

---------------------------------------------------------------------------------------------------------------------------------------------------------

Please take note that the bold italicized words are words that I inserted into an Orion pre-built report to fit what I want; yet, it's not working.

I suspect that something is wrong with the way I tweaked it into the INNER JOIN statements.

Thanks for your assistance.

Regards,

David.

borgan

Now that I think about it, the Manage Reports feature is also in version 10.6, so you could do what I described. I think it might actually be even easier since you need to combine node availability, interface utilization and node response time in the same report. Each of the three data types would be a separate content added to the report.

sgenius

Hi Borgan,

Many thanks for the tip. You are very correct; these three data types that I desire can be combined on the same report by adding them as seperate contents.

However, what if I want to tweak it in such a way that it will come out as a single table in one content on a report?

This is actually my major challenge. This is the main reason why I need a tweak in the code aforementioned.

Any ideas?

Regards,