PowerShell: Parse all Text Files in a Directory for a String Pattern

Here’s a snippet of PowerShell that goes through all files in a directory looking for a string. Those files that match the string pattern are then moved to another directory.

Set-Location C:\somewhere
Get-ChildItem -Recurse | Select-String -pattern "Delivery Status Notification" | Group Path | Select -ExpandProperty Name | Move-Item -Destination C:\temp\emails_moved\

Hope this helps!

Posted in PowerShell | Leave a comment

T-SQL: View and Set SQL Server Maximum Memory Usage

Here’s how you can use T-SQL to set the maximum memory to use. In this example, if the server has more than 4GB of RAM, it reduces SQL Server’s usage by 1GB; if it has less than 4GB, it reduces by 512MB.

DECLARE @ServerProperties TABLE (ID INT, Name sysname, Internal_Value INT, VALUE nvarchar(512))
DECLARE @PhsycialServerMemory INT
DECLARE @MaximumMemoryForSql INT
INSERT @ServerProperties EXEC xp_msver
SELECT @PhsycialServerMemory = Internal_Value FROM @ServerProperties WHERE Name = 'PhysicalMemory'
SELECT 'Current Memory Settings'
EXEC sp_configure 'max server memory'
IF @PhsycialServerMemory > 4096
  SET @MaximumMemoryForSql = @PhsycialServerMemory - 1024
  SET @MaximumMemoryForSql = @PhsycialServerMemory - 512 
EXEC sp_configure 'Show Advanced Options', 1
EXEC sp_configure 'max server memory', @MaximumMemoryForSql
SELECT 'New Memory Settings'
EXEC sp_configure 'max server memory'

Hope this helps!

Posted in T-SQL | Leave a comment

PowerShell DSC: Get Product IDs of Installed Products

The PowerShell DSC Package resource requires a ProductId and the exact product name.

If you aren’t sure what product id or product name associated with the software you wish to install, you can try this method:

  1. Install the software manually on a similar target machine
  2. Open a PowerShell command prompt or the PowerShell ISE
  3. Run the following command:
            Get-WmiObject Win32_Product

This command lists most of the installed software on the system. Find your target and record the ProductId and Name. They will need to match your DSC configuration exactly. Next, in your PowerShell DSC configuration, update the Package resource with the values you’ve discovered, e.g.

Package InstallSomeSoftware
  Ensure = "Present"  
  Path  = "D:\DSC\SomeSoftware_v4.4.msi"
  Name = "Some Software"
  ProductId = "ad9b6775-28d2-4ef4-b2e3-58941ea51c26"
  Arguments = "/qn"
  DependsOn = "[Script]DownloadSomeSoftware"

Also note that the Arguments should be whatever your software package requires to run a “silent install.”

Hope this helps!

Posted in PowerShell, PowerShell DSC | Leave a comment

Azure: Run Windows Custom Script Extension with VM Credentials

By default, the Windows Custom Script Extension in Azure will run as Local System.

Here’s how you can pass your VM credentials from your Azure Resource Manager (ARM) template to the custom script extension and ensure they are not easily visible.

First, in your ARM template’s definition of the Custom Script Extension resource, ensure “commandToExecute” is defined in the “protectedSettings” section of the properties.

Note how the username and password of the VM creation are being passed to the command. The admin password is wrapped in double quotes to ensure special characters are escaped correctly on the command line.

"protectedSettings": { 
  "commandToExecute": "[concat('powershell -ExecutionPolicy Unrestricted -File CustomScripts/MyCustomScriptExtension.ps1 -vmAdminUsername ', parameters('adminUsername'), ' -vmAdminPassword \"', parameters('adminPassword'), '\"')]"

Next, in the custom script referenced by the “commandToExecute” above, you use Invoke-Command to run as the user you provided to the script.

# MyCustomScriptExtension.ps1
param (
$password =  ConvertTo-SecureString $vmAdminPassword -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential("$env:USERDOMAIN\$vmAdminUsername", $password)
Write-Verbose -Verbose "Entering Custom Script Extension..."
Invoke-Command -Credential $credential -ComputerName $env:COMPUTERNAME -ArgumentList $PSScriptRoot -ScriptBlock {
  # Elevated custom scripts go here 
  Write-Verbose -Verbose "Entering Elevated Custom Script Commands..."

Note that you can still expose your sensitive information to the execution context if you use elements like Start-Transcript in the script wrapper as it will dump the full command line in clear text to the log.

Hope this helps!

Posted in Azure, PowerShell | Leave a comment

Assign a Drive Letter from the Command Line Using DiskPart

Here’s how you can assign a drive letter to a volume using the command line tool DiskPart. For more information, see this TechNet article.

list volume
select volume n
assign letter F

Hope this helps!

Posted in Batch Files, Windows, Windows 7, Windows Server 2008 R2, Windows Server 2012 | Leave a comment

T-SQL: Trailing Spaces And String Comparison

Here’s a useful tidbit regarding trailing spaces and SQL Server queries. Note the count of the results from the query below. Because SQL Server follows the ANSI/ISO SQL-92 specification, the string ‘test’ and ‘test    ‘ (with spaces at the end) are treated as the same value; only the length will be different. See How SQL Server Compares Strings with Trailing Spaces for more information.

;WITH StringsToTest AS
  SELECT 'test       ' AS test
  SELECT 'test'
  SELECT '      test'
SELECT COUNT(test), test
FROM StringsToTest 

Hope this helps!

Posted in T-SQL | Leave a comment