Custom Report showing a total of times nodes went down for the last month

I've created a report that works very well that allows me to view the total # of times a node has gone down for the past month. I can get the counts to work I'm just trying to utilize the compute command to give me a total # at the bottom of the report for the node down events.

Here's my SQL - it works but doesn't show the total at the bottom:

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

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

SELECT Nodes.SysName, Interfaces.InterfaceAlias, Count(events.EventType) AS CountOfEventType

FROM (Events LEFT JOIN Interfaces ON Events.NetObjectID = Interfaces.NodeID) LEFT JOIN Nodes ON Events.NetObjectID = Nodes.NodeID

WHERE ((Interfaces.InterfaceAlias) Like '%cir%') AND ((Events.EventType)=1) AND Events.EventTime >= @StartDate AND Events.EventTime <= @EndDate
GROUP BY Nodes.SysName, Interfaces.InterfaceAlias
ORDER BY CountOfEventType DESC
Compute sum(Count(events.EventType))

Find more posts tagged with

sql

report_writer

npm

Accepted answers

Dropped the first result into a temporary variable table, and unioned your count afterward. Ran the query in report writer, and came up with a working table.

I also thought you didn't really need to count & sum the count, but I don't know what you're expecting from your data.

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
declare @Results TABLE (SysName varchar(255),InterfaceAlias varchar(255),CountOfEventType int)

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

INSERT INTO @Results
SELECT Nodes.SysName, Interfaces.InterfaceAlias, Count(events.EventType) AS CountOfEventType
FROM (Events LEFT JOIN Interfaces ON Events.NetObjectID = Interfaces.NodeID) LEFT JOIN Nodes ON Events.NetObjectID = Nodes.NodeID

WHERE ((Interfaces.InterfaceAlias) Like '%cir%') AND ((Events.EventType)=1) AND Events.EventTime >= @StartDate AND Events.EventTime <= @EndDate
GROUP BY Nodes.SysName, Interfaces.InterfaceAlias

select * from @Results
union
select 'TOTAL','',Sum(CountOfEventType) From @Results
ORDER BY CountOfEventType DESC

All comments

mtxpert

Well I've verified this works in a regular SQL query so is this a short coming in Orion report writer?

Let me know!

Mike

rehs235

I have a simular problem where I created a query to compute the grand total space and the total spaced used on all of our servers drives. The SQL query works but I have yet to figure out how to get it to work in Report Writer.

If this a Report Writer short coming maybe SW could comment.

Try adding SET NOCOUNT ON at the top of your query. As soon as report writer and database manager's query tool see a "Query OK x/y rows affected" it thinks it is done. Setting NOCOUNT will suppress the output of this and allow the query to continue processing after you declare your variables.