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."