Write an Application Monitor Template With Advanced Display of Component data - Part 1

Question

or How to Display a Lot of Data in a Small Space How do you summarise data down to a simple numbers, and still provide enough detail for users to see the details - and not run out of space? My first solution to this was to embed a table. This was the result: The three monitors you can see were all build using the SQL Server User Experience Monitor component. The tables are constructed in the SQL, and everything is wrapped in a DEV set to show scroll-bars when required. For simplicity, it is broken up into three parts - description follows after the code... with det as ( select '' + case when len(Last_Metric_Run_Name) > 30 THEN '' + left(Metric_Name, 28) + '..' else coalesce(Last_Metric_Run_Name, 'N/A' ) end + '' + case when len(Scheduled_Task_Name) > 30 THEN '' + + case when charindex(',', Scheduled_Task_Name) > 0 then left(Scheduled_Task_Name, charindex(',', Scheduled_Task_Name) ) ELSE left(Scheduled_Task_Name, 28) END + '..' ELSE coalesce(Scheduled_Task_Name, 'N/A' ) END + '' + cast(metric_Count as varchar(10)) + '' + cast( cast(size_mb/1024.0 as numeric(10,1)) as varchar(20) ) + 'GB' + '' as dsc ,size_mb from dbo.Metrics ) ,p2 as( SELECT distinct count(*) over (partition by 1) as Result ,descs = STUFF((SELECT N'' + dsc FROM det AS p2 ORDER BY size_mb desc FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 5, N'') FROM det AS p -- --where Delete_After is null and scheduled_task_name is not null ) select Result, '

' + descs + '

Metric	Scheduled Task	Count	Storage

' as Msg from p2 DET (for Detail) This returns several rows of data, and for neatness, if the text is too long (characters > 30), truncates it and adds a tool-tip with the full text. Each column to display in the final table the row is wrapped in tags. The second column is there to sort the rows by in the next query step. This is an example resultset for DET: P2 This takes the rows of data from DET, and appends them all together, separated by tags to define the rows. The rows appear in order of the last column - size_mb. The first column is to eventually be used as the metric - in this case, the number of records returned. I could also be a sum or another value - like the size_mb column I have here for example. The definition of this may look a bit funky because it is using the SQL Analytics function - count(*) over (partition by 1) as Result - but if you don't know what this is doing, just change the count(*) to another aggregate function you may typically use in a standard grouped query. The descs column is the final table contents. This is an example result-set for P2: Final Query This is where you define the size of the scroll-box on screen - so that your results will never fill up the screen. It is also where you define the table headers (if you want them). You can see I have specified a box that is 480px by 50px. Conclusion If you want to use this method yourself, do the following: * write a query to return the detailed results you want, and put that into the DET section. It should return two rows of data - one for the text and one for the sort key. * typically leave P2 alone - unless you change the column used for sorting * adjust the final query to match the size you need, and the column headers you want to see. If you are paying attention, you may have noticed that the box size can be wider than the standard description field, as the box pushes the response time out of the way - so don't feel too cramped. I hope this has been of value. The follow-on article to this will go go into the dynamic html methods available thanks for Solarwinds use of Node.js. Expandable boxes and the like.

frak · Answer

I don't have time to write something up right now, though I do have some powershell-related articles coming. I do have a powershell module that pulls in log files, then lays the result out in the same way and I do play to post that. (In that case I was looking to emulate the log views that the App Insight monitors have available to them). If you follow me you will see it when I post it, though I will try to remember and send you a message anyway. In the meantime I can say that to do this with a script - eg powershell - the same rules would apply. Write a script that: - returns a number - returns text. - in the form of an HTML table. I wrote mine in the following way: 1. loop through results of whatever detail lines you need to display, and concatenate a string, where each line has an open and close tag. Also add up the result number in this loop (for my error log handler, I add up the number of error log entries in the time period). 2. put the final output from step 1, into a table ie wrap in tags, and add the top header row. 3. wrap the output from step 2 into a DIV

shocko · Answer

Can you give an example with a simple powershell monitor? Like this idea.