Script to poll all servers in AD for disk space

• Adam Bean

  One Orange Chip

  Points: 26631

  More actions

  June 23, 2009 at 6:52 am

  #211192

  Hey guys,

  Ideally I'd like to hit all servers in AD (with exceptions, ie. desktops) to retrieve drive letter, size and free then write the records to a SQL table. I've come across a few samples online that are based on reading from an input file of a server list and then exporting to a .csv file.

  This is what I'm working off of so far ...

  gwmi -query "SELECT SystemName,Caption,VolumeName,Size,Freespace FROM win32_logicaldisk WHERE DriveType=3" -computer (gc c:\servers.txt) | Select-Object SystemName,Caption,VolumeName,@{Name="Size(GB)"; Expression={"{0:N2}" -f ($_.Size/1GB)}},@{Name="Freespace(GB)"; Expression={"{0:N2}" -f ($_.Freespace/1GB)}} | export-csv c:\Disk-GB.csv

  If anyone can assist or has anything written, I'd greatly appreciate it ...

  Thanks

• Mike - CI

  SSCarpal Tunnel

  Points: 4146

  More actions

  January 7, 2010 at 2:21 pm

  #1100509

  This is similar to what you are looking for and also uses an input text file with the list of servers you want to get data from but instead of exporting to CSV this is loading into a database for keeping the data historically/trending.

  I got a lot of the ideas from this from the powershell extensions stuff on codeplex

  http://www.codeplex.com/SQLPSX%5B/url%5D

  For mine to work, I have a table with the following structure

  CREATE TABLE [dbo].[ServerDiskUsage]

  (

  [CreateDate] [datetime] NOT NULL,

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

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

  [VolumeName] [varchar](50) NULL,

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

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

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

  [id] [int] IDENTITY(1,1) NOT NULL primary key clustered

  )

  Then as noted from the powershell extensions I created a powershell script for the Datatable with the following code (I called the file Datatable.ps1)

  Function out-DataTable {

  $dt = new-object Data.datatable

  $First = $true

  foreach ($item in $input){

  $DR = $DT.NewRow()

  $Item.PsObject.get_properties() | foreach {

  if ($first) {

  $Col = new-object Data.DataColumn

  $Col.ColumnName = $_.Name.ToString()

  $DT.Columns.Add($Col) }

  if ($_.value -eq $null) {

  $DR.Item($_.Name) = "[empty]"

  }

  elseif ($_.IsArray) {

  $DR.Item($_.Name) =[string]::Join($_.value ,";")

  }

  else {

  $DR.Item($_.Name) = $_.value

  }

  }

  $DT.Rows.Add($DR)

  $First = $false

  }

  return @(,($dt))

  }

  And then lastly you just need a powershell script that will load the results into your table (assuming your input file name is servers.txt)

  $SearchStrings = @((Get-Content "servers.txt"))

  ForEach($string in $SearchStrings)

  {

  $dataTable = ./Get-DiskUsage.ps1 $string | Out-DataTable;

  $connectionString = "Data Source=YourServerHere;Integrated Security=true;Initial Catalog=YourDatabaseHere;";

  $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString;

  $bulkCopy.DestinationTableName = "ServerDiskUsage";

  $bulkCopy.WriteToServer($dataTable);

  }

  So to run you just have to load the Datatable.ps1 file and then run this above.

  . ./datatable.ps1

  . ./getdiskspace.ps1

  I then have this scheduled to run regularly on on of our servers and we have various reports that run off of this data to monitor diskspace getting low, how fast it grows etc.. You could just as easily export to .csv.

  Hopefully this is somewhat useful.