How To Get Alert History Metrics And More
EdCarden
Posts: 138 Silver 2
Please bear with me as I try to be as non-verbose in describing this as I can. Is there a way in RedGate SQL Monitor 2.X to get at the Alert data that the system is capturing so that one can perform aggregations on that recorded data to determine t8insg like:
Which of the LRQ (Long Running Query) Alert occurs most frequently between @StartDate and @EndDate?
Which LRQ Alert has the longest duration between @StartTimeOfDay and @EndTimeOfDay?
Which LRQ Alert has the longest duration (which alert has the longest duration between going Active and going Inactive)?
And much more. It would seem to me that all the necessary data to answer these kinds of questions is captured by SQL Monitor but there is just no way for anyone to use that captured information.
Am I correct in that there is no way to get this kind of captured Alert information via T-SQL?
Which of the LRQ (Long Running Query) Alert occurs most frequently between @StartDate and @EndDate?
Which LRQ Alert has the longest duration between @StartTimeOfDay and @EndTimeOfDay?
Which LRQ Alert has the longest duration (which alert has the longest duration between going Active and going Inactive)?
And much more. It would seem to me that all the necessary data to answer these kinds of questions is captured by SQL Monitor but there is just no way for anyone to use that captured information.
Am I correct in that there is no way to get this kind of captured Alert information via T-SQL?
Comments
Can I in the first instance point you towards this post that has quite a lot of detail regarding getting data out of SQL Monitor using T-SQL.
http://www.red-gate.com/MessageBoard/vi ... hp?t=12594
Best regards, Fiona
Fionag
Thanks for the link but that thread is dead. The last post which has gone unaddressed is from more than 6 months back, September 2011. While a reply to the last post would certainly not come in a day nor two or even a week, but six months without a reply is a clear indicator that it is unlikely any additional replies to the thread or even an answer to the last post in the thread will ever be forthcoming.
That said, where does Red-Gate stand with regards to a DDF or equivalent for its SQL monitor data?
Is this something we users of SQL Monitor can expect and fi so then when? Or is this something that is unlikely to happen leaving customers to do it themselves?
I can guarantee you that if Red-Gate does not take control of this by providing customers with a DDF or a similar set of base tools they can use to report on data in SQL Monitor then the users will come up with something themselves.
A proper base set of tools would be best met with some simple views that can be combined to make complex data sets that can be used for reporting purposes. While it may be tempting, as Red-Gate employee benrees made reference to in the thread you mentioned, to provide users with a handful of detailed SSRS Reports the end result of that would be that only a handful of users’ needs were met and the rest were left coming up with their own custom solution. Most of your clients will have a working knowledge of SQL Server sufficient enough to create the reports they need so long as they have a useable set of base objects like views. Instead of providing a few detailed SSRS reports SQL Monitor users would be better served with user friendly base views that can be combined together to create rich data sets for use in whatever reporting solution the customer likes be it SSRS, Crystal Reports or even Excel.
In the end if Red-Gate provided a set of usable base views to customers then Red-Gate could be confident that the source data used in a customer’s reports is accurate (as opposed to being inaccurate and the user incorrectly blaming the product for something that is instead a flaw in the source that the report is using) and it would also have an additional “feature†to advertise with the product. Currently the marketing for SQL Monitor does not include: That feature would carry a lot of weight with many a customer.
In closing let me say that this is an opportunity for Red-Gate to increase the features in its product by doing nothing more than documenting its database schema using non-cryptic views that users can then use for custom reports. If Red-Gate doesn’t do this you can bet one or more customers will and when that happens Red-Gate loses the ability to steer the direction of custom reporting in SQL Monitor. I await your response.
BTW – If this suggestion does not fall on deaf ears then let me add this to the above. If its decided to embrace something like this then poll your customers and ask them what kind of base views they would like to see made available for custom reporting purposes instead of asking one or more in house developers to make that determination. The Red-Gate developers certainly are more knowledgeable about the inner workings of SQL Monitor and its database schema but it’s the customers who best know what they want to see from Red-Gate as far as reporting goes that is not already available.