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

SelectResults

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
$Results
}
$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
}#EndCustomObject
}#EndForEach
$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
}#EndForEach
$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
 $Results
}
$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.

Troubleshooting Policys and xp_dirtree

Today we had a policy failure on a newly built SQL server. The policy verifies that every SQL server has a backup file stored on a remote directory. Simple enough, but in the past, this policy has been prone to permissions errors.

In case you would like to implement something like this on your system, the dumbed-down condition of the policy is below:


ExecuteSql('NUMERIC', '
--Get DBs on server
DECLARE @DBCount INT,@File VARCHAR(250)
SELECT  @DBCount = COUNT(*) FROM sys.databases WHERE database_id <> 2
SELECT  @File = ''\\BACKUPSERVER\BACKUP\''+CONVERT(VARCHAR(100),SERVERPROPERTY(''MachineName''))
--Insert DirTree results into a table variable to return a numeric result
DECLARE @Check TABLE (SubDirectory VARCHAR(250), Depth BIT, [File] BIT)
INSERT  @Check
EXEC master.dbo.xp_dirtree @File,1,1
--Numeric result for easy policy evaluation 1=Pass if( # files = # dbs )
IF((SELECT COUNT(*) FROM @Check) = @DBCount)
SELECT 1
ELSE
SELECT 0
')

Policy Explanation

The dumbed-down policy checks a backup server, which has a folder named for each SQL server, to see if it has the correct count of backups.  Each folder should have a backup file for each database, except for TempDB. This isn’t exactly how we do it, but it gets my point across and it provides a good example of how a moderately complex policy works using ExecuteSQL. There aren’t a lot of examples on the web, so I try to provide as many as I can.

The Troubleshooting

The new server wasn’t processing the policy correctly. My first idea was, “Well, there’s no files!” Easy! Problem solved!  Or not…the files were in the folder, and the number was even correct. So it wasn’t the backups or copy failing. That meant that there was something wrong with the permissions, the problem was, what?

I verified the logins were all present in SQL by reviewing Security -> Logins through Management Studio. This was a new server, so I assumed it would be something simple like that. The account in question was the SQL Server service account.  Policy Based Management and xp_dirtree use this account. The account was in SQL and it had all the proper permissions. OK, next thing.

The folder where all the files are stored is a shared folder, so I went into the Properties of the folder and verified the account that was shared. Everything looked right there. Every time I’ve had an issue with the permissions of xp_dirtree reading servers, it’s been because the service account in the shared folder was wrong, so my next stop was checking out Active Directory.

FileSharing

Sharing a folder makes the file path easier. Just make sure your service account has permission here.

Drilling down through Active Directory, I found the account used for sharing the folder. This account had every SQL service account as a member. It looked fine in AD, it looked fine on the Shared folder. Everything worked perfectly except on this new server. What was wrong?!?

At this point, I just decided to throw a hammer at it and throw in extra service accounts until hopefully one worked. Eventually I added the SQL Agent account. Suddenly, the policy passed. The new server could read the backup directory! So I took the normal account out of the permissions. Now everything failed except this new server. Wait, what…?

I logged into the new server and loaded up SQL Configuration Manager…

Oh…

When the server was setup, someone switched the accounts. SQL Server was running using the SQL Agent service account. SQL Agent was running using the SQL Server service account. I restarted each with the correctly corresponding service and account. I then fixed the permissions for the shared backup directory back to the original setup.

Voila! Everything worked perfectly!

The Moral of the Story

Make sure new servers and settings are configured properly before trying to fix things that weren’t broken before the new stuff existed. I’m sad to say it took me so long to understand the problem. Even with other DBAs chiming in. None of us thought about checking the Configuration Manager…that or they assumed I had already checked that.

Sometimes, it’s the little things that get you…OK, normally its the little things that get you.

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?

Win32Product_Evil

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!)

#### GET INSTALLED JAVA VERSION - SLOW!
$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)"
$Java

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.


########## GET INSTALLED JAVA VERSION - FAST!
$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]}
$Results
}
$Products
}#EndFunction Get-Uninstaller

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

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)"
$Java.DisplayName
$Java.ProdID

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.


########## UNINSTALL JAVA
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
}