The 2012 Powershell Scripting Game(shows) in review

Over the past few weeks, those around me know that I’ve been talking a lot about Powershell, how much it can improve your life, and about the 2012 Powershell Scripting Games. Nearly everyone I’ve shared with has asked “What are the Scripting Games?”

My answer to them was and will always be “It’s a bunch of geeks learning new stuff by attempting to solve 10 real-world scenarios by using Powershell.” For the less technical, I would follow that up with “It’s kind of like a reality game show only with computers and stuff.” Oddly enough, that made sense to most people, so I’ll continue with that theme as I share a few of my observations below.

“Survivor” it was not

When others around me (both technical and non-technical) heard this was titled “Games”, the near immediate response was “What do you get if you win?” Before feeling compelled to look it up, the first few people got the answer “I don’t know…” I really didn’t know, and I wouldn’t be surprised if others competing would have had a similar response. Never did I feel like the other competitors were in it to beat me – I know I wished no ill will on any of the other competitors. To the contrary – unlike Survivor – I found that those who were participating did their best to help everyone else while not giving up the actual solutions. The Powershell community is a group of people who like sharing knowledge, not a bunch of people who want to out-wit their peers, clamoring to win. Even as the games came to a close, the winners were grateful for their recognition.

“Wheel of Fortune”

Growing up, my Papaw Neier was an avid wheel-watcher. He would watch Wheel of Fortune every night. If we were at his house, it was mandatory to watch it with him. Each of us grandchildren would sometimes randomly scream letters at the TV, but Papaw would always be there to offer sage advice on what letter combinations made the most sense and guide us to the correct solution, never letting on that he knew the answer. These games, as in real life as an IT professional, were a lot like Wheel of Fortune. The initial event description provided the basis – the RSTLN and E. However, it didn’t stop there. We weren’t left to blindly guess with only the letters first put on the board. A lot of the information was available, but it was the comments to the posts that helped to complete the picture. Scripters would ask questions and someone, usually IamMred, would clarify the requirements, but always without giving up too much information as to give away the solution. Without this careful dissemination of information, some of these events would have been difficult at best. This is a good practice to take back into real life – always be ready to help out someone else, but be slow to give up the answer. Encourage learning, not leeching.

“Beat the Clock”

During the first week of the games, I was able to get the scripts turned in within a reasonable time frame, some within the same day. I even won one of the daily prizes donated by O’Reilly Media! As the second week came, life happened. I had to leave the games on the back burner, not knowing whether I’d get back to them or not. As the second week ended and life returned to normal, I realized I still had time to get all the events completed, but it wouldn’t be easy. I had work to be done and leaving the family to fend for themselves favor of the scripting games was not an option. Each evening, after everyone was in bed, I began working feverishly into the early morning (fortunately, Eastern time-zone) to get the events completed just minutes to hours before each of the deadlines. I did my best to make certain that my wife and kids were only moderately affected by my own personal game of “Beat the Clock”. I knew the task, I knew how much time I had to get it done, and I knew no one else could do it for me. As a microcosm of real life – we only have so much time, so use it wisely, do it right, and don’t forget those who love you.

“Dancing with the Stars”

I’ve tried on multiple occasions to get through an entire episode of “Dancing with the Stars” without success. While I’m certain that it has significant entertainment value to others, I have never been able to enjoy watching it. However, the premise of the show seems clear – learn from the expert and you’ll do better. I am incredibly grateful for the time that the expert judges took to not only analyze and run our scripts, but take the time to comment and offer advice in the process. In Event 2, it was suggested to me to avoid using Read-Host and switch to using PromptForChoice() by Boe Prox. I had never heard of this before, but soon realized how cool it was, implementing it in Event 8. In the comments of my Event 3, Bartek Bielawski introduced me to the ConvertToDateTime WMI method, which I have used professionally already. My scripting and therefore my career are now better off having been granted the privilege of “Dancing with the Stars” during the scripting games. These guys are masters of the craft. For as much time as I put in just writing 10 scripts, I can’t imagine how many hours these judged sacrificed to make each of us better at what we do – for free. They deserve an award.

Thanks again to the Scripting Guys, all the judges, and their families for creating an environment where we can all learn to do our jobs better, for free, and have fun doing it. Congratulations to the winners and to everyone, both beginner and advanced who competed.

I look forward to gaining more knowledge and experience next year!

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

PowerShell: Using Exponents and Logs to Format Byte Sizes

In a recent Scripting Games event, I had to format some byte values into their most appropriate size according to how many bytes were present. Anyone who hangs around with me long enough will realize I love numbers and the preciseness that they provide. This is one of many reasons why I enjoy computer programming and especially working with SQL Server. Even more than my love of numbers is my hatred toward duplicating work.

This is one of my longer posts, but I promise some neat stuff if you stick with me on this…

When I started to code for the event, I headed down the path of using the PowerShell switch statement. I had never known that you could use an expression to evaluate a switch – so that was something really cool. This is the function I initially came up with (all the commenting and help removed for brevity):

switch ($a)
{
    #each step in the switch increases by a multiplier of 1024
    {$_ -lt 1024} {"{0:N2} Bytes" -f ($a)}
    {$_ -ge 1024 -and $_ -lt (1048576) } {"{0:N2} KiloBytes" -f ($a/1024)}
    {$_ -ge 1048576 -and $_ -lt (1073741824) } {"{0:N2} MegaBytes" -f ($a/1048576)}
    {$_ -ge 1073741824 -and $_ -lt (1099511627776) } {
        "{0:N2} GigaBytes" -f ($a/1073741824)}
        
    #Stop @ TeraBytes, could be more than 1024 TeraBytes, but that is acceptable
    default {{"{0:N2} TeraBytes" -f ($a/1099511627776)}}
    
}

This was a workable function, but it didn’t feel right. I had to do a lot of typing and we all know we only have so many keystrokes in our lives, so I stepped back and re-evaluated.

At each expression, I was looking at a multiplier of 1024. Like the veil was removed, I realized that these were exponents. Time for the .NET Math Class (pun intended). Among some of the other cool methods of this class is the Pow method. No, we’re not in the original Batman series fighting the super villain, it’s the equivalent of the T-SQL POWER function. I soon had a simple function and all those large nasty numbers replaced with their exponential equivalents. Here is version two:

function Get-Power
{
    
    param([double]$RaiseMe)
    
    [Math]::Pow(1024, $RaiseMe)

}

switch ($a)
{
    #each step in the switch increases by a multiplier of 1024
    {$_ -lt (Get-Power 1)} {"{0:N2} Bytes" -f ($a)}
    {$_ -ge (Get-Power 1) -and $_ -lt (Get-Power 2) } {
        "{0:N2} KiloBytes" -f ($a/(Get-Power 1))}
        
    {$_ -ge (Get-Power 2) -and $_ -lt (Get-Power 3) } {
        "{0:N2} MegaBytes" -f ($a/(Get-Power 2))}
        
    {$_ -ge (Get-Power 3) -and $_ -lt (Get-Power 4) } {
        "{0:N2} GigaBytes" -f ($a/(Get-Power 3))}
        
    #Stop @ TeraBytes, could be more than 1024 TeraBytes, but that is acceptable
    default {"{0:N2} TeraBytes" -f ($a/(Get-Power 4))}
    
}

This worked the same and it was a little cleaner, but it still bothered me. There was a lot of copied and pasted code in there and it was all to determine what text to slap on the end of a quotient. I once again stepped back and realized that it was the switch’s fault. I then made it my goal to eliminate the switch.

In the process, I remembered that 1024 is 2^10… this meant that 1024^2 was 2^20, 1024^3 was 2^30 and so on. Believe it or not, this made my path clear.

A quick Algebra refresher – exponents and logs are functionally the same things, just expressed in a way that makes each valuable for different circumstances. For instance, 2^10=1024 is the functional equivalent of LOG_2(1024) = 10. For our purposes, we will want to know the power of 2 each number being provided is. To find the unknown “power” value in an equation, (the 10 above) the log base is technically irrelevant. With that in mind, we can use the natural logarithm (ln) to determine what power of 2 any number represents.

2^x = 1024 bytes
ln(2^x) = ln(1024 bytes)
ln(2)x = ln(1024 bytes)
x = ln(1024) / ln(2)
x = 6.93 / 0.693
x = 10

Before you think, that’s great, thanks for the math lesson and stop reading - this information really does have some decent uses in PowerShell. Let’s see how this math can be applied to make my function above re-usable and easily maintainable code.

The Math class has a method named Log . When only provided with a single value, this function will use “e” as the base – the natural algorithm which is the functional equivalent of “ln” used above. Armed with this function, we can determine which power of 2 any particular number is. More importantly, this information can eventually be used to format any size of number appropriately.

In the code below, I use PowerShell to extract the closest whole power of 2 that makes up the $byte value by implementing the Floor method.

$PowerOfTwo = [Math]::Floor([Math]::Log($byte)/[Math]::Log(2))

If I now create an array of my “descriptors”, I can use this text to be added to the end of each of the numbers to make it pretty.

$ByteDescriptors = ("B", "KB", "MB", "GB", "TB", "PB", 
                "EB", "ZB", "YB", "WYGTMSB")

With the array prepared, the $PowerOfTwo variable can be divided by 10 (and Floored) to provide the index into the descriptor array.

$DescriptorID = [Math]::Floor($PowerOfTwo/10)

Finally, we use the format method to combine all this information into an output. The $Scale variable is set in the script to be 2. Not only is the $DescriptorID used to determine the description, it is also used as a power of 2 in the divisor with the total byte value as the dividend.

Write-Output ("{0:N$Scale} $($ByteDescriptors[$DescriptorID])" -f (
                $byte / [Math]::Pow(2, ($DescriptorID*10))))

I’ve included the full function below which includes all of these pieces as well as full comments and a few extra parameters. While I may have been able to use the first function, the flexibility that the most recent iteration of this script provides seems worth the effort. Not only do I think this is a neat function, I now have an answer for my kids when they ask “When am I ever going to use this stuff in real life?” :-)

function Format-Byte
{
<#    
    .SYNOPSIS
    Formats a number into the appropriate byte display format.

    .DESCRIPTION
    Uses the powers of 2 to determine what the appropriate byte descriptor
    should be and reduces the number to that appropriate descriptor.
    
    The LongDescriptor switch will switch from the generic "KB, MB, etc."
    to "KiloBytes, MegaBytes, etc."
    
    Returns valid values from byte (2^0) through YottaByte (2^80).
    
    .PARAMETER ByteValue        
    Required double value that represents the number of bytes to convert.
    
    This value must be greater than or equal to zero or the function will error.

    This value can be passed as a positional, named, or pipeline parameter.    
    
    .PARAMETER LongDescriptor
    Optional switch parameter that can be used to specify long names for 
    byte descriptors (KiloBytes, MegaBytes, etc.) as compared to the default
    (KB, MB, etc.) Changes no other functionality.
    
    .PARAMETER Scale
    Optional parameter that specifies how many numbers to display after 
    the decimal place.
    
    The default value for this parameter is 2. 
    
    .EXAMPLE
    Format-Byte 123456789.123
    
    Uses the positional parameter and returns returns "117.74 MB"
    
    .EXAMPLE
    Format-Byte -ByteValue 123456789123 -Scale 0
    
    Uses the named parameter and specifies a Scale of 0 (whole numbers). 
    
    Returns "115 GB"
    
    .EXAMPLE
    Format-Byte -ByteValue 123456789123 -LongDescriptor -Scale 4
    
    Uses the named parameter and specifies a scale of 4 (4 numbers after the
    decimal) 
    
    Returns "114.9781 GigaBytes"
    
    .EXAMPLE
    (Get-ChildItem "E:\KyleScripts")|ForEach-Object{$_.Length}|Format-Byte
    
    Passes an array of the sizes of all the files in the E:\KyleScripts folder
    through the pipeline.
    
    .NOTES
    Author:    Kyle Neier
    Blog: http://sqldbamusings.blogspot.com
    Twitter: Kyle_Neier
    
    Because of the 14 significant digit issue, anything nearing 2^90
    will be marked as WYGTMSB aka WheredYouGetThatMuchStorageBytes. If you
    have that much storage, feel free to find a different function and or
    travel back in time a hundred years years or so and slap me...

#>    

    [CmdletBinding()]
    
    param(
        [parameter(
                Mandatory=$true, 
                Position=0,
                ValueFromPipeline= $true
                
            )]
             #make certain value won't break script
             [ValidateScript({$_ -ge 0 -and 
                 $_ -le ([Math]::Pow(2, 90))})] 
        [double[]]$ByteValue,
        
        [parameter(
                Mandatory=$false, 
                Position=1,
                ValueFromPipeline= $false
                
            )]
        [switch]$LongDescriptor,
        
        [parameter(
                Mandatory=$false, 
                Position=2,
                ValueFromPipeline= $false
                
            )]
            [ValidateRange(0,10)]
             [int]$Scale = 2
        
    )
    
    #2^10 = KB, 2^20 = MB, 2^30=GB...
    begin
    {
        if($LongDescriptor)
        {
            Write-Verbose "LongDescriptor specified, using longer names."
            $ByteDescriptors = ("Bytes", "KiloBytes", "MegaBytes", "GigaBytes", 
                "TeraBytes", "PetaBytes", "ExaBytes", "ZettaBytes", 
                "YottaBytes", "WheredYouGetThatMuchStorageBytes")
        }
        else
        {
            Write-Verbose "LongDescriptor not specified, using short names."
            $ByteDescriptors = ("B", "KB", "MB", "GB", "TB", "PB", 
                "EB", "ZB", "YB", "WYGTMSB")
        }
    }
    
    process
    {
        foreach($byte in $ByteValue)
        {
            #Determine which power of 2 this value is based from
            Write-Verbose "Determine which power of 2 the byte is based from."
            $PowerOfTwo = [Math]::Floor([Math]::Log($byte)/[Math]::Log(2))
            
            #Determine position in descriptor array for the text value
            Write-Verbose "Determine position in descriptor array."
            $DescriptorID = [Math]::Floor($PowerOfTwo/10)

            #Determine appropriate number by rolling back up through powers of 2
            #format number with appropriate descriptor
            Write-Verbose ("Return the appropriate number with appropriate "+
                "scale and appropriate desciptor back to caller.")
                
            Write-Output ("{0:N$Scale} $($ByteDescriptors[$DescriptorID])" -f (
                $byte / [Math]::Pow(2, ($DescriptorID*10))))
            
        }
    }
}

Powershell – Validating an IP Address

 

A task that I have had to do several times over the years is determine if an IP Address string entered from a prompt or imported from a file is indeed valid. Even more, determining if that IP Address is in use once verifying that the IP Address is well formed.

I’ve created the following Powershell function – Test-IPAddressString – to handle both of these tasks.

Either provide a single or several IP Address candidates to this function and it will return a $true if it is valid or a $false if it is not. As an added bonus, if you enable the –FailIfInUse swtich,the function will attempt to ping a valid IP Address. If the address responds to a ping, $false is returned.

