View Limitations - SQL Errors for Report From Orion Report Writer

What are the limitations and pitfalls of showing SQL Reports from Orion Report Writer in Views where Limitations are applied?

----------

I have several different Views that I used for reporting weekly performance for different groups in our network.

The views would include a Custom Network Wide Graph for the Last 7 Days and then my associated Report from Orion Report Writer (custom SQL report). A True/False limitation on a custom property is applied to the view so that the graph represents my desired group (IsOPSGear = 1).

I found before that the report fails if it does not already meet the limitation.

The original SQL report I created shows our Top 20 worst performing Sites according to Average Availability and Packet Loss from ResponseTime_Detail (e.i. last 7 days). Note that Nodes.IsOPSGear = 1 is the same limitation I apply to my View. This SQL report has worked fine

====ORIGINAL====
SELECT     TOP (20) AVG(TempTable.AvgAvail) AS NodeAvail, AVG(TempTable.AvgLoss) AS PacketLoss, Nodes.Department, Nodes.Site, Nodes.City
FROM         (SELECT     NodeID, AVG(Availability) AS AvgAvail, AVG(PercentLoss) AS AvgLoss, YEAR(DateTime) AS YearDate, MONTH(DateTime) AS MonthDate,
                                              DAY(DateTime) AS DayDate
                       FROM          ResponseTime_Detail
                       GROUP BY YEAR(DateTime), MONTH(DateTime), DAY(DateTime), NodeID
                       ) AS TempTable LEFT OUTER JOIN
                      Nodes ON TempTable.NodeID = Nodes.NodeID
WHERE     (Nodes.IsOPSGear = 1)
GROUP BY Nodes.Site, Nodes.City, Nodes.Department
ORDER BY NodeAvail, PacketLoss DESC, Nodes.Department, Nodes.Site, Nodes.City
===============

The above SQL worked for showing the Last 7 Days when ResponseTime_Detail was set for 7 Days. We have since changed this to 30 Days. Therefore an additional line was required in my SQL report to select only the Last 7 Days [WHERE (DateTime > { fn NOW() } - { fn CURTIME() } - 7)].

The SQL Report with the added line runs successfully in the View when no Limitations are applied. When the Limitation IsOPSGear=1 is applied to the View the report produces the following error. I have been unable to resolve this annoyance and I don't know why this does not work. How does the View Limitation get applied to the Report? Why is it causing the Report to fail?

====ERROR====
System.Data.SqlClient.SqlException: The multi-part identifier "Nodes.IsOPSGear" could not be bound. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at
...
============

====ORIGINAL====
SELECT     TOP (20) AVG(TempTable.AvgAvail) AS NodeAvail, AVG(TempTable.AvgLoss) AS PacketLoss, Nodes.Department, Nodes.Site, Nodes.City
FROM         (SELECT     NodeID, AVG(Availability) AS AvgAvail, AVG(PercentLoss) AS AvgLoss, YEAR(DateTime) AS YearDate, MONTH(DateTime) AS MonthDate,
                                              DAY(DateTime) AS DayDate
                       FROM          ResponseTime_Detail
WHERE      (DateTime > { fn NOW() } - { fn CURTIME() } - 7)
                       GROUP BY YEAR(DateTime), MONTH(DateTime), DAY(DateTime), NodeID
                       ) AS TempTable LEFT OUTER JOIN
                      Nodes ON TempTable.NodeID = Nodes.NodeID
WHERE     (Nodes.IsOPSGear = 1)
GROUP BY Nodes.Site, Nodes.City, Nodes.Department
ORDER BY NodeAvail, PacketLoss DESC, Nodes.Department, Nodes.Site, Nodes.City
===============

Find more posts tagged with

view_limitations

custom_availability_report

custom_view

7_days

sql

report_writer

availability_graph

npm

sql_report_writer

custom_report

average_availability

availability_report

Accepted answers

All comments

svanderz

I have created a simplified example of the issue. It contains only a single Custom.Property checkbox called "IsOPSGear".

Placed on a view with the custom Limitation of IsOPSGear = 1 the first code runs fine while the second code produces a sqlexception.

