PowerShell: Finding commands (functions) with Risk Mitigation capabilities

Yesterday I was talking to a workshop about implementing Risk Mitigation capabilities into your own Advanced Functions in PowerShell. This got me thinking about how to identify the commands that have that capability enabled. More importantly the configuration of the ConfirmImpact setting.

Background:
If you don't know PowerShell uses a number of inputs when determining how the Risk Mitigation capabilities are applied.
For the WhatIf capability it is purely the presence of the -WhatIf switch parameter.
For the Confirm capability it is a combination of the ConfirmImpact value in the [CmdLetBinding()] attribute and the $ConfirmPreference variable value. Or the presence of -Confirm parameter

The following command we show all commands with the RiskMitigation capability.

Get-Command | Where-Object {$PsItem.Parameters.Keys.Count -gt 0 -and $PsItem.Parameters.ContainsKey('WhatIf')}

We can extend this further to retrieve the ConfirmImpact value however this is not exposed through a Parameter on the command object and can only be retrieved from the source definition. Unfortunately for CmdLets this is not exposed as they are compiled. This will work though for Functions so you can check your own commands etc.

Get-Command -CommandType Function | `
   Where-Object {$PsItem.Parameters.Keys.Count -gt 0 -and $PsItem.Parameters.ContainsKey('WhatIf')} | `
   Select-Object -Property Name,CommandType,@{Name="Impact";Expression={[Regex]::Match($PSItem.Definition, "(ConfirmImpact='(?<impact>.{1,})'{1})").Groups["Impact"].Value}}

This pipeline command uses the RegEx type accelerator and Match static member but similar could be achieved with the -Match operator.


Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Parsing SQL ERRORLOG's for errors with PowerShell

I've been finding the need to parse / grep the SQL Server ERRORLOG of late to look for any Errors which have occurred and for that I have been turning to my trusted friend PowerShell.

The cmdlet that gives us all the power here is Select-String

I will admit the hardest part about the use of the Select-String cmdlet for me was learning RegEx, but once you have the basics it is very powerful and this approach could be used for any number of applications.

So at the very basic level the following command can be used to report the lines with errors from the latest SQL ERRORLOG

Select-String -Path .\ERRORLOG -Pattern "(error:)\s\d{1,6}"

You could even use a wildcard in the path parameter to look for historical files, but for my need I was only interested in the current file.

Now that is all good and well but SQL Server doesn't record the description of the error on the same line as the error number, so I needed a way to pull the next line from the file as well. For this I used the powershell pipeline to then loop through each of the matched objects returned by Select-String using the Foreach-Object cmdlet and within the scriptblock of that cmdlet I first output the line we retrieved with Select-String, and then use Get-Content to pull in the details from the file and retrieve only the line number I am interested in (remember the Get-Content is a zero based array so I don't have to do anything with the line number from the Select-String matched object to get the next line.... think about it and you will understand).

Select-String -Path .\ERRORLOG.1 -Pattern "(error:)\s\d{1,6}" | Foreach-object {Write-Host "$($_.filename):$($_.linenumber):$($_.line)"; Write-Host "$($_.filename):$($_.linenumber+1):$((Get-Content "$($_.filename)")[$_.linenumber])"}

The performance of this one-line approach is not the greatest because it has to run the Get-Content cmdlet for each matched line. So if you are looking for speed this two-line approach has you covered there. The only downside is that if you change the file name you are looking at then you have to update this twice, where as the one line approach passes that value along the pipeline.

$filedata = (Get-Content .\ERRORLOG)
Select-String -Path .\ERRORLOG -Pattern "(error:)\s\d{1,6}" | Foreach-object {Write-Host "$($_.filename):$($_.linenumber):$($_.line)"; Write-Host "$($_.filename):$($_.linenumber+1):$($filedata[$_.linenumber])"}

Now if you were processing multiple files then you could do something like this multiline or scripted version.

$filelist = Get-ChildItem .\ERRORLOG*;
Foreach ($file in $filelist)
{
$filedata = (Get-Content $file.Name);
Select-String -Path $file.Name -Pattern "(error:)\s\d{1,6}" | Foreach-object {Write-Host "$($_.filename):$($_.linenumber):$($_.line)"; Write-Host "$($_.filename):$($_.linenumber+1):$($filedata[$_.linenumber])"}
} 

Of if you want to execute that from the powershell console then the two-line approach would be:

$filelist = Get-ChildItem .\ERRORLOG*;
Foreach ($file in $filelist){$filedata = (Get-Content $file.Name);Select-String -Path $file.Name -Pattern "(error:)\s\d{1,6}" | Foreach-object {Write-Host "$($_.filename):$($_.linenumber):$($_.line)"; Write-Host "$($_.filename):$($_.linenumber+1):$($filedata[$_.linenumber])"}}

Of if you are looking to summarize the number of errors occuring from all of the ERRORLOG files then the following provides an insight into that

Select-String -Path ERRORLOG* -Pattern "(error:)\s\d{1,6}" | Group-Object -Property {$_.Line.Substring(0,10)},{$_.Matches.Value} | Sort-Object Name

Happy investigations :)

If you are looking for a RegEx reference here are the ones I use:
http://www.regular-expressions.info/


Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.