Sunday, 21 June 2009

How to write your own capacity management tool

Nothing is better that a free tool, except maybe one you've made yourself.
So here is one I made to collect all the drive space and free space on them into a SQL database.

PART ONE
First of all we need to collect the data from each PC, how you could use a manual list of all the servers but when I was writing this list I was feeling lazy to I used the NET VIEW command to create the list for me, then using WMIC commands to query all the PC for space, just take the code below and save it to a bat or cmd file and you can create the report just by double clicking.

for /f "delims=\ " %%i in ('net view ^| findstr "\\"') do @echo %%i >> servers.txt
del c:\reports\SRVSPACE.CSV
FOR /F %%A IN (servers.txt) DO (
WMIC /Node:%%A LogicalDisk Where DriveType="3" Get DeviceID,FileSystem,FreeSpace,Size /Format:csv | MORE /E +2 >> c:\reports\SRVSPACE.CSV
)

del servers.txt


you could collect more details or change what is collected this was just an example, remember that anything that you can collect in CSV format is easy to import into SQL where reporting services can provide charts and reports that can be scheduled to be sent to your inbox if you so wish.

PART TWO
in the first part we showed the example of creating the csv file that we are importing into SQL so now we need a database and a table to store it.

The following script creates the database the table and the SQL job that will run the import, by creating more table and columns you can add more reports and create a powerful tool to monitor your network not just your hard drives.

--Create DATABASE Capacity_DB
USE MASTER
CREATE DATABASE Capacity_DB
go

--create table Capacity
USE Capacity_DB
CREATE TABLE Capacity

--create columns
(Node VARCHAR(40),
Drive VARCHAR(40),
Format VARCHAR(40),
Freespace VARCHAR(40),
TotalSpace VARCHAR(40),
Collection_Date VARCHAR(40))

GO

use Capacity_DB
go
--create procedure that will be late used by SQL job
CREATE PROCEDURE SP_CapacityImport
AS
-- create temp table
CREATE TABLE #cmimport
(Node VARCHAR(40),
Drive VARCHAR(40),
Format VARCHAR(40),
Freespace VARCHAR(40),
TotalSpace VARCHAR(40))

--import from CSV file
BULK
INSERT #cmimport
FROM 'c:\reports\SRVSPACE.CSV'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

--copy data into capacity table
INSERT INTO Capacity (Node, Drive, Format,Freespace, TotalSpace)
SELECT * FROM #cmimport

--clean up
IF OBJECT_ID('tempdb..#cmimport', 'U') IS NOT NULL
DROP TABLE #cmimport

--update missing dates on new imports
update Capacity
set Collection_Date = (current_timestamp)
where Collection_Date is null
GO

-- Create SQL job to trigger procedure
USE [msdb]
GO

/****** Object: Job [capacity_import] ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 06/21/2009 17:20:29 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND

category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'capacity_import',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'SA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [step1] Script Date: 06/21/2009 17:20:29 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'use capacity_db
go
exec sp_CapacityImport
go',
@database_name=N'Capacity_DB',
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20090621,
@active_end_date=99991231,
@active_start_time=160000,
@active_end_time=235959,
@schedule_uid=N'8aa58ff4-a990-4cb0-8e8c-1780a4b258b7'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


Now you have the database and the table plus the import job set to run once a day, if you place the script from part one on a schedule to run once a day as well then you'll have a working import.

The final part is to create a report from this data, you can ether create a job that will create a summery version and email it too you or create a report using SQL reporting services.

If you aren't already experienced with SSRS (SQL Server Reporting Service) then
Steve Joubert's posting should help you out.

Exactly the same can be use to MySQL or Oracle personally I would recommend using MySQL for this as gives you the greatest number of supported platforms however this example I was using Microsoft SQL 2008




Reblog this post [with Zemanta]

No comments: