PowerShell Snippets

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
    $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            

Generate an empty file of a certain size

Try Catch Finally and error handling in PowerShell

  • Try
        $AuthorizedUsers = Get-Content \\ FileServer\HRShare\UserList.txt -ErrorAction Stop
    Catch [System.OutOfMemoryException]
        Restart-Computer localhost
        $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"
        "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

Get password with asterix mask

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

Un-encrypt secure string password

Output to NULL

  • ($foo = someFunction) | out-null

Spawn new process

  • start-process $cmd $cmdArguments

Powershell file attribute list

Reading Windows Counters and exporting values to PerfMon display graph:

  • $CtrList = @(
        "\System\Processor Queue Length",
        "\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

Format numbers