function Test-IPAddressString
{
<#
        .SYNOPSIS
        Tests to see if an IP address string is a valid address and can determine
        if IP Address is responding on network.

        .DESCRIPTION
        Implements the .Net.IPAddress classes to validate that the string provided
        can parse into an IP Address. If the parse is successful, the function returns
        boolean $true unless the -FailIfInUse switch is provided.
        
        If -FailIfInUse is specified, a $true is only returned if the IP Address
        does not respond to a ping three times.

        This function supports the -Verbose switch as well.
        
        .PARAMETER IPaddressString
        This is a string value representing the address that you want to test.
        
        This value can be passed either from pipeline or as a parameter.
        
        .PARAMETER FailIfInUse 
        This is a switch parameter. Specifying this in the parameter list will
        cause the IP Address, if successfully parsed, to attempt to be pinged.
        
        A successful ping result will result in a $false value being returned
        if this switch is used.
        
        
        .EXAMPLE
        Test-IPAddressString -IPAddressString "192.168.1.1"
        
        Using full parameter name

        .EXAMPLE
        Test-IPAddressString "192.168.1.1" -FailIfInUse
        
        Using Positional parameters and specifiying to fail test if IP Address responds to ping.
        
        .EXAMPLE
        ("192.168.1.1", "192.123456", "IsThisAnIP") | Test-IPAddressString -Verbose
        
        Passing serveral potential IP Address strings to the function through the pipeline. 
        
        FailIfInUse is ommitted, so no ping attempts will be made on valid IP Addresses.
        
        This function supports the verbose switch. Using this switch will provide you with
        several indicators of progress through the process.
        
        .NOTES
        
        Author:    Kyle Neier
        Blog: http://sqldbamusings.blogspot.com
        Twitter: Kyle_Neier
        
        .LINK
        http://sqldbamusings.blogspot.com/2012/04/powershell-validating-ip-address.html
    #>

    param(
    [parameter(
            Mandatory=$true, 
            Position=0,
            ValueFromPipeline= $true
        )]
        [string]$IPaddressString,
    [parameter(
            Mandatory=$false, 
            Position=1,
            ValueFromPipeline= $false
        )]
        [switch]$FailIfInUse
    )

    process
    {
        [System.Net.IPAddress]$IPAddressObject = $null    
        if([System.Net.IPAddress]::tryparse($IPaddressString,[ref]$IPAddressObject) -and
             $IPaddressString -eq $IPAddressObject.tostring())
        {
            Write-Verbose "$IPaddressString successfully parsed."
            if($FailIfInUse -eq $true)
            {
                $Pinger = new-object System.Net.NetworkInformation.Ping
                $p = 1
                $p_max = 3
                do 
                {
                    Write-Verbose "Attempting to ping $IPaddressString - Attempt $p of $p_max"
                    $PingResult = $Pinger.Send("$IPaddressString")
                    Write-Verbose "Connection Result: $($PingResult.Status)"
                    $p++
                    Start-Sleep -Milliseconds 500

                } until ($PingResult.Status -eq "Success" -or $p -gt $p_max)

                if($PingResult.Status -eq "Success")
                {
                    Write-Verbose "The IP Address $IPAddressString parsed successfully but is responding to ping."
                    Write-Output $false
                }
                else
                {
                    Write-Verbose "The IP Address $IPAddressString parsed successfully and is not responding to ping."
                    Write-Output $true
                }
            }
            else
            {
                Write-Verbose "The IP Address $IPAddressString parsed successfull - No ping attempt made."
                Write-Output $true
            }
        
        }
        else
        {
            Write-Verbose "The IP Address $IPAddressString could not be parsed."
            Write-Output $false
        }
    }
}

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

Powershell – Adding accounts to Local Security Policy

 

One of the challenges I’ve had over the years is figuring out a way to add the SQL Service accounts to the “Perform Volume Maintenance Tasks” and “Lock Pages in Memory” local security policy privileges.

Kendra Little published a post a while back that utilized NTRights.exe. Unfortunately, this is not available for Server 2008R2 and Server Core. I have several clients on which I cannot use NTRights.exe.

I developed this function to handle this task. It takes an export of the current local security policy and evaluates it. When it finds the particular privilege you want to add, it will append the account you provided to that file and re-import that setting.

This function was written to accept pipeline and function properly with Confirm, WhatIf, and Verbose common switches. This function must be run locally on the box you are needing to modify the policy and must be run as an administrator to execute the underlying "secedit” command.

Please feel free to share any comments or suggestions you may have.

