Report Writer Bug--- in NPM 9.5 with the 95th percentile reports

I have contacted support about this and the support rep was able to replicate the problem. I just wanted to post on thwack to see if others are also seeing this issue and if anyone has a workaround.

All 3 of the 95th Percentile traffic rate reports under the Historic Traffic Reports group do not work in Report writer. They display just fine on the web page, but the SQL query hangs in report writer.

Here is a screen shot:

The report just sits here like this and never completes.

This also henders you from making modifications to the report. If you modify the sql code, then try to click the "field format" or "report grouping" tab, it has to run the query in order to see the new fields. Since the query never completes, you cannot modify the field format of grouping. The report on the website will display any changes that you made to the sql code, but there is no way to format it, other than sql formating commands.

Any recommendations or work arounds.

Find more posts tagged with

report_writer

historical_traffic_reports

95th_percentile

95%

Accepted answers

All comments

jcooler

Thanks for moving the thread. didnt realize I was under the certification forum.

jeffnorton

If I were to guess I'd say it hangs because you have a very large network. This particular report appears to be averaging the 95th percentile of every interface on every device

jcooler

Nah, thats not the problem. I current only have 238 interfaces. Also, the report runs correctly and fast on the website, just not in report writer.

Its a pretty big problem for me, because I have made a few modifications to a copy of the report, and I cannot format the output because it will not display the "field formating" tab.

FormerMember

I had the same issues and corrected them by removing everything under the report grouping tab. I saw this on other reports as well and the same fix action corrected it. I also upgraded to SP2 which may have been the fix as well. I did both around the same time so which option fixed the problem is hard to tell.

jcooler

The problem is that the first line of the sql code in the report does not start with "select". It works fine out of the box, assumming that SP2 is installed (fixed the grouping problem). But as soon as you try to alter the report by changeing the sql code, it will not work.

I wanted to add a new field to the report, as soon as I did that, it broke.

Anthony_

Same bug here.

The 95th percentile reports in the web console run fine, but in the Report Writer it just hangs when you preview it.

networkers

Hi,

I had a similar issue when i wanted to run a 95th percentile report. I edited few codes and that fixed my problem.

Here is the code for the same.

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = CAST((ROUND(CAST(GetDate() - 1 AS FLOAT), 0, 1)) as datetime)
SET @EndDate = CAST((ROUND(CAST(GetDate() - 0 AS FLOAT), 0, 1)) as datetime)

/*SET @EndDate = GetDate()*/

SELECT

Interfaces.InterfaceId,
@startdate As Startdate,
@Enddate As Enddate,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
Interfaces.InBandwidth,
Nodes.NodeID,

round((Maxbps_In95/interfaces.InBandwidth*100),2) as In_Percent,
round((Maxbps_Out95/interfaces.OutBandwidth*100),2) as Out_Percent

FROM dbo.Nodes
INNER JOIN dbo.Interfaces
    ON (dbo.Nodes.NodeID = dbo.Interfaces.NodeID)
INNER JOIN
(
----------------
    SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95
    FROM (
        SELECT DISTINCT A.InterfaceID
        FROM dbo.InterfaceTraffic A
        WHERE A.DateTime >= @StartDate AND A.DateTime <= @EndDate
    ) AS AA
---------------------
) as RESULT_IN
    ON (dbo.Interfaces.InterfaceID = RESULT_IN.InterfaceID)
INNER JOIN
(
----------------
    SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95
    FROM (
        SELECT DISTINCT A.InterfaceID
        FROM dbo.InterfaceTraffic A
        WHERE A.DateTime >= @StartDate AND A.DateTime <= @EndDate
    ) AS AA
---------------------
) as RESULT_OUT
    ON (dbo.Interfaces.InterfaceID = RESULT_OUT.InterfaceID)
WHERE dbo.Interfaces.InterfaceID = 'enter the ID'
    OR dbo.Interfaces.InterfaceID = 'enter the ID'

ORDER BY NodeName, Interface_Caption

The above code works for previous day and if you need for last 7 days and month change the two lines as requried.

SET @StartDate = CAST((ROUND(CAST(GetDate() - 1 AS FLOAT), 0, 1)) as datetime)
SET @EndDate = CAST((ROUND(CAST(GetDate() - 0 AS FLOAT), 0, 1)) as datetime)

This should fix your problem right away.

ecklerwr1

After reading your post I went and tested this myself. I have the same exact problem. My report writer is stuck querying the database just like yours. I tried networkers SQL in above post and got this error:

smoothstone-mcj

Just in case anyone is looking at this because they are stuck at 9.5 and need a workaround, here's the solution:

The SQL script above is part of the solution, but it's maybe tailored a bit too much to that specific need. The actual problem appears to be related to the creation of the temporary table, and that's what we need to work around.

Here's a functionally identical query that will actually work in NPM 9.5 - All that has changes is removal of the temporary table, and adding where clauses instead of the joins on the temp tables:

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))

SELECT Interfaces.InterfaceId,
Nodes.NodeID,
Nodes.Caption AS NodeName,
Nodes.VendorIcon AS Vendor_Icon,
Interfaces.Caption AS Interface_Caption,
Interfaces.InterfaceIcon AS Interface_Icon,
Maxbps_In95,
Maxbps_Out95,
Maxbps_95
FROM Nodes
INNER JOIN Interfaces
    ON (Nodes.NodeID = Interfaces.NodeID)

INNER JOIN
(
    SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95
    FROM (
        SELECT DISTINCT A.InterfaceID
        FROM dbo.InterfaceTraffic A
        WHERE A.DateTime >= @StartDate AND A.DateTime <= @EndDate
    ) AS AA
) as RESULT_IN
    ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)

INNER JOIN
(
    SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95
    FROM (
        SELECT DISTINCT A.InterfaceID
        FROM dbo.InterfaceTraffic A
        WHERE A.DateTime >= @StartDate AND A.DateTime <= @EndDate
    ) AS AA
) as RESULT_OUT
    ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)

INNER JOIN
(
    SELECT InterfaceID, dbo.GetMaxBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_95
    FROM (
        SELECT DISTINCT A.InterfaceID
        FROM dbo.InterfaceTraffic A
        WHERE A.DateTime >= @StartDate AND A.DateTime <= @EndDate
    ) AS AA
) as RESULT_MAX
    ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)
ORDER BY NodeName, Interface_Caption