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)
Thanks for your feedback Michael.
Michael Pettersson commented
I'm very interested to know the sql server instance uptime. We run most of our databases on cluster and we need to provide uptime reports.
Thanks for your suggestion Chris.
Thanks a lot for your feedback.
For the time being, you can try to extract data directly from SQL Monitor database. Unfortunately, we do not have any documenatation for the schema but the naming should be self explantory. Try using the views.
Matt Cunningham commented
I've been asked to capture 3 months worth of performance information from a production system and would like to use SQL Monitor to provide that report. I'd like the ability to choose how the data is exported. Apparently the export feature only exports what is shown in the graph on the screen. For example, if I needed to export a metric with data points every 5 min, I would need to set the view for 18 hrs. Subsequently, if I needed 1 months worth of data exported with 5 min intervals, I would have to export more than 30 times and concatenate the files.
Thanks a lot for your suggestion. We do have some very basic report available which covers similar information. You can check them here: http://www.red-gate.com/products/dba/sql-monitor/ssrs-pack
Also, if you are on SQL Monitor V 3 then you can also write your own metric using 'Custom metric' feature.
Do you think the above is enough for your requirement and you are happy to close this issue or would you still like Monitored servers page to have SQL and Windows version?
Eric Humphrey commented
Show SQL / Windows version on monitored servers list or provide a report that shows simple hardware / software stats like processor, RAM, OS version, SQL version, Total Disk Space.
Sean Alexander commented
NOTE: I realized only after posting the above (I copied and pasted it as is from a post I made in the support forum and was told to put that some post here instead) that the description was not complete with regards to the title, specifically the use of the term Normalized.
A base set of Normalized views would be views that like normalized tables return information that is normalized to 3rd normal form that can then be joined with other views to create more complex queries for reporting. The problem with the views and existing SSRS reports are that they use complex queries that fit specific reporting needs and therefore are not useful for other reporting needs. To make use of these one would need to reverse engineer the views and then re-build them to meet their own needs. A set of base normalized views like tables would server as building blocks and therefore far more useful and flexible.
Currently there is no proper DDF or similar tool in SQL Monitor 2.x or 3.x that users can employ to meet custom reporting needs. There are a handful of queries floating around as well as some SSRS Reports but in general there is no set of general purpose tools for custom reporting, not even a partially completed Data dictionary.
I have proposed (in a separate thread in the older SQL Monitor 2.3 forum) that Red-gate consider putting together a set of base views that like their table counterparts can be combined together to produce rich data sets for the purposes of custom reporting independent of what reporting solution you use be it SSRS, Crystal Reports, Excel or any of several popular reporting software solutions.
The issue isn’t that there’s no reporting tool a customer can use but that there’s no base objects that can be used to feed a custom report. All of the solutions I’ve seen mentioned in various threads are designed for specific reporting needs and therefore not flexible. What I am proposing/asking of Red-Gate, is a set of base tools that can be used for custom reporting. The simplest and most flexible of choices to meet this need would be views. They are easily updated (to accommodate future changes in SQL Monitor) and do not require that any changes be made to the source data nor to the way the product works. They also make it possible for the customer to use whatever reporting software solution they want.
If this sounds like something you would be interest in then please post a reply in this thread and make Red-Gate aware of your custom reporting interests.
Chris (Tech Support) commented
It could be really useful to have a seperate Red Gate interface which accesses all the data in the RedGateMonitor database but is specifically geared toward Business Intelligence
Thanks for the suggestion Gail, we've logged it and I've added that your preffered format would be CSV so correct me if I'm wrong!
Gail Shaw commented
Put an export button with the 'top 10 expensive queries' so that the list can be easily pulled into excel for offline investigation or distribution.
Thanks Kevan for your suggestion.
To the machine overview screen, add machine uptime (in days:hours:minutes) or the time of the last system reboot - anything just to give a quick indication of how long the machine has been up.