SQL query to get a quick useful report from DiskMonitor

Topics: Developer Forum, User Forum
May 9, 2008 at 6:42 PM
Edited May 9, 2008 at 6:49 PM
We had a need here to create a SQL job that ran against the PolyMon database and the new DiskMonitor (that I just uploaded) which will email us where we stand on disk usage on our SAN LUNs. Below is that query. We use a custom CLR assembly to email stuff from SQL Express (since SQL Express doesn't have Database Mail). I took that out and replaced it with a print statement, which prints the varchar that I'm building to the screen. This varchar can be sent in an email in Database Mail or SQLMail or whatever. Change the "m.Name like '%'" part to whatever pattern you want to use to match up your DiskMonitor monitor entries. You can also filter by monitor type by using MonitorTypeID to make sure you are including ONLY DiskMonitors, but that number unique to every installation and we didn't need that because all our MonitorIDs begin wtih a string that identifies what they are for. Also included is the code for fn_padding, which you will need for this query to work. :-)

USE [PolyMon]
GO
/****** Object: UserDefinedFunction [dbo].[fn_Padding] Script Date: 05/09/2008 13:44:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
fn_Padding is a function that helps you to normalize numbers or strings. For example, you keep a sequence number
in your table in numeric format, but when you display the value, you want the number to be always 4 digits. The
function will return the parameters formated. For example:

Parameters:
@Text : Text or Numeric value to be formatted
@Char : Character or Number to be use to format @Text
@HowMany : How long the formatted string should be
@LeftRight : Which side the padding should be placed (L=Left(default), R=Right)

Example: Result:
SELECT dbo.fn_Padding('1', '0', 3, 'L') 001
SELECT dbo.fn_Padding('15', '0', 3, 'L') 015
SELECT dbo.fn_Padding('ABC', '*', 10, 'R') ABC*******
*/
CREATE FUNCTION [dbo].[fn_Padding] (@Text AS VARCHAR(500), @Char AS CHAR(1), @HowMany AS INT, @LeftRight AS CHAR(1) = 'L')
RETURNS VARCHAR(500) AS
BEGIN

DECLARE @Count AS INT

--If the @Text was not provided, assume the padding char as @Text
IF @Text IS NULL
SET @Text = @Char

--Store the lenght of the text to pad
SET @Count = LEN(@Text)

--For the LEFT padding
IF @LeftRight = 'L'
BEGIN
--Loop as long as the number of padding character is lower that the
--counter
WHILE @Count < @HowMany
BEGIN
SET @Text = @Char + @Text --Add the padding
SET @Count = @Count + 1 --Increment the counter
END
END

--For the RIGHT padding
ELSE
BEGIN
--Loop as long as the number of padding character is lower that the
--counter
WHILE @Count < @HowMany
BEGIN
SET @Text = @Text + @Char --Add the padding
SET @Count = @Count + 1 --Increment the counter
END
END

--Return the formatted text
RETURN (@Text)
END






-- BEGIN QUERY

use PolyMon

declare @res varchar(4000)

SELECT @res = dbo.fn_padding('Drive',' ',50,'R') + char(9)
+ dbo.fn_padding('Free Space',' ',12,'R') + char(9)
+ dbo.fn_padding('Used Space',' ',12,'R') + char(9)
+ dbo.fn_padding('Total Space',' ',12,'R') + char(13)+ char(10)
+ dbo.fn_padding('','-',50,'R') + char(9)
+ dbo.fn_padding('','-',12,'R') + char(9)
+ dbo.fn_padding('','-',12,'R') + char(9)
+ dbo.fn_padding('','-',12,'R') + char(13)+ char(10)

select @res = @res +
dbo.fn_padding(m.Name,' ',50,'R') + char(9) +
dbo.fn_padding(cast(convert(Decimal(6,1),round(mecFree.CounterValue / 1024,1)) as varchar(12)) + ' GB',' ',12,'R') + char(9) +
dbo.fn_padding(cast(convert(Decimal(6,1),round(mecUsed.CounterValue / 1024,1)) as varchar(12)) + ' GB',' ',12,'R') + char(9) +
dbo.fn_padding(cast(convert(Decimal(6,1),round(mecTotal.CounterValue / 1024,1)) as varchar(12)) + ' GB',' ',12,'R') + char(13)+ char(10)
from
Monitor m
inner join MonitorEventCounter mecFree on m.MonitorID = mecFree.MonitorID
inner join MonitorEventCounter mecUsed on m.MonitorID = mecUsed.MonitorID
inner join MonitorEventCounter mecTotal on m.MonitorID = mecTotal.MonitorID
where
m.Name like '%' and
mecFree.CounterName = 'FreeMB' and
mecUsed.CounterName = 'UsedMB' and
mecTotal.CounterName = 'TotalMB' and
mecFree.eventDT = (select max(mecTopDate1.eventDT) from MonitorEventCounter mecTopDate1 where mecTopDate1.MonitorID = mecFree.MonitorID and mecFree.CounterName = 'FreeMB') and
mecUsed.eventDT = (select max(mecTopDate2.eventDT) from MonitorEventCounter mecTopDate2 where mecTopDate2.MonitorID = mecUsed.MonitorID and mecUsed.CounterName = 'UsedMB') and
mecTotal.eventDT = (select max(mecTopDate3.eventDT) from MonitorEventCounter mecTopDate3 where mecTopDate3.MonitorID = mecTotal.MonitorID and mecTotal.CounterName = 'TotalMB')
order by m.Name

print @res