Disk Space Monitor

Monitor drive capacities in a sql environment -

How to implement ...

1. create a database for the project
2. run the sql script to create the 3 tables in the new database
3. populate tblSQLPhysicalServers manually with servernames to monitor
4. edit .vbs script file to change the database connection parameters
5. schedule the .vbs file to run using scheduled tasks in control panel (frequency as desired)

Table Schema Script -

-- table creation script

CREATE TABLE [dbo].[tblSQLPhysicalServers] (
 [servername] [nvarchar](50)
)
GO

CREATE TABLE [dbo].[tblDiskSpaceLog](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [notes] [varchar](1000) NULL,
 [date] [datetime] NULL DEFAULT (getdate())
) 
GO

CREATE TABLE [dbo].[tblDiskSpace] (
 [Computer] [varchar](128),
 [Drive] [varchar](2),
 [DiskSize] [decimal](28, 5) NULL,
 [FreeSpace] [decimal](28, 5) NULL,
 [Percentage] [decimal](10, 5) NULL,
 [Date] [datetime] NULL
)
GO

VBS Script -

'*********************************************** 
'*                                             * 
'*  Drive Monitor Script - save as a .vbs file * 
'*                                             * 
'*********************************************** 

On Error Resume Next 

Const intBytesPerMegabyte = 1048576 
   
Dim AdCn 
Dim AdRec 
Dim strMonitorSQL, strSQL, strSQL2, strdate 
Dim strServerName, strDataBase , strUsername, strPassword, strConnection 

strServerName = "RIO\SQL2005" 
strDataBase = "Monitor"  
strUsername = "rd"  
strPassword = "pass" 
strConnection = "Provider=SQLOLEDB.1;Data Source=" + strServerName + ";Initial Catalog=" + strDataBase + ";user id = '" + strUsername + "';password='" + strPassword + "' " 

Function fnPadLeadingZero(intInputNumber, intTotalDigits)  
    If intTotalDigits > Len(intInputNumber) Then  
        fnPadLeadingZero = String(intTotalDigits -Len(intInputNumber),"0") & intInputNumber  
    Else  
        fnPadLeadingZero = intInputNumber  
    End If  
End Function  

Set AdCn = CreateObject("ADODB.Connection") 
Set AdRec1 = CreateObject("ADODB.Recordset") 
Set AdRec2 = CreateObject("ADODB.Recordset") 
AdCn.Open = strConnection 
strSQL = "Select ServerName from tblSQLPhysicalServers" 
AdRec1.Open strSQL, AdCn,1,1 
  
strMonitorSQL="insert into tblDiskSpaceLog(Notes) values ('Disk Monitoring - Started')" 
AdRec2.Open strMonitorSQL, AdCn,1,1 

'loop servers  
While Not Adrec1.EOF  
Computer = Adrec1("ServerName") 
  
'connect to wmi for server 
Set objWMIService = GetObject("winmgmts://" & Computer) 
'wscript.echo err.number 

'check for wmi connection error 
If err.number <> 0 Then 

  'wmi connection error 
  AdRec2.Open "insert into tblDiskSpaceLog(Notes) values ('Disk Monitoring - wmi error')", AdCn,1,1 
  strMonitorSQL="insert into tblDiskSpaceLog(Notes) values ('" + Computer + ":  Error-- " + Err.description + "')" 
  AdRec2.Open strMonitorSQL, AdCn,1,1 

Else 

  'wmi connected ok 

  Set colLogicalDisk = objWMIService.InstancesOf("Win32_LogicalDisk")   
  If err.number <> 0 Then 
    'cant connect to w32_logicaldisk 
    strMonitorSQL="insert into tblDiskSpaceLog(Notes) values ('" + Computer + ":   Error-- " + Err.description+ "')" 
    AdRec2.Open strMonitorSQL, AdCn,1,1 
  Else 
    'w32_logicaldisk connected... 
     
   For Each objLogicalDisk In colLogicalDisk   
   If objLogicalDisk.drivetype=3 Then 

   strdate = YEAR(Date()) & "-" & fnPadLeadingZero(Month(Date()),2) & "-" & fnPadLeadingZero(DAY(Date()),2) & " " & time() 


   If objLogicalDisk.FreeSpace = "" Then 
    
        'freespace property empty 

   strSQL2 = "Insert into tblDiskSpace (Computer,Drive,DiskSize,date) values('"+Computer+"','" + objLogicalDisk.DeviceID + "'," + CInt(objLogicalDisk.size/intBytesPerMegabyte) +  + ",'"  + strdate + "')" 
        AdRec2.Open strSQL2, AdCn,1,1 

   Else 

   'freespace property given. 

   strSQL2 = "Insert into tblDiskSpace (Computer,Drive,DiskSize,FreeSpace,Percentage,date) values('"_ 
   &Computer&"','" & objLogicalDisk.DeviceID &"',"& objLogicalDisk.size/intBytesPerMegabyte &_ 
    "," & objLogicalDisk.freespace/intBytesPerMegabyte &_ 
    "," &((objLogicalDisk.freespace/intBytesPerMegabyte)/(objLogicalDisk.size/intBytesPerMegabyte))*100_ 
   &",'" & strdate &"')" 

         AdRec2.Open strSQL2, AdCn,1,1 
    
   If err.number <> 0 Then 
   wscript.echo err.description 
   End If 

   End If 

    'debug to see sql used for insert 
    'strMonitorSQL="insert into tblDiskSpaceLog(Notes) values ('" + replace(sql,"'","_")+ "')" 
    'AdRec2.Open strMonitorSQL, AdCn,1,1 

   End If 

   Next   
  End If 

End If 
  
err.Clear 
Adrec1.movenext 
  
Wend 
  
AdRec2.Open "insert into tblDiskSpaceLog(Notes) values ('Disk Monitoring - Completed')", AdCn,1,1 

Periodically review the recorded data e.g. within daily checks.
Use sql to query the tblDiskSpace table, like this -

SELECT     Computer, Drive, DiskSize, FreeSpace, Percentage, Date
FROM         tblDiskSpace
WHERE Dateadd(dd, -1,getdate()) < date
AND Percentage < 25
ORDER BY Date DESC

(this query become a scheduled job itself)