Link to home
Start Free TrialLog in
Avatar of Leo Torres
Leo TorresFlag for United States of America

asked on

Powershell drive percent free

Code below has no syntax errors. But as I analyze the data I notice that my division at the end is wrong. Right now I am merely getting a percentage change in the drive. That is wrong. what I really need is my disk space left on the drive after script runs.

So what I need is  :
This logic here
 ([int]($Text[0]."MB Free") / [int]($Text[3]."MB Free")

Should be replaced by:
[int]($Text[3]."MB Free / (Total space of the drive as denominator)

Not sure How to get the denominator here for the E or T drives but here is where I would need it to determine percent of free space left on drive.



$Text = SQL-ClearTempDB -Computers $Server -ServerType $ServerYear | ? { $_.Drive -in 'E', 'T','S' }
                     $OutputBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d}`t`t {4,8:n} `r`n" -f $Server, $Text[0].Drive.Replace("Space",""), $Text[0]."MB Free", $Text[3]."MB Free", ([int]($Text[0]."MB Free") / [int]($Text[3]."MB Free"))
                     $OutputBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d}`t`t {4,8:n} `r`n" -f $Server, $Text[1].Drive.Replace("Space",""), $Text[1]."MB Free", $Text[3]."MB Free", ([int]($Text[1]."MB Free") / [int]($Text[4]."MB Free"))
                     $OutputBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d}`t`t {4,8:n} `r`n" -f $Server, $Text[2].Drive.Replace("Space",""), $Text[2]."MB Free", $Text[3]."MB Free", ([int]($Text[2]."MB Free") / [int]($Text[5]."MB Free"))
                     $OutputBox.text += @"

Open in new window

Avatar of footech
footech
Flag of United States of America image

Does $Text include the drive size?  If not, I would modify SQL-ClearTempDB  (which I assume is a function) so that it returns the drive size.  Then you can use it in the following commands.
Avatar of Leo Torres

ASKER

Actually no its an internal procedure I dont have access to.

EXEC master..xp_fixeddrives

I did think about what you said and that may be the easiest way. So I dug a little bit and found this link

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0de21ef6-ea2a-44a1-ba95-0abdf8d84c52/getting-available-server-disk-space-total-size-free-space-using-tsql-on-sql-2000?forum=transactsql

I will us that code to make a powershell function and get back to you.
Well here it is but how do I return this and as what type? The return at the bottom needs to display how ever many drives exists on the server. Its not always going to be for drive so how would I return this back to the main function that calls this function so I can use the data there?

Function SQL-DriveCheck([String] $Server){

$Query = 

"Use [master]
go


SET NOCOUNT ON

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')
	DROP TABLE ##_DriveSpace

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')
	DROP TABLE ##_DriveInfo


DECLARE @Result INT
	, @objFSO INT
	, @Drv INT 
	, @cDrive VARCHAR(13) 
	, @Size VARCHAR(50) 
	, @Free VARCHAR(50)
	, @Label varchar(10)

CREATE TABLE ##_DriveSpace 
	(
	 DriveLetter CHAR(1) not null
	, FreeSpace VARCHAR(10) not null

	 )

CREATE TABLE ##_DriveInfo
	(
	DriveLetter CHAR(1)
	, TotalSpace bigint
	, FreeSpace bigint
	, Label varchar(10)
	)

INSERT INTO ##_DriveSpace 
	EXEC master.dbo.xp_fixeddrives


-- Iterate through drive letters.
DECLARE curDriveLetters CURSOR
	FOR SELECT driveletter FROM ##_DriveSpace

DECLARE @DriveLetter char(1)
	OPEN curDriveLetters

FETCH NEXT FROM curDriveLetters INTO @DriveLetter
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN

		 SET @cDrive = 'GetDrive(""' + @DriveLetter + '"")' 

			EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT 

				IF @Result = 0 

					EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT 

				IF @Result = 0 

					EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT 

				IF @Result = 0 

					EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT 

				IF @Result = 0 

					EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT 

				IF @Result <> 0 
 
					EXEC sp_OADestroy @Drv 
					EXEC sp_OADestroy @objFSO 

			SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )

			SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )

			INSERT INTO ##_DriveInfo
				VALUES (@DriveLetter, @Size, @Free, @Label)

	END
	FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END

CLOSE curDriveLetters
DEALLOCATE curDriveLetters

PRINT 'Drive information for server ' + @@SERVERNAME + '.'
PRINT ''


Alter Table ##_DriveInfo
add Escalate varchar(25)


