Use PowerShell
Real Admins Script
Real Admins Script
Nov 13th
I’ve been exploring the Sync Framework for use in a couple of projects I have going and PowerShell is my preferred exploratory environment.
It was a bit of fun, since I got to work with eventing for the first time in V2.
First, I downloaded the Sync Framework Software Development Kit. That provided me with the Sync Framework runtime as well as some documentation.
The easiest way for me to get started was to take one of the samples and convert that to PowerShell.
I’m going to walk along the MSDN Sample and provide the equivalent PowerShell, as well as any changes I made to make it feel more PowerShell-y.
We are working with the File Sync Provider First up is setting the FileSyncOptions. FileSyncOptions are an enumeration (a limited list defined in code that maps to certain values) whose values are controlled by setting the appropriate bits to indicate the presence or absence of a flag. Mark Schill has a great post about how to set bitwise operations.
$options = [Microsoft.Synchronization.Files.FileSyncOptions]::ExplicitDetectChanges $options = $options -bor [Microsoft.Synchronization.Files.FileSyncOptions]::RecycleDeletedFiles $options = $options -bor [Microsoft.Synchronization.Files.FileSyncOptions]::RecyclePreviousFileOnUpdates $options = $options -bor [Microsoft.Synchronization.Files.FileSyncOptions]::RecycleConflictLoserFiles
With the File System provider, we can provide filters to include or exclude files and directories.
$FileNameFilter and $SubdirectoryNameFilter are parameters that take strings or string arrays.
$filter = New-Object Microsoft.Synchronization.Files.FileSyncScopeFilter if ($FileNameFilter.count -gt 0) { $FileNameFilter | ForEach-Object { $filter.FileNameExcludes.Add($_) } } if ($SubdirectoryNameFilter.count -gt 0) { $SubdirectoryNameFilter | ForEach-Object { $filter.SubdirectoryExcludes.Add($_) } }
After configuring the filter, we examine the folders and files located at the paths specified. If there has not been any previous synchronization, a metadata file will be created in each location to track any changes, updates, and deletes for later synchronization.
function Get-FileSystemChange() { param ($path, $filter, $options) try { $provider = new-object Microsoft.Synchronization.Files.FileSyncProvider -ArgumentList $path, $filter, $options $provider.DetectChanges() } finally { if ($provider -ne $null) { $provider.Dispose() } } }
Get-FileSystemChange $SourcePath $filter $options Get-FileSystemChange $DestinationPath $filter $options
Conflict resolution in the Sync Framework happens at the at the event level. An event is merely something that happens that can trigger other actions. Using Register-ObjectEvent, we can associate one or more scriptblocks with an event.
First, I defined scriptblocks to handle the conflicts. There is an enumeration, the ConflictResolutionAction enumeration, that provides some options for dealing with conflicts. For this example, we are going to pick the source object as the winner for any conflicts.
You will also notice another type of conflict defined, and that is a Constraint conflict. That can occur when an object of the same name is added on both sides in between synchronizations. The resolution options for these conflicts can be found in the ConstraintConflictResolutionAction enumeration.
$ItemConflictAction = { $event.SourceEventArgs.SetResolutionAction([Microsoft.Synchronization.ConflictResolutionAction]::SourceWins) [string[]]$global:FileSyncReport.Conflicted += $event.SourceEventArgs.DestinationChange.ItemId } $ItemConstraintAction = { $event.SourceEventArgs.SetResolutionAction([Microsoft.Synchronization.ConstraintConflictResolutionAction]::SourceWins) [string[]]$global:FileSyncReport.Constrained += $event.SourceEventArgs.DestinationChange.ItemId } # Configure the events for conflicts or constraints for the source and destination providers $destinationCallbacks = $destinationProvider.DestinationCallbacks Register-ObjectEvent -InputObject $destinationCallbacks -EventName ItemConflicting -Action $ItemConflictAction | Out-Null Register-ObjectEvent -InputObject $destinationCallbacks -EventName ItemConstraint -Action $ItemConstraintAction | Out-Null $sourceCallbacks = $SourceProvider.DestinationCallbacks Register-ObjectEvent -InputObject $sourceCallbacks -EventName ItemConflicting -Action $ItemConflictAction | Out-Null Register-ObjectEvent -InputObject $sourceCallbacks -EventName ItemConstraint -Action $ItemConstraintAction | Out-Null
We also see for the first time in the script blocks a variable called $event. This is an automatic variable exposed by the event and provides us information that we can use in our action.
Finally, I’m updating a variable in the global scope. There probably is a better way to handle this, but scriptblocks executed in response to events only have access to the global scope and any of the automatic variable exposed to it. Therefore, I use a variable in the global scope to gather my reporting information.
To start to synchronize the two sides, first we set up the synchronization via a SyncOrchestrator and assign it the local and remote providers, as well as defining the direction of the synchronization. In this example (sticking with the format from MSDN, we will do an Upload, which is in the SyncDirectionOrder enumeration (other options are Download, DownloadAndUpload, and UploadAndDownload).
# Create the agent that will perform the file sync $agent = New-Object Microsoft.Synchronization.SyncOrchestrator $agent.LocalProvider = $sourceProvider $agent.RemoteProvider = $destinationProvider # Upload changes from the source to the destination. $agent.Direction = [Microsoft.Synchronization.SyncDirectionOrder]::Upload Write-Host "Synchronizing changes from $($sourceProvider.RootDirectoryPath) to replica: $($destinationProvider.RootDirectoryPath)" $agent.Synchronize();
To achieve two way synchronization, we will do the upload twice, reversing the order of the providers.
Invoke-OneWayFileSync -SourcePath $SourcePath -DestinationPath $DestinationPath -Filter $null -Options $options Invoke-OneWayFileSync -SourcePath $DestinationPath -DestinationPath $SourcePath -Filter $null -Options $options
I modified the example to write out a custom object (and the logging is in the variable in the global scope as noted in the Handling Conflicts section) with the results of the synchronization (rather than logging it to the console).
In all, my translation is pretty similar to the example code, but there are some differences.
# Requires -Version 2 # Also depends on having the Microsoft Sync Framework 2.0 SDK or Runtime # --SDK-- # http://www.microsoft.com/downloads/details.aspx?FamilyID=89adbb1e-53ff-41b5-ba17-8e43a2e66254&displaylang=en # --Runtime-- # http://www.microsoft.com/downloads/details.aspx?FamilyId=109DB36E-CDD0-4514-9FB5-B77D9CEA37F6&displaylang=en # # [CmdletBinding(SupportsShouldProcess=$true)] param ( [Parameter(Position=1, Mandatory=$true, ValueFromPipelineByPropertyName=$true)] [Alias('FullName', 'Path')] [string]$SourcePath , [Parameter(Position=2, Mandatory=$true)] [string]$DestinationPath , [Parameter(Position=3)] [string[]]$FileNameFilter , [Parameter(Position=4)] [string[]]$SubdirectoryNameFilter ) <# .Synopsis Synchronizes to directory trees .Description Examines two directory structures (SourcePath and DestinationPath) and uses the Microsoft Sync Framework File System Provider to synchronize them. .Example An example of using the command #> begin { [reflection.assembly]::LoadWithPartialName('Microsoft.Synchronization') | Out-Null [reflection.assembly]::LoadWithPartialName('Microsoft.Synchronization.Files') | Out-Null function Get-FileSystemChange() { param ($path, $filter, $options) try { $provider = new-object Microsoft.Synchronization.Files.FileSyncProvider -ArgumentList $path, $filter, $options $provider.DetectChanges() } finally { if ($provider -ne $null) { $provider.Dispose() } } } function Invoke-OneWayFileSync() { param ($SourcePath, $DestinationPath, $Filter, $Options) $ApplyChangeJobs = @() $AppliedChangeJobs = @() try { # Scriptblocks to handle the events raised during synchronization $AppliedChangeAction = { $argument = $event.SourceEventArgs switch ($argument.ChangeType) { { $argument.ChangeType -eq [Microsoft.Synchronization.Files.ChangeType]::Create } {[string[]]$global:FileSyncReport.Created += $argument.NewFilePath} { $argument.ChangeType -eq [Microsoft.Synchronization.Files.ChangeType]::Delete } {[string[]]$global:FileSyncReport.Deleted += $argument.OldFilePath} { $argument.ChangeType -eq [Microsoft.Synchronization.Files.ChangeType]::Update } {[string[]]$global:FileSyncReport.Updated += $argument.OldFilePath} { $argument.ChangeType -eq [Microsoft.Synchronization.Files.ChangeType]::Rename } {[string[]]$global:FileSyncReport.Renamed += $argument.OldFilePath} } } $SkippedChangeAction = { [string[]]$global:FileSyncReport.Skipped += $event.SourceEventArgs.CurrentFilePath if ($event.SourceEventArgs.Exception -ne $null) { Write-Error '[' + "$($event.SourceEventArgs.Exception.Message)" +']' } } # Create source provider and register change events for it $sourceProvider = New-Object Microsoft.Synchronization.Files.FileSyncProvider -ArgumentList $SourcePath, $filter, $options $AppliedChangeJobs += Register-ObjectEvent -InputObject $SourceProvider -EventName AppliedChange -Action $AppliedChangeAction $AppliedChangeJobs += Register-ObjectEvent -InputObject $SourceProvider -EventName SkippedChange -Action $SkippedChangeAction $ApplyChangeJobs += $SourceApplyChangeJob # Create destination provider and register change events for it $destinationProvider = New-Object Microsoft.Synchronization.Files.FileSyncProvider -ArgumentList $DestinationPath, $filter, $options $AppliedChangeJobs += Register-ObjectEvent -InputObject $destinationProvider -EventName AppliedChange -Action $AppliedChangeAction $AppliedChangeJobs += Register-ObjectEvent -InputObject $destinationProvider -EventName SkippedChange -Action $SkippedChangeAction $ApplyChangeJobs += $DestApplyChangeJob # Use scriptblocks for the SyncCallbacks for conflicting items. $ItemConflictAction = { $event.SourceEventArgs.SetResolutionAction([Microsoft.Synchronization.ConflictResolutionAction]::SourceWins) [string[]]$global:FileSyncReport.Conflicted += $event.SourceEventArgs.DestinationChange.ItemId } $ItemConstraintAction = { $event.SourceEventArgs.SetResolutionAction([Microsoft.Synchronization.ConstraintConflictResolutionAction]::SourceWins) [string[]]$global:FileSyncReport.Constrained += $event.SourceEventArgs.DestinationChange.ItemId } #Configure the events for conflicts or constraints for the source and destination providers $destinationCallbacks = $destinationProvider.DestinationCallbacks $AppliedChangeJobs += Register-ObjectEvent -InputObject $destinationCallbacks -EventName ItemConflicting -Action $ItemConflictAction $AppliedChangeJobs += Register-ObjectEvent -InputObject $destinationCallbacks -EventName ItemConstraint -Action $ItemConstraintAction $sourceCallbacks = $SourceProvider.DestinationCallbacks $AppliedChangeJobs += Register-ObjectEvent -InputObject $sourceCallbacks -EventName ItemConflicting -Action $ItemConflictAction $AppliedChangeJobs += Register-ObjectEvent -InputObject $sourceCallbacks -EventName ItemConstraint -Action $ItemConstraintAction # Create the agent that will perform the file sync $agent = New-Object Microsoft.Synchronization.SyncOrchestrator $agent.LocalProvider = $sourceProvider $agent.RemoteProvider = $destinationProvider # Upload changes from the source to the destination. $agent.Direction = [Microsoft.Synchronization.SyncDirectionOrder]::Upload Write-Host "Synchronizing changes from $($sourceProvider.RootDirectoryPath) to replica: $($destinationProvider.RootDirectoryPath)" $agent.Synchronize(); } finally { # Release resources. if ($sourceProvider -ne $null) {$sourceProvider.Dispose()} if ($destinationProvider -ne $null) {$destinationProvider.Dispose()} } } # Set options for the synchronization session. In this case, options specify # that the application will explicitly call FileSyncProvider.DetectChanges, and # that items should be moved to the Recycle Bin instead of being permanently deleted. $options = [Microsoft.Synchronization.Files.FileSyncOptions]::ExplicitDetectChanges $options = $options -bor [Microsoft.Synchronization.Files.FileSyncOptions]::RecycleDeletedFiles $options = $options -bor [Microsoft.Synchronization.Files.FileSyncOptions]::RecyclePreviousFileOnUpdates $options = $options -bor [Microsoft.Synchronization.Files.FileSyncOptions]::RecycleConflictLoserFiles } process { $filter = New-Object Microsoft.Synchronization.Files.FileSyncScopeFilter if ($FileNameFilter.count -gt 0) { $FileNameFilter | ForEach-Object { $filter.FileNameExcludes.Add($_) } } if ($SubdirectoryNameFilter.count -gt 0) { $SubdirectoryNameFilter | ForEach-Object { $filter.SubdirectoryExcludes.Add($_) } } # Perform the detect changes operation on the two file locations Get-FileSystemChange $SourcePath $filter $options Get-FileSystemChange $DestinationPath $filter $options # Reporting Object - using the global scope so that it can be updated by the event scriptblocks. $global:FileSyncReport = New-Object PSObject | Select-Object SourceStats, DestinationStats, Created, Deleted, Overwritten, Renamed, Skipped, Conflicted, Constrained # We don't need to pass any filters here, since we are using the file detection that was previously completed. # this will only $global:FileSyncReport.SourceStats = Invoke-OneWayFileSync -SourcePath $SourcePath -DestinationPath $DestinationPath -Filter $null -Options $options $global:FileSyncReport.DestinationStats = Invoke-OneWayFileSync -SourcePath $DestinationPath -DestinationPath $SourcePath -Filter $null -Options $options # Write result to pipeline Write-Output $global:FileSyncReport }
Nov 12th
I’m updating Crystal Reports and trying to determine which reports might have been affected by some schema changes or functional changes in how the data was being stored.
The problem I’ve had is that when there are a large number of reports, it is very time consuming to open each one, look at it, and see if it contains any affected tables or views.
I’ve had to deal with this in my previous role as well. After feeling the pain a few times, I turned my intern loose on the problem and shelved the problem as “just another pain in dealing with Crystal Reports”.
Now, I’m back dealing with Crystal Reports more frequently and in the position to have to possibly update around 30 or 40 reports that were written before I started.
I’ve recently had a bit of exposure to the object model for the .NET API for Crystal Reports and thought maybe I could leverage that through PowerShell and whip together a quick script to help me list out the tables in each report.
It turned out to be painfully easy…
[reflection.assembly]::LoadWithPartialName('CrystalDecisions.Shared') [reflection.assembly]::LoadWithPartialName('CrystalDecisions.CrystalReports.Engine') $report = New-Object CrystalDecisions.CrystalReports.Engine.ReportDocument $report.load($pathToScript) $report.Database.Tables | Select-Object -expand Name $report.Dispose()
After I got the basics, I poked around and updated the script further (and posted it on PoshCode).
The full script also accesses the first level of subreports and retrieves their tables as well.
NOTE: Requires either the Crystal Report Runtime (Visual Studio 2008) or Visual Studio to be installed.
Apr 8th
UPDATE: The script was moved to Google Code. The links in the post have been updated to reflect that. Or you can just go here… http://code.google.com/p/poshcodegen/
I’ve been working on some data conversion at work, converting records from one system to a new system. I’ve built quite a library of SQL queries with PowerShell wrappers for dealing with data in the first system, but I don’t have the same luxury with the new system.
The new system does, however, have a nice set of stored procedures that make moving data into their application much easier.
I started writing my conversion scripts in PowerShell, since I do have to do some processing on the records to accommodate the new workflow and data layout. I was looking at having to call almost 100 stored procedures through various parts of this process. That is a lot of boiler plate code or referring back to the database often to check parameter names and types. So, I’ve written a little PowerShell script that will take stored procedures (either as a parameter or from the pipeline) and create a function that wraps that stored procedure.
The benefits of this are great with V2 CTP3 or with a more advanced editor (one that will provide tab completion on parameters).
One a wider scope, I think that this type of utility is one of PowerShell’s great strengths. Using PowerShell for metaprogramming (another example here on the Telling Machine blog) can be a great time saver. I spent a couple of hours working on this script, but it would have cost me much more time to handle each case individually.
Now, when I hear “metaprogramming”, my head starts to hurt a bit as I start to think about programs about programs about programs, but this isn’t that bad. PowerShell makes this pretty easy to understand though. To create a function dynamically, all that is needed is a string that contains text that the PowerShell runtime can evaluate (PowerShell will check for syntax errors, but not logic errors – as is the case with any script or function).
Example:
$text = ‘Get-ChildItem *.ps1 | Measure-Object’
Set-Item –Path function:global:Get-PowerShellScriptCount –Value $Text
This takes advantage of the Function provider and creates a function object in the global scope with the specified name and $Text is turned into a scriptblock. I can then call that function as needed.
Since I know PowerShell, to build dynamic functions I just have to create text that can be evaluated to do the function I need.
Here’s what my script does after it runs a query against the database to get the stored procedure’s text:
What are you automating with PowerShell?
How about trying to automate some of your automation code?
New-StoredProcFunction.ps1 here.
PSMDTAG:metaprogramming sql stored procedure
UPDATED SCRIPT: Thanks to Chad Miller for the idea.. instead of parsing the text of the stored procedure, the parameter information is available in the Information_Schema.Parameters
Mar 26th
Previously, I published a script on comparing what tables two databases contained. Going a bit further, I put together a script that compares the columns and what type of data they store.
Compare-DatabaseColumns has similar parameters to the Compare-DatabaseSchema script.
This script can also take pipeline input, either strings or a property of “Name” or “TableName”.
You can find this script on PoshCode.org.
Feb 13th
A question was asked on StackOverflow regarding how to add properties to a function, and then be able to retain that custom property when recalling that function from the function Provider. I’m not going to copy my answer here, but I do want to throw out a possible work around for this issue.
(It has been bugging me and I can’t really concentrate on my other tasks, so I need to get this out of the way.)
One suggestion I had was to create a type extension that had the property he was adding, but he was more interested in tagging specific functions, not all of them.
So, I’ve written a couple of little ScriptProperties to save and restore NoteProperties which have been added to FunctionInfo objects. I’ve added these to a PS1XML.
1: <?xml version="1.0" encoding="utf-8" ?>
2: <Types>
3: <Type>
4: <Name>System.Management.Automation.FunctionInfo</Name>
5: <Members>
6: <ScriptMethod>
7: <Name>SaveMetadata</Name>
8: <Script>
9: $DefaultProperties = 'PSPath', 'PSDrive', 'PSProvider', 'PSIsContainer'
10: $SaveDirectory = Split-Path $profile
11: $File = Join-Path $SaveDirectory "$($this.Name).xml"
12: $this.PSObject.Properties | Where-Object {$DefaultProperties -notcontains $_.Name -and $_.MemberType -like 'NoteProperty'} | Export-Clixml -Path $file
13: </Script>
14: </ScriptMethod>
15: <ScriptMethod>
16: <Name>LoadMetadata</Name>
17: <Script>
18: $SaveDirectory = split-path $profile
19: $PathToCustomProp = Join-Path $SaveDirectory "$($this.name).xml"
20: if (Test-Path $PathToCustomProp)
21: {
22: foreach ($Property in Import-Clixml -Path $PathToCustomProp)
23: {
24: Add-Member -InputObject $this -MemberType NoteProperty -Name $Property.Name -Value $Property.Value
25: }
26: }
27: </Script>
28: </ScriptMethod>
29: </Members>
30: </Type>
31: </Types>
After saving this file as a PS1XML file, you can call Update-TypeData –Prepend path\to\thefile.ps1xml, and every FunctionInfo object will have two script properties – SaveMetadata and LoadMetadata. As I’ve configured it, the data will be saved to the user’s profile directory and under a filename that matches the function name. So, you can add NoteProperties to your heart’s desire and save and recall them as needed.
I don’t have a direct application for this, and it can probably be cleaned up or done more efficiently, but I had to work through the problem. I’d love to hear your feedback!
Feb 12th
I regularly am working with several versions of a database for an application that I manage (a live database, training database, test database, and previous version database). Occasionally, I need to know what the differences between the databases are, especially after our vendor updates my test environment or right after an update in my training or live environment.
Since I spend a good portion of my day in PowerShell, I wrapped some system table queries in a PowerShell script and use Compare-Object to find any differences in the tables and compare the column definitions as well. The queries targets only user tables.
Compare-DatabaseSchema.ps1 takes several parameters.
I still have to add some checks for the various constraints, but that will come later.
You can find the script on PoShCode.org.
Feb 7th
Doug Finke recently posted a blog post about finding the most common words in a file.
Doug put together a little 19 line PowerShell script to solve the issue, but something just called to me about how it wasn’t necessarily playing to some of the included cmdlets in PowerShell.
So, here’s my interpretation as a one liner:
get-content big.txt | foreach-object {[regex]::split($_.ToLower(), ‘\W+’)} | where-object {$_.length -gt 0} | group-object | sort-object -property count -descending | select-object -property name -first 6
EDIT: One thing I’ve noticed is Doug’s script runs much faster..
Feb 4th
EDIT :
I have a bunch of scripts that I use regularly, but it can be a pain to type the path. These are scripts I don’t always need, so I don’t want to include them in my profile.
I thought it might be convenient to have a way to convert those scripts into functions on demand. I searched around but couldn’t find an existing script to do that, so here one is.
Kirk Munro (@poshoholic) pointed out that aliases (and expands on it in this blog post) might be an easier way to go. That would definitely be an option.
Comments and feedback are always appreciated!