Quick Post- Storing Disk Space Info on a Table with PowerShell and SQL Agent Jobs.


Guys, quick and simple-post. My good friend Edvaldo (@edvaldocastro02), or Socorro´s Husband , he needed a quick and simple way to gather and store the disk information from his servers on a table in a central repository. For security reasons of his company, it can not do this from his machine. Then we put the script on each server, running with a SQL Server Agent and saving on his machine.

The Script is simple. First we need to download the Aaron Nelson´s (@sqlvariant) Get-DisksSpace , Chad Miller´s (@cmille19) Out-DataTable and Write-DataTable.

Then it is just add this function in your PowerShell module, in my case functions.psm1, and import it in the profile.

Lets create the table in the PoshTest Database :

   1: USE [PoshTest]

   2: GO

   3:  

   4: /****** Object:  Table [dbo].[tbl_DiskSpace]    Script Date: 25/05/2012 22:03:53 ******/

   5: SET ANSI_NULLS ON

   6: GO

   7:  

   8: SET QUOTED_IDENTIFIER ON

   9: GO

  10:  

  11: SET ANSI_PADDING ON

  12: GO

  13:  

  14: CREATE TABLE [dbo].[tbl_DiskSpace](

  15:     [Date] [smalldatetime] NULL,

  16:     [SystemName] [varchar](50) NULL,

  17:     [Name] [varchar](50) NULL,

  18:     [SizeInGB] [varchar](50) NULL,

  19:     [FreeInGB] [varchar](50) NULL,

  20:     [PercentFree] [varchar](50) NULL,

  21:     [Label] [varchar](50) NULL

  22: ) ON [PRIMARY]

  23:  

  24: GO

  25:  

  26: SET ANSI_PADDING OFF

  27: GO

Now it is time to write the script :

   1: param ($ServerName = “.”)

   2: $variable = ( Get-DisksSpace $ServerName | select @{Expression= {(Get-Date -Format "yyyy-MM-dd hh:mm:ss") };Label = "Date"},SystemName,Name,SizeInGB,FreeInGB,PercentFree,Label)

   3: $valuedatatable = Out-DataTable -InputObject $variable 

   4: Write-DataTable -ServerInstance YourComputer -Database PoshTest -TableName tbl_DiskSpace -Data $valuedatatable

Save in some folder, in my case c:\posh\diskspace.ps1

Now It is just create the SQL Server Agent job. If you are using SQL Server 2012 you can do it with PowerShell Job type, otherwise should be  CMDExec Type. For us is CMD.

   1: PowerShell.exe  "c:\posh\diskspace.ps1 ."

image

and schedule it Sorriso

image

Simple, Clean and Fast. Classic PowerShell

#PowerShellLifeStyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, Virtual Pass BR. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s