Update di
set Escalate = 
                Case 
                    When ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) < 5 Then 'Escalate'
                    Else 'Under Threshold'
                END
from ##_DriveInfo di

-- Produce report.
SELECT @@SERVERNAME Servername
       , DriveLetter
	--, Label
	, FreeSpace AS [FreeSpace MB]
	, (TotalSpace - FreeSpace) AS [UsedSpace MB]
	, TotalSpace AS [TotalSpace MB]
	, ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]
    , Escalate

FROM ##_DriveInfo
ORDER BY [DriveLetter] ASC	
GO

DROP TABLE ##_DriveSpace
DROP TABLE ##_DriveInfo"

#Write-Host $Query


$resultset = Invoke-Sqlcmd -serverinstance $Server -database "master" -Query $Query -QueryTimeout 0 | Format-Table

Return @(,$resultset)
}

Open in new window

I guess you may want to know how I am using the function.

Here are the calls to the function

$BeforeScan += SQL-DriveCheck $Server 					
$Text = SQL-ClearTempDB -Computers $Server -ServerType $ServerYear | ? { $_.Drive -in 'E', 'T','S' }

$AfterScan += SQL-DriveCheck $Server 

$OutPutBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d}`t`t {4,8:n} {5,8:d}`t`t {6,8:d} {7,8:d}`t`t`t`t `r`n" -f $Server, $BeforeScan.Item(0).DriveLetter,$BeforeScan.Item(0)."UnsedSpace MB",$BeforeScan.Item(0)."Percentage Free",$AfterScan.Item(0).DriveLetter,$AfterScan.Item(0)."UnsedSpace MB",$AfterScan.Item(0)."Percentage Free", $AfterScan.Item(0).Escalate
$OutPutBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d}`t`t {4,8:n} {5,8:d}`t`t {6,8:d} {7,8:d}`t`t`t`t `r`n" -f $Server, $BeforeScan.Item(0).DriveLetter,$BeforeScan.Item(1)."UnsedSpace MB",$BeforeScan.Item(1)."Percentage Free",$AfterScan.Item(1).DriveLetter,$AfterScan.Item(1)."UnsedSpace MB",$AfterScan.Item(1)."Percentage Free", $AfterScan.Item(1).Escalate
$OutPutBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d}`t`t {4,8:n} {5,8:d}`t`t {6,8:d} {7,8:d}`t`t`t`t `r`n" -f $Server, $BeforeScan.Item(0).DriveLetter,$BeforeScan.Item(2)."UnsedSpace MB",$BeforeScan.Item(2)."Percentage Free",$AfterScan.Item(2).DriveLetter,$AfterScan.Item(2)."UnsedSpace MB",$AfterScan.Item(2)."Percentage Free", $AfterScan.Item(2).Escalate

$OutputBox.text += @"


"@

Open in new window

Here my second failed Attempt

$BeforeScan += SQL-DriveCheck $Server 					
$Text = SQL-ClearTempDB -Computers $Server -ServerType $ServerYear | ? { $_.Drive -in 'E', 'T','S' }

$AfterScan += SQL-DriveCheck $Server                    


$OutPutBox.text += Write-Output $BeforeScan | Select-Object  Servername, DriveLetter, "FreeSpace MB","Percentage Free"

$OutputBox.text += @"

"@

Open in new window

Thrid attempt

$BeforeScan += SQL-DriveCheck $Server 					
$Text = SQL-ClearTempDB -Computers $Server -ServerType $ServerYear | ? { $_.Drive -in 'E', 'T','S' }

$AfterScan += SQL-DriveCheck $Server                     

$OutPutBox.text += Write-Output $BeforeScan | Select-Object  Servername, DriveLetter, "FreeSpace MB","Percentage Free", Escalate #| Format-table
$OutPutBox.text += Write-Output "After"
$OutPutBox.text += Write-Output $AfterScan | Select-Object  Servername, DriveLetter, "FreeSpace MB","Percentage Free", Escalate #| Format-table

$OutputBox.text += @"

Open in new window


User generated image
Since I have practically zero experience with SQL queries I don't have any idea what the output of
SQL-DriveCheck $Server
is.  Maybe if you post that and the output of
SQL-ClearTempDB -Computers $Server -ServerType $ServerYear | ? { $_.Drive -in 'E', 'T','S' }
I can see how to put it together.

Often the way people query for drive info is to use WMI, like
gwmi win32_logicaldisk -filter "drivetype = 3" | Select DeviceID,FreeSpace,Size

Open in new window

At the moment I dont have the code around for
SQL-ClearTempDB -Computers $Server -ServerType $ServerYear | ? { $_.Drive -in 'E', 'T','S' }

