Custom reports against alerts, queries and metrics
CONTEXT: We want to see the bigger picture arising from the data presented in SQL Monitor.
PROBLEM: Currently you can't pull together a report in the SQL Monitor UI, and the monitoring database is not particularly accessible. We need reports against:
- The metrics shown in the analysis graph
- The top queries and waits
- The alerts raised over time
- Server configuration and SLA
- Provide reporting scripts against the monitoring database
- Privide a BI interface against the monitoring database
- Priovide richer data-export functionality in the UI
- Provide a report designer in the UI (perhaps allowing for regular reports to be sent)
I like to take a summary of alerts over a definable period by server, instance, database. This will help me monitor error rate. A facility to present this graphically would be fantastic.
Thanks for the suggestion. I'm interested to know a bit more about what kind of information you'd like to report on and what kind of format. Regarding an alerts report, what would be most useful for the Application Managers/Developers to see?
Tony Maddonna commented
There are no real reporting functions in SQL Monitor. We have a need to print reports based on the Alerts we are receiving and distribute them amongst the appropriate Application Managers/Developers for correction to code. At this time all that can be done is a screen scrape. There has to be reporting functions of the alerts, with details so they can be remedied by other parties than those monitoring the systems.
Thanks a lot to all of you for your suggestion.
Yes. We have this for our Legacy systems and it would be great to have this for SQL Monitor monitored systems. This is the one key thing that SQL Monitor is missing.
Thanks for your feedback.
I realize this thread is several months old but this is another isssue where have a base set of Normalized views would solve the problem because the user would then have the necessary tools to get at the info that is in the SQL Monitor DB but is not otherwise easily accessed.
I'm a big fan of ideas like this. I would like to see more *built-in* analysis as a whole. I'll add one piece to this... the ability to archive these reports for a configurable period separate from the period of time all of the detailed data is stored for.
Obviously it's possible for DBAs to create this on their own by pushing data to other databases, creating or installing reports (I know Red Gate provides a few SSRS reports) in reporting services and scheduling the email from there. That solution is fine, except I doubt the DBAs that purchase SQL Monitor are the type that have excessive free time to create tools on their own. I'm a fan of Red Gate tools because they allow DBAs to focus on the core of their job by creating complete DB tools so we don't have to. :)
Erwin Teunisse commented
Extracting the data directly from the database is not as easy as it sounds.
Quote from a RGsupport email:
"For the CPU usage specifically, extracting the information from the data repository directly might be tricky. While the relevant query would be this:
SELECT c._Name ClusterName
FROM [data].[Cluster_Machine_Processors_UnstableSamples] a
INNER JOIN [data].[Cluster_Machine_Keys] b ON a.Id = b.Id
INNER JOIN [data].[Cluster_Keys] c ON c.Id = b.ParentId
For the CPU usage specifically, the data itself is cumulative, that is, in order to extract meaningful data, a delta-y over delta-x operation on successive data rows would be necessary. That is a bit of a pain to do in T-SQL, although not impossible."
Erwin Teunisse commented
Exactly what I need. I want to use the exported data for analysis on different server to see if they could be consolidated. Therefor I need the detailed performance data, with an interval of 1 minute for one month of data.....
Derek Glick commented
The analysis reports should be able to run on a schedule and send the via email. For instance it would be great if I could schedule the data/log size report to run every monday and send me the results via email. the ability to export these report to excel is already there, so just needs to be able to run on a schedule and send as an email attachment.
Thanks Matt for confirming.
Yep :) That fixed it, thanks!
We have just updated the SSRS pack. Could you please try and let us know if this works for you?
From what I've researched in the past it's not the same schema. For your research - here is the output of when changed to 2005:
Error 2 [rsInvalidReportDefinition] The report definition is not valid. Details: The element 'ReportItems' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'; has invalid child element 'Tablix' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'. List of possible elements expected: 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Line http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Rectangle http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Textbox http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Image http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Subreport http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:List http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Matrix http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Table http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Chart http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:CustomReportItem ##other:*'. c:\****\****\*****\visual studio 2005\projects\sql monitor reports\sql monitor reports\07 - Top DBs by Growth.rdl 0 0
Thanks for pointing this out to us. Could you please try the following:
1. Open the rdl file in notepad
2. Change '2008' to '2005'.
Could you let us know if it works after this change?
Similar to when I have created 2008 reports the schema is a 2008 definition. When I try to build it using the BIDS that comes with SQL Server 2005 Reporting services I get the following error:
Error 1 [rsInvalidReportDefinition] The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'; which cannot be upgraded. ....\visual studio 2005\projects\sql monitor reports\sql monitor reports\07 - Top DBs by Growth.rdl 0 0
Thanks for your message. As far as we have aware, SSRS reports 07 and 08 works for SQL Server 2005. Could you please let us know what exact error are you getting on SQL Server 2005 for reports 07 amd 08?
Thanks for sharing the ssrs-pack link. I wasn't aware they were out there eventhough the link is on the product page :) . I see that it is in an unsupported pack, however I figure I would drop in a note to mention that reports 07 and 08 (Top DBs by Growth/Size) do not appear to be compatible with SSRS for SQL 2005. The Installation documentation mentions that it is compatible with 2005 so I figured you all were not aware :)
Seann Alexander commented