Powershell Formatting

Hello I wrote this Function to clear space on temp DBs but I dont know how to display results in a table format

add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue

Function SQL-ClearTempDB{
	[cmdletbinding(
				   DefaultParameterSetName = '',
				   ConfirmImpact = 'low'
				   )]
	
	Param (
	[Parameter(
			   Mandatory = $True,
			   Position = 0,
			   ParameterSetName = '',
			   ValueFromPipeline = $True)]
		[array]$Computers,
		
		[Parameter(
				   Mandatory = $True,
				   Position = 1,
				   ParameterSetName = ''
				   )]
		[array]$ServerType
	)
	

Foreach($Server in $Computers){
	
    Write-Output $Server
	invoke-sqlcmd -inputfile "C:\Automation\SQLScripts\$ServerType\ShrinkTransactionLog_TDrive.sql" -serverinstance $Server -database "master" -QueryTimeout 20000  # the parameter -database can be omitted based on what your sql script does.
	}
}	

$Text = SQL-ClearTempDB -Computers "EZ4SUP2DB02" -ServerType 2008
Write-output "$($Text.Drive)			$($Text."MB free")		`r`n"

Open in new window



Need out put to be in columns and rows
PS C:\Users\usgltorres> C:\Windows\System32\WindowsPowerShell\v1.0\Modules\Functions\SQL-ClearTempDB.ps1
C E S T                         C E S T			9104 17292 34162 25259                         9104 17292 34162 25259		

Open in new window


Ideally this is out but I want. I dont even want to display C and S.
Drive                                                                  MB Free
---------                                                               ---------------
E                                                                          17292
T                                                                          25259

Open in new window

LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Yes, I pivoted that. And I have a small typo - the "T: free before" is the same as "E: free before", as seen in your screenshot :/.
$Text = SQL-ClearTempDB -Computers $Server -ServerType $ServerYear | ? { $_.Drive -in 'E', 'T' } 
$OutputBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d} `r`n" -f $Server, $Text[0].Drive.Replace("Space",""), $Text[0]."MB Free", $Text[2]."MB Free"
$OutputBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d} `r`n" -f $Server, $Text[1].Drive.Replace("Space",""), $Text[1]."MB Free", $Text[3]."MB Free"
$OutputBox.text += @"


"@
$OutputBox.Refresh

Open in new window

And yes, currently there should be no difference before "before" and "after", if you have run the script all the time for the same machines - nothing to clean out then.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
$text is an array (precisely it is a collection). You should use formatting or exporting cmdlets. And filter before doing so.
$text | ? { $_.Drive -in 'E', 'T' } | format-table -auto Drive, 'MB Free'

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
When i run the function by itself yes i get the desired results but when string is passed to an Output window I get this

$Text = SQL-ClearTempDB -Computers $Server -ServerType $ServerYear
$OutputBox.text += $Server
$OutputBox.text += $Text | ? { $_.Drive -in 'E', 'T' } | format-table -auto Drive, 'MB Free' #Write-output "`r`n $($Text.Drive)	`r`n	$($Text."MB free")		`r`n" 
$OutputBox.Refresh

Open in new window


code
0
Protect Your Employees from Wi-Fi Threats

As Wi-Fi growth and popularity continues to climb, not everyone understands the risks that come with connecting to public Wi-Fi or even offering Wi-Fi to employees, visitors and guests. Download the resource kit to make sure your safe wherever business takes you!

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Putting the output into a text box is something completely different.  Up to now we discussed console or file output only.
In this case I recommend to use different approach:
$Text = SQL-ClearTempDB -Computers $Server -ServerType $ServerYear
$OutputBox.text += $Server
$OutputBox.text += $Text | ? { $_.Drive -in 'E', 'T' } | Select Drive, 'MB Free' | convertTo-CSV -NoType -Delimiter "`t"
$OutputBox.Refresh

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
Your Code produced this output now
error2
I then added some newline
$Text = SQL-ClearTempDB -Computers $Server -ServerType $ServerYear
 $OutputBox.text += $Server
 $OutputBox.text += "`r`n"
$OutputBox.text += $Text | ? { $_.Drive -in 'E', 'T' } | Select Drive, 'MB Free' | convertTo-CSV -NoType -Delimiter "`t"
$OutputBox.text += "`r`n"
$OutputBox.Refresh

Open in new window


Produced this(Would be nice to remove quotes too )
error3
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I see that leads to nothing usuable. I forgot about the double quotes ...
Would you not have the server name as first column? Like with:
$Text = SQL-ClearTempDB -Computers $Server -ServerType $ServerYear
$OutputBox.text += $Server
$OutputBox.text += $Text | ? { $_.Drive -in 'E', 'T' } | % {
  "{0}          `t`t {1}`t {2,8:d} `r`n" -f $Server, $_.Drive, $_,{MB Free}
}
OutputBox.Refresh

Open in new window

"{2,8:d}" formats parameter 3 (2+1) to a decimal number (d) with 8 chars, right- aligned.
0
 
Leo TorresSQL DeveloperAuthor Commented:
WOW now it looks like what should.

Current Code
$Text = SQL-ClearTempDB -Computers $Server -ServerType $ServerYear
 $OutputBox.text += $Text | ? { $_.Drive -in 'Drive Before','Drive After','E', 'T' } | % {"{0}`t`t {1}`t`t {2,8:d} `r`n" -f $Server, $_.Drive.Replace("Space",""), $_."MB Free" }
$OutputBox.text += "`r`n"
$OutputBox.text += "`r`n"	
$OutputBox.Refresh

Open in new window


Now does this
Sample4

Any way to add this in between the 4 rows?
FYI the dataset comes from run this command in SQL "EXEC master..xp_fixeddrives" twice befoe clearing space and after.
Would like user to know which is which.
Server			Drive			MB FREE
-------			------			--------
Drive Before 
EZ4SUP2DB02          	  E	   		 17292 
 EZ4SUP2DB02          	  T	    		25259 

Drive After 
 EZ4SUP2DB02          	  E	    		17292 
 EZ4SUP2DB02          	  T	   	 	25259 

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I don't think your screenshot is the result of the code posted in http:#a40696557, as you should see "Drive Before" and "Drive After" then (with the server name prepended) ;-).

Wouldn't it be better to have something like:
Server          Drive     MB FREE before     MB FREE after
-------         -----     --------------     -------------
EZ4SUP2DB02      E          17292             17292
EZ4SUP2DB02      T          25259             25259

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That would then be:
$Text = SQL-ClearTempDB -Computers $Server -ServerType $ServerYear | ? { $_.Drive -in 'E', 'T' } 
$OutputBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d} `r`n" -f $Server, $Text[0].Drive.Replace("Space",""), $Text[0]."MB Free", $Text[2]."MB Free"
$OutputBox.text += "{0}`t`t {1}`t`t {2,8:d}`t`t {3,8:d} `r`n" -f $Server, $Text[1].Drive.Replace("Space",""), $Text[0]."MB Free", $Text[3]."MB Free"
$OutputBox.text += @"


"@
$OutputBox.Refresh

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
wow, this is better than I thought. I see you created an extra column I am assuming its the after column. You pivoted the data. Correct?

The shrink is not changing drive space because the drive is completely free.
sample
If thats the case I will validate the sizes before and after.
0
 
Leo TorresSQL DeveloperAuthor Commented:
Other issues but this works from an output perspective.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.