PowerShell Recursive Registry Update

A friend asked me earlier this week if I could provide a PowerShell solution for him.  The task called for searching all sub folders under a root in the registry and updating a specific key name to a new value.
This sounded like fun and something easily handled, but it turned out to be rather difficult to provide a solution.  I floundered for a while trying to understand his requirements.  He finally thought to send me a reg file so I could test with the keys he needed.
Suddenly my eyes were opened and things went much smoother. I had a list of keys now, but a lot of long code that didn’t work well for the update task.  Eventually I had a concise, simple solution, but I’m a bit embarrassed to say that it took me much longer than I had planned.

Background

This problem was specifically for Putty, which I had never heard of before this week.  I still know basically nothing about it, except that it was made by this guy because his name is in the registry path.  (clever way to make sure everyone knows who created your software)

You can use my final solution at the bottom to get and update Putty’s registry properties. I assume that’s useful, my friend seemed to be happy with it. For non-Putty users, you may have some need to search the registry for a key that you know exists but are not sure exactly where it is located. The Get statement can help you out there, since searching in regedit can be quite slow. I haven’t fathomed a scenario where I would need to update all like-named keys to a new identical value outside of Putty though.

The Script

Originally, my plan was to just use the following code for a fast solution.

Get-ItemProperty -Path $Path -Recurse

That’s not actually valid code though.

Turns out -Recurse isn’t a valid parameter in every cmdlet

So I started working on a horrific work-around involving ForEach statements and lots of piping. I eventually had a solution to get all the keys, but then updating them from that list did not work, so I went back to the basics, and realized it was a very easy query.

You can search the registry using Get-ItemProperty or Get-ChildItem. The latter has pros and cons though. While you can use -Recurse with it, the results are a bit uglier to read and why I was originally avoiding it.  I also assumed that I could not Set values that were piped from it. However, in this case, it is the superior method. I have included a before and after Get statement in the working script so that you can see the keys you are about to update before, and then verify the new values afterwards.

Disclaimer: I apologize if this code has dreadful formatting, WordPress was ignoring my line breaks while I used an outdated browser.

## Set Variables
$Path = "HKCU:\Software\SimonTatham\Putty\Sessions"
$Key = "LogFileName"
$NewValue = "C:\text.txt"

### Check Existing Values
Get-ChildItem $Path | Get-ItemProperty -Name $Key | 
    Select PsChildName,LogFileName

### Update Values
Get-ChildItem $Path | Set-ItemProperty -Name $Key -Value $NewValue

### Verify New Values
Get-ChildItem $Path | Get-ItemProperty -Name $Key | 
    Select PsChildName,LogFileName

Advertisements

Enterpise Policy Management Framework – Skip the Report

Policy-Based Management in SQL Server is a wonderful and underused feature. The Enterprise Policy Management Framework available through Codeplex further enhances this with reports and history tables, but if you have ever used the main report, you know how distressingly slow it can be. Not only that, but drilling through the report to find failed policies on instances is an extremely painful process. Visual reports are pretty, and the aggregation is a really cool feature, but expanding nodes are not practical for a large environment.

It depressed me just pulling up the slow report to screen shot this. Expanding nodes is even worse.

So while I use EPMF, I do not check the reports daily. Instead, I script the daily failures and errors to quickly review them. Below are three scripts that can provide policy results, use whichever you prefer.

Script 1: Short and sweet. This provides all failures and errors for the current day, including the unshredded XML evaluation results. This is my go to script each morning. Find failures and investigate.

SELECT
phd.EvaluatedServer
,phd.EvaluationDateTime
,phd.EvaluatedPolicy
,CategoryName
,PolicyResult
,ph.EvaluationResults
FROM EESqlReports.policy.PolicyHistoryDetail phd
INNER JOIN EESqlReports.policy.PolicyHistory ph ON ph.PolicyHistoryID = phd.PolicyHistoryID
WHERE
PolicyResult <> 'PASS'
AND CONVERT(DATE,phd.EvaluationDateTime) = CONVERT(DATE,GETDATE())
ORDER BY PolicyResult DESC,Policy_id,phd.EvaluatedServer;

