Create, insert, and Query SQLite with PowerShell.

So I have C# code that grabs the print records from the Windows event logs on the print servers in order to create reports and easy searches from my web environment. I dump this data into a SQLite database, because I am a control freak and the place where I work is also a control freak. I am thinking of moving the whole thing to PowerShell so I needed to see about using SQLite with PowerShell. I could put this into a big database running as a server, but fuck that shit. This is easy and I don’t have to manage yet another server or deal with server folks, management, etc…

This is working example code that takes you from creating the database, to populating it, and doing a small query.



# Grab the SQLite3 DLL here:
#    https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
#
# PowerShell SQLite DB example.
# C. Nichols <mohawke@gmail.com>, Aug. 2019
# Make sure to change DLL, database, and log file paths.
 
Add-Type -Path "c:\sqlite_tests\System.Data.SQLite.dll" # Change path
 
Function createDataBase([string]$db) {
    Try {
        If (!(Test-Path $db)) {
       
            $CONN = New-Object -TypeName System.Data.SQLite.SQLiteConnection
 
            $CONN.ConnectionString = "Data Source=$db"
            $CONN.Open()
 
            # TEXT as ISO8601 strings ('YYYY-MM-DD HH:MM:SS.SSS')
            # ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, INSERT NULL to increment.
            $createTableQuery = "CREATE TABLE printer_usage (
                                        ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                        printed_dt        TEXT    NULL,
                                        printed_tm        TEXT    NULL,
                                        printed_doc_nm    TEXT    NULL,
                                        printed_doc_sz    INTEGER NULL,
                                        printed_page_cnt  INTEGER NULL,
                                        print_user_id     TEXT    NULL,
                                        print_user_comp   TEXT    NULL,
                                        print_serv_nm     TEXT    NULL,
                                        printer_nm        TEXT    NULL
                                        );"
            $createUniqueIndex = "CREATE UNIQUE INDEX print_idx ON printer_usage(printed_dt, printed_tm, print_user_id, printed_doc_nm);"
 
 
            $CMD = $CONN.CreateCommand()
            $CMD.CommandText = $createTableQuery
            $CMD.ExecuteNonQuery()
            $CMD.CommandText = $createUniqueIndex
            $CMD.ExecuteNonQuery()
 
            $CMD.Dispose()
            $CONN.Close()
            Log-It "Create database and table: Ok"
 
        } Else {
            Log-It "DB Exists: Ok"
        }
 
    } Catch {
        Log-It "Could not create database: Error"
    }
}
 
Function queryDatabase([string]$db, [string]$sql) {
 
    Try {
        If (Test-Path $db) {
 
            $CONN = New-Object -TypeName System.Data.SQLite.SQLiteConnection
            $CONN.ConnectionString = "Data Source=$db"
            $CONN.Open()
 
            $CMD = $CONN.CreateCommand()
            $CMD.CommandText = $sql
 
            $ADAPTER = New-Object  -TypeName System.Data.SQLite.SQLiteDataAdapter $CMD
            $DATA = New-Object System.Data.DataSet
 
            $ADAPTER.Fill($DATA)
 
            $TABLE = $DATA.Tables
 
            ForEach ($t in $TABLE){
                Write-Output $t
            }
 
            $CMD.Dispose()
            $CONN.Close()
 
        } Else {
            Log-It "Unable to find database: Query Failed"
        }
 
    } Catch {
        Log-It "Unable to query database: Error"
    }
}
 
Function insertDatabase([string]$db, [System.Collections.ArrayList]$rows) {
 
    Try {
        If (Test-Path $db) {
       
            $CONN = New-Object -TypeName System.Data.SQLite.SQLiteConnection
 
            $CONN.ConnectionString = "Data Source=$db"
            $CONN.Open()
 
            $CMD = $CONN.CreateCommand()
            #$Counter = 0
            ForEach($row in $rows) {
       
                $sql = "INSERT OR REPLACE INTO printer_usage (ID,printed_dt,printed_tm,printed_doc_nm,printed_doc_sz,printed_page_cnt,print_user_id,print_user_comp,print_serv_nm,printer_nm)"
                $sql += " VALUES (@ID,@printed_dt,@printed_tm,@printed_doc_nm,@printed_doc_sz,@printed_page_cnt,@print_user_id,@print_user_comp,@print_serv_nm,@printer_nm);"
                
                $CMD.Parameters.AddWithValue("@ID", $NULL)
                $CMD.Parameters.AddWithValue("@printed_dt", $row.printed_dt)
                $CMD.Parameters.AddWithValue("@printed_tm", $row.printed_tm)
                $CMD.Parameters.AddWithValue("@printed_doc_nm", $row.printed_doc_nm)
                $CMD.Parameters.AddWithValue("@printed_doc_sz", $row.printed_doc_sz)
                $CMD.Parameters.AddWithValue("@printed_page_cnt", $row.printed_page_cnt)
                $CMD.Parameters.AddWithValue("@print_user_id", $row.print_user_id)
                $CMD.Parameters.AddWithValue("@print_user_comp", $row.print_user_comp)
                $CMD.Parameters.AddWithValue("@print_serv_nm", $row.print_serv_nm)
                $CMD.Parameters.AddWithValue("@printer_nm", $row.printer_nm)
 
                Write-Output $sql
 
                $CMD.CommandText = $sql
                $CMD.ExecuteNonQuery()
                #$Counter += 1
            }
 
            $CMD.Dispose()
            $CONN.Close()
 
            Log-It "Inserted records successfully: Ok"
 
        } Else {
            Log-It "Unable to find database: Insert Failed"
        }
 
    } Catch {
        Log-It "Unable to insert into database: Error"
    }
}
 
Function Log-It([string]$logLine)
{
    $LogPath = "c:\sqlite_tests\sqlite.log" # Change path
    $NewLine = "`r`n"
 
    $Line = "{0}{1}" -f $logLine, $NewLine
    if ($logPath) {
        write-output $Line
        $Line | Out-File $logPath -Append
    } else {
        write-output $Line
    }
}
 
# ******** MAIN ********
 
$DBPath = "c:\sqlite_tests\print_events.sqlite" # Change path
$Rows = New-Object System.Collections.ArrayList
 
$CDate = Get-Date -format "yyyy-MM-dd"
$CTime = Get-Date -format "HH:mm:ss"
 
# Fake Records
$Rows.Add(@{'printed_dt'=$CDate; 'printed_tm'= $CTime; 'printed_doc_nm'='test.txt'; 'printed_doc_sz'=10; 'printed_page_cnt'=10; 'print_user_id'='nich12'; 'print_user_comp'='m123'; 'print_serv_nm'='printA'; 'printer_nm'='l52'})
$Rows.Add(@{'printed_dt'=$CDate; 'printed_tm'= $CTime; 'printed_doc_nm'='test.doc'; 'printed_doc_sz'=20; 'printed_page_cnt'=12; 'print_user_id'='ward32'; 'print_user_comp'='m234'; 'print_serv_nm'='printA'; 'printer_nm'='l67'})
$Rows.Add(@{'printed_dt'=$CDate; 'printed_tm'= $CTime; 'printed_doc_nm'='test.ps1'; 'printed_doc_sz'=30; 'printed_page_cnt'=14; 'print_user_id'='jame67'; 'print_user_comp'='m345'; 'print_serv_nm'='printB'; 'printer_nm'='l87'})
 
$Query = "Select * From printer_usage"
 
# Create Db and Table.
createDataBase $DBPath
 
# Insert records.
insertDatabase $DBPath $Rows
 
# Fetch records.
queryDatabase $DBPath $Query