I'm trying to create a report on node availability (actually some Terminal servers). I can filter out Saturday and Sunday succesfully but when I try to add a filter for business hours (07:00 to 19:00) all the data is removed.
It's now a week on from when I posted this and I can now see 6 weeks of data in my report. So the report should have all the detail required in 7 weeks time....
The fix was to keep hourly data for (in my case) 100 days. (Must remember to keep an eye on the size of the database )
Andrew,
That looks like a bug. I have reports that I filter for business hours using the same type of filtering you're doing and they work just fine. The only thing I can think to suggest you check is to see what the SQL statement that the wizard is creating looks like. In Report Writer, go to Report | Show SQL. This will add a SQL tab to the Report Designer. Your "where" clause should look something like this:
WHERE ( DateTime BETWEEN 39997 AND 40090 ) AND ( (Nodes.Caption = 'TS') AND (DATEPART(weekday, DateTime) <> 1) AND (DATEPART(weekday, DateTime) <> 7) AND (DatePart(Hour,DateTime) >= 6) AND (DatePart(Hour,DateTime) <= 18))
Hope this helps.
Which report type did you choose? I had the same issue until i used the Historical Availability Details report.
here are some that you can edit for your needs:
kweise,
This is the sql that the report is generating when I have the time (and day) filtering turned on:
SELECT TOP 10000 DatePart(Week,(DateTime)) AS SummaryWeek,Nodes.Caption AS NodeName,AVG(DailyNodeAvailability.Availability) AS AVERAGE_of_Availability
FROM Nodes INNER JOIN DailyNodeAvailability ON (Nodes.NodeID = DailyNodeAvailability.NodeID)
WHERE ( DateTime BETWEEN 39993 AND 40089 ) AND ( (DATEPART(weekday, DateTime) >= 2) AND (DATEPART(weekday, DateTime) <= 6) AND (Convert(Char,DateTime,108) > '07:00') AND (Convert(Char,DateTime,108) < '19:00') AND ( (Nodes.Caption LIKE 'TS%')))
GROUP BY DatePart(Week,(DateTime)), Nodes.Caption
ORDER BY SummaryWeek ASC, 2 ASC
The report is supposed to cover the last 3 months and summarise weekly. No records are returned (and I can assure you I'd know if all 6 of out terminal servers where down for 3 months! )
My limited sql skills say the above looks ok. Any comments?
r0berth,
Thanks for you reply.
I think I copied another (unfiltered) report under the History group. Perhaps I should try from scratch...?
Just tried creating a report from scratch using the Historical -availability option. It works but only returns the last 5 weeks instead of the 3 months I opted for...weird!
Andrew
Well, go to your web console, go to admin, then Polling Settings and post the numbers for:
