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