Get and Set Folder Permissions with PowerShell

Managing permissions for numerous servers is the theme today. Drilling down into a folder, right-clicking properties, then reviewing security on the same folder for potentially dozens of computers is time consuming and, with the capabilities of scripting, unnecessary.

PowerShell lets us do this very easily. The first script allows you to view each account and their corresponding read/write permissions on any number of computers. By default the script will only search the local computer. You can filter to only display a specific right. A full list and explanation of each right is available here.

Function Get-Permission
  [string[]]$ComputerName = $Env:COMPUTERNAME,
Process {
 $ComputerName |
 ForEach-Object {
 $Server = "$_"
 Write-Verbose "Getting Permissions for \\$Server\$Folder"
 (Get-Acl "\\$Server\$Folder").Access |
 Where { $_.FileSystemRights -LIKE "*$Rights*" } | Select IdentityReference, FileSystemRights, AccessControlType

Now for a simple example. Remember to supply a $ instead of a : after the drive letter, as this is designed to run remotely.

#Example of Get-Permission
Get-Permission -ComputerName "COMP1","COMP2" -Folder "C$\logs\SQL"
Now that you have verified the permissions list, you might need to make some adjustments. This set command will allow you to change $Access and $Rights for a specific $Account with minimal effort across your domain.
Function Set-Permission
  [string[]]$ComputerName = $env:COMPUTERNAME,
  [string]$Access = "Allow",
  [string]$Right = "FullControl"
Process {
  ForEach-Object {
  $Server = "$_"
  $Acl = Get-Acl "\\$Server\$Folder"
  $Rule = New-Object System.Security.AccessControl.FileSystemAccessRule("$Account","$Right","ContainerInherit,ObjectInherit","None","$Access")
  Set-Acl "\\$Server\$Folder" $Acl
  Write-Verbose "Permission Set for \\$Server\$Folder"
 And here is a quick example of how to execute the function. This can be used to allow or deny rights to the folder.
#Example Set-Permission
Set-Permission -ComputerName "Comp1","Comp2" -Folder "C$\logs\sql" -Account "Domain\ServiceUser" -Access "Allow" -Right "FullControl"

Failover Cluster Manager Connection Error Fix

A few days ago I encountered a new error with Failover Cluster Manager.  A couple of servers had been rebuilt to upgrade them from Windows Server 2008 to 2012. They were added back to the cluster successfully. However, one of the servers would not open Failover Cluster Manager properly, and tracking down the solution took a long time.

The problem server successfully joined the cluster, but now it would not connect to the cluster using Failover Cluster Manager. If you opened up the application, it didn’t try to automatically connect, and manually connecting with the fully qualified name failed too. Below is the generated error.


I love how this error has absolutely no useful information to it. Luckily I was able to track Error 0x80010002 down online.

Research indicated that there was some sort of WMI error on the computer. Rebooting didn’t help anything, and after numerous attempts to correct/rebuild the WMI repository, not much was accomplished. Eventually, the server could connect to the cluster, but that only worked about 30% of the time, and it nearly timed out even when it did succeed! The cluster still never connected automatically.

After further poking around on the internet, I found a few suggested solutions, with my ultimate fix closely following this post. I still had to combine everything together and run scripts all over the cluster before things returned to normal.

First of all, this is a condensed version of the Cluster Query from the TechNet post linked above.

1) Cluster Query

$Nodes = Get-ClusterNode
ForEach ($Node in $Nodes)
 If($Node.State -eq "Down")
  { Write-Host "$Node : Node down skipping" }
   $Result = (Get-WmiObject -Class "MSCluster_CLUSTER" -NameSpace "root\MSCluster" -Authentication PacketPrivacy -ComputerName $Node -ErrorAction Stop).__SERVER
   Write-Host -ForegroundColor Green "$Node : WMI query succeeded"
   Write-Host -ForegroundColor Red "$Node : WMI Query failed" -NoNewline
   Write-Host  " //"$_.Exception.Message

Any server that throws an error with the above query needs to have the following scripts ran on it:

2) MOF Parser
This will parse data for the cluster file. 

cd c:\windows\system32\wbem
mofcomp.exe cluswmi.mof

FCM was still not working correctly, so I reset WMI with the following command.

3) Reset WMI Repository

Winmgmt /resetrepository

That will restart the WMI service, so you’ll probably have to try running it multiple times until all the dependent services are stopped. The command shouldn’t take more than a few seconds to process either way though.

After that, the server that failed the Cluster Query (1) was reporting good connections, but FCM still wouldn’t open properly!

I decided to try the two WMI commands (2 & 3) again on the original server that couldn’t connect to FCM. I had already ran those commands there during the initial troubleshooting, so I was starting to think this was a dead end. Still, it couldn’t hurt, so I gave it a shot.

I reopened FCM and voila! Now the cluster was automatically connecting and looking normal.

As a further note, after everything appeared to be working correctly, SQL was having trouble validating connections to each node in the cluster during install, and I had to run commands 2 & 3 on yet another node in the cluster before things worked 100%, even though that node never had a connection error using the Cluster Query (1).

SQL Server Storage: Reading Block Size

This is the final post in the SQL Server Storage line of blog posts I’ve made. First we discussed Pages & Extents, then how to Read A Page using T-SQL, and finally this post will be about Disk Partition Offsets and Allocation.

Knowing that Pages are 8 KB and Extents are 64 KB, it’s understandable that SQL would have the best performance when the disks are aligned in the same manner. However, in earlier versions of Windows, this was never the case. Instead, Windows used an alignment configuration of 63 512 Byte sectors for a total of 31.5 KB. Since the way SQL Server reads and writes was not aligned with Windows, havoc ensued in the form of increased I/O operations. As of Windows 2008, partitions are aligned at 1024 KB by default, providing fewer I/O operations since 1024 is a multiple of 64.

I tried to keep the above explanation simple, but it still got wordy. The takeaway should be: you want SQL Server disks to have a Starting Offset that is a multiple of 64 KB, with the desirable default being 1024 KB. Higher values are fine for special circumstances, just so long as your Starting Offset is evenly divisible by 64.
As for your block size, that should also be 64 KB, or the size of a single Extent, to improve performance.

There is a single script to find both Block Size and Starting Offset, and it almost even works!

Get-WmiObject win32_DiskPartition |
	SELECT SystemName, Name, BlockSize, StartingOffSet |
	FT -Auto

That script returns the correct Starting Offset, but that Block Size is a screwy number that should not be trusted. To make matters worse, you get a Partition Number, but no Drive Letter. That doesn’t help me understand if my data disk is formatted correctly. In comes another query.

Get-WmiObject -Class win32_Volume |
    FT DriveLetter, Label, BlockSize -Auto

Great, now we have an accurate BlockSize and a Drive Letter! The only problem is, now there is no Partition Number to match up with the OffSet from the first query. Getting ALL the information at one time is a pain. Luckily there are Custom Objects to help us smash results together and Win32_LogicalDisk to help us relate the drives and partitions.

$Report = @()
$Disks = Get-WMIObject Win32_logicaldisk | WHERE { $_.DriveType -eq '3' }
$Result = ForEach ( $Disk in $Disks)
    $OffSet = Get-WmiObject -Query "Associators of {Win32_LogicalDisk.DeviceID='$($Disk.DeviceID)'} WHERE ResultRole=Antecedent"
    $Report = [PSCustomObject] @{
		ComputerName = $Disk.SystemName
		DriveLetter = $Disk.DeviceID
		Partition = $OffSet.Name
		BlockSize = ( Get-WmiObject Win32_Volume | WHERE {$_.DriveLetter -eq "$($Disk.DeviceID"} | SELECT -expand BlockSize )
		StartingOffSet = $OffSet.StartingOffSet
$Result | FT -Auto

Now you can determine the Block Size and Starting Offset of your drives easily. Changing those values, well that’s another story. Normally that will require formatting the drive, so its best to get it right in the first place. Make sure all your SQL Server drives are formatted correctly before you get started. Just educate your friendly Storage Admin on what SQL Server needs so everything is correct by the time you get it.

Remotely Enable Always On

Always On Availability Groups is the new feature for High Availability in SQL Server 2012. It’s been out for awhile now, but unless you have Enterprise Edition SQL, you might not have been able to use it much.

Of course you need a cluster to utilize Always On, but once that is complete, you also have to enable Always On in Configuration Manager on all your servers that will be participating in the AG as well.

Continuing on with my lazy, automated DBA goals of logging into computers as rarely as possible, I developed the below PowerShell script to connect to SQL Servers, enable Always On, and then restart the SQL Service in order for the changes to take effect.

The only thing you need to change below is the computer names, it should automatically detect your SQL instance names. If that doesn’t work (I haven’t been able to test every possible name parsing possibility), you can supply the instance names yourself.

## List Servers in AG Here ##
$Computers = 'Computer1','Computer2'
## Everything Else is Automated ##

# Finds the servers running the services, and the services' names
Invoke-Command -ComputerName $Computers -Scriptblock {
$Services = (Get-Service -Include MsSql* | Where { $_.Status -eq 'Running' } )
$Nodes = @()

# Parses the names of the SQL Instances
ForEach( $Service in $Services )
{ $Nodes += $Env:ComputerName'\'+$Service.DisplayName.Split('(')[1].Replace(')','') }

# Loops through each instance and enables AlwaysOn, restarting with -Force
ForEach ( $Node in $Nodes )
{ Enable-SQLAlwaysOn -ServerInstance $Node -Force }

# Starts SQL Service on the affected server(s) if it's still stopped
If($Services -ne $NULL)
{ Start-Service -DisplayName ($Services.DisplayName) }

The key code here for enabling Always On is this snippet below.

Enable-SQLAlwaysOn -ServerInstance $Node -Force

If the longer script cannot automatically detect your ServerInstance, you can provide it manually and run the command. Restart your SQL Service for the change to take affect.

String Manipulation in T-SQL or PowerShell

I don’t often have to do string manipulation in T-SQL, but when I do, it’s rare enough that I don’t remember the syntax. I’ve done it in the past, so I keep a script handy with a working example though. I recently added to that example after wanting to know not only the file names, but also the folder location.  This article will cover getting a string after a specific character. This can be used to get a folder path or just a file name.

The scripts are cumbersome, to say the least, in SQL. Some people use this problem as an interview question, but I feel like the only way you’d be able to answer this off the top of your head is if you did it on a daily basis. While I remember the general idea of switching the order and checking the text after a specific character, I couldn’t write this from scratch if my life depended on it. Even with intellisense helping me out, it’s hard for me to remember how CHARINDEX can be manipulated to get the correct answer here, since I use it so rarely. I can pull it from my script library quickly though, and now I’ll be able to refer to my own blog as well. (I do that every time I set up Transparent Data Encryption).

I’ve commented into the code a quick explanation of what each command does in order to find the correct answer. Then I found out WordPress refused to color code the comments correctly. So I removed them. Now I’ll just explain it instead.

SQL can do it

The below script will find the File Name of each master file by reversing the direction of the text and using CHARINDEX to get the length of the string until it reaches a specific character, in this case, the \. The RIGHT command then returns the number of characters at the end of the original string based on the inner index command. The -1 removes the \ from the returned results.

To get just the folder path, perform the same command again, but this time issue a REPLACE command for the FileName, replacing it with an empty string.

In order to get a single folder, you combine the steps above to get just the FolderPath, and then once again get the last value after the \. This returns the final folder in the list. This may not be the optimal way to find the results, but it uses the same methods in order to keep it relatively simple.

,[FileName] = RIGHT(physical_name,CHARINDEX( '\', REVERSE(physical_name)) -1)
,[FolderPath] = REPLACE(physical_name, '\' +RIGHT(physical_name,CHARINDEX( '\' ,REVERSE(physical_name)) -1), '')
,[FileFolder] = RIGHT(REPLACE(physical_name, '\' +RIGHT(physical_name,CHARINDEX( '\' ,REVERSE(physical_name)) -1),''),CHARINDEX( '\' ,REVERSE(REPLACE(physical_name, '\' +RIGHT(physical_name,CHARINDEX( '\' ,REVERSE(physical_name)) -1), ''))) -1)
FROM sys.master_files mf

PowerShell just does it better

On the other hand, I feel that the syntax to perform the same string manipulation is much more intuitive in PowerShell. It’s definitely less code, although I will concede that I assigned variables here, while with the SQL approach, I chose to do it all in a single statement. No variable assignments makes it much harder to read.

Leveraging the SPLIT command and LENGTH is about all you need for PowerShell. SPLIT creates an array from the string, with each new value separated by the character supplied in the parenthesis, that \ again. Returning the array value based on the length of the newly split variable returns any folder level or the file name alone, it just depends on the number you subtract from the length of the split.

$FullPath = "C:\Users\Test\Desktop\Text.txt"

$Split = $FullPath.Split('\')
$File = $Split[$Split.Length-1]
$LastFolder = $Split[$Split.Length-2]
$FolderPath = $FullPath.Replace($File,'')



PowerShell: Custom Objects and Tables

Returning data in PowerShell is normally an automatic feature, but oftentimes you will need to specially format or splice some data into a single table. There are a lot of ways to go about it, but none of them are exactly intuitive. I’ll list my four preferred methods, all using a moderately complex statement that I’ve previously mentioned.

Method 1 – String

The first method is probably the easiest one to remember syntax-wise. You create an empty variable, pipe column names to it, add results to the table, and finally return the result. This will leave the data as a string, which has certain pros and cons. It supports multiple rows of data just fine.

## Simple Example - String ##
$Results = '' | SELECT ComputerName,UserName,Processes,Services
$Results.ComputerName = $Env:ComputerName
$Results.Username = $Env:UserName
$Results.Processes = (Get-Process *).Count
$Results.Services = (Get-Service *).Count
$Results | Format-Table -Autosize


A sample result of the custom table

A much more complex, but realistic, example is below. I’m querying network adapter data to get the name of the adapter and match it to the IP and Mac Addresses, then returning the result set.

## Advanced Example - String ##
$Adapters = Get-WmiObject win32_networkadapter | WHERE {$_.NetConnectionId -NOTLIKE '' -AND $_.NetConnectionId -NOTLIKE 'Local Area Connection*' }
#  $ErrorActionPreference = 'SilentlyContinue'
$Info = ForEach($Adapter in $Adapters)
$Results = '' | SELECT ComputerName,AdapterName,IpAddress
$Results.ComputerName = $Env:ComputerName
$Results.AdapterName = $Adapter.NetConnectionId
$Results.IpAddress = (Get-WmiObject win32_networkadapterconfiguration -Filter "Index = $($Adapter.DeviceId)").IpAddress
$Info | FT -auto

I’ll continue to show the simple and complex example for each custom object method.

Method 2 – Hashtable to PsCustomObject

Method 2 is very similar to Method 1. It’s actually the first one I learned, so I use it the most. This creates a HashTable instead of a String then converts that to a Custom Object so it will display nicely as a table. Older versions of PowerShell might not format it to look like a table, instead listing out the results in 2 columns. One of my machines displays as a list, while the other, with a newer version of PowerShell, displays as a table.

## Simple Example - HashTable to PsCustomObject ##
$Results = @{}
$Results.ComputerName = $Env:ComputerName
$Results.Username = $Env:UserName
$Results.Processes = (Get-Process *).Count
$Results.Services = (Get-Service *).Count
[PsCustomObject]$Results | Format-Table -Autosize

The advanced hash table isn’t overly complicated, the setup is only a bit different.

## Advanced Example - HashTable ##
$Results = @()
$Adapters = Get-WmiObject win32_networkadapter | WHERE {$_.NetConnectionId -NOTLIKE '' -AND $_.NetConnectionId -NOTLIKE 'Local Area Connection*' }
ForEach($Adapter in $Adapters)
 $Results += [PSCustomObject] @{
 ComputerName = $Env:ComputerName
 AdapterName = $Adapter.NetConnectionId
 IpAddress = (Get-WmiObject win32_networkadapterconfiguration -Filter "Index = $($Adapter.DeviceId)").IpAddress
$Results | FT -auto

Method 3 – Custom Table

This method is very syntax heavy with lots of punctuation. It’s a good method to create custom column names in a table, as you can just use the @{l='ColumnName';e={$Value}} format for any column in the table. Just remember l is for label and e is for expression,  and you can mix custom column names with default ones.

## Simple Example - Custom Table ##
Get-Process * | SELECT -First 1 |
Format-Table @{l='ComputerName';e={$Env:ComputerName}},@{l='UserName';e={$Env:UserName}},
@{l='Processes';e={(Get-Process *).Count}},@{l='Services';e={(Get-Service *).Count}} -AutoSize

The advanced example is very similar to the simple example for this method. Just remember to keep track of those brackets. They can be overwhelming.

## Advanced Example - Custom Table ##
Get-WmiObject win32_networkadapter |
WHERE {$_.NetConnectionId -NOTLIKE '' -AND $_.NetConnectionId -NOTLIKE 'Local Area Connection*' } |
Format-Table @{l='ComputerName';e={$Env:ComputerName}},@{l='Adaptername';e={$_.NetConnectionId}},
@{l='IpAddress';e={Get-wmiObject win32_networkadapterconfiguration -Filter "Index = $($_.DeviceId)" |
Select-Object -Expand ipaddress}} -auto

Method 4 – Add Member

Coders may tell you that this is the preferred method, but I feel the syntax and execution is too troublesome for just displaying some query results. This method returns as a PSCustomObject. There is a lot of syntax here, but it’s mostly repetitive and easy to read. You also have the benefit of explicitly defining the MemberType with this method, although you’d normally just leave it as NoteProperty. My major complaint with this is that it does not easily support multiple rows of data…I’ll explain in the advanced example.

$Results = New-Object PSObject
Add-Member -InputObject $Results -MemberType NoteProperty -Name ComputerName -Value $Env:ComputerName
Add-Member -InputObject $Results -MemberType NoteProperty -Name UserName -Value $Env:UserName
Add-Member -InputObject $Results -MemberType NoteProperty -Name Processes -Value (Get-Process *).Count
Add-Member -InputObject $Results -MemberType NoteProperty -Name Services -Value (Get-Service *).Count
$Results | Format-Table -Autosize

The advanced example requires some consideration. In this case, you’ll need to make sure that the New-Object command is inside of a ForEach loop, and thus creating a new set object each time. Otherwise, you’ll just repeat the same first line of data over and over. The Add-Member command creates columns, so if they already exist…you just get errors. With the looping, we are generating a few tables and splicing them into one. At the end of the ForEach, we assign the generated data into another variable to populate the custom table and display the data.

$Adapters = Get-WmiObject win32_networkadapter | WHERE {$_.NetConnectionId -NOTLIKE '' -AND $_.NetConnectionId -NOTLIKE 'Local Area Connection*' }
Clear-Variable Results
ForEach($Adapter in $Adapters)
$obj = New-Object PSObject
Add-Member -InputObject $obj -MemberType NoteProperty -Name ComputerName -Value $Env:ComputerName
Add-Member -InputObject $obj -MemberType NoteProperty -Name Adaptername -Value $Adapter.NetConnectionId
Add-Member -InputObject $obj -MemberType NoteProperty -Name MacAddress -Value $Adapter.MacAddress
Add-Member -InputObject $obj -MemberType NoteProperty -Name IpAddress -Value (Get-WmiObject win32_networkadapterconfiguration -Filter "Index = $($Adapter.DeviceId)").IpAddress
$Results += $obj
$Results | Format-Table -Autosize

In my opinion, use whichever method fits your needs and is the easiest to implement and remember. Personally, I use method 1 and 2 the most.

PowerShell: Get Network Adapters with WMI

I thought I had already blogged about this topic, but evidently not. I was in the middle of finishing another post with the intention of linking back to this one, when I realized it did not actually exist.

The script I’m about to explain actually exists in PowerShell now, but only if you are using Windows 8 or Server 2012. I don’t have that option most of the time professionally, so I can’t use Get-NetAdapter or Get-NetIpAddress. Too bad, it would solve all some one of my problems. I’d still have to splice those two commands together to get all the results I want. The work-around in older systems requires combining two wmi classes instead.

The snippet below will give you all the properties each wmi query can return. I need to match IP Addresses with the name of the adapter. NetworkAdapter can get me the IP, but NetworkAdapterConfiguration has the name. After reviewing the properties of each command, you might say, “but NetworkAdapter does have the name property!” Sure it does, but what it actually returns is more like the product model information. I want the personalized name of the adapter.

Get-WmiObject win32_NetworkAdapter | GM -Membertype Property

Get-WmiObject win32_NetworkAdapterConfiguration | GM -Membertype Property

The script I have to run is moderately complex. First I’ll get the Adapter information and assign it to a variable. I’m excluding blank Connection Ids and Local Area Connections. I don’t care about default stuff. If you do, just drop the WHERE clause. I then perform a ForEach loop to get the name of each adapter. The IpAddress is matched using the Filter parameter and passing in each adapter’s Deviceid, the unique adapter id that is available in both commands. Without that embedded second wmi query for the IP with the id filter, any data returned couldn’t be trusted.

The results are saved to a string variable which creates a custom table. This is necessary to get the data from two different queries in a single, readable result set.

$Adapters = Get-WmiObject win32_networkadapter | WHERE {$_.NetConnectionId -NOTLIKE '' -AND $_.NetConnectionId -NOTLIKE 'Local Area Connection*' }

$Info = ForEach ( $Adapter in $Adapters )
 $Results = '' | SELECT ComputerName,AdapterName,MacAddress,IpAddress
 $Results.ComputerName = ($Adapter.SystemName)
 $Results.AdapterName = $Adapter.NetConnectionId
 $Results.IpAddress = (Get-WmiObject win32_networkadapterconfiguration -Filter "Index = $($Adapter.DeviceId)").IpAddress
 $Results.MacAddress = $Adapter.MacAddress
$Info | FT -auto

There are a lot of ways to display and format data with custom objects and tables, and I’ll discuss a few of those in my next post.

PowerShell: Detect and Uninstall Java FAST

I’ve been building out a remote, silent Java Updater for awhile now. Making sure it works perfectly in my environment is not an easy task. I’m 90% there, I just have to get over the last few hurdles.
One of the major problems was uninstalling Java efficiently. You can Google scripted Java uninstalls, and you’ll probably find the same recycled code over and over again. (I’ve provided it below, but don’t touch it) The problem is, this code is terrible.

Let me explain. The code works as intended. That’s not why it’s terrible. Why am I complaining then? It uses a very, very bad WMI class. What’s WMI you ask? Not the problem. The class is. It’s a huge waste of time. How bad is it?


When even Bing thinks it’s bad. It’s bad.


Get-WmiObject -Class win32_product would have been a very useful command, however, it’s by far the slowest and most inconsistent thing I have ever used.  Worse, you can’t fix it. Use a WHERE clause, use a FILTER, use wildcards, use explicit values. It doesn’t matter. It’s still slower than molasses. If you plan to use this command, you might as well just go to lunch after you hit execute.

If you didn’t check out those links above, let me summarize:

Invoking win32_product causes a computer to enumerate through the entire list of installed programs and validates each install, causing potentially extremely long wait times before returning results. Regardless of where clauses or filters, every program is always checked in this way.

In testing, the command takes 20-40 minutes to return results on a production server. Even on my computer it takes at least 5 minutes, normally much longer. I have ran this command and gotten results as fast as 11 seconds…after I ran it three times in quick succession. Then running it again five minutes later already took three minutes again. And that was the best case scenario test I had. That’s just unacceptable. Here’s that terrible code with a timer included. Take my word for it and just skip past this.

Win32_Product (Very Slow – Just Skip It!)

$Start = Get-Date
$Java = Get-WmiObject -Class win32_product | WHERE { $_.Name -LIKE "Java [0-9]*" }
$Current = (Get-Date) - $Start
"Execution Time: $($Current.Hours):$($Current.Minutes):$($Current.Seconds)"

If you ran that, I’m sorry you wasted away half your life waiting for it to complete, but I did warn you.

My Method (Fast but Wordy)

I came up with a method to uninstall through registry guids. Detecting the java installation guid goes from 30 minutes to <1 second in this manner. The entire installation process was cut by at least 98%, over half an hour down to roughly 25 seconds! I can work with that! The code is a lot longer though.

$Start = Get-Date
Function Get-Uninstaller
$ErrorActionPreference = "SilentlyContinue"
$Guids = Get-ChildItem -Path 'Registry::HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall' | ForEach {$_.Name}
$Products = ForEach ($Guid in $Guids)
$Results = "" | SELECT DisplayName, IdentifyingNumber
$Results.DisplayName = Get-ItemProperty -Path $($Guid.Replace("HKEY_LOCAL_MACHINE","Registry::HKLM")) | ForEach {$_.DisplayName}
$Results.IdentifyingNumber = Get-ItemProperty -Path $($Guid.Replace("HKEY_LOCAL_MACHINE","Registry::HKLM")) | ForEach {$_.UninstallString.Split("X")[1]}
}#EndFunction Get-Uninstaller

$Java = Get-Uninstaller | WHERE { $_.DisplayName -LIKE "Java [0-9]*" }
$Current = (Get-Date) - $Start
"Execution Time: $($Current.Hours):$($Current.Minutes):$($Current.Seconds)"

Win32Reg_AddRemovePrograms (Fast, but last minute discovery)

But wait, there’s more! The documentation on how broken win32_products is provided me with an even easier method. I didn’t find it until researching some links for this blog though. I was very skeptical about this code until I ran it, because I never saw anyone suggesting its use online while searching for a PowerShell uninstaller. It’s actually just as instantaneous as my registry read method!

########## GET INSTALLED JAVA VERSION - Alternative
$Start = Get-Date
$Java = Get-WmiObject -Class win32reg_addremoveprograms | WHERE { $_.DisplayName -LIKE "Java [0-9]*" }
$Current = (Get-Date) - $Start
"Execution Time: $($Current.Hours):$($Current.Minutes):$($Current.Seconds)"

Uninstall Script

Whichever method you choose, you’ll need to then run the uninstaller still. Just change the Guid Identifier appropriately for whichever script you choose. That is, $App.IdentifyingNumber or $App.ProdID. I’m logging to a Temp Java folder, so make sure that folder exists or just remove the /l and everything after it if you don’t care about the logging. The uninstall runs silently with no user interface and suppresses reboots.

ForEach ($App in $Java)
$ArgumentList = "/x $($App.IdentifyingNumber) /qn /norestart /l*vx C:\Temp\Java\UninstallLog.log"
Start-Process -FilePath "msiexec.exe" -ArgumentList $ArgumentList -Wait -PassThru


PowerShell: Get Files Containing a String

I have hundreds of script files, and as hard as I try, I don’t always name them effectively. Many of those scripts contain an obscure piece of code that I’m not very familiar with. I normally refer to previous scripts to remember the rarely used bits. While the standard windows search feature helps me narrow down which file I need when I’ve named the file efficiently, it fails to help a lot. When I’m looking for a script with a particular line of code on say, line 1021, where I did a weird time conversion, I’m not going to find it fast, if at all.

Last week I was hunting for a script that used the PowerShell feature of creating a Progress Bar. In the past I’ve mostly used the bar as a gimmick, but this time I wanted to keep track of how far along a script was in a long-running multi-server query. I quickly scripted something to search the contents of my documents for a specific piece of text. I even added a progress bar to this script. Granted the search is normally so fast that the bar doesn’t matter, but hey, I needed to practice the calculations for the progress bar anyways.

Function Search-Folder
 $Files = Get-ChildItem $Folder
 $Count = $Files.Count
 $i = 0
 ForEach( $File in $Files )
  $Percent = $i/$Count*100
  Write-Progress -Activity "Searching through files..." -Status "$Percent% Complete" -PercentComplete $Percent
  $Text = Get-Content "$Folder\$File" -ErrorAction SilentlyContinue
  IF( $Text | WHERE { $Text -LIKE "*$SearchTerm*" } )
  { Write-Host $File }

Most of the math and code here is to display the Progress Bar. The function takes two mandatory parameters, $Folder and $SearchTerm. It then loops through each file in the folder using Get-ChildItem and then Get-Content, filtering for the desired text string.

I commonly clear the screen with CLS before executing a function, just to keep the results easy to read. The below example execution will cycle through my PowerShellScripts folder and return the name of any files with the word progress in them.

Search-Folder -Folder "C:\Scripts\PowerShellScripts" -SearchTerm "progress"

PowerShell: Get SQL Servers From AD

Today I’d like to share a quick script that is useful when working with a new domain of SQL servers. It’s also good for anyone who doesn’t know their environment very well or suspects there might be some hidden or rarely used servers. There are probably many ways to do this scattered around the internet, but this method is easy and as long as you have a properly designed Active Directory, you’ll be good to go.

The script isn’t anything special, but it will require the Active Directory PowerShell module. You’ll need to run the script on an existing terminal server or at least be able to successfully import the module. Importing a module is very straightforward and this one should already be available on a terminal server.

Import-Module ActiveDirectory

Once you have the module loaded, you can run the following script.

Get-ADComputer -Filter { Description -LIKE "*sql*" } -Properties Description | SELECT Name, Description

Let’s break this down since this is a short post. First of all, we are running the Get-ADComputer cmdlet. That’s going to search through your Active Directory for a list of computer names. The -Filter parameter is looking for any Description that contains the word SQL. If you want to instead search for a name prefix, you can change the filter to { Name -LIKE "Prefix*" }. The -Properties Description parameter will populate the description of the computers from AD. Without that piece, your descriptions will be blank even though we are specifically selecting them. Finally, we pipe the results to a SELECT since I’m only interested in the name of the computers and their descriptions.

This script relies on your Active Directory having useful descriptions. If your coworkers have been lazy and left those blank or put in less than useful information…it won’t be very effective.