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

LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

footechCommented:
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.
0
Leo TorresSQL DeveloperAuthor Commented:
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.
0
Leo TorresSQL DeveloperAuthor Commented:
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

0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Leo TorresSQL DeveloperAuthor Commented:
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

0
Leo TorresSQL DeveloperAuthor Commented:
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

0
Leo TorresSQL DeveloperAuthor Commented:
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


sample
0
footechCommented:
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

0
Leo TorresSQL DeveloperAuthor Commented:
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
0
footechCommented:
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.
0
Leo TorresSQL DeveloperAuthor Commented:
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.

http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_28651784.html#a40711638

if you need a visual this is what the Function outputs to console.
sample
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.
0
footechCommented:
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.
0
Leo TorresSQL DeveloperAuthor Commented:
Ok wait do go far please. I am doing this right now.

Need to login to this environment and it takes a bit.
0
Leo TorresSQL DeveloperAuthor Commented:
OK this actually has given me another avenue.

The data not appears in my dialog box here is the screen shot
sql
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
0
footechCommented:
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).image1What 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

0
Leo TorresSQL DeveloperAuthor Commented:
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
http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_28651784.html#a40712961
0
Leo TorresSQL DeveloperAuthor Commented:
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?
0
footechCommented:
I was under the impression that you still wanted to use the output from SQL-ClearTempDB.  I thought the screenshot was just in response to my asking for the output of SQL-DriveCheck, and didn't realize you wanted to use the output of that directly now.

Give this a shot.  I haven't been able to test, but may be able to tonight.
$before = SQL-DriveCheck $Server
SQL-ClearTempDB -Computers $Server -ServerType $ServerYear | Out-Null
$OutputBox.text = SQL-DriveCheck $Server | Select Servername,
                                DriveLetter,
                                @{n="UsedSpace MB Before";e={$drive = $_.DriveLetter; ($before | ? {$_.DriveLetter -eq $drive})."UnusedSpace MB"}},
                                @{n="UsedSpace MB After";e={$_."UsedSpace MB"}},
                                "TotalSpace MB",
                                "Percentage Free",
                                Escalate |
 Format-Table -AutoSize | Out-String

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leo TorresSQL DeveloperAuthor Commented:
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

0
Leo TorresSQL DeveloperAuthor Commented:
Thank you!
0
footechCommented:
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.
form
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.