Leo Torres
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.
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 += @"
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.
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.
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.
ASKER
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)
}
ASKER
I guess you may want to know how I am using the function.
Here are the calls to 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 += @"
"@
ASKER
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 += @"
"@
ASKER
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 += @"
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
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
ASKER
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
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.
ASKER
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.
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.
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.
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
and then post both file1.xml and file2.xml here.
ASKER
Ok wait do go far please. I am doing this right now.
Need to login to this environment and it takes a bit.
Need to login to this environment and it takes a bit.
ASKER
OK this actually has given me another avenue.
The data not appears in my dialog box here is the screen shot
add these lines as you said
Really feel like we made progress here.
File1.xml
File2.xml
The data not appears in my dialog box here is the screen shot
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
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).What 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.
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")
ASKER
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
Look at this image
https://www.experts-exchange.com/questions/28651784/Powershell-drive-percent-free.html?anchorAnswerId=40712961#a40712961
ASKER
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
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.
The result of running the above.
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()
The result of running the above.