========FIRST=======

SELECT     TOP (20) AVG(TempTable.AvgAvail) AS NodeAvail, AVG(TempTable.AvgLoss) AS PacketLoss, Nodes.Vendor, Nodes.Status, Nodes.IsOPSGear
FROM         (SELECT     NodeID, AVG(Availability) AS AvgAvail, AVG(PercentLoss) AS AvgLoss, YEAR(DateTime) AS YearDate, MONTH(DateTime) AS MonthDate, DAY(DateTime)
                                              AS DayDate
                       FROM          ResponseTime_Detail
                       GROUP BY YEAR(DateTime), MONTH(DateTime), DAY(DateTime), NodeID) AS TempTable LEFT OUTER JOIN
                      Nodes ON TempTable.NodeID = Nodes.NodeID
WHERE     (Nodes.IsOPSGear = 1)
GROUP BY Nodes.Vendor, Nodes.Status, Nodes.IsOPSGear ORDER BY NodeAvail, PacketLoss DESC, Nodes.Vendor

===================

========SECOND=======

SELECT     TOP (20) AVG(TempTable.AvgAvail) AS NodeAvail, AVG(TempTable.AvgLoss) AS PacketLoss, Nodes.Vendor, Nodes.Status, Nodes.IsOPSGear
FROM         (SELECT     NodeID, AVG(Availability) AS AvgAvail, AVG(PercentLoss) AS AvgLoss, YEAR(DateTime) AS YearDate, MONTH(DateTime) AS MonthDate, DAY(DateTime)
                                              AS DayDate
                       FROM          ResponseTime_Detail
                       WHERE      (DateTime > { fn NOW() } - { fn CURTIME() } - 7)
                       GROUP BY YEAR(DateTime), MONTH(DateTime), DAY(DateTime), NodeID) AS TempTable LEFT OUTER JOIN
                      Nodes ON TempTable.NodeID = Nodes.NodeID
WHERE     (Nodes.IsOPSGear = 1)
GROUP BY Nodes.Vendor, Nodes.Status, Nodes.IsOPSGear ORDER BY NodeAvail, PacketLoss DESC, Nodes.Vendor

===================

Atamido

I'm in a similar boat. I created a report to show the most recently downed nodes. But if there are any limitations applied (either to the account or the page) then I get the above error. The only thing that I'm doing differently than I've done in the past is having another SELECT statement inside of a JOIN.

SELECT TOP 10
Nodes.NodeID,
Nodes.Caption,
MaxDate

FROM Nodes
LEFT JOIN (SELECT NodeID, MAX(DateTime) MaxDate
           FROM ResponseTime
           WHERE PercentLoss < 100
           GROUP BY NodeID) g1 ON g1.NodeID=Nodes.NodeID

WHERE (Nodes.Status = 2)

ORDER BY MaxDate Desc

Atamido

I can confirm that if I remove the WHERE from within the SELECT that is inside of the JOIN, then it stops throwing the error. It appears that Solarwinds chokes on a WHERE statement inside of the select statement. I also tried inverting the statement so that the Nodes table was selected on the inner SELECT, and the ResponseTime was on the outer, but it didn't make a difference.

Atamido

I managed to work around this problem by reworking how I solved the problem. It's not nearly as efficient, and there still couldn't be a WHERE in the upper section, but it does work with account limitations.

WITH LastResponse (NodeId, MaxDate, PercentLoss)
AS (
SELECT
ResponseTime.NodeID,
MAX(DateTime) MaxDate,
ResponseTime.PercentLoss

FROM ResponseTime

GROUP BY NodeID, PercentLoss
)

SELECT TOP 10
LastResponse.NodeID,
Nodes.Caption,
MAX(LastResponse.MaxDate) MaxTime

FROM LastResponse
LEFT JOIN Nodes ON (Nodes.NodeID=LastResponse.NodeID AND Nodes.Status = 2)

WHERE (Nodes.ServiceClass = 'production')
AND (Nodes.Status = 2)
AND (LastResponse.PercentLoss < 100)

GROUP BY LastResponse.NodeID, Caption

ORDER BY MaxTime Desc