Adventures in DSC

I'm experimenting with creating a deployment package with Desired State Configuration.  Huge thanks to Jacob Benson for his wonderful DSC journal, which has helped me enormously to work through problems caused by Microsoft's sparse DSC documentation.  I read the Day 11 post and was able to then proceed as described below to attempt to use a script to install a few utilities.

I have a configuration script that, first, recursively copies a File (directory type) Resource to a folder.  The script follows:

#Requires -version 4.0

Configuration NG_BackendDSC
{
param (
[String[]]$ComputerName = $env:COMPUTERNAME
,
$ReleaseSourcepath = "d:\download\ng"
,
$NGTargetPath = 'd:\ng_backend'

)

Node $ComputerName
{
File BackendInstallationFiles
{
Ensure = "Present"

SourcePath = (Join-Path $ReleaseSourcepath '7.0\ng_backend')

DestinationPath = $NGTargetPath

Type = "Directory"

Recurse = $true

}

Log AfterDirectoryCopy
        {
            # The message below gets written to the Microsoft-Windows-Desired State Configuration/Analytic log
            Message = "Finished running the file resource with ID BackendInstallationFiles"
            DependsOn = "[File]BackendInstallationFiles" # This means run "BackendInstallationFiles" first.
        }
# ... [package resources follow here as described below]
} # End configuration block

The Configuration script then continues with a Package Resource to install 7Zip and another for Winmerge:

Package 7Zip
{
DependsOn = '[File]BackendInstallationFiles'

Ensure = "Present"

Path = (Join-Path $ReleaseSourcepath 'Utilities\7z457-x64.msi')

Name = '7-Zip'

ProductID = '23170F69-40C1-2702-0457-000001000000'

LogPath = (Join-Path $ReleaseSourcepath '7zip_dsc.log')
}

Package WinMerge
{
DependsOn = '[File]BackendInstallationFiles'

Ensure = 'Present'

Path =  (Join-Path $ReleaseSourcepath 'Utilities\WinMerge-2.14.0-Setup.exe')

Name = 'WinMerge'

ProductID = ''

LogPath = (Join-Path $ReleaseSourcepath 'winmerge_dsc.log')
}

There are three more packages:

Package NotePadPlus
{
DependsOn = '[File]BackendInstallationFiles'

Ensure = 'Present'

Path =  (Join-Path $ReleaseSourcepath 'Utilities\npp.6.5.5.Installer.exe')

Name = 'Notepad++'

ProductID = ''

LogPath = (Join-Path $ReleaseSourcepath 'notepadplus_dsc.log')
}

Package FileZilla
{
DependsOn = '[File]BackendInstallationFiles'

Ensure = 'Present'

Path =  (Join-Path $ReleaseSourcepath 'Utilities\FileZilla_3.7.4.1_win32-setup.exe')

Name = 'FileZilla FTP Client'

ProductID = ''

LogPath = (Join-Path $ReleaseSourcepath 'FileZilla_dsc.log')
}

Package AgentRansack
{
DependsOn = '[File]BackendInstallationFiles'

Ensure = 'Present'

Path =  (Join-Path $ReleaseSourcepath 'Utilities\AgentRansack_820.exe')

Name = 'Agent Ransack'

ProductID = ''

LogPath = (Join-Path $ReleaseSourcepath 'Agent_Ransack_dsc.log')
}

At the end of the script, after the closing Configuration bracket, I create the MOF files and start off the implementation of the configuration:

    ng_backendDSC -OutputPath d:\dsc\MOF
    Start-DscConfiguration -Wait -Path d:\dsc\MOF -Verbose

I executed the script within the ISE.

The directory copy happened fine.  7zip was already installed, so DSC noticed this and skipped it.

Winmerge then began to install.  I waited a half-hour, but the script did not finish.  In fact Winmerge did install, which I could see from the Start Menu, but the log file 'winmerge_dsc.log' was only just created and has zero bytes.

Here's all I got from the verbose comments:
VERBOSE:  [[Package]WinMerge] The package WinMerge is not installed
VERBOSE: [[Package]WinMerge] Package configuration starting

I clicked the Stop button in the ISE.  The status bar says "Stopping" but not "Stopped."
I closed the ISE and checked the Event log (Diagnostics/Applications and Services Logs/Microsoft/Windows/Desired State Configuration.  There's an entry for the starting of the script and one for the ending of the script.  Both logs have the same message:
Job {66685F9E-D242-4A6C-9CA5-DD47AED6B62B} : 
Configuration is sent from computer NULL by user sid S-1-5-21-2621261384-2063666624-962377667-1000.

Great.  Nothing helpful there.  There was no debug log even though analytic and debug logging are enabled as described here.

I've seen scripts hang the ISE before, so I tried running the script from the PowerShell console.  As I had dreaded, I saw this output:

VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' =
SendConfigurationApply,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' =
root/Microsoft/Windows/DesiredStateConfiguration'.
Cannot invoke the SendConfigurationApply method. The SendConfigurationApply method is in progress and must return before SendConfigurationApply can be invoked.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : QAIDD7

VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 0.557 seconds

I had dreaded this because I have seen this before.  Jason's blog is so far the sole page on the Internet that I have found that even mentions this error.  When he saw it, it went away by itself.  When I saw it, I waited several days and rebooted the server and still see the message, preventing me from doing any other DSC script runs.

Before running this script, I had created a snapshot of the virtual machine, so I can recover, but I tried rebooting the server.  It's a Windows 2008 R2 machine by the way, with PowerShell 4.0 installed, which is the minimum version that supports DSC.  I understand that DSC is not PowerShell.  That only makes it more difficult to figure out ways to troubleshoot the underlying technology, when there is no helpful output into logs.

After the reboot, the "SendConfigurationApply method is in progress and must return before SendConfigurationApply can be invoked" error still came up, strangely.  The output recommends using -Force in this case, so I changed the command to delete the -Wait and add -Force:

Start-DscConfiguration -Wait -Path d:\dsc\MOF -Verbose

Cool.  The script seems to run this time:
HasMoreData     : True
StatusMessage   : Running
Location        :  Server1
StartParameters : {}
Command         : Start-DscConfiguration -Force -Path d:\dsc\MOF -Verbose
JobStateInfo    : Running
Finished        : System.Threading.ManualResetEvent
InstanceId      : 9282f535-6026-49eb-932b-c4cd58e3f815
Id              : 4
Name            : Job4
ChildJobs       : {Job5}
PSBeginTime     : 3/20/2014 2:33:16 PM
PSEndTime       :
PSJobTypeName   : ConfigurationJob
Output          : {}
Error           : {}
Progress        : {}
Verbose         : {}
Debug           : {}
Warning         : {}
State           : Running

VERBOSE: Time taken for configuration job to complete is 0.077 seconds

So what's a scripter to do?  Microsoft recommends using the -Wait, but the script won't run unless I use -Force instead.

I am still not seeing anything in the winmerge log file.  I ran a Get-Job | FormatList and see the job is still in the Running state:

HasMoreData     : True
StatusMessage   : Running
Location        : Server1
StartParameters : {}
Command         : Start-DscConfiguration -Force -Path d:\dsc\MOF -Verbose
JobStateInfo    : Running
Finished        : System.Threading.ManualResetEvent
InstanceId      : 9282f535-6026-49eb-932b-c4cd58e3f815
Id              : 4
Name            : Job4
ChildJobs       : {Job5}
PSBeginTime     : 3/20/2014 2:33:16 PM
PSEndTime       :
PSJobTypeName   : ConfigurationJob
Output          : {}
Error           : {}
Progress        : {}
Verbose         : {}
Debug           : {}
Warning         : {}
State           : Running

I then ran receive-job -id 4 -Keep.  No output at all.

Get-Job did show a ChildJobs property, so I tried Get-Member on it but didn't receive anything I can use.

PS D:\> get-job | select childjobs | gm

   TypeName: Selected.System.Management.Automation.ContainerParentJob

Name        MemberType   Definition
----        ----------   ----------
Equals      Method       bool Equals(System.Object obj)
GetHashCode Method       int GetHashCode()
GetType     Method       type GetType()
ToString    Method       string ToString()
ChildJobs   NoteProperty System.Collections.Generic.List`1[[System.Management.Automation.Job, System.Management.Auto...

get-command *config* turned up a Get-DSCConfiguration cmdlet.  Sounds promising.  I was stopped by the same error as before:

get-dscconfiguration : Cannot invoke the GetConfiguration method. The SendConfigurationApply method is in progress and must return before GetConfiguration can be invoked.

At line:1 char:1

+ get-dscconfiguration

+ ~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (MSFT_DSCLocalConfigurationManager:root/Microsoft/...gurationManager) [Get

   -DscConfiguration], CimException

    + FullyQualifiedErrorId : MI RESULT 1,Get-DscConfiguration

I will revert the VM, but I don't know how to proceed from here.  Is Winmerge not compatible with DSC?  How can I get more information?

PowerShell to the Rescue

I've started a Paper.li publication:  PowerShell to the Rescue to gather interesting tips and articles about PowerShell.

SQL Script to find matching column names in tables

Let's say you want to create a join between two tables, you think they have some matching column names that would be good candidates for the WHERE clause, and you do not want to scroll up and down through SQL Server Management System and scribble notes.  Here is an easy way to do this using the INTERSECT keyword and two sys tables (SQL Server 2005 and above only.)



select b.name
from sys.tables a, sys.columns b
where a.name = 'EXTRACT_DIM_COURSE'
and a.object_id = b.object_id

INTERSECT 

select b.name
from sys.tables a, sys.columns b
where a.name = 'EXTRACT_FCT_COURSE_GRADE'
and a.object_id = b.object_id

INTERSECT gives you the results that are part of both result sets.  In this case the result was:

name

DATE_STAMP

DIM_DISTRICT_DISTRICT_ID

DIM_DISTRICT_SCHOOL_YEAR

DIM_SCHOOL_SCHOOL_ID

SCHOOL_YEAR

A more verbose but less readable way of getting the same result is by using a subselect in the where clause:

select b.name

from sys.tables a, sys.columns b

where a.name = 'EXTRACT_FCT_COURSE_GRADE'

and a.object_id = b.object_id

and b.name in (select b.name from sys.tables a, sys.columns b where a.name = 'EXTRACT_DIM_COURSE' and a.object_id = b.object_id)

Feature Requests for Idera's PowerShell Plus v4.1

If you would like these enhancements done as well, consider copying the list below and sending an email to support@idera.com.  The more customers ask for an enhancement, the quicker it gets done!

Name:
Company:
Product Version: 4.1
PowerShell Version: 2.0
Question/Issue: Enhancement requests

·         One-chord execution to comment or uncomment selected lines in the editor (e.g., ctrl+shift+c to comment, ctrl+shift+u to uncomment)

·         Live IntelliSense for new variables without having to first execute the script (PowerGUI has that!)

·         More standard keystrokes or customizable keystrokes for editor shortcuts such as

o   Ctrl+k, ctrl+k for bookmark, like SQL Management Studio

o   F3 to repeat last search

o   Ctrl+h for search and replace

·         Option to reopen last-opened files when starting PowerShell Plus

·         Option to save “solutions” or groups of files to open at once

·         Start page to remember a longer list of recently opened files, and make its window a lot bigger.

PowerShell/Excel Automation with ACE drivers

This is to document my experience using the ACE drivers to automate generating a report from a multisheeted Excel spreadsheet.

The spreadsheet was created by someone else, and the worksheet names included a hyphen, for example, "Project A - Client1". I experienced PowerShell exception errors when I tried to list the data, with the error message stating the punctuation is not allowed in the TABLE_NAME (which is the worksheet name) property, even though it's not a problem for Excel itself.

I had to find a way to change the worksheet names programmatically because renaming the Excel sheet by right clicking the tab did not change the name in the TABLE_NAME property itself for some reason, even if I closed the spreadsheet file and powershell session and reopened them.

A PowerShell Script to deploy/share/update a profile.ps1 and module

I hope this script is helpful to anyone who needs to share or deploy a customized PowerShell profile script and module.

<#
.SYNOPSIS
Install DF_Deploy module file to $pshome and profile.ps1 in the "My Documents\WindowsPowerShell" folder.

.DESCRIPTION
Creates a WindowsPowerShell folder under the current user's My Documents folder,
copies profile.ps1 to that folder, creates a module folder under $pshome,
copies module files to it.

.EXAMPLE
.\Install-DF_Deploy
Have a copy of the profile and module files in the same folder as this script.
#>
$mydocs = [system.Environment]::GetFolderPath("MyDocuments")
$UserPoshHome = Join-Path $mydocs "WindowsPowerShell"
$UserModulesPath = Join-Path $userPoshHome "Modules\DF_Deploy"
if (!(Test-Path($UserPoshHome))) {
md $UserPoshHome # -WhatIf
Write-Host "Created $userPoshHome"
}
else {

Write-Host "$userposhhome exists already.`n"
}

if (!(Test-Path($UserModulesPath))) {
md $UserModulesPath # -WhatIf
Write-Host "Created $UserModulesPath`n"
}
if (!(Test-Path("C:\Windows\System32\WindowsPowerShell\v1.0\Modules\df_deploy"))) {
md "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\DF_Deploy" # -WhatIf
Write-Host "Created C:\Windows\System32\WindowsPowerShell\v1.0\Modules\df_deploy`n"
}
if (Test-Path($UserPoshHome)) {
Write-Host "$userPoshHome is a valid path."
Write-Host "Copying profile.ps1 to $UserPoshHome"
$currentDir = [Environment]::CurrentDirectory=(Get-Location -PSProvider FileSystem).ProviderPath
if(Test-Path (Join-Path $currentDir profile.ps1)) {
Copy-Item (Join-Path $currentDir profile.ps1) $UserPoshHome # -WhatIf
Write-Host "Copied profile.ps1."
}


if(Test-Path (Join-Path $currentDir DF_Deploy.psm1)) {
Copy-Item (Join-Path $currentDir DF_Deploy.psm1) "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\df_deploy" -Force # -WhatIf
Copy-Item (Join-Path $currentDir dflogo.ico) "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\df_deploy" -Force # -WhatIf

if (Test-Path "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\df_deploy\df_deploy.psm1") {
Write-Host "Copied DF_Deploy.psm1."
}
else{
Write-Host "Copying of DF_Deploy.psm1 failed." -BackgroundColor red -ForegroundColor white
}
if (Test-Path "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\df_deploy\dflogo.ico") {
Write-Host "Copied dflogo.ico."
}
else{
Write-Host "Copying of dflogo.ico failed." -BackgroundColor red -ForegroundColor white
}
if(Test-Path (Join-Path $currentDir DF_Deploy.psd1)) {
Copy-Item (Join-Path $currentDir DF_Deploy.psd1) "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\df_deploy" -Force # -WhatIf
}
}
}

How to Pass a Variable to PowerShell from a DOS Batch File

Update: Many thanks to @ye110wbeard for a much simpler solution to pass the current working directory to a powershell command from a DOS batch file.

Powershell -executionpolicy remotesigned -file %~dp0\YourScript.ps1.

Scott

------------------------------------------------------

I'm sure there is a better way, but here is a way to pass the current directory for an executing batch file to a PowerShell script, to execute a PowerShell script that is in that directory. Then control returns to the batch file. Thanks to Wes' Puzzling Blog for the tip on the %~dp0 trick!

• Echo the current directory to a text file in the $pshost directory. This is almost always the directory c:\windows\system32\WindowsPowerShell\v1.0.





• The command to do this in the batch files is:
  echo %~dp0 > c:\windows\system32\WindowsPowerShell\v1.0\currentBatchPath.txt





• Now start the PowerShell session, read the file contents into a variable. The variable will end in a backslash, so use substring to parse out all but the last character. Then use the Set-Location to change to the batch directory, and execute the PowerShell script.





• The (ugly) command looks like this:





• powershell -executionpolicy remotesigned $currentBatchPath=get-content c:\Windows\System32\WindowsPowerShell\v1.0\currentBatchPath.txt;Set-Location $currentBatchPath.substring(0,($currentBatchPath.Length-1));.\YourPowerShellScript.ps1