function Add-LoginToLocalPrivilege
{
<#
        .SYNOPSIS
Adds the provided login to the local security privilege that is chosen. Must be run as Administrator in UAC mode.
Returns a boolean $true if it was successful, $false if it was not.

.DESCRIPTION
Uses the built in secedit.exe to export the current configuration then re-import
the new configuration with the provided login added to the appropriate privilege.

The pipeline object must be passed in a DOMAIN\User format as string.

This function supports the -WhatIf, -Confirm, and -Verbose switches.

.PARAMETER DomainAccount
Value passed as a DOMAIN\Account format.

.PARAMETER Domain 
Domain of the account - can be local account by specifying local computer name.
Must be used in conjunction with Account.

.PARAMETER Account
Username of the account you want added to that privilege
Must be used in conjunction with Domain

.PARAMETER Privilege
The name of the privilege you want to be added.

This must be one in the following list:
SeManageVolumePrivilege
SeLockMemoryPrivilege

.PARAMETER TemporaryFolderPath
The folder path where the secedit exports and imports will reside. 

The default if this parameter is not provided is $env:USERPROFILE

.EXAMPLE
Add-LoginToLocalPrivilege -Domain "NEIER" -Account "Kyle" -Privilege "SeManageVolumePrivilege"

Using full parameter names

.EXAMPLE
Add-LoginToLocalPrivilege "NEIER\Kyle" "SeLockMemoryPrivilege"

Using Positional parameters only allowed when passing DomainAccount together, not independently.

.EXAMPLE
Add-LoginToLocalPrivilege "NEIER\Kyle" "SeLockMemoryPrivilege" -Verbose

This function supports the verbose switch. Will provide to you several 
text cues as part of the execution to the console. Will not output the text, only presents to console.

.EXAMPLE
("NEIER\Kyle", "NEIER\Stephanie") | Add-LoginToLocalPrivilege -Privilege "SeManageVolumePrivilege" -Verbose

Passing array of DOMAIN\User as pipeline parameter with -v switch for verbose logging. Only "Domain\Account"
can be passed through pipeline. You cannot use the Domain and Account parameters when using the pipeline.

.NOTES
The temporary files should be removed at the end of the script. 

If there is error - two files may remain in the $TemporaryFolderPath (default $env:USERPFORILE)
UserRightsAsTheyExist.inf
ApplyUserRights.inf

These should be deleted if they exist, but will be overwritten if this is run again.

Author:    Kyle Neier
Blog: http://sqldbamusings.blogspot.com
Twitter: Kyle_Neier
#>

    #Specify the default parameterset
    [CmdletBinding(DefaultParametersetName="JointNames", SupportsShouldProcess=$true, ConfirmImpact='High')]
param
    (
[parameter(
Mandatory=$true, 
Position=0,
ParameterSetName="SplitNames")]
[string] $Domain,

[parameter(
Mandatory=$true, 
Position=1,
ParameterSetName="SplitNames"
            )]
[string] $Account,

[parameter(
Mandatory=$true, 
Position=0,
ParameterSetName="JointNames",
ValueFromPipeline= $true
            )]
[string] $DomainAccount,

[parameter(Mandatory=$true, Position=2)]
[ValidateSet("SeManageVolumePrivilege", "SeLockMemoryPrivilege")]
[string] $Privilege,

[parameter(Mandatory=$false, Position=3)]
[string] $TemporaryFolderPath = $env:USERPROFILE
        
)

#Determine which parameter set was used
    switch ($PsCmdlet.ParameterSetName)
{
"SplitNames"
        { 
#If SplitNames was used, combine the names into a single string
            Write-Verbose "Domain and Account provided - combining for rest of script."
            $DomainAccount = "$Domain`\$Account"
        }
"JointNames"
        {
Write-Verbose "Domain\Account combination provided."
            #Need to do nothing more, the parameter passed is sufficient.
        }
}

#Created simple function here so I didn't have to re-type these commands
    function Remove-TempFiles
    {
#Evaluate whether the ApplyUserRights.inf file exists
        if(Test-Path $TemporaryFolderPath\ApplyUserRights.inf)
{
#Remove it if it does.
            Write-Verbose "Removing $TemporaryFolderPath`\ApplyUserRights.inf"
            Remove-Item $TemporaryFolderPath\ApplyUserRights.inf -Force -WhatIf:$false
        }

#Evaluate whether the UserRightsAsTheyExists.inf file exists
        if(Test-Path $TemporaryFolderPath\UserRightsAsTheyExist.inf)
{
#Remove it if it does.
            Write-Verbose "Removing $TemporaryFolderPath\UserRightsAsTheyExist.inf"
            Remove-Item $TemporaryFolderPath\UserRightsAsTheyExist.inf -Force -WhatIf:$false
        }
}

Write-Verbose "Adding $DomainAccount to $Privilege"

    Write-Verbose "Verifying that export file does not exist."
    #Clean Up any files that may be hanging around.
    Remove-TempFiles
    
Write-Verbose "Executing secedit and sending to $TemporaryFolderPath"
    #Use secedit (built in command in windows) to export current User Rights Assignment
    $SeceditResults = secedit /export /areas USER_RIGHTS /cfg $TemporaryFolderPath\UserRightsAsTheyExist.inf

#Make certain export was successful
    if($SeceditResults[$SeceditResults.Count-2] -eq "The task has completed successfully.")
{

Write-Verbose "Secedit export was successful, proceeding to re-import"
        #Save out the header of the file to be imported
        
Write-Verbose "Save out header for $TemporaryFolderPath`\ApplyUserRights.inf"
        
"[Unicode]
Unicode=yes
[Version]
signature=`"`$CHICAGO`$`"
Revision=1
[Privilege Rights]" | Out-File $TemporaryFolderPath\ApplyUserRights.inf -Force -WhatIf:$false
                                    
#Bring the exported config file in as an array
        Write-Verbose "Importing the exported secedit file."
        $SecurityPolicyExport = Get-Content $TemporaryFolderPath\UserRightsAsTheyExist.inf

        #enumerate over each of these files, looking for the Perform Volume Maintenance Tasks privilege
        [Boolean]$isFound = $false
        foreach($line in $SecurityPolicyExport)
{
if($line -like "$Privilege`*")
{
Write-Verbose "Line with the $Privilege found in export, appending $DomainAccount to it"
                            #Add the current domain\user to the list
                            $line = $line + ",$DomainAccount"
                            #output line, with all old + new accounts to re-import
                            $line | Out-File $TemporaryFolderPath\ApplyUserRights.inf -Append -WhatIf:$false
                            
$isFound = $true
            }
}

if($isFound -eq $false)
{
#If the particular command we are looking for can't be found, create it to be imported.
            Write-Verbose "No line found for $Privilege - Adding new line for $DomainAccount"
            "$Privilege`=$DomainAccount" | Out-File $TemporaryFolderPath\ApplyUserRights.inf -Append -WhatIf:$false
        }

#Import the new .inf into the local security policy.
        if ($pscmdlet.ShouldProcess($DomainAccount, "Account be added to Local Security with $Privilege privilege?"))
{
# yes, Run the import:
            Write-Verbose "Importing $TemporaryfolderPath\ApplyUserRighs.inf"
            $SeceditApplyResults = SECEDIT /configure /db secedit.sdb /cfg $TemporaryFolderPath\ApplyUserRights.inf

#Verify that update was successful (string reading, blegh.)
            if($SeceditApplyResults[$SeceditApplyResults.Count-2] -eq "The task has completed successfully.")
{
#Success, return true
                Write-Verbose "Import was successful."
                Write-Output $true
            }
else
            {
#Import failed for some reason
                Write-Verbose "Import from $TemporaryFolderPath\ApplyUserRights.inf failed."
                Write-Output $false
                Write-Error -Message "The import from$TemporaryFolderPath\ApplyUserRights using secedit failed. Full Text Below:
$SeceditApplyResults)"
            }
}
}
else
    {
#Export failed for some reason.
        Write-Verbose "Export to $TemporaryFolderPath\UserRightsAsTheyExist.inf failed."
        Write-Output $false
        Write-Error -Message "The export to $TemporaryFolderPath\UserRightsAsTheyExist.inf from secedit failed. Full Text Below:
$SeceditResults)"
        
}