But its really not relevant to what I am doing. The part of the code works. What it does is shrinks log files and tempdb in Sql. This shrink allows SQL to release space back to the OS.

Now the
SQL-DriveCheck $Server

Code is listed above. This procedure Checks disk space before we clear space. Then I run it again so the user of the GUI can see that we freed space from drive.

Basically its a before and after of disk space to validate that the code freed space on drive.

Then if it didnt not shrink drive to desired amount the last column says Escalate server to next person.

Hope this helps
I don't really need the code which generates the output, just the output.  Is that possible?  I've made some guesses about the output of SQL-ClearTempDB from the later commands which parse some of the output, but it would be best not to guess.
Sorry, I am willing to give you what ever you need but I am not understanding what you need from me.

SQL-ClearTempDB: I dont have code here and its shrinks sql log files

SQL-DriveCheck : This command has already been posted here here is the link.

https://www.experts-exchange.com/questions/28651784/Powershell-drive-percent-free.html?anchorAnswerId=40711638#a40711638

if you need a visual this is what the Function outputs to console.
User generated image
I need it to output to my textbox shown above.
this is quite a bit But I can post here.
let me know if you need something else.
Yes, the output is the result of running the command (whether to console, file, etc.).  The screenshot is good, but even better would be if you could run
SQL-DriveCheck $Server | Export-Clixml file1.xml -Depth 5
SQL-ClearTempDB -Computers $Server -ServerType $ServerYear | ? { $_.Drive -in 'E', 'T','S' } | Export-Clixml file2.xml -Depth 5

Open in new window

and then post both file1.xml and file2.xml here.
Ok wait do go far please. I am doing this right now.

Need to login to this environment and it takes a bit.
OK this actually has given me another avenue.

The data not appears in my dialog box here is the screen shot
User generated image
add these lines as you said
SQL-DriveCheck $Server | Export-Clixml C:\file1.xml -Depth 5
SQL-ClearTempDB -Computers $Server -ServerType $ServerYear | ? { $_.Drive -in 'E', 'T','S' } | Export-Clixml C:\file2.xml -Depth 5

Open in new window


Really feel like we made progress here.
File1.xml
File2.xml
From the output of SQL-ClearTempDB -Computers $Server -ServerType $ServerYear | ? { $_.Drive -in 'E', 'T','S' } it appears that the data is repeated twice (see screenshot).User generated imageWhat is are these two groups?  Are they always the same?


Here's what I have so far.  I changed the numeric specifier to "p" for percentage, but you could put it back to "n" if you want.
$DriveInfo = SQL-DriveCheck $Server
$Text = SQL-ClearTempDB -Computers $Server -ServerType $ServerYear | ? { $_.Drive -in 'E', 'T','S' }
$OutputBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d}`t`t {4,8:p} `r`n" -f $Server, $Text[0].Drive.Replace("Space",""), $Text[0]."MB Free", $Text[3]."MB Free", ($Text[0]."MB Free" / ($DriveInfo | ? {$_.DriveLetter -eq $Text[0].Drive})."TotalSpace MB")
$OutputBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d}`t`t {4,8:p} `r`n" -f $Server, $Text[1].Drive.Replace("Space",""), $Text[1]."MB Free", $Text[3]."MB Free", ($Text[1]."MB Free" / ($DriveInfo | ? {$_.DriveLetter -eq $Text[1].Drive})."TotalSpace MB")
$OutputBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d}`t`t {4,8:p} `r`n" -f $Server, $Text[2].Drive.Replace("Space",""), $Text[2]."MB Free", $Text[3]."MB Free", ($Text[2]."MB Free" / ($DriveInfo | ? {$_.DriveLetter -eq $Text[2].Drive})."TotalSpace MB")

Open in new window

Yes, if there is no change then yes it will be duplicate. I created the SQL-DriveCheck to give me the info I need your still using the SQL-ClearTempDB. For example your  calculating drive space when SQL-DriveCheck has it already calculated. Just curious with your reasoniing here? You may have a valid reason Like to understand it tho. Where is my Escalated Column?

Look at this image
https://www.experts-exchange.com/questions/28651784/Powershell-drive-percent-free.html?anchorAnswerId=40712961#a40712961
Can we add Escalate value at the end there.
Here is what I envisioned

1st Column = ServereName
2nd Column = Drive letter
3rd coulmn = Drive space Before SQL-ClearTempDB runs
(To me this is the tricky on 4)
4th coulmn = Drive space After SQL-ClearTempDB runs
5th column = Percentage free After the SQL-ClearTempDB run
6th Coulmn = Escalate value

