Powershell 7 on Ubuntu, the Weird Way?

So, I work in Powershell for most everything, in a 99.999% Windows environment, but recently gained access to spin up development servers. Lo and behold, there were Linux servers available. I am a Debian/Ubuntu guy, so I was able to get a Run Level 3 Ubuntu server up in no time. My intention is to test Powershell 7 with my Windows DEV environment, but naturally, there were some hurdles.

First, I try to get Powershell 7, per the instruction at Microsoft.

# Download the Microsoft repository GPG keys
wget -q https://packages.microsoft.com/config/ubuntu/16.04/packages-microsoft-prod.deb

# Register the Microsoft repository GPG keys
sudo dpkg -i packages-microsoft-prod.deb
# nothing happens

# Update the list of products
sudo apt-get update
#dpkg: error processing archive packages-microsoft-prod.deb (--install):
# cannot access archive: No such file or directory
#Errors were encountered while processing:
# packages-microsoft-prod.deb

# Install PowerShell
sudo apt-get install -y powershell
#Reading package lists... Done
#Building dependency tree
#Reading state information... Done
#E: Unable to locate package powershell

# Start PowerShell
pwsh
#No command 'pwsh' found, did you mean:
# Command 'posh' from package 'posh' (universe)
# Command 'push' from package 'heimdal-clients' (universe)
# Command 'pdsh' from package 'pdsh' (universe)
# Command 'ppsh' from package 'ppsh' (universe)

#pwsh: command not found

Clearly that did not work. Sadly, wget and/or packages.microsoft.com are blocked from my new server. wget exists, but there is some conflict. Hmm.

wget -S --spider https://packages.microsoft.com/config/ubuntu/16.04/packages-microsoft-prod.deb
Spider mode enabled. Check if remote file exists.
--2019-08-19 14:40:34--  https://packages.microsoft.com/config/ubuntu/16.04/packages-microsoft-prod.deb
Resolving packages.microsoft.com (packages.microsoft.com)... 40.76.35.62
Connecting to packages.microsoft.com (packages.microsoft.com)|40.76.35.62|:443... failed: Connection refused.

But wait.. I was able to download Powershell 7 for Windows, so why not just download the .deb file and do it manually? One download and an sftp session later, and here I go:

#Use the actual package name
sudo dpkg -i powershell_6.2.0-1.ubuntu.16.04_amd64.deb
sudo apt-get install -f

Well, that was easy. Hey, how come...?

$ which powershell
$ which powershell-preview
$ which pwsh

I guess I need to find it? I am a bit lazy, so:

sudo find / -name pwsh
/opt/microsoft/powershell/7-preview/pwsh

So, add that to your PATH variable:

$ PATH=$PATH:/opt/microsoft/powershell/7-preview
$ echo $PATH
/home/vdev2/bin:/home/vdev2/.local/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/opt/microsoft/powershell/7-preview
$ which pwsh
/opt/microsoft/powershell/7-preview/pwsh

So...

$ pwsh
PowerShell 7.0.0-preview.2
Copyright (c) Microsoft Corporation. All rights reserved.

https://aka.ms/powershell
Type 'help' to get help.

PS /home/dave>

That whole copyright/help section is a bit of a pain, though. Doing a quick info pwsh however, show me we can throw standard Powershell arguments at the Linux version.

$ pwsh -NoLogo
PS /home/vdev2>

So, we can change our .bashrc file to contain that switch and never see it again, and add an alias so we can use powershell, if we want:

alias pwsh='pwsh -NoLogo'
alias powershell='pwsh -NoLogo'

Then reload our profile, and we are done.

$ exec bash
$ pwsh
PS /home/vdev2>

Viola!

Linux Powershell $PROFILE

One of my favorite things in Windows Powershell is the $PROFILE. I always refer to the How-To Geek article for creating it.

But what about $PROFILE in Linux Powershell?

PS /home/david> Test-Path $PROFILE
False
PS /home/david> New-Item -Path $PROFILE -Type File -Force

    Directory: /home/david/.config/powershell

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
------          3/29/19  10:26 AM              0 Microsoft.PowerShell_profile.ps1

PS /home/david> $PROFILE
/home/david/.config/powershell/Microsoft.PowerShell_profile.ps1


That was easy, but what do we do with this?

Well, for one thing, I notice that my terminal while in Powershell is named "Untitled".

That's an easy fix:

PS /home/david> Set-Content -Path $PROFILE -Value '$Host.UI.RawUI.WindowTitle = "Powershell"'
PS /home/david> Get-Content $PROFILE
$Host.UI.RawUI.WindowTitle = "Powershell"
PS /home/david> .$PROFILE

Now it looks like a shell should:

You could also add a function, allowing you to rename the terminal on demand. Add to your $PROFILE in an editor:

function Rename-Shell{
    param([string]$Name)
    $Host.UI.RawUI.WindowTitle = $Name
}


Then reload your $PROFILE and add a new title:

PS /home/david> .$PROFILE
PS /home/david> Rename-Shell -Name 'My Terminal'


Your  $PROFILE loads every time you start Powershell and imports the contents. If you change the $PROFILE you can reload it by "dot sourcing" (the .$PROFILE mentioned above). You can set variables, create functions, assign aliases... $PROFILE is a Powershell script, so if you can script it, you can add it.

Linux Powershell Issues: History

So, my Mint system has Powershell. "Let's see how it works," I say.

david@mint ~ $ powershell
PowerShell 6.1.3

https://aka.ms/pscore6-docs
Type 'help' to get help.

PS /home/david> Get-Location
Error reading or writing history file '/home/david/.local/share/powershell/PSReadLine/ConsoleHost_history.txt': Access to the path '/home/david/.local/share/powershell/PSReadLine/ConsoleHost_history.txt' is denied.
ưm
Path
----
/home/david


Well, that's no good. It ran the Get-Location command, but I certainly don't want to see all that red every time I perform an action in the shell. Fortunately, the error shows us where our history file is.

david@mint ~ $ ls -l /home/david/.local/share/powershell/PSReadLine/ConsoleHost_history.txt
-rw-r--r-- 1 root root 336 Mar 28 11:04 /home/david/.local/share/powershell/PSReadLine/ConsoleHost_history.txt


Why does root own my history file? I installed Powershell as root, but that still seems odd. Well, there's no going back, so let's take over the file.

david@mint ~ $ sudo chown david /home/david/.local/share/powershell/PSReadLine/ConsoleHost_history.txt
[sudo] password for david:
david@mint ~ $ sudo chgrp david /home/david/.local/share/powershell/PSReadLine/ConsoleHost_history.txt
david@mint ~ $ ls -l /home/david/.local/share/powershell/PSReadLine/ConsoleHost_history.txt
-rw-r--r-- 1 david david 336 Mar 28 11:04 /home/david/.local/share/powershell/PSReadLine/ConsoleHost_history.txt


Now, back to the shell:

david@mint ~ $ powershell
PowerShell 6.1.3
Copyright (c) Microsoft Corporation. All rights reserved.

https://aka.ms/pscore6-docs
Type 'help' to get help.

PS /home/david> Get-Location

Path
----
/home/david

PS /home/david> Get-History

  Id CommandLine
  -- -----------
   1 Get-Location

Sha-Bang Your Scripts

Sometimes, Windows can be a friendly scripting environment. You write a batch file, and you run it. You write a Powershell script, and provided your Execution Policy allows it, you run it. If it is a batch file, you can even double-click it in the GUI. Powershell, however, you either need to run in its shell, or wrap it in a batch script. The bash shell works a bit differently.

The Sha-Bang (Sharp Bang) declares to the shell what is needed to execute the script. A bash script (example.sh) would normally look like:

#!/bin/sh
echo "Hello, World!"


You would then add the executable flag:

chmod +x example.sh

The same can be done with other interpreters, such as python or perl:

#!/usr/bin/env python
print("Hello, World!")


Or:

#!/usr/bin/perl -w
print "Hello, World!\n"


The same can be done with Powershell on linux, provided your shell knows where Powershell lives.

$ which powershell
/snap/bin/powershell


If you get a response, then Powershell is in your env PATH, so you can have:

#!/usr/bin/env powershell
Write-Host "Hello, World!"


Powershell users in Windows will note that just as running a local script, you need a dot-slash:

$ ./example.ps1
Hello, World!

Sqlite3 Export to JSON

I create custom scripted Detection Methods for SCCM utilizing JSON. Powershell handles JSON nicely, particularly if converting from an object. Powershell will do the necessary escaping of characters, which can be missed when manually creating JSON, or when you know there will be many characters to escape. For example, here is some data which would need escaping, and its resultant JSON:

PS A:\> $Example = @"
>> localpath,registry,share,text
>> C:\Temp,HKLM:\SOFTWARE,\\thatshare\me,use a`ttab
>> "@
PS A:\> $Example
localpath,registry,share,text
C:\Temp,HKLM:\SOFTWARE,\\thatshare\me,use a     tab
PS A:\> $Example | ConvertFrom-Csv | ConvertTo-Json
{
    "localpath":  "C:\\Temp",
    "registry":  "HKLM:\\SOFTWARE",
    "share":  "\\\\thatshare\\me",
    "text":  "use a\ttab"
}

My Detection Methods will often look for file checksums, which means a path to the file and the expected checksum.To avoid mistakes, and to make it easier on me, I create a SqLite3 database with the items for my JSON. Exporting from SqLite3 can be sent to a CSV file, and then parsed into JSON. A simple database example:

PS A:\> sqlite3 .\example.db
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> .mode line
sqlite> SELECT * FROM example;
   name = David
twitter = dbsteimle
    url = rhymeswithtimely.blogspot.com

   name = Commander Candy
twitter = codingComander
    url = codingcommanders.com
sqlite> .quit

Now, a new trick to me it utilizing temporary files. I am using the dot NET method, which is usable in Linux Powershell as well (your mileage may vary). You can create a temp file with [System.IO.Path]::GetTempFileName(). To use that file, you want to assign it to a variable.

PS A:\> $TempCsv = [System.IO.Path]::GetTempFileName()
PS A:\> $TempCsv
C:/Users/david/AppData/Local/Temp/tmp47AD.tmp

Next, because SqLite3 does not use \ character in its paths, we need to change them to /.

PS A:\> $TempCsv = $TempCsv.Replace("\","/")
PS A:\> $TempCsv
C:/Users/david/AppData/Local/Temp/tmp47AD.tmp

Now we can create our SQL commands. This could also be a file, but I will use a here-string instead.

PS A:\> $TempSql = @"
>> .headers on
>> .mode csv
>> .output $TempCsv
>> SELECT name,
>>        twitter,
>>        url
>> FROM example;
>> .quit
>> "@
PS A:\> $TempSql
.headers on
.mode csv
.output C:/Users/david/AppData/Local/Temp/tmp47AD.tmp
SELECT name,
       twitter,
       url
FROM example;
.quit

Notice our converted $TempCsv value is in the $TempSql here-string.

Now, pipe the $TempSql into SqLite3:

PS A:\> $TempSql | sqlite3 .\example.db

Our $TempCsv frile will now have the output from SqLite3 in CSV format.

PS A:\> gc $TempCsv
name,twitter,url
David,dbsteimle,rhymeswithtimely.blogspot.com
"Commander Candy",codingComander,codingcommanders.com

We can now use this CSV formatted data, but must convert it to a Powershell Object.

PS A:\> Get-Content $TempCsv | ConvertFrom-Csv | ConvertTo-Json
[
    {
        "name":  "David",
        "twitter":  "dbsteimle",
        "url":  "rhymeswithtimely.blogspot.com"
    },
    {
        "name":  "Commander Candy",
        "twitter":  "codingComander",
        "url":  "codingcommanders.com"
    }
]

Powershell on Linux

Process from https://websiteforstudents.com/install-microsoft-powershell-core-for-linux-on-ubuntu-16-04-18-04/

The only process that worked for me, however, was the snapd approach:

sudo apt update
sudo apt install snapd
sudo snap install powershell --classic


One issue, though, is that I must sudo powershell to launch.

EDIT: to use powershell as a normal user, with the snap install, you need to add /snap/bin to your path

david@mybox ~ $ sudo which pwsh
/snap/bin/pwsh
david@mybox ~ $ PATH=$PATH:/snap/bin

---

Distributor ID: LinuxMint
Description: Linux Mint 18.3 Sylvia
Release: 18.3
Codename: sylvia

Name                           Value
----                           -----
PSVersion                      6.1.3
PSEdition                      Core
GitCommitId                    6.1.3
OS                             Linux 4.15.0-46-generic #49~16.04.1-Ubuntu SMP Tue Feb 12 17:45:24 UTC 2019
Platform                       Unix
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Using Powershell to Get Objects from Sqlite3

As discussed in SQLite3 and Powershell Redirection, we do not need to add any special Powershell tools to access sqlite3 if we utilize piping.

Versioning on my system:

SQLite version 3.24.0 2018-06-04 19:24:41
PSVersion 5.1.14393.2791

Note: I create an Alias for sqlite3:

PS A:\sqlite_to_csv> Set-Alias -Name sqlite3 -Value A:\sqlite3\sqlite3.exe


Creating an Object in Powershell is generally the best way to utilize data, and in my case I need an easy way to generate JSON files, which Powershell can be quite helpful at.

First, let's make a sample database:

PS A:\sqlite_to_csv> sqlite3 sample.db
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> CREATE TABLE sample (
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> name TEXT,
   ...> twitter TEXT,
   ...> blog TEXT
   ...> );
sqlite> INSERT INTO sample
   ...> (name,twitter,blog)
   ...> VALUES
   ...> ('David Steimle','dbsteimle','rhymeswithtimely.blogspot.com');
sqlite> SELECT * FROM sample;
1|David Steimle|dbsteimle|rhymeswithtimely.blogspot.com


Now we have something to query.

First, we want to create the header, which will provide output in CSV mode with headers. For this I use a Here-String.

PS A:\sqlite_to_csv> $QuerySetup = @"
>> .mode csv
>> .headers on
>>
>> "@


Then we can define our query; we will do a simple SELECT only, for now, and combine it with our query setup.

PS A:\sqlite_to_csv> $Query = "SELECT * FROM sample;"
PS A:\sqlite_to_csv> $MySql = "$QuerySetup$Query"
PS A:\sqlite_to_csv> Write-Host $MySql
.mode csv
.headers on
SELECT * FROM sample;


We then assign our result to an Object with pipes:

PS A:\sqlite_to_csv> $MyObject = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv
PS A:\sqlite_to_csv> $MyObject | Format-List

id      : 1
name    : David Steimle
twitter : dbsteimle
blog    : rhymeswithtimely.blogspot.com


This Object is now usable in all the normal ways, such as turning it into JSON (I will output to a file, as that would be my end goal):

PS A:\sqlite_to_csv> $MyObject | ConvertTo-Json | Set-Content sample.json
PS A:\sqlite_to_csv> Get-Content .\sample.json
{
    "id":  "1",
    "name":  "David Steimle",
    "twitter":  "dbsteimle",
    "blog":  "rhymeswithtimely.blogspot.com"
}


While in a basic sense this works for me, I do need the ability to have sub-objects and arrays in my JSON. This means additional tables. The obvious place to start in this example would be with Social Media. First, we will make a table of social media sites:

sqlite> CREATE TABLE socialmedium (
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> name TEXT,
   ...> url TEXT
   ...> );
sqlite> INSERT INTO socialmedium (name,url) VALUES
   ...> ('Twitter','https://twitter.com/');
sqlite> INSERT INTO socialmedium (name,url) VALUES
   ...> ('Instagram','https://instagram.com/');
sqlite> SELECT * FROM socialmedium;
id          name        url
----------  ----------  --------------------
1           Twitter     https://twitter.com/
2           Instagram   https://instagram.co


Then a lookup table with user details:

sqlite> CREATE TABLE socialuser (
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> user INTEGER,
   ...> site INTEGER,
   ...> username TEXT
   ...> );
sqlite> INSERT INTO socialuser (user,site,username) VALUES
...> (1,1,'dbsteimle');
sqlite> SELECT * FROM socialuser;
id user site username
---------- ---------- ---------- ----------
1 1 1 dbsteimle

These tables use the PRIMARY KEY from the sample table as a reference point. To work with out current Object, we need to add a new Property, and assign it as an empty hash table:

PS A:\sqlite_to_csv> $MyObject | Add-Member -NotePropertyName SocialMedia -NotePropertyValue @{}
PS A:\sqlite_to_csv> $MyObject

id          : 1
name        : David Steimle
twitter     : dbsteimle
blog        : rhymeswithtimely.blogspot.com
SocialMedia : {}


We need to use an INNER JOIN in this scenario, since we are looking up values in two lookup tables:

PS A:\sqlite_to_csv> $Query = @"
>> SELECT user,name,url,username
>> FROM socialuser
>> INNER JOIN socialmedium ON socialmedium.id=socialuser.site
>> WHERE user=$($MyObject.id);
>> "@
PS A:\sqlite_to_csv> $MySql = "$QuerySetup$Query"
PS A:\sqlite_to_csv> Write-Host $MySql
.mode csv
.headers on
SELECT user,name,url,username
FROM socialuser
INNER JOIN socialmedium ON socialmedium.id=socialuser.site
WHERE user=1;


Now, pipe through sqlite3:

PS A:\sqlite_to_csv> $MyObject.SocialMedia = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv
PS A:\sqlite_to_csv> $MyObject.SocialMedia

user name    url                  username
---- ----    ---                  --------
1    Twitter https://twitter.com/ dbsteimle


And Conversion to JSON:

PS A:\sqlite_to_csv> $MyObject | ConvertTo-Json | Set-Content sample.json
PS A:\sqlite_to_csv> Get-Content .\sample.json
{
    "id":  "1",
    "name":  "David Steimle",
    "twitter":  "dbsteimle",
    "blog":  "rhymeswithtimely.blogspot.com",
    "SocialMedia":  {
                        "user":  "1",
                        "name":  "Twitter",
                        "url":  "https://twitter.com/",
                        "username":  "dbsteimle"
                    }
}


To best utilize this, we would want a function where we could utilize a known value as a parameter. We may not know that id 1 matches name 'David Steimle', but if we know the database, we might know to look for name="David Steimle"

function Get-MyObject {
param( [string]$Name )

$QuerySetup = @"
.mode csv
.headers on

"@

$Query = "SELECT * FROM sample WHERE name='$Name';"
$MySql = "$QuerySetup$Query"
$Temp = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv

$Temp | Add-Member -NotePropertyName SocialMedia -NotePropertyValue @{}

$Query = @"
SELECT user,name,url,username
FROM socialuser
INNER JOIN socialmedium ON socialmedium.id=socialuser.site
WHERE user=$($Temp.id);
"@
$MySql = "$QuerySetup$Query"
$Temp.SocialMedia = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv

$Temp | ConvertTo-Json | Set-Content ".\$Name.json"

return $Temp
}

PS A:\sqlite_to_csv> Get-MyObject -Name "David Steimle"

id          : 1
name        : David Steimle
twitter     : dbsteimle
blog        : rhymeswithtimely.blogspot.com
SocialMedia : @{user=1; name=Twitter; url=https://twitter.com/; username=dbsteimle}

PS A:\sqlite_to_csv> Get-Content '.\David Steimle.json'
{
    "id":  "1",
    "name":  "David Steimle",
    "twitter":  "dbsteimle",
    "blog":  "rhymeswithtimely.blogspot.com",
    "SocialMedia":  {
                        "user":  "1",
                        "name":  "Twitter",
                        "url":  "https://twitter.com/",
                        "username":  "dbsteimle"
                    }
}

Or, assign the result to a variable:

PS A:\sqlite_to_csv> $DavidSteimle = Get-MyObject -Name 'David Steimle'
PS A:\sqlite_to_csv> $DavidSteimle

id : 1
name : David Steimle
twitter : dbsteimle
blog : rhymeswithtimely.blogspot.com
SocialMedia : @{user=1; name=Twitter; url=https://twitter.com/; username=dbsteimle}


SQLite3 Output to CSV

Info from: http://www.sqlitetutorial.net/sqlite-tutorial/sqlite-export-csv/

Run below in sqlite3, or create myexport.sql

.headers on
.mode csv
.output myexport.csv
SELECT * FROM mytable;
.quit

If myexport.sql, pipe through Powershell:

gc .\myexport.sql | sqlite3 mydatabase.db

If you want to look at the CSV as an object, execute:

$MyObject = gc myexport.csv | ConvertFrom-Csv

More about Powershell and SQLite3 inegration here.

Powershell and Creating Event Logs

While logging to a text file is a good way to capture script activity, creating an event log can be more useful. Using Powershell, you can have a script utilize (or create) an event log with a custom source, and then write events to it. I found this article helpful: How To Use PowerShell To Write to the Event Log.

First, you want to determine what Log Name and custom Source you want to use/create. Since my scripts typically affect applications, I use the Application log, but System might be good in some instances. For this example I will use "SteimleEvents" as my new custom source.

New-EventLog -LogName Application -Source SteimleEvents

We could then verify the Source is working with the log by running:

Get-EventLog -Logname Application -Source SteimleEvents -ErrorAction SilentlyContinue

This is fine the first time, but what if I have a new script which will utilize this Source? I would build logic into the script to check for the Source, and if it does not exist, create it. This can be tricky, because an empty source and a non-existent source give the same error. In our logic below.

EDIT: Note that my writing logic does not match my scriping logic, and the function New-EventLogEntry is required, which is provided below (highlighting red). The entire script flow is included at the end of this post.

$Source = 'SteimleEvents'
if(Get-EventLog -Logname Application -Source $Source -ErrorAction SilentlyContinue){
    # this indicates that the log, and a 
    # log entry were found for the if
    # condition
    New-EventLogEntry -Information -Message "There is an existing event log for $Source"
} else {
    # the if was false, so we try to 
    # create the log/source, and pass the
    # error to a variable
    New-EventLog -LogName Application -Source $Source -ErrorAction SilentlyContinue -ErrorVariable TestSource
    if($TestSource -match 'already registered'){
        # if a match is found, then the log
        # exists, so we log that
        New-EventLogEntry -Information -Message "There is an existing event log for $Source"
    } else {
        New-EventLogEntry -Information -Message "Initializing event log for $Source"
    }

}

Now that we have our log, we can start utilizing it. I have created two functions and a preliminary hashtable for parameter:

# Define basic event parameters
$EventParameters = @{
    'LogName' = 'Application'
    'Source' = $Source
}
# Function to clear added parameters
function Initialize-EventParameters{
    $script:EventParameters = @{
        'LogName' = 'Application'
        'Source' = $script:Source
    }
}
# Function to create an eventlog entry
function New-EventLogEntry{
    param(
        [switch]$Error,
        [switch]$Warning,
        [switch]$Information,
        [string]$Message
    )
    if($Error){
        $EventID = 1113
        $EntryType = 'Error'
    } elseif($Warning){
        $EventID = 1112
        $EntryType = 'Warning'
    } else {
        $EventID = 1111
        $EntryType = 'Information'
    }
    Initialize-EventParameters
    $script:EventParameters += @{
        'EventId' = $EventID
        'EntryType' = $EntryType
        'Message' = $Message
    }
    Write-EventLog @EventParameters
}

The hashtable $EventParameters is created as an initialization in the script-level scope.

The function Initialize-EventParameters is called to reset $EventParameters to its initialized values.

Finally, New-EventLogEntry adds an event log entry. The function accepts three parameters:

• Error
• Warning
• Information
• Message

Including switches for 'EntryType' will make decisions based on priority. I am not great with parameters, so if you call -Error and -Information the decision tree will make your entry an Error. The -Message switch includes what you want the log entry to say. So use of the function would look like:

New-EventLogEntry -Error -Message "Oh no! Something went wrong!"

Or, you could call -ErrorVariable on every commandlet, and if it has a length, log it. Note that not all commandlets return errors, Test-Path does not, but Test-Connection does.

Get-Content C:\Temp\NotARealFile.txt -ErrorVariable result

if($result.Length -gt 0){

    New-EventLogEntry -Error -Message "C:\Temp\NotARealFile.txt not found"

}

Entire Script Section

# Define Source
$Source = "SteimleEvents"
# Define basic event parameters
$EventParameters = @{
    'LogName' = 'Application'
    'Source' = $Source
}
# Function to clear added parameters
function Initialize-EventParameters{
    $script:EventParameters = @{
        'LogName' = 'Application'
        'Source' = $script:Source
    }
}
# Function to create an eventlog entry
function New-EventLogEntry{
    param(
        [switch]$Error,
        [switch]$Warning,
        [switch]$Information,
        [string]$Message
    )
    if($Error){
        $EventID = 1113
        $EntryType = 'Error'
    } elseif($Warning){
        $EventID = 1112
        $EntryType = 'Warning'
    } else {
        $EventID = 1111
        $EntryType = 'Information'
    }
    Initialize-EventParameters
    $script:EventParameters += @{
        'EventId' = $EventID
        'EntryType' = $EntryType
        'Message' = $Message
    }
    Write-EventLog @EventParameters
}
# test for existing event log for this application; if it does not exist, create it
if(Get-EventLog -Logname Application -Source $Source -ErrorAction SilentlyContinue){
    New-EventLogEntry -Information -Message "There is an existing event log for $Source"
} else {
    New-EventLog -LogName Application -Source $Source -ErrorAction SilentlyContinue -ErrorVariable TestSource
    if($TestSource -match 'already registered'){
        New-EventLogEntry -Information -Message "There is an existing event log for $Source"
    } else {
        New-EventLogEntry -Information -Message "Initializing event log for $Source"
    }
}

SQLite3 and Powershell Redirection

I have a terrible memory, and always forget how this goes.

(Full disclosure, I am using Powershell 5.1.14393.2636/Desktop, and SQLite version 3.24.0)

My primary database at work is SQLite3, and my shell is Powershell. Sometimes I need to grab a set of information from a number of systems, or over a period of time, and record them for analysis. I could use a PS-Object for this, but what if my system crashes or is rebooted by IT? If I use a database I can at least get all data up-until the crash/reboot occurs. So, without adding any fancy connectors from github, lets just pipe.

All of these operations could be performed with a pipe, but I created a sample database in sqlite3. Note that I have created an alias to the executable.

PS A:\> sqlite3 .\sample.db
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> .mode column
sqlite> .headers on
sqlite> CREATE TABLE sample (data TEXT);
sqlite> INSERT INTO sample (data) VALUES ('this');
sqlite> SELECT * FROM sample;
data
----------
this
sqlite> .quit

Now, I like to put text into a $() to variableize it in Powershell. Let's insert a new row and verify:

PS A:\> $("INSERT INTO sample (data) VALUES ('is');") | sqlite3 .\sample.db
PS A:\> sqlite3 .\sample.db
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM sample;
data
----------
this
is
sqlite> .quit

How about two more?

PS A:\> $("INSERT INTO sample (data) VALUES ('an');") | sqlite3 .\sample.db
PS A:\> $("INSERT INTO sample (data) VALUES ('example');") | sqlite3 .\sample.db

In the above instances we are only performing an INSERT query. If we wanted to do a SELECT query with some style options, we need to create a file (often with a .sql extension):

PS A:\> vi sample.sql
.mode column
.headers on
SELECT * FROM sample;
:wq

Now, our variableized query is in the form of Get-Content:

PS A:\> $(Get-Content .\sample.sql) | sqlite3 .\sample.db
data
----------
this
is
an
example
PS A:\>

If we want our query in a hashtable, we need to make a few changes. First, we need the mode to be CSV:

PS A:\> vi sample.sql
.mode csv
.headers on
SELECT * FROM sample;
:wq

Next, we need to add another pipe to our string:

PS A:\> $samples = $(Get-Content .\sample.sql) | sqlite3 .\sample.db | ConvertFrom-Csv

This will assign the queries output to a hashtable named $samples.

PS A:\> $samples

data
----
this
is
an
example

PS A:\> $samples[0]

data
----
this

PS A:\>

Let's expand that hashtable a bit, by adding a new column:

PS A:\> $("ALTER TABLE sample ADD COLUMN data2 TEXT") | sqlite3 .\sample.db
PS A:\> $("UPDATE sample SET data2='database' WHERE data='this'") | sqlite3 .\sample.db
PS A:\> $("UPDATE sample SET data2='not' WHERE data='is'") | sqlite3 .\sample.db
PS A:\> $("UPDATE sample SET data2='excellent' WHERE data='an'") | sqlite3 .\sample.db
PS A:\> $("UPDATE sample SET data2='of a database' WHERE data='example'") | sqlite3 .\sample.db

PS A:\> vi .\sample.sql
.mode csv
.headers on
SELECT * FROM sample;

PS A:\> $samples = $(Get-Content .\sample.sql) | sqlite3 .\sample.db | ConvertFrom-Csv
PS A:\> $samples

data    data2
----    -----
this    database
is      not
an      excellent
example of a database

Now, editing that .sql file might be a bit of a pain, especially if you use an external editor, or are working on a remote machine. Two options are available.

First, escape the newlines with `n notation. Not that you must use double quotes:

PS A:\> $query = ".mode column`n.headers on`nSELECT * FROM sample;"
PS A:\> $query
.mode column
.headers on
SELECT * FROM sample;
PS A:\> $query | sqlite3 .\sample.db
data        data2
----------  ----------
this        database
is          not
an          excellent
example     of a datab

Second, you can use a here string:

PS A:\> $query = @"
>> .mode column
>> .headers on
>> SELECT * FROM sample;
>> "@
PS A:\> $query | sqlite3 .\sample.db
data        data2
----------  ----------
this        database
is          not
an          excellent
example     of a datab

For a slightly more practical example, let's grab all the DLL files in C:\Windows\System32, and create a database:

$testfiles = Get-ChildItem C:\Windows\System32\*.dll
PS A:\> $query = @"
>> CREATE TABLE dllfiles (
>> lastwritetime TEXT,
>> length INT,
>> name TEXT
>> );
>> "@
PS A:\> $query | sqlite3 dllfiles.db
PS A:\> $('.schema') | sqlite3 dllfiles.db
CREATE TABLE dllfiles (
lastwritetime TEXT,
length INT,
name TEXT
);

Note that .schema is a command to see table information in SQLite3.

Now, let's loop through $testfiles, and populate our database. My system shows 3229 such files.

PS A:\> foreach($file in $testfiles){
>> $("INSERT INTO dllfiles (lastwritetime,length,name) VALUES ('$($file.lastwritetime)',$($file.length),'$($file.name)');") | sqlite3 dllfiles.db
>> }

So, now we can query those 10 files as above from the database:

PS A:\> $query = @"
>> .mode column
>> .headers on
>> SELECT name,length FROM dllfiles LIMIT 10;
>> "@
PS A:\> $query | sqlite3 dllfiles.db
name               length
-----------------  ----------
aadauthhelper.dll  34816
aadcloudap.dll     425984
aadtb.dll          1122304
AagMmcRes.dll      26112
AboveLockAppHost.  284672
accelerometerdll.  53280
accessibilitycpl.  3825152
accountaccessor.d  322048
AccountsRt.dll     441856
ACCTRES.dll        39936

One use this might serve is to add a column for "new_length" or "delta_length", which could be populated after monthly security updates have been applied. You could then query where length != new_length. You would just change the query above (after adding the column) to:

PS A:\> $testfiles = Get-ChildItem C:\Windows\System32\*.dll
PS A:\> foreach($file in $testfiles){
>> $("UPDATE dllfiles SET new_length=$($file.length) WHERE name='$($file.name)');") | sqlite3 dllfiles.db
>> }

Powershell: Calculated Properties

Posting for reference; a bit a colleague sent me in chat:

Subtle...but important trick...turning literal strings into objects with a property:

'apple','orange','banana' | Select-Object -Property @{ Name = 'FruitName'; Expression = {$_} }

This way, if I had a cmdlet that took -FruitName as a parameter and accepted pipeline input by name...I could use this to map into that parameter...

[...]

In powershell parlance that's called a "calculated property"

[...]

Normally, you'd use it to transform a property. So for instance, say an existing property was in bytes and you wanted it in MB

Name='MB'; Expression = {$_.Bytes / 1MB }

[...]

And then the other use I can think of is if you had objects that had say a property called 'Name' that was actually a service name...but the cmdlet you wanted to pipe into needed '-ServiceName' ...you could use the calculated property trick to make your objects have a 'ServiceName' property.

@{ Name = 'ServiceName'; Expression = {$_.Name} }

Creating pipeable commandlets is a bit out of my wheelhouse. Funny how often I pipe existing commandlets, but do not think of making mine that way.

Working with Your Command Line History

A trick I always forget is utilizing command line history, aside from just up-arrow.

Powershell:

This is all from "How To Geek: How to Use Your Command History in Windows PowerShell", which is a very useful site.

I spend a lot of time in Powershell, but also in PS Sessions with other systems. The up-arrow method is not always great when entering/returning from a PS Session, as it will iterate through all recent commands on your native and remote sessions. Get-History will, however, stick to the current session.

PS A:\> Clear-History
PS A:\> Get-History

  Id CommandLine
  -- -----------
 109 Clear-History

PS A:\> Enter-PSSession remotesystem
[remotesystem]: PS C:\Users\johndoe\Documents> Get-History
[remotesystem]: PS C:\Users\johndoe\Documents> cd C:\Temp\
[remotesystem]: PS C:\Temp> Get-History

  Id CommandLine
  -- -----------
   1 Get-History
   2 cd C:\Temp\

[remotesystem]: PS C:\Temp> exit
PS A:\> Get-History

  Id CommandLine
  -- -----------
 109 Clear-History
 110 Get-History
 111 Enter-PSSession remotesystem

In order to use my history (were it lengthy) I would call Invoke-History -Id n. Let's say I wanted to clear my history:

PS A:\> Invoke-History -Id 109
Clear-History
PS A:\> Get-History

  Id CommandLine
  -- -----------
 113 Clear-History

There is other great info at the linked article.

BASH:

BASH is a bit simpler, and maintains history better than Powershell. A good reference is at ss64.com, an invaluable site for the command line. Their article on BASH history is at: Connamd Line History.

The basics are easy. To see your history, use the history command. It will display the commands and their id number:

$ history
    1  history
    2  cd ~
    3  history
    4  touch somefile.txt
    5  history
    6  cd /c/temp
    7  history

To execute history item 4, simply enter !4 and return. If your history is lengthy, you could use [ctrl]+r and then start typing the command you want to find. This will begin to autocomplete the command with the appropriate text. If you want to find the command's id in your history, pipe history through grep.

$ history | grep -i some
    4  touch somefile.txt
    9  touch somefile.txt
   10  history | grep -i some

RegEx Testing Tools

One of the websites I use most is regex101. It is a great place to test your RegEx against text you provide. You can provide sample text, define your expression, then see the substitution results. What's more, you can login and save your expressions for future use. I used this feature in the past for Rexx scripts. We had a file cleanup section in each script. Rather than re-writing the delete line with each file, I would do a Get-ChildItem, and take out the file info, as so. Which leads to another nice feature; if you save an expression, you can share it with a link. Lastly, you can generate code for your expression in javascript, python, php, c#, and several other languages.

It does not, however, do Powershell...

Luckily I found another; regexhero, which is purely for Powershell regular expressions. I have just started using this for a fairly simple -replace line I was writing. regexhero will do -match, -replace, and -split functions where you provide your target string and expression to run against it.

Sadly, regexhero uses Microsoft Silverlight and reportedly only works on Internet Explorer 9+, so Linux users are left out. So too, apparently, are Microsoft Edge users (though it is possible Silverlight is not properly configured on my Edge browser). The web developer in me would like the site to be more universally available, but is also intrigued by the use of Silverlight.

Now, if I could find a way to look at the peculiarities of vi regex...

Powershell: Get System IP Address

For logging purposes, I needed to get the ethernet ip address for a system. I could parse ipconfig, using regex to find the address based on my subnet, or the simple powershell way with Get-NetIPAddress.

To make things a bit easier, and to avoid issues where your ip address is not what is expected, you could use one of the following:

((Get-NetIPAddress | Where-Object InterfaceAlias -match Ethernet) `
     | Where-Object AddressFamily -Match IPv4).IPAddress

((Get-NetIPAddress | Where-Object InterfaceAlias -match Ethernet) `
     | Where-Object AddressFamily -Match IPv6).IPAddress

((Get-NetIPAddress | Where-Object InterfaceAlias -match Wi-Fi) `
     | Where-Object AddressFamily -Match IPv4).IPAddress

((Get-NetIPAddress | Where-Object InterfaceAlias -match Wi-Fi) `
     | Where-Object AddressFamily -Match IPv6).IPAddress

Obviously, your mileage may vary. For instance, the system I am on now does not obtain IPv6 addresses. My target system does.

How does this work? Get-NetIPAddress returns an object with all of your ip addresses; ethernet, wi-fi, loopback, vpn, vm adapters, etc. You may not be interested in all of these. In my case, I am only interested in the IPv4 ethernet address of a system. Get-NetIPAddress gets me all interfaces. Get-NetIPAddress | Where-Object InterfaceAlias -match Ethernet on my IPv4/IPv6 system gives me:

IPAddress         : aa11::bb22:cc33:dd44:ee55%4
InterfaceIndex    : 4
InterfaceAlias    : Ethernet
AddressFamily     : IPv6
Type              : Unicast
PrefixLength      : 64
PrefixOrigin      : WellKnown
SuffixOrigin      : Link
AddressState      : Preferred
ValidLifetime     : Infinite ([TimeSpan]::MaxValue)
PreferredLifetime : Infinite ([TimeSpan]::MaxValue)
SkipAsSource      : False
PolicyStore       : ActiveStore

IPAddress         : 192.168.0.2
InterfaceIndex    : 4
InterfaceAlias    : Ethernet
AddressFamily     : IPv4
Type              : Unicast
PrefixLength      : 24
PrefixOrigin      : Dhcp
SuffixOrigin      : Dhcp
AddressState      : Preferred
ValidLifetime     : 21:43:14
PreferredLifetime : 21:43:14
SkipAsSource      : False
PolicyStore       : ActiveStore

To break this down and get just the IPv4 entry, we pipe our expression again through a new Where-Object:

(Get-NetIPAddress | Where-Object InterfaceAlias -match Ethernet) `
     | Where-Object AddressFamily -Match IPv4

IPAddress         : 192.168.0.2
InterfaceIndex    : 4
InterfaceAlias    : Ethernet
AddressFamily     : IPv4
Type              : Unicast
PrefixLength      : 24
PrefixOrigin      : Dhcp
SuffixOrigin      : Dhcp
AddressState      : Preferred
ValidLifetime     : 21:43:14
PreferredLifetime : 21:43:14
SkipAsSource      : False
PolicyStore       : ActiveStore

To get just the IPv4 address, enclose the entire expression, and then request the property desired.

((Get-NetIPAddress | Where-Object InterfaceAlias -match Ethernet) `
     | Where-Object AddressFamily -Match IPv4).IPAddress

192.168.0.2

Passing a Variable as a ScriptBlock in Powershell

Recently I was tasked with creating a deployment script which would run an executable with an argument. This is performed monthly, and we have just begun deploying it with SCCM. Originally, we would call the executable with its argument, but we find that the executable hangs and many manual fixes must be performed. For some reason, deployment with SCCM seemed to create this hanging executable situation more often than our previous (ancient) distribution method.

Previously, the unscripted deployment would simply call the executable with the argument. I do not want to rewrite my script every month, so my Powershell script should:

• Accept an argument to pass the executable
• Run the executable as a Job
• Monitor the Job, retrying if it takes too long 

The problem was getting $Command = "my.exe /arg x" into a ScriptBlock. Using this syntax we get:

PS A:\> $Command = "my.exe /arg x"
PS A:\> Start-Job -ScriptBlock{ $Command }

Id  Name  PSJobTypeName  State    HasMoreData  Location   Command
--  ----  -------------  -----    -----------  --------   -------
1   Job1  BackgroundJob  Running  True         localhost  $Command

Obviously, our ScriptBlock is not "$Command", so we need to turn it into an acceptable form. To do this we create a ScriptBlock, and then call it into the Job without -ScriptBlock, as follows:

PS A:\> $Command = [scriptblock]::create("my.exe /arg x")
PS A:\> Start-Job $Command

Id  Name  PSJobTypeName  State    HasMoreData  Location   Command
--  ----  -------------  -----    -----------  --------   -------
3   Job3  BackgroundJob  Running  True         localhost  my.exe /arg x

As this is a monthly item, I would not hardcode the $Command with a static argument, I would instead accept an argument into my Powershell script, and then pass it as so (assigning a value for this example):

PS A:\> $arg = "y"
PS A:\> $Command = [scriptblock]::create("my.exe /arg $arg")
PS A:\> Start-Job $Command

Id  Name  PSJobTypeName  State    HasMoreData  Location   Command
--  ----  -------------  -----    -----------  --------   -------
5   Job5  BackgroundJob  Running  True         localhost  my.exe /arg y