Write-Verbose "Cleaning up temporary files that were created."
    #Delete the two temp files we created.
    Remove-TempFiles
    
}

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

Verifying Last Successful CheckDB with Powershell and SMO - sorta

A few years ago, I wrote a script that was published on SQLServerCentral.com about how to verify the last successful CheckDB for all the databases on an instance. This was a tremendous help to me and other DBAs as we could now tell when the integrity was valid on our databases. If we kept record of this, we could know exactly to when we would have to restore to be guaranteed a consistent database.

I recently worked on a project where I needed to have Powershell collect this date. Much to my chagrin, SMO does not expose this property. I can get the LastBackupDate, LastLogBackupDate, and LastDifferentalBackupDate as properties of the database class, however LastSuccessfulIntegrityCheck is just not there.

I filed a Microsoft Connect in December, 2011 that requested to have this feature added to the SMO database class - - but that has yet to be responded to by Microsoft.

I knew I had to do something so that Lord Vader wouldn't smack me with the dark side of the force. I eventually just reverted to T-SQL and the ExecuteWithResults database method to return the DBCC DBINFO result set to Powershell for each database. The script below will give you the last successful checkdb for all databases in any instances that you provide in a text file. This script uses a very simple custom object - $iDatabase - and loads these objects up into an array for further processing later. This array could be piped to the ConvertTo-HTML or the ConvertTo-CSV cmdlet to create a more flexible output or even through the Where-Object to only report on those that were out of spec. In this example, however, it just sends all the rows unfiltered to the Format-Table cmdlet.

