2022 UETN Tech Summit Presentation

Functional Powershell

Learn a few basic PowerShell tricks like sending emails and querying databases.

Time permitting we will discuss how to schedule PowerShell tasks in Windows Task Scheduler.

Prerequisites

You can use built-in Windows Powershell ISE (easiest option if Windows) or install Visual Studio Code on your workstation (Windows, Mac, *nix)
(Optional) Install Powershell Core unless you just want to stick with built-in "Windows Powershell"
(If you installed VS Code) Install the Powershell extension in VS Code:

Jonathon English

San Juan School District

Play around:

Command line will generally echo back what you input
2+3
"hello" + 4
variables start with $ comments start with #
# assign a value to a variable:
$a = 5
dir command is actually an alias for Powershell cmdlet get-childitem
$a = dir
$a now holds a collection of objects representing all the files in the current directory
$a = dir -file
will get only the files in the current directory
$a[0] will show you the first item in the collection
$a[0] | get-member will show you the different properties and methods
$a[0].basename will show you just the base file name
$a[0].extension will show you only the file extension

Let's start emailing!

#sometimes this is handy in Windows powershell?

#[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

$apikey = 'aa37cc6fd.....e501fd2f30'

$secretkey = ConvertTo-SecureString '...............0' -AsPlainText -Force

$cred = New-Object System.Management.Automation.PSCredential ($apikey,$secretkey)

$sendMailParams = @{

From = 'noreply@w8.net' #if you're going to use above smtp server, address must be from @w8.net

To = 'youremail@yourdomain.org'

Subject = 'Test subject'

Body = 'from the Functional Powershell UETN session. (This body could be html code too if we wanted...)'

SMTPServer = 'in-v3.mailjet.com'

Port = 587

# UseSsl = $true

Credential = $cred

}

Send-MailMessage @sendMailParams

"check $to to see if message was received."

Let's connect to a database!

$server = 'a-........windows.net'

$dbname = 'summit'

$username='sumuser'

$pwrd = '.......2'

$ConnectionString = ("Server={0};User Id={1};Password={2};Database={3};" -f $server,$username,$pwrd,$dbname)

$sql='SELECT * FROM RegDocMaster'

$dt = New-Object System.Data.DataTable

$da = New-Object System.Data.SqlClient.SqlDataAdapter($Sql,$ConnectionString)

$RecordsAffected = $da.fill($dt)

$dt #this will output the data to the console for us to see

GROUP Assignment

Connect to the database
Retrieve records from RegDocMaster
Loop through them and make sure they're all PDFs
If any are not, let's send an email notification to ourselves.

(admin use only)

$server = 'a-.......windows.net'

$dbname = 'summit'

$username='sumuser'

$pwrd = '.......2'

$ConnectionString = ("Server={0};User Id={1};Password={2};Database={3};" -f $server,$username,$pwrd,$dbname)

$sql='SELECT * FROM RegDocMaster'

$dt = New-Object System.Data.DataTable

$da = New-Object System.Data.SqlClient.SqlDataAdapter($Sql,$ConnectionString)

$RecordsAffected = $da.fill($dt)

#1. check out results

#$dt

#initialize an array

$a = @()

# 2. loop through results

ForEach ($row in $dt) {

#$row.pdfurl

#$row.pdfurl.LastIndexOf('.') #place of last . in the string

#if we were working with files we could use get-item and then access the .extension property, but we're working with urls here so

$ext = $row.PdfUrl.Substring($row.pdfurl.LastIndexOf('.')+1)

#$ext

if ($ext -ne 'pdf') {

$a += " $($row.Title) ($($row.PdfUrl)) is not a pdf document"

}

#Body of email

$body = "Hey just wanted to let you know:`n"

#Join our array into a body string

$body += $a -join "`n"

$apikey = 'aa3..........f30'

$secretkey = ConvertTo-SecureString '..........0' -AsPlainText -Force

$cred = New-Object System.Management.Automation.PSCredential ($apikey,$secretkey)

$sendMailParams = @{

From = 'noreply@w8.net' #if you're going to use this smtp server, must be @w8.net

To = 'youremail@yourdomain.com'

Subject = '[System Notification] Guess what?'

Body = $body

SMTPServer = 'in-v3.mailjet.com'

Port = 587

Credential = $cred

}

Send-MailMessage @sendMailParams

"check $to to see if message was received."