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  and Volume 3 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}

Current Time in a different timezone based of current local time

# 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')
Snippet - Remote Time via TZ Lookup

Converting from Unix Time to Local Time

Take a Unix Time string and convert it to local time. Use the function below and all the function with the Unix Time. e.g
Convert-UnixTime(1561849370)
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)

If you have downloaded/unarchived a swath of files you may need to unblock the files for use on you Windows system.
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.

Snippet - Azure Function Application Settings for Timezone
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

When reading data from Oracle using the Oracle Data Provider for .NET you need to specify the field type when reading the data in.
e.g.
$reader.GetDecimal(fieldNumber), $reader.GetString(fieldNumber), $reader.GetDateTime(fieldNumber)
With the Reader stream open the following will provide the field-types for each column being returned.
[int]$fields = 0
$fieldCount = $reader.FieldCount

do {
   $($reader.GetFieldType($fields))
   $fields++
} while ($fields -lt $fieldCount)
Snippet - Oracle Column Field Data Types

Multi-Value Fields in an Oracle SQL Column

When reading data from Oracle using the Oracle Data Provider for .NET you may have a column that contains multiple values. You will want to split the field into a Collection of values. The following will take Column 14 that contains multiple values and split them on  CRLF.
$reader.GetString(14).Split([string[]]"`r`n", [StringSplitOptions]::None)
That’s it for Vol 2 of Darren’s PowerShell snippets. I’ll start compiling others as I search for them and don’t find them in this Vol or Vol 1 or Vol 3.