Be certain to vote for the Microsoft Connect suggestion if you think that this would be a valuable addition to the SMO database class.

#************************
#     LastSuccessfulCheckDB
#    Author:  Kyle Neier, Perpetual Technologies
#    Reports on the last successful checkdb for all databases within all instances provided
#    
#    $InstanceList file should be a file of SQL instances in either server, server,port or server\instancename
#    
#************************

param
(
    [string]$InstanceList = "C:\Users\kneier\Documents\Powershell\InstanceList.txt"
)

# Load SMO assembly
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null;

#Initialize Array to hold new database objects
$iDatabases = @()

#Loop over each instance provided
foreach ($instance in $(get-content $InstanceList))
{
     try
    {
        "Connecting to $instance" | Write-Host -ForegroundColor Blue
        
        $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $instance;
        #How many seconds to wait for instance to respond
        $srv.ConnectionContext.ConnectTimeout = 5
        $srv.get_BuildNumber() | out-Null
    }
    catch
    {
        "Instance Unavailable - Could Not connect to $instance." | Write-Host -ForegroundColor Red
        continue
    }
    
    $srv.ConnectionContext.StatementTimeout = $QueryTimeout
    
    foreach($Database in $srv.Databases)
    {
        #create object with all string properties
        $iDatabase = "" | SELECT InstanceName, DatabaseName, LastSuccessfulCheckDB
        #populate object with known values
        $iDatabase.InstanceName = $srv.Name
        $iDatabase.DatabaseName = $database.Name
        
        try
        {
            #Get date of last successful checkdb
            #executes dbcc dbinfo on database and narrows by dbi_dbcclastknowngood
            $database.ExecuteWithResults('dbcc dbinfo() with tableresults').Tables[0] | `
            ?{$_.Field -eq "dbi_dbccLastKnownGood"}| `
            %{$iDatabase.LastSuccessfulCheckDB = [System.DateTime]$_.Value} -ErrorAction Stop
        }
        catch
        {
            "CheckDB could not be determined for $instance.$database" | Write-Host -ForegroundColor Red
        }
        
        #add the iDatabase object to the array of iDatabase objects
        $iDatabases += $iDatabase
    }
}

#output all the databases as a table for viewing pleasure
$iDatabases | ft

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter