Home

Verify file exists

• Set-Location c:
  $path = "\\server\folder\filename*.txt"
  $FilesExist = test-path $path
  if (-Not ($FilesExist)) {
  	throw "No files to process"
  }

SQL Query with Parameters to avoid SQL Injection, and return results

• $instanceName = "localhost"
  $initialDatabaseName = "master"
  $sqlText = "SELECT @MyVar1 AS Var1;"
  $commandTimeout = 30
  
  $SQLConnection = new-object System.Data.SqlClient.SQLConnection ("Data Source=$instanceName; Integrated Security=SSPI; Initial Catalog=$initialDatabaseName");
  $SqlCommand = new-object System.Data.SqlClient.SqlCommand ($sqlText, $SQLConnection);
  $SqlCommand.Parameters.AddWithValue ("@MyVar1","Str';ing1")
  $SqlCommand.CommandTimeout = $commandTimeout
  
  $SQLConnection.Open();
  $reader = $SqlCommand.ExecuteReader()
  
  $results = @()
  while ($reader.Read())
  {
  	$row = @{}
  	for ($i = 0; $i -lt $reader.FieldCount; $i++) {
  		$row[$reader.GetName($i)] = $reader.GetValue($i)
  	}
  	$results += new-object psobject -property $row            
  }
  
  $SQLConnection.Close();
  
  $results

Generate an empty file of a certain size

• $f = new-object System.IO.FileStream c:\temp\test.dat, Create, ReadWrite
  $f.SetLength(42MB)
  $f.Close()

• http://stackoverflow.com/questions/808005/how-to-generate-file-of-a-determinate-size-in-windows

Try Catch Finally and error handling in PowerShell

• Try
  {
      $AuthorizedUsers = Get-Content \\ FileServer\HRShare\UserList.txt -ErrorAction Stop
  }
  Catch [System.OutOfMemoryException]
  {
      Restart-Computer localhost
  }
  Catch
  {
      $ErrorMessage = $_.Exception.Message
      $FailedItem = $_.Exception.ItemName
      Send-MailMessage -From ExpensesBot@MyCompany.Com -To WinAdmin@MyCompany.Com -Subject "HR File Read Failed!" -SmtpServer EXCH01.AD.MyCompany.Com -Body "We failed to read file $FailedItem. The error message was $ErrorMessage"
      Break
  }
  Finally
  {
      $Time=Get-Date
      "This script made a read attempt at $Time" | out-file c:\logs\ExpensesScript.log -append
  }

• http://www.vexasoft.com/blogs/powershell/7255220-powershell-tutorial-try-catch-finally-and-error-handling-in-powershell

Current DateTime Formatting

• $a = Get-Date -format "yyyy-MM-dd HH:mm:ss"

• https://technet.microsoft.com/en-us/library/ee692801.aspx?f=255&MSPPError=-2147217396

Get password with asterix mask

• $Password = Read-Host -assecurestring "Password: "

Un-encrypt secure string password

• Easy way:

  • $Password = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($Password))

• More secure way:
  • http://blogs.msdn.com/b/fpintos/archive/2009/06/12/how-to-properly-convert-securestring-to-string.aspx

Output to NULL

• ($foo = someFunction) | out-null

Spawn new process

• start-process $cmd $cmdArguments

Powershell file attribute list

• http://blogs.technet.com/b/heyscriptingguy/archive/2011/01/26/use-a-powershell-cmdlet-to-work-with-file-attributes.aspx

Reading Windows Counters and exporting values to PerfMon display graph:

• $CtrList = @(
      "\System\Processor Queue Length",
      "\Memory\Pages/sec",
      "\Memory\Available MBytes",
      "\Processor(*)\% Processor Time",
      "\Network Interface(*)\Bytes Received/sec",
      "\Network Interface(*)\Bytes Sent/sec",
      "\LogicalDisk(C:)\% Free Space",
      "\LogicalDisk(*)\Avg. Disk Queue Length"
      )
  Get-Counter -Counter $CtrList -SampleInterval 5 -MaxSamples 5 | Export-Counter -Path C:\PerfExample.blg -FileFormat BLG -Force

• https://www.simple-talk.com/sysadmin/powershell/powershell-day-to-day-admin-tasks-monitoring-performance/

Get file sizes in subfolders by extension

• $directory = "XXXXX"
  Get-ChildItem -Path $directory -Recurse |
  Where-Object { !$_.PSIsContainer } |
  Group-Object Extension |
  Select-Object @{n="Extension";e={$_.Name -replace '^\.'}}, @{n="Size (MB)";e={[math]::Round((($_.Group | Measure-Object Length -Sum).Sum / 1MB), 2)}}, Count

• http://stackoverflow.com/questions/22616634/determine-recursively-both-count-and-sum-of-all-extensions-in-a-folder

Format numbers

• "{0:N2}" -f ($colItems.sum / 1MB) + " MB"

• https://technet.microsoft.com/en-us/library/ff730945.aspx?f=255&MSPPError=-2147217396