Any reason why you calculated percent free when SQL-Check did that for you?
ASKER CERTIFIED SOLUTION
Avatar of footech
footech
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Went with this oone for now. Thanks for yours as well

$Text = SQL-ClearTempDB -Computers $Server -ServerType $ServerYear | ? { $_.Drive -in 'E', 'T'}#,'S' }
$DriveInfo = SQL-DriveCheck $Server
$OutputBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d}`t`t {4,8:p}`t`t {5,8:d} `r`n" -f $Server, $Text[0].Drive.Replace("Space",""), $Text[0]."MB Free", ($DriveInfo | ? {$_.DriveLetter -eq $Text[0].Drive})."FreeSpace MB", ($Text[0]."MB Free" / ($DriveInfo | ? {$_.DriveLetter -eq $Text[0].Drive})."TotalSpace MB"),($DriveInfo | ? {$_.DriveLetter -eq $Text[0].Drive}).Escalate
$OutputBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d}`t`t {4,8:p}`t`t {5,8:d} `r`n" -f $Server, $Text[1].Drive.Replace("Space",""), $Text[1]."MB Free", ($DriveInfo | ? {$_.DriveLetter -eq $Text[1].Drive})."FreeSpace MB", ($Text[1]."MB Free" / ($DriveInfo | ? {$_.DriveLetter -eq $Text[1].Drive})."TotalSpace MB"),($DriveInfo | ? {$_.DriveLetter -eq $Text[1].Drive}).Escalate
$OutputBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d}`t`t {4,8:p}`t`t {5,8:d} `r`n" -f $Server, $Text[2].Drive.Replace("Space",""), $Text[2]."MB Free", $Text[3]."MB Free", ($Text[2]."MB Free" / ($DriveInfo | ? {$_.DriveLetter -eq $Text[2].Drive})."TotalSpace MB"),($DriveInfo | ? {$_.DriveLetter -eq $Text[2].Drive}).Escalate
                            
            

Open in new window

Thank you!
I was surprised that using the "d" specifier didn't throw an error for the "Escalate" substitution in the above.  But I'm glad you have something that works for you.

I went ahead and tested my code and made some changes based on the sample files you provided earlier.  One advantage of using the style I did ( | Format-Table -AutoSize | Out-String ) is that you shouldn't have to worry about manually specifying the exact format of each and every line.  One thing it does require when doing this is to use a fixed-width font for the form in order for everything to line up.

Here's some test code to see how it displays.
Add-Type -AssemblyName "System.Windows.Forms"

$mainform = New-Object System.Windows.Forms.Form
$mainform.Text = "Test Form"
$mainform.Size = New-Object System.Drawing.Size(1000,500)
$mainform.StartPosition = "CenterScreen"
$mainform.AutoSize = $false
$mainform.MinimizeBox = $false
$mainform.MaximizeBox = $false
$mainform.SizeGripStyle = "Hide"
$mainform.WindowState = "Normal"
$mainform.FormBorderStyle = "Fixed3D"
$mainform.Font = New-Object System.Drawing.Font("Lucida Console",9)

$OutputBox = New-Object System.Windows.Forms.TextBox
$OutputBox.Size = New-Object System.Drawing.Size(950,300)
$OutputBox.Top = 60
$OutputBox.Name = "StatusTextBox"
$OutputBox.Multiline = $true
$OutputBox.ScrollBars = "Vertical"
$OutputBox.WordWrap = $true

# The next should be the same for testing as running "$before = SQL-DriveCheck $Server" 
$before = Import-Clixml .\File1.xml
# I can't run the next line so it is commented out.
#SQL-ClearTempDB -Computers $Server -ServerType $ServerYear | Out-Null
# File3.xml is just a copy of File1.xml with some numbers changed.  For real would use "SQL-DriveCheck $Server" instead of the Import-Clixml command.
$OutputBox.text = (Import-Clixml .\File3.xml) | Select Servername,
                                DriveLetter,
                                @{n="FreeSpace MB Before";e={$drive = $_.DriveLetter; ($before | ? {$_.DriveLetter -eq $drive})."FreeSpace MB"}},
                                @{n="FreeSpace MB After";e={$_."FreeSpace MB"}},
                                "TotalSpace MB",
                                @{n="Percentage Free";e={[math]::Round($_."Percentage Free",2)}},
                                Escalate |
 Format-Table -AutoSize | Out-String

$mainform.Controls.Add($OutputBox)
$mainform.ShowDialog()

Open in new window


The result of running the above.
User generated image