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
DarkArtistry.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.com.