OS Disk Capacity Information

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 6, 2014 at 11:10 am

  #1686701

  Robert klimes (2/6/2014)

  ---

  try this out

  $ds = New-Object system.Data.DataSet

  $ds = Get-WMIObject Win32_LogicalDisk -filter "DriveType=3" -computer (Get-Content c:\computers.txt) | Select SystemName,DeviceID,VolumeName,@{Name="size"; Expression={"{0:N1}" -f($_.size/1gb)}},@{Name="freespace";Expression={"{0:N1}" -f($_.freespace/1gb)}}

  foreach ($d in $ds)

  {

  $systemname = $d.systemname

  $deviceID = $d.DeviceID

  $volumename = $d.volumename

  $size = $d.size

  $freespace = $d.freespace

  $SQLServer = ".\SQLEXPRESS"

  $SQLDBName = "AdventureWorks2008R2"

  $SqlQuery = "insert into diskusage values('$systemname','$deviceID','$volumename','$size','$freespace')"

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

  $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

  $SqlCmd.CommandText = $SqlQuery

  $SqlCmd.Connection = $SqlConnection

  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

  $SqlAdapter.SelectCommand = $SqlCmd

  $DataSet = New-Object System.Data.DataSet

  $SqlAdapter.Fill($DataSet)

  $SqlConnection.Close()

  }

  Very good stuff Bob. This was at least useful to me.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  February 7, 2014 at 3:31 am

  #1686954

  a simple WMI query via WMIC can get this info and can be run via the agent

  wmic volume get blocksize, capacity, "free space", name

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  February 7, 2014 at 3:51 am

  #1686960

  Lowell (2/5/2014)

  ---

  would you consider adding a CLR procedure that gets the data instead? that seems like a much better way to go.

  http://www.mssqltips.com/sqlservertip/1986/sqlclr-function-to-return-free-space-for-all-drives-on-a-server/

  That only gets the drive level info and will not be helpful when using volume mount points

  Importing

  using System;

  using System.Management;

  and using something along the lines of this should be better

  foreach( ManagementObject volume in

  new ManagementObjectSearcher("Select * from Win32_Volume" ).Get())

  {

  if( volume["FreeSpace"] != null )

  {

  Console.WriteLine("{0} = {1} out of {2}",

  volume["Name"],

  ulong.Parse(volume["FreeSpace"].ToString()).ToString("#,##0"),

  ulong.Parse(volume["Capacity"].ToString()).ToString("#,##0"));

  }

  }

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉

• wak_no1

  SSCrazy

  Points: 2400

  More actions

  February 7, 2014 at 3:52 am

  #1686961

  Perfect, thanks.

• Jeff Moden

  SSC Guru

  Points: 1004280

  More actions

  February 7, 2014 at 7:53 am

  #1687066

  Robert klimes (2/6/2014)

  ---

  try this out

  $ds = New-Object system.Data.DataSet

  $ds = Get-WMIObject Win32_LogicalDisk -filter "DriveType=3" -computer (Get-Content c:\computers.txt) | Select SystemName,DeviceID,VolumeName,@{Name="size"; Expression={"{0:N1}" -f($_.size/1gb)}},@{Name="freespace";Expression={"{0:N1}" -f($_.freespace/1gb)}}

  foreach ($d in $ds)

  {

  $systemname = $d.systemname

  $deviceID = $d.DeviceID

  $volumename = $d.volumename

  $size = $d.size

  $freespace = $d.freespace

  $SQLServer = ".\SQLEXPRESS"

  $SQLDBName = "AdventureWorks2008R2"

  $SqlQuery = "insert into diskusage values('$systemname','$deviceID','$volumename','$size','$freespace')"

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

  $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

  $SqlCmd.CommandText = $SqlQuery

  $SqlCmd.Connection = $SqlConnection

  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

  $SqlAdapter.SelectCommand = $SqlCmd

  $DataSet = New-Object System.Data.DataSet

  $SqlAdapter.Fill($DataSet)

  $SqlConnection.Close()

  }

  VERY cool. Since I'm not a PowerShell heavyweight by any means, I've been looking for one of these that inserts data into a table directly instead of going through a gyration with CSVs or screen parses.

  What would be even more cool is if it got the list of computers from a table, as well. Any chance of you showing us how to modify the code above to do that?

  As a bit of a side bar and as strange as it might seem, as poor as my code currently is, I don't limit the disk type and I don't just for one good reason. The folks in NetOps will frequently use a CD or DVD on a box or attach a thumb drive, get interrupted, and forget where they left it. I don't save all the non-type 3 information every night but I do keep a full copy of the latest in a staging table which lists everything and makes it quite easy to locate such forgotten CDs, DVDs, and thumb drives.

  I'm actually very excited about your code. Someone finally understands that DBAs need to store this stuff in tables instead of just returning it to the screen and without having to use some rather long code with multiple functions. I really look forward to a possible mod to read the computers from a table. Thanks for any and all help there, Robert.

  Are there any special "includes" or "attaches" that need to be done to make this script work?

  I've got to go so I can bookmark this post in about 72 places so I don't ever lose it. 😛 This is freakin' awesome and I can't wait to try it out.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004280

  More actions

  February 7, 2014 at 8:02 am

  #1687073

  wak_no1 (2/6/2014)

  ---

  Bob, thank you, this is working as I'd hoped. I can sort of see what I needed to do.

  One thing has just occurred to me though, I need a date stamp to record when the data was recorded; is there another WMI object that I could query at the same time to get this info?

  My suggestion would be to just add a "DateCreated" column to the target table and have it default to GETDATE(). It's one less thing that you have to worry about after that.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004280

  More actions

  February 7, 2014 at 8:05 am

  #1687076

  wak_no1 (2/6/2014)

  ---

  Thank you.

  Code to retrieve disk information needed via Powershell

  Get-WMIObject Win32_LogicalDisk -filter "DriveType=3" -computer (Get-Content c:\computers.txt) | Select SystemName,DeviceID,VolumeName,@{Name="size(GB)"; Expression={"{0:N1}" -f($_.size/1gb)}},@{Name="freespace(GB)";Expression={"{0:N1}" -f($_.freespace/1gb)}}

  Of the back of this, I've created a table on a test db within sql server, where I want the information from the above command to get written to:

  CREATE TABLE [dbo].[DiskUsage](

  [SystemName] [varchar](50) NOT NULL,

  [DeviceID] [varchar](50) NOT NULL,

  [VolumeName] [varchar](50) NULL,

  [SizeGB] [numeric](18, 2) NOT NULL,

  [FreeGB] [numeric](18, 2) NOT NULL )

  Here's my connection string from Powershell (at the minitue, it's just contains a simple SELECT, which i want to replace with an INSERT of the data retrieved from within Powershell):

  #set the security - set-executionpolicy unrestricted

  $SQLServer = ".\SQLEXPRESS"

  $SQLDBName = "AdventureWorks2008R2"

  $SqlQuery = "select * from Person.Address where AddressID = 1"

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

  $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

  $SqlCmd.CommandText = $SqlQuery

  $SqlCmd.Connection = $SqlConnection

  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

  $SqlAdapter.SelectCommand = $SqlCmd

  $DataSet = New-Object System.Data.DataSet

  $SqlAdapter.Fill($DataSet)

  $SqlConnection.Close()

  clear

  $DataSet.Tables[0]

  Very cool, as well. Thanks for taking the time to post it. It started a side discussion that I've REALLY been looking forward to.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 7, 2014 at 8:08 am

  #1687078

  Jeff Moden (2/7/2014)

  ---

  wak_no1 (2/6/2014)

  ---

  Bob, thank you, this is working as I'd hoped. I can sort of see what I needed to do.

  One thing has just occurred to me though, I need a date stamp to record when the data was recorded; is there another WMI object that I could query at the same time to get this info?

  My suggestion would be to just add a "DateCreated" column to the target table and have it default to GETDATE(). It's one less thing that you have to worry about after that.

  That is pretty easy to do. Adding the date to the insert clause is pretty innocuous too. I learned that from this thread yesterday. It works pretty slick.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• Robert Klimes

  SSCoach

  Points: 17865

  More actions

  February 7, 2014 at 9:20 am

  #1687111

  Jeff Moden (2/7/2014)

  ---

  Robert klimes (2/6/2014)

  ---

  try this out

  $ds = New-Object system.Data.DataSet

  $ds = Get-WMIObject Win32_LogicalDisk -filter "DriveType=3" -computer (Get-Content c:\computers.txt) | Select SystemName,DeviceID,VolumeName,@{Name="size"; Expression={"{0:N1}" -f($_.size/1gb)}},@{Name="freespace";Expression={"{0:N1}" -f($_.freespace/1gb)}}

  foreach ($d in $ds)

  {

  $systemname = $d.systemname

  $deviceID = $d.DeviceID

  $volumename = $d.volumename

  $size = $d.size

  $freespace = $d.freespace

  $SQLServer = ".\SQLEXPRESS"

  $SQLDBName = "AdventureWorks2008R2"

  $SqlQuery = "insert into diskusage values('$systemname','$deviceID','$volumename','$size','$freespace')"

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

  $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

  $SqlCmd.CommandText = $SqlQuery

  $SqlCmd.Connection = $SqlConnection

  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

  $SqlAdapter.SelectCommand = $SqlCmd

  $DataSet = New-Object System.Data.DataSet

  $SqlAdapter.Fill($DataSet)

  $SqlConnection.Close()

  }

  VERY cool. Since I'm not a PowerShell heavyweight by any means, I've been looking for one of these that inserts data into a table directly instead of going through a gyration with CSVs or screen parses.

  What would be even more cool is if it got the list of computers from a table, as well. Any chance of you showing us how to modify the code above to do that?

  As a bit of a side bar and as strange as it might seem, as poor as my code currently is, I don't limit the disk type and I don't just for one good reason. The folks in NetOps will frequently use a CD or DVD on a box or attach a thumb drive, get interrupted, and forget where they left it. I don't save all the non-type 3 information every night but I do keep a full copy of the latest in a staging table which lists everything and makes it quite easy to locate such forgotten CDs, DVDs, and thumb drives.

  I'm actually very excited about your code. Someone finally understands that DBAs need to store this stuff in tables instead of just returning it to the screen and without having to use some rather long code with multiple functions. I really look forward to a possible mod to read the computers from a table. Thanks for any and all help there, Robert.

  Are there any special "includes" or "attaches" that need to be done to make this script work?

  I've got to go so I can bookmark this post in about 72 places so I don't ever lose it. 😛 This is freakin' awesome and I can't wait to try it out.

  I'm no heavyweight either, i just know enough to get in trouble;-)

  there are no includes or attaches because Get-WMIObject is a native PS cmdlet and System.Data.SqlClient and System.Data.DataSet are .NET assemblies.

  for your other question of read from a table, I normally use a CMS server to get the list of servers and I also use the invoke-sqlcmd cmdlet which comes with sql server to use the sql cmdlets. You could modify the CMS query to query any table

  you need to load them into your session(different way for 2008 and 2012)

  #sql 2008

  Add-PSSnapin sqlservercmdletsnapin100

  Add-PSSnapin sqlserverprovidersnapin100

  #sql 2012

  Import-Module sqlps

  $CMSServer = "<cms server>"

  $CMSquery = "SELECT svr.name

  FROM msdb.dbo.sysmanagement_shared_registered_servers_internal svr

  INNER JOIN msdb.dbo.sysmanagement_shared_server_groups_internal grp

  ON svr.server_group_id = grp.server_group_id"

  $targets = invoke-sqlcmd -query $CMSquery -ServerInstance $CMSServer

  foreach ($target in $targets)

  {

  $servername = $target[0]

  $ds = New-Object system.Data.DataSet

  $ds = Get-WMIObject Win32_LogicalDisk -filter "DriveType=3" -computername $servername| Select SystemName,DeviceID,VolumeName,@{Name="size"; Expression={"{0:N1}" -f($_.size/1gb)}},@{Name="freespace";Expression={"{0:N1}" -f($_.freespace/1gb)}}

  foreach ($d in $ds)

  {

  $systemname = $d.systemname

  $deviceID = $d.DeviceID

  $volumename = $d.volumename

  $size = $d.size

  $freespace = $d.freespace

  $SQLServer = ".\express"

  $SQLDBName = "test"

  $SqlQuery = "insert into diskusage values('$systemname','$deviceID','$volumename','$size','$freespace')"

  Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDBName -Query $SqlQuery

  }

  }

  Bob
  -----------------------------------------------------------------------------
  How to post to get the best help[/url]

• Jeff Moden

  SSC Guru

  Points: 1004280

  More actions

  February 7, 2014 at 9:31 am

  #1687115

  My problem is that I'm still stuck with 2005 at work (apologies for forgetting to mention that on a 2K12 forum). CMS isn't possible for me at this time.

  I'll try to combine the two scripts that you and wak_no1 posted. If I can get it up and running, I'll try to remember to post it back here.

  Thanks again for your help.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Robert Klimes

  SSCoach

  Points: 17865

  More actions

  February 7, 2014 at 9:37 am

  #1687117

  Jeff Moden (2/7/2014)

  ---

  My problem is that I'm still stuck with 2005 at work (apologies for forgetting to mention that on a 2K12 forum). CMS isn't possible for me at this time.

  I'll try to combine the two scripts that you and wak_no1 posted. If I can get it up and running, I'll try to remember to post it back here.

  Thanks again for your help.

  You should be able to install the 2008 snapins only, along with pre-reqs

  http://vsteamsystemcentral.com/cs21/blogs/timbenninghoff/archive/2008/12/21/Installing-SQL-Server-2008-PowerShell-snap_2D00_ins-for-SQL-Server-2005.aspx

  Bob
  -----------------------------------------------------------------------------
  How to post to get the best help[/url]

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 7, 2014 at 11:21 am

  #1687165

  Jeff Moden (2/7/2014)

  ---

  My problem is that I'm still stuck with 2005 at work (apologies for forgetting to mention that on a 2K12 forum). CMS isn't possible for me at this time.

  I'll try to combine the two scripts that you and wak_no1 posted. If I can get it up and running, I'll try to remember to post it back here.

  Thanks again for your help.

  If you can install the 2008 sql posh snapins, then using the invoke sql-cmd seems to be the easiest way to retrieve the data from the table and then to use it in the posh script later (by assigning the result to a dataset etc).

  That's my observation from dabbling with my first posh script yesterday.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• Robert Klimes

  SSCoach

  Points: 17865

  More actions

  February 7, 2014 at 11:48 am

  #1687181

  Jeff Moden (2/7/2014)

  ---

  My problem is that I'm still stuck with 2005 at work (apologies for forgetting to mention that on a 2K12 forum). CMS isn't possible for me at this time.

  I'll try to combine the two scripts that you and wak_no1 posted. If I can get it up and running, I'll try to remember to post it back here.

  Thanks again for your help.

  Hey Jeff, here I am doing the same thing as my previous script just using the .NET classes.

  $CMSServer = "<servername>"

  $CMSDatabase = "msdb"

  $CMSquery = "SELECT svr.name FROM msdb.dbo.sysmanagement_shared_registered_servers_internal svr

  INNER JOIN msdb.dbo.sysmanagement_shared_server_groups_internal grp

  ON svr.server_group_id = grp.server_group_id"

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

  $SqlConnection.ConnectionString = "Server = $CMSServer; Database = $CMSDatabase; Integrated Security = True"

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

  $SqlCmd.CommandText = $CMSquery

  $SqlCmd.Connection = $SqlConnection

  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

  $SqlAdapter.SelectCommand = $SqlCmd

  $DataSet = New-Object System.Data.DataSet

  $SqlAdapter.Fill($DataSet)

  $SqlConnection.Close()

  foreach ($target in $dataset.tables[0])

  {

  $servername = $target[0]

  $ds = New-Object system.Data.DataSet

  $ds = Get-WMIObject Win32_LogicalDisk -filter "DriveType=3" -computername $servername| Select SystemName,DeviceID,VolumeName,@{Name="size"; Expression={"{0:N1}" -f($_.size/1gb)}},@{Name="freespace";Expression={"{0:N1}" -f($_.freespace/1gb)}}

  foreach ($d in $ds)

  {

  $systemname = $d.systemname

  $deviceID = $d.DeviceID

  $volumename = $d.volumename

  $size = $d.size

  $freespace = $d.freespace

  $SQLServer = "<servername>"

  $SQLDBName = "test"

  $SqlQuery = "insert into diskusage values('$systemname','$deviceID','$volumename','$size','$freespace')"

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

  $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

  $SqlCmd.CommandText = $SqlQuery

  $SqlCmd.Connection = $SqlConnection

  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

  $SqlAdapter.SelectCommand = $SqlCmd

  $DataSet = New-Object System.Data.DataSet

  $SqlAdapter.Fill($DataSet)

  $SqlConnection.Close()

  }

  }

  the invoke-sqlcmd cmdlet replaces this but they essentially do the same thing

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

  $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

  $SqlCmd.CommandText = $SqlQuery

  $SqlCmd.Connection = $SqlConnection

  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

  $SqlAdapter.SelectCommand = $SqlCmd

  $DataSet = New-Object System.Data.DataSet

  $SqlAdapter.Fill($DataSet)

  $SqlConnection.Close()

  Bob
  -----------------------------------------------------------------------------
  How to post to get the best help[/url]

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 7, 2014 at 12:22 pm

  #1687199

  Robert klimes (2/7/2014)

  ---

  Jeff Moden (2/7/2014)

  ---

  My problem is that I'm still stuck with 2005 at work (apologies for forgetting to mention that on a 2K12 forum). CMS isn't possible for me at this time.

  I'll try to combine the two scripts that you and wak_no1 posted. If I can get it up and running, I'll try to remember to post it back here.

  Thanks again for your help.

  Hey Jeff, here I am doing the same thing as my previous script just using the .NET classes.

  $CMSServer = "<servername>"

  $CMSDatabase = "msdb"

  $CMSquery = "SELECT svr.name FROM msdb.dbo.sysmanagement_shared_registered_servers_internal svr

  INNER JOIN msdb.dbo.sysmanagement_shared_server_groups_internal grp

  ON svr.server_group_id = grp.server_group_id"

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

  $SqlConnection.ConnectionString = "Server = $CMSServer; Database = $CMSDatabase; Integrated Security = True"

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

  $SqlCmd.CommandText = $CMSquery

  $SqlCmd.Connection = $SqlConnection

  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

  $SqlAdapter.SelectCommand = $SqlCmd

  $DataSet = New-Object System.Data.DataSet

  $SqlAdapter.Fill($DataSet)

  $SqlConnection.Close()

  foreach ($target in $dataset.tables[0])

  {

  $servername = $target[0]

  $ds = New-Object system.Data.DataSet

  $ds = Get-WMIObject Win32_LogicalDisk -filter "DriveType=3" -computername $servername| Select SystemName,DeviceID,VolumeName,@{Name="size"; Expression={"{0:N1}" -f($_.size/1gb)}},@{Name="freespace";Expression={"{0:N1}" -f($_.freespace/1gb)}}

  foreach ($d in $ds)

  {

  $systemname = $d.systemname

  $deviceID = $d.DeviceID

  $volumename = $d.volumename

  $size = $d.size

  $freespace = $d.freespace

  $SQLServer = "<servername>"

  $SQLDBName = "test"

  $SqlQuery = "insert into diskusage values('$systemname','$deviceID','$volumename','$size','$freespace')"

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

  $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

  $SqlCmd.CommandText = $SqlQuery

  $SqlCmd.Connection = $SqlConnection

  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

  $SqlAdapter.SelectCommand = $SqlCmd

  $DataSet = New-Object System.Data.DataSet

  $SqlAdapter.Fill($DataSet)

  $SqlConnection.Close()

  }

  }

  the invoke-sqlcmd cmdlet replaces this but they essentially do the same thing

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

  $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

  $SqlCmd.CommandText = $SqlQuery

  $SqlCmd.Connection = $SqlConnection

  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

  $SqlAdapter.SelectCommand = $SqlCmd

  $DataSet = New-Object System.Data.DataSet

  $SqlAdapter.Fill($DataSet)

  $SqlConnection.Close()

  Wow Bob.

  Thanks

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 7, 2014 at 6:09 pm

  #1687291

  @jeff and others,

  You might be interested in this solution provided here.

  http://sqlblog.com/blogs/merrill_aldrich/archive/2011/01/04/posh-y-sql-disk-space-monitoring.aspx

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events