Script 2: This script is essentially the same as above, but it includes the evaluated object or database along with a cleaner XML result. While still XML, it is easier to read, and the best solution I’ve found to read the evaluation results reliably for any policy. Sometimes the XML will not provide an actual value on a failed result. I’ve yet to discover what causes this. Theories include network issues or unexpected values.

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/DMF/2007/08'AS DMF)
SELECT
phd.EvaluatedServer
,phd.EvaluationDateTime
,phd.EvaluatedPolicy
,phd.CategoryName
,phd.PolicyResult
,[CheckParam1] = res.Expr.value('Function[1]/Constant[2]/Value[1]','nvarchar(100)')
,[ExpectedValue1] = res.Expr.value('Constant[1]/Value[1]','nvarchar(100)')
,[ActualValue1] = res.Expr.value('Function[1]/ResultValue[1]','nvarchar(100)')
FROM policy.PolicyHistoryDetail phd
INNER JOIN policy.PolicyHistory ph on ph.PolicyHistoryID = phd.PolicyHistoryID
CROSS APPLY ResultDetail.nodes('Operator') AS Res(Expr)
WHERE
PolicyResult <> 'PASS'
AND CONVERT(DATE,phd.EvaluationDateTime) = CONVERT(DATE,GETDATE())
ORDER BY PolicyResult DESC,EvaluatedServer;

Script 3: This script attempts to shred the XML results, showing you the check parameter, expected value, and actual value. This sample script is very short because shredding a policy result gets complicated fast. I’m only shredding the first line of a policy here. Since most policies have more than one parameter and thus values, this script gets excessive fast.  Further, if the policy doesn’t fit the XML format scripted perfectly, you’ll get NULL results. Before you know it, you have to tailor-make a script for almost every policy in your environment. It’s just not effective, and why I suggest just reading the XML from the above script if you really need to view results. As terrible as XML is to read, it’s better than crafting a script for dozens or even hundreds of policies.

<span style="color: #808080; font-family: Consolas; font-size: small;">Consolas; font-size: small;"><span style="color: #808080; font-family: Consolas; font-size: small;">WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/DMF/2007/08' AS DMF)
SELECT
PH.EvaluatedServer
,PH.EvaluationDateTime
,PH.EvaluatedPolicy
,Res.Expr.value('(../DMF:TargetQueryExpression)[1]', 'nvarchar(150)') AS EvaluatedObject
,(CASE  WHEN Res.Expr.value('(../DMF:Result)[1]','nvarchar(150)') = 'FALSE'
AND Expr.value('(../DMF:Exception)[1]','nvarchar(max)') = '' THEN 'FAIL'
WHEN Res.Expr.value('(../DMF:Result)[1]','nvarchar(150)') = 'FALSE'
AND Expr.value('(../DMF:Exception)[1]','nvarchar(max)') <> '' THEN 'ERROR'
ELSE 'PASS'
END) AS PolicyResult
,CAST(Expr.value('(../DMF:ResultDetail)[1]','nvarchar(max)') AS XML) AS ResultDetail
FROM policy.PolicyHistory PH
INNER JOIN policy.PolicyHistoryDetail phd ON ph.PolicyHistoryID = phd.PolicyHistoryID
INNER JOIN msdb.dbo.syspolicy_policies p ON p.name = PH.EvaluatedPolicy
CROSS APPLY EvaluationResults.nodes('
declare default element namespace "http://schemas.microsoft.com/sqlserver/DMF/2007/08";
//TargetQueryExpression') AS Res(Expr)
WHERE phd.PolicyResult = 'FAIL'
AND CONVERT(DATE,phd.EvaluationDateTime) = CONVERT(DATE,GETDATE())
ORDER BY PolicyResult DESC,EvaluatedServer;</span>