Darren’s PowerShell Snippets Volume 2

This is my PowerShell Snippets Volume 2. A collection of PowerShell commands for tasks that I don’t perform often and can’t recall easily from memory. Those ‘I know I’ve worked this out before’ type moments. Volume 1 is available here.

A quick recap.

I live in PowerShell and my memory is pretty good. There are a number of common PowerShell commands and one-liners or functions that I use a lot and I can remember them. However, then there are the ones I use less regularly and I often find myself trying to recall the last time I used them in a script, in order to locate that script just to get those couple of lines. So I’m posting a bunch of them in this post, if for nothing else to help me find them quickly.

PowerShell Azure Functions Query Parameters

When using an Azure PowerShell v1 Function with GET Query Parameters the syntax to retrieve the query parameters is;

$req_query_parameterName

So if your Azure Function URI looked like this;

Invoke-RestMethod -Method GET -Uri 'https://yourFunctionPlan.azurewebsites.net/api/yourFunc?code=12345678dyQ==&api=/find-users&method=get'

then in your Azure PowerShell Function you could get the values passed for API and Method with;

$api = $req_query_api
$method = $req_query_method

To then accept parameters via GET and POST methods in an Azure Function the following will suffice;

# POST method: $req
$requestBody = Get-Content $req -Raw | ConvertFrom-Json
if ($requestBody){
   $apiEndPoint = $requestBody.api
   $apiMethod = $requestBody.method
} else {
   # GET method
   $apiEndPoint = $req_query_api
   $apiMethod = $req_query_method
}

City, State, Country to Timezone

Using Cities to Time Zones and Timezone Lookup data from Geonames it is possible to calculate in PowerShell what the current time is elsewhere relative to your current location. Download the time zone reference files from here. I use the 15000 Cities file as the All Countries file is too big. If 15000 Cities doesn’t provide enough data maybe look to put All Countries in a Database. The Timezones file is then used to determine the offset for the city.

# Load Cities
$cities1500 = Import-Csv -Delimiter "`t" -Path 'C:\data\cities15000\cities15000.txt' -Encoding UTF8 -Header 'geonameid','name','asciiname','alternatenames','latitude','longitude','feature class','feature code','country code','cc2','admin1 code','admin2 code','admin3 code','admin4 code','population','elevation','dem','timezone','modification date' -ErrorAction SilentlyContinue
$tzLookup = $cities1500 | Where-Object {$_.name -eq "Auckland"} | Select-Object timezone

# Load Timezones
$timeZones = Import-Csv -Delimiter "`t" -Path 'C:\Users\Darren Robinson\Downloads\cities15000\Timezones.txt' -Encoding UTF8 -ErrorAction SilentlyContinue
# Find Timezone for Lookup City from above
$tzResult = $timeZones | Select-Object | Where-Object {$_.TimeZoneId -eq $tzLookup.timezone}

# NYC
$tzLookup = $cities1500 | Where-Object {$_.name -like "New York*"} | Select-Object timezone
$tzResult = $timeZones | Select-Object | Where-Object {$_.TimeZoneId -eq $tzLookup.timezone}

# Raw Time
$currentRemoteTime = $dateTimeNow.ToUniversalTime().AddHours($tzResult.'rawOffset (independant of DST)')

# Daylight Savings Time
$currentRemoteTime = $dateTimeNow.ToUniversalTime().AddHours($tzResult.'DST offset 1. Jul 2019')

Converting from Unix Time to Local Time

function Convert-UnixTime {
    Param(
        [Parameter(Mandatory = $true)][int32]$unixDate
    )
     
    $orig = (Get-Date -Year 1970 -Month 1 -Day 1 -hour 0 -Minute 0 -Second 0 -Millisecond 0)        
    $timeZone = Get-TimeZone
    $utcTime = $orig.AddSeconds($unixDate)
    $localTime = $utcTime.AddHours($timeZone.BaseUtcOffset.Hours)
    # Return local time
    return $localTime
}

Unblock Downloaded Files (Recursively)

Get-ChildItem -Path 'C:\Downloads\' -Recurse | Unblock-File

Azure Function Application Timezone Settings

Set your Azure Function App Timezone to your local timezone so that CRON Schedules and DateTime Functions are relative to your location. Get the Timezone Location info from Microsoft Time Zone Index and set the WEBSITE_TIME_ZONE Application setting on your Function App.

Azure Function Application Settings for Timezone

Skip SSL Certificate Checks

This is useful when connecting to local/development sites that have Self Signed Certs.

# Allow connection with the Unifi Self Signed Cert
add-type @"
using System.Net;
using System.Security.Cryptography.X509Certificates;
public class TrustAllCertsPolicy : ICertificatePolicy {
    public bool CheckValidationResult(
        ServicePoint srvPoint, X509Certificate certificate,
        WebRequest request, int certificateProblem) {
        return true;
    }
}
"@

[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Ssl3, [Net.SecurityProtocolType]::Tls, [Net.SecurityProtocolType]::Tls11, [Net.SecurityProtocolType]::Tls12

Enumerating Oracle Field Types

$reader.GetDecimal(fieldNumber), $reader.GetString(fieldNumber), $reader.GetDateTime(fieldNumber)

[int]$fields = 0
$fieldCount = $reader.FieldCount

do {
   $($reader.GetFieldType($fields))
   $fields++
} while ($fields -lt $fieldCount)

Multi-Value Fields in an Oracle SQL Column

$reader.GetString(14).Split([string[]]"`r`n", [StringSplitOptions]::None)