Well I've verified this works in a regular SQL query so is this a short coming in Orion report writer?
Let me know!
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.
That didn't work.
Compute is creating a second result set - Solarwinds is only processing the first result set. UNION'ing the result sets (I'm trying to figure out how) might help here.
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
select 'TOTAL','',Sum(CountOfEventType) From @Results
ORDER BY CountOfEventType DESC