Excel Automation Using PowerShell and Data from the AdventureWorks2012 Database

In this blog post I will demonstrate how to create the Excel Report below using PowerShell and data from the AdventureWorks2012 database.

 

SalesYTD

 

1. Creating a New Excel Workbook

In this step we will be creating a new Excel Workbook

#Open a new Excel Workbook
$EXCL = New-Object -ComObject Excel.Application
$EXCL.Visible = $true
$WB = $EXCL.Workbooks.Add()

 

2. Rename a Worksheet

In this step we will be renaming “Sheet1” to “Worksheet1”.

#Rename Worksheets
$SHEET = $EXCL.WorkSheets.item("Sheet1")
$SHEET.Name = "Worksheet1"

 

3. Add a new Worksheet

In this step we will be adding a new Worksheet and the name will be “Sheet4”

#Add WorkSheets
$SHEET = $EXCL.WorkSheets.add()
$SHEET = $workbook.ActiveSheet

4. Move Worksheets Order

In this step we will moving worksheet “Sheet3” to be in front of Worksheet “Sheet4”

#Move Worksheets
$SHEET1 = $EXCL.sheets.item("Sheet3");
$SHEET2 = $EXCL.sheets.item("Sheet4"); 
$SHEET1.Move($SHEET2)

5. Delete a Worksheet

In this step we will be deleting Worksheet “WorkSheet3”

#Delete Sheet3
$DeleteSheet = $EXCL.sheets.item("Sheet3");
$DeleteSheet.delete()

6. Add Report Title

In this step we will add a report title  of “AdventureWorks Sales Year to Date By Country”, change the text to bold and change the text size to 16 pt. We are also going to Merge A1,B1,C1 cell together, change the font color to white, change the fill color to Green and change the Alignment to the middle.

#Add Report Title
$SHEET = $EXCL.WorkSheets.item("Sheet2")
$SHEET.Cells.Item(1,1) = 'AdventureWorks Sales Year to Date By Country'
$SHEET.Cells.Item(1,1).Font.Bold=$True
$SHEET.Cells.Item(1,1).Font.size=16
$SHEET.Range("A1:C1").MergeCells =$True
$SHEET.Range("A1:C1").Interior.ColorIndex = 10  #Fill Color Green
$SHEET.Range("A1:C1").Font.ColorIndex = 2
$SHEET.Range("A1:C1").HorizontalAlignment = -4108  #Alignment Middle
#Right Alignment -4152
#Left Alignment -4131

 

7. Add Column Titles

In this step we will add three column titles in row 2, change the text to bold and change the column width

#Add Column Titles
$SHEET = $EXCL.sheets.item("Sheet2")
$SHEET.Cells.Item(2,1) = 'CountryRegionCode'
$SHEET.Cells.Item(2,1).Font.Bold=$True
$SHEET.Columns.item(1).ColumnWidth=23
$SHEET.Cells.Item(2,2) = 'SalesYTD'
$SHEET.Columns.item(2).ColumnWidth=18
$SHEET.Cells.Item(2,2).Font.Bold=$True
$SHEET.Cells.Item(2,3) = 'SalesLastYear'
$SHEET.Cells.Item(2,3).Font.Bold=$True
$SHEET.Columns.item(3).ColumnWidth=18

 

 

8. Create DataTable

In this step I am selecting data from the AdventureWorks2012 SQL Server Database. Note in the $Datatable variable you will need to enter your SQL Server Name in the “-ServerInstane” parameter.

function Invoke-Sqlcmd3

{
    param(
    [string]$Query,             
    [string]$Database="tempdb",
    [Int32]$QueryTimeout=30
    )
    $conn=new-object System.Data.SqlClient.SQLConnection
    $conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $Server,$Database
    $conn.Open()
    $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
    $cmd.CommandTimeout=$QueryTimeout
    $ds=New-Object system.Data.DataSet
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
    [void]$da.fill($ds)
    $conn.Close()
    $ds.Tables[0]
}

#Create DataTable with our report data
[String]$TSQL= "
use AdventureWorks2012

SELECT 
CountryRegionCode
,sum(SalesYTD) as SalesYTD
,SUM(SalesLastYear) as SalesLastYear
FROM AdventureWorks2012.Sales.SalesTerritory
GROUP BY CountryRegionCode
ORDER BY CountryRegionCode
"
$Datatable = Invoke-Sqlcmd3 -ServerInstance [Enter SQL Server Name] -Database AdventureWorks2012 -Query $TSQL -as 'DataTable'

 

9. Populate the Worksheet

In this step starting in row 3 we will be populating the Worksheet with data from the DataTable that was created in step 8.

#Populate the Worksheet
$SHEET = $EXCL.sheets.item("Sheet2")
$x=3
$Datatable | FOREACH-OBJECT{
$SHEET.cells.item($x, 1) =  $_.CountryRegionCode
$SHEET.cells.item($x, 2) =  $_.SalesYTD
$SHEET.cells.item($x, 3) =  $_.SalesLastYear
$x++
}

 

10. Clean Up

In this Step we will rename “WorkSheet2” to “SalesYTD” and delete worksheets “WorkSheet1” and “sheet4”.

#Clean Up
#Rename Sheet2 to SalesYTD
$SHEET = $EXCL.sheets.item("Sheet2")
$SHEET.Name = "SalesYTD"
#Delete Worksheet and sheet4
$DeleteSheet = $EXCL.sheets.item("WorkSheet1");
$DeleteSheet.delete() 
$DeleteSheet = $EXCL.sheets.item("sheet4");
$DeleteSheet.delete()

 

11. Save the Excel File

In this step we will save the Excel file to C:\Temp\SalesYTD.xlsx

#Save Excel File to C:\Temp\SalesYTD.xlsx
$SHEET = $EXCL.sheets.item("SalesYTD")
$SHEET.SaveAs("C:\temp\SalesYTD.xlsx")

Celebrating Pi Day

To All The SQL DBA’s and Programmers Happy Pi Day.

SQL Server

Select pi()

SQL_Pi

PowerShell

[math]::Pi

PowerShell_Pi

Visual C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Pi
{
    class Program
    {
        static void Main(string[] args)
        {
            double Pi = Math.PI;
            Console.WriteLine(Pi);
            Console.ReadKey();
        }
    }
}

VisualC#_Pi

Python

import math
print(math.pi)

Python

Part 3 Adding Tab Controls – Create your own SQL tools with PowerShell and windows forms

Using Tab Controls can help you organize and define your work space. In this blog post, I am going to be adding Tab Controls with the following Tab Pages for CPU, Disk, Memory to my SQL Tool app. I am going to highlight how you can take my code and add your own Tab Pages to meet your needs. Drop me a comment and let me know how your SQL Tool app is coming along or if you want me to cover a topic. Check out Part One where I introduced the SQL Tool app and in Part Two were I went over how to add a new Check Box.

 

TabControl

 

Adding New Tab Pages

Step 1.

For example, you want to add a new Tab Page called Tempdb , under the heading called #Form Setup, you will need to add a new system Tab Page object.

$TempDBPage = New-Object System.Windows.Forms.TabPage

Step 2.

Next you will need to create the Tab Page, this can be added under a previous Tab Page.

#TempDB Page
$TempDBPage.DataBindings.DefaultDataSourceUpdateMode = 0
$TempDBPage.UseVisualStyleBackColor = $True
$TempDBPage.Text = "TempDB”
$tabControl.Controls.Add($TempDBPage)

 

Full Code

function CreateForm {
#[reflection.assembly]::loadwithpartialname("System.Windows.Forms") | Out-Null
#[reflection.assembly]::loadwithpartialname("System.Drawing") | Out-Null

Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.drawing

#Form Setup
$form1 = New-Object System.Windows.Forms.Form
$button1 = New-Object System.Windows.Forms.Button
$button2 = New-Object System.Windows.Forms.Button
$checkBox1 = New-Object System.Windows.Forms.CheckBox
$checkBox2 = New-Object System.Windows.Forms.CheckBox
$checkBox3 = New-Object System.Windows.Forms.CheckBox
$checkBox4 = New-Object System.Windows.Forms.CheckBox
$TabControl = New-object System.Windows.Forms.TabControl
$SQLHealthPage = New-Object System.Windows.Forms.TabPage
$CPUPage = New-Object System.Windows.Forms.TabPage
$DiskPage = New-Object System.Windows.Forms.TabPage
$MemoryPage = New-Object System.Windows.Forms.TabPage

$InitialFormWindowState = New-Object System.Windows.Forms.FormWindowState

#Form Parameter
$form1.Text = "My PowerShell Form"
$form1.Name = "form1"
$form1.DataBindings.DefaultDataSourceUpdateMode = 0
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 725
$System_Drawing_Size.Height = 450
$form1.ClientSize = $System_Drawing_Size


#Tab Control 
$tabControl.DataBindings.DefaultDataSourceUpdateMode = 0
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 75
$System_Drawing_Point.Y = 85
$tabControl.Location = $System_Drawing_Point
$tabControl.Name = "tabControl"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Height = 300
$System_Drawing_Size.Width = 575
$tabControl.Size = $System_Drawing_Size
$form1.Controls.Add($tabControl)

#SQLHealth Page
$SQLHealthPage.DataBindings.DefaultDataSourceUpdateMode = 0
$SQLHealthPage.UseVisualStyleBackColor = $True
$SQLHealthPage.Name = "SQLHealthPage"
$SQLHealthPage.Text = "SQL Health Check”
$tabControl.Controls.Add($SQLHealthPage)

#CPU Page
$CPUPage.DataBindings.DefaultDataSourceUpdateMode = 0
$CPUPage.UseVisualStyleBackColor = $True
$CPUPage.Name = "CPUPage"
$CPUPage.Text = "CPU”
$tabControl.Controls.Add($CPUPage)

#Disk Page
$DiskPage.DataBindings.DefaultDataSourceUpdateMode = 0
$DiskPage.UseVisualStyleBackColor = $True
$DiskPage.Name = "DiskPage"
$DiskPage.Text = "Disk”
$tabControl.Controls.Add($DiskPage)

#Memory Page
$MemoryPage.DataBindings.DefaultDataSourceUpdateMode = 0
$MemoryPage.UseVisualStyleBackColor = $True
$MemoryPage.Name = "MemoryPage"
$MemoryPage.Text = "Memory”
$tabControl.Controls.Add($MemoryPage)

#Add Label and TextBox
$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(10,45)  
$objLabel.Size = New-Object System.Drawing.Size(110,50)  
$objLabel.Text = "Enter Server Name"
$form1.Controls.Add($objLabel)
$objTextBox = New-Object System.Windows.Forms.TextBox 
$objTextBox.Location = New-Object System.Drawing.Size(120,45) 
$objTextBox.Size = New-Object System.Drawing.Size(200,20)  
$form1.Controls.Add($objTextBox) 
 
#Button 1 Action 
$button1_RunOnClick= 
{   
    if ($checkBox1.Checked)     {  SQLVersion }
    if ($checkBox2.Checked)    {  LastReboot }
    if ($checkBox3.Checked)    {  Requests }   
}

#Button 2 Action
$button2_RunOnClick= 
{   
    if ($checkBox1.Checked) {$checkBox1.CheckState = 0}
    if ($checkBox2.Checked) {$checkBox2.CheckState = 0}
    if ($checkBox3.Checked) {$checkBox3.CheckState = 0}
}

$OnLoadForm_StateCorrection=
{
    $form1.WindowState = $InitialFormWindowState
}
 
#Button 1
$button1.TabIndex = 4
$button1.Name = "button1"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 75
$System_Drawing_Size.Height = 25
$button1.Size = $System_Drawing_Size
$button1.UseVisualStyleBackColor = $True
$button1.Text = "Run"
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 350
$System_Drawing_Point.Y = 45
$button1.Location = $System_Drawing_Point
$button1.DataBindings.DefaultDataSourceUpdateMode = 0
$button1.add_Click($button1_RunOnClick)
$form1.Controls.Add($button1)

#Button 2
$button2.TabIndex = 4
$button2.Name = "button2"
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 150
$System_Drawing_Size.Height = 25
$button2.Size = $System_Drawing_Size
$button2.UseVisualStyleBackColor = $True
$button2.Text = "Clear CheckBox"
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 450
$System_Drawing_Point.Y = 45
$button2.Location = $System_Drawing_Point
$button2.DataBindings.DefaultDataSourceUpdateMode = 0
$button2.add_Click($button2_RunOnClick)
$form1.Controls.Add($button2)


#SQLVersion
$checkBox1.UseVisualStyleBackColor = $True
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 104
$System_Drawing_Size.Height = 24
$checkBox1.Size = $System_Drawing_Size
$checkBox1.TabIndex = 0
$checkBox1.Text = "SQL Version"
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 25
$System_Drawing_Point.Y = 25
$checkBox1.Location = $System_Drawing_Point
$checkBox1.DataBindings.DefaultDataSourceUpdateMode = 0
$checkBox1.Name = "checkBox1"
$SQLHealthPage.Controls.Add($checkBox1)



#LastReBoot
$checkBox2.UseVisualStyleBackColor = $True
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 104
$System_Drawing_Size.Height = 24
$checkBox2.Size = $System_Drawing_Size
$checkBox2.TabIndex = 1
$checkBox2.Text = "Last Reboot"
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 25
$System_Drawing_Point.Y = 50
$checkBox2.Location = $System_Drawing_Point
$checkBox2.DataBindings.DefaultDataSourceUpdateMode = 0
$checkBox2.Name = "checkBox2"
$SQLHealthPage.Controls.Add($checkBox2)


#Request
$checkBox3.UseVisualStyleBackColor = $True
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 104
$System_Drawing_Size.Height = 24
$checkBox3.Size = $System_Drawing_Size
$checkBox3.TabIndex = 0
$checkBox3.Text = "Requests"
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 25
$System_Drawing_Point.Y = 75
$checkBox3.Location = $System_Drawing_Point
$checkBox3.DataBindings.DefaultDataSourceUpdateMode = 0
$checkBox3.Name = "checkBox3"
$SQLHealthPage.Controls.Add($checkBox3)


#Save the initial state of the form
$InitialFormWindowState = $form1.WindowState
#Init the OnLoad event to correct the initial state of the form
$form1.add_Load($OnLoadForm_StateCorrection)
#Show the Form
$form1.ShowDialog()| Out-Null
} #End function CreateForm
 
 function Invoke-Sqlcmd3

{
    param(
    [string]$Query,             
    [string]$Database="tempdb",
    [Int32]$QueryTimeout=30
    )
    $conn=new-object System.Data.SqlClient.SQLConnection
    $conn.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $Server,$Database
    $conn.Open()
    $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
    $cmd.CommandTimeout=$QueryTimeout
    $ds=New-Object system.Data.DataSet
    $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
    [void]$da.fill($ds)
    $conn.Close()
    $ds.Tables[0]
}

 

Function SQLVersion
{
[string]$SQLVersion = @"
SELECT  @@Version
"@ 
 $Server = $objTextBox.text
Invoke-Sqlcmd3 -ServerInstance $Server -Database Master -Query $SQLVersion | Out-GridView -Title "$server SQL Server Version"
}

Function LastReboot
{
$Server = $objTextBox.text
$wmi = Get-WmiObject -Class Win32_OperatingSystem -Computer $server
$wmi.ConvertToDatetime($wmi.LastBootUpTime) | Select DateTime | Out-GridView -Title "$Server Last Reboot"
}

Function Requests
{
[string]$Requests = @"
SELECT
   db_name(r.database_id) as database_name, r.session_id AS SPID,r.status,s.host_name,
     r.start_time,(r.total_elapsed_time/1000) AS 'TotalElapsedTime Sec',
   r.wait_type as current_wait_type,r.wait_resource as current_wait_resource,
   r.blocking_session_id,r.logical_reads,r.reads,r.cpu_time as cpu_time_ms,r.writes,r.row_count,
   substring(st.text,r.statement_start_offset/2,
   (CASE WHEN r.statement_end_offset = -1 THEN len(convert(nvarchar(max), st.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset)/2) as statement
FROM
   sys.dm_exec_requests r
      LEFT OUTER JOIN sys.dm_exec_sessions s on s.session_id = r.session_id
      LEFT OUTER JOIN sys.dm_exec_connections c on c.connection_id = r.connection_id       
      CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st 
WHERE r.status NOT IN ('background','sleeping')
"@ 
 $Server = $objTextBox.text
Invoke-Sqlcmd3 -ServerInstance $Server -Database Master -Query $Requests | Out-GridView -Title "$server Requests"
}



#Call the Function

CreateForm

What To Do When SQL Server Stops Responding, Did You Try Using the Dedicated Administrator Connection?

Over the years I have seen my share of SQL Servers that have stopped responding. Most of the time in these situations there is a process that has consumed all the available resources on the server. You will need to use the Dedicated Administrator Connection (DAC) to gain access to the SQL Server to diagnose and collect information on the request that caused the issue and to bring SQL back online. The DAC is a safety line so you don’t have to restart the SQL Service and possibly put the database into recovery for several hours. Since DAC uses minimal resources, you will need to keep your queries simple. I have provided some queries below to view the requests on the SQL Server.

Dedicated Administrator Connection

  1. Open SQL Server Management Studio (SSMS)
  2. Click on the Database Engine Query Icon to open the login prompt or from SSMS go to File>>New>>Database Engine Query
  3. Enter ADMIN:Server Name, if using an instance use ADMIN:Server Name\Instance Name
  4. Enter the sysadmin credentials

DAC

 

If you get the following error, re-login using the Database Engine Query login prompt

BAC_Error

 

Now your connected to DAC what do you run?

When using DAC or when SQL Server is under a lot of resource pressure you need to keep you queries simple. Avoid using large complex queries and resource expensive DMV’s.

1.      View the Requests on SQL Server

Select Session_id, 
       Start_Time, 
       Status, 
       plan_handle, 
       database_id, 
       blocking_session_id, 
       wait_type, 
       cpu_time, 
       reads, 
       logical_reads,
       writes
from sys.dm_exec_requests
where status not in ('background','sleeping')

2. View the Session Information

Note you will need the Session ID from the First query

Select session_id, 
       login_name, 
       host_name, 
       program_name
from sys.dm_exec_sessions
where session_id = '[session_id]'

3.      View the SQL Text and Execution Plan.

Note you will need the Plan Handle from the First query

Select text from sys.dm_exec_sql_text([plan_handle);

Select query_plan from sys.dm_exec_query_plan (plan_handle);

4.       View the Database Name

Note you will need the Database ID from the First query

Select name from sys.databases where database_id = '(database_id)'

 

 

 

 

 

 

 

Caesar Shift Cipher Using PowerShell

I am reading “The Code Book” by Simon Singh, and was inspired to create my version of the Caesar Shift Cipher in PowerShell just to see if I could do it. The main concept of the Caesar Shift Cipher is that it shifts the plaintext letters N spaces to create a new encrypted letter, for example “Hello” is shifted (Caesar Cipher Shift Key) 2 places to the right to create the encrypted text “Jgnnq”.

In my Caesar Shift Cipher program, I am using the ASCII value of each letter plus the Caesar Cipher Shift Key value to create a new letter, then I concatenate the new letters back together again to create the encrypted message. I have added a number of Write-Host commands to show you the encryption\decryption process. Enjoy having fun encrypting your own messages or changing the program, let me know how it goes.

Lets create an encrypted message, you can Copy & Paste the full code below into your favorite PowerShell Editor or open Windows PowerShell ISE.

Create Encrypted Message

  1. Enter “e” to create the encrypted message
  2. Enter “2” for the Caesar Cipher Shift key, this will shift the letter two places to the right. Note you need to remember this value to decrypt the message
  3. Enter message “Hello World”
  4. The Encrypted text will be “Jgnnq Yqtnf”

caesar1

To Decrypt the message

  1. Enter “d” to decrypt the message
  2. Enter “2” for the Caesar Cipher Shift key
  3. Enter the encrypted text “Jgnnq Yqtnf”
  4. The decrypted text is now back to the original message “Hello World”

caesar2

 

I will highlight main sections of the program to show you how the encryption\decryption works

1. This section of code displays the PlainText (unencrypted) letter ASCII value.

Write-Host "PlainText Letter ASCII Values" -foregroundcolor "green" #Hide


foreach ($letter in $String) #Hide (the foreeach cmd)
{
    $nbr = [int[]][char]$letter
    Write-Host $nbr -foregroundcolor "green"
}

 

2. This Section of code I am using the letter ASCII value to determine if the letter is upper\lower case or if it is a symbol. If the letter is a non alphabetic character I am passing it directly to the concatenation process.

Write-Host "Encrypted Text Letter ASCII Values"  -foregroundcolor "red" #Hide

$String=[char[]]$msg
foreach ($letter in $String)
{
    $nbr = [int[]][char]$letter
     
    If ($nbr -ge 0 -and $nbr -le 64) #ASCII Codes
       {
       [string]$Snbr = $nbr
       [int]$Nnbr = $Snbr  
       [int]$Enbr = $Nnbr 
       Write-Host $Enbr -foregroundcolor "red" #Hide
       [string]$ELetter = [char]$Enbr
       }
   
    If ($nbr -ge 65 -and $nbr -le 90) #Alphabet UpperCase
       {
       [string]$Snbr = $nbr
       [int]$Nnbr = $Snbr 
       [int]$nkey = $key
       [int]$Enbr = $Nnbr + $nkey 
       If ($Enbr -gt 90) {$Enbr = $Enbr - 26}
       If ($Enbr -lt 65) {$Enbr = $Enbr + 26}
       Write-Host $Enbr -foregroundcolor "red" #Hide
       [string]$ELetter = [char]$Enbr
       #Write-Host "Lowercase " + $ELetter        
       } 
   
    If ($nbr -ge 91 -and $nbr -le 96) #ASCII Codes
       {
       [string]$Snbr = $nbr
       [int]$Nnbr = $Snbr  
       [int]$Enbr = $Nnbr 
       Write-Host $Enbr -foregroundcolor "red"
       [string]$ELetter = [char]$Enbr
       }
   
    If ($nbr -ge 97 -and $nbr -le 122) #Alphabet Lowercase
       {
       [string]$Snbr = $nbr
       [int]$Nnbr = $Snbr 
       [int]$nkey = $key
       [int]$Enbr = $Nnbr + $nkey 
       If($Enbr -gt 122) {$Enbr = $Enbr - 26}
       If  ($Enbr -lt 97) {$Enbr = $Enbr + 26}
       Write-Host $Enbr -foregroundcolor "red" #Hide
       [string]$ELetter = [char]$Enbr
       #Write-Host "Lowercase " + $ELetter        
       }
        
     If ($nbr -ge 123 -and $nbr -le 127) #ASCII Codes
        {
        [string]$Snbr = $nbr
        [int]$Nnbr = $Snbr  
        [int]$Enbr = $Nnbr 
        Write-Host $Enbr -foregroundcolor "red" #Hide
        [string]$ELetter = [char]$Enbr
        }

 

3. Let’s take a closer look at the encryption process. In this case, I am using the letter ASCII value to determine that the letter is an upper case letter. Then I need to add the Caesar Cipher shift key value to the letter ASCII value to create the new encrypted letter ([int]$Enbr = $Nnbr + $nkey). One of the challenges that I had is to keep the letter an upper case after it was encrypted, this is being accomplished by the two inner If statements ( If ($Enbr -gt 90) {$Enbr = $Enbr – 26}, If ($Enbr -lt 65) {$Enbr = $Enbr + 26}), I needed to keep the letter looping through the Upper case letters. Then the new ASCII value is being convert back to a letter character and saved to the $ELetter parameter for the concatenation process.

    If ($nbr -ge 65 -and $nbr -le 90) #Alphabet UpperCase
       {
       [string]$Snbr = $nbr
       [int]$Nnbr = $Snbr 
       [int]$nkey = $key
       [int]$Enbr = $Nnbr + $nkey 
       If ($Enbr -gt 90) {$Enbr = $Enbr - 26}
       If ($Enbr -lt 65) {$Enbr = $Enbr + 26}
       Write-Host $Enbr -foregroundcolor "red" #Hide
       [string]$ELetter = [char]$Enbr
       #Write-Host "Lowercase " + $ELetter        
       }

 

4. For every letter that is ran through the foreach commend it is concatenated back into the encrypted message and then displayed, this is accomplished by $EMsg = $EMsg + $ELetter .

$EMsg = $EMsg + $ELetter 
    
} 
Write-Host "Encrypted Text: " $EMsg -foregroundcolor "red"
Clear-variable EMsg
Write-Host "-------------------------------------------------" 
}

 

5. The Decrypt process is the same as the encryption process except for the Caesar Cipher Shift key is minus from the letter ASCII value ([int]$Enbr = $Nnbr – $nkey). I have the encryption process looping to the right and the decryption process looping through to the left.

    If ($nbr -ge 65 -and $nbr -le 90) #Alphabet UpperCase
       {
       [string]$Snbr = $nbr
       [int]$Nnbr = $Snbr 
       [int]$nkey = $key
       [int]$Enbr = $Nnbr - $nkey 
       If ($Enbr -gt 90) {$Enbr = $Enbr - 26}
       If ($Enbr -lt 65) {$Enbr = $Enbr + 26}
       Write-Host $Enbr -foregroundcolor "green" #Hide
       [string]$ELetter = [char]$Enbr
       #Write-Host "Lowercase " + $ELetter        
       }

 

Full Code

Write-Host "Caesar Shift Cipher"

$EType = read-host "Enter 'e' for Encryption or Enter 'd' from Decryption"
[int]$key = read-host "Create Caesar Cipher Shift Key, enter in a number from 1 to 26"
$msg = read-host "Enter Message"
Write-Host "-------------------------------------------------" 

#Encryption
if ($EType -eq "e")
{

Write-Host "PlainText Message: " $msg -foregroundcolor "green" #Hide

$String=[char[]]$msg


Write-Host "PlainText Letter ASCII Values" -foregroundcolor "green" #Hide


foreach ($letter in $String) #Hide (the foreeach cmd)
{
    $nbr = [int[]][char]$letter
    Write-Host $nbr -foregroundcolor "green"
}


Write-Host "Encrypted Text Letter ASCII Values"  -foregroundcolor "red" #Hide

$String=[char[]]$msg
foreach ($letter in $String)
{
    $nbr = [int[]][char]$letter
     
    If ($nbr -ge 0 -and $nbr -le 64) #ASCII Codes
       {
       [string]$Snbr = $nbr
       [int]$Nnbr = $Snbr  
       [int]$Enbr = $Nnbr 
       Write-Host $Enbr -foregroundcolor "red" #Hide
       [string]$ELetter = [char]$Enbr
       }
   
    If ($nbr -ge 65 -and $nbr -le 90) #Alphabet UpperCase
       {
       [string]$Snbr = $nbr
       [int]$Nnbr = $Snbr 
       [int]$nkey = $key
       [int]$Enbr = $Nnbr + $nkey 
       If ($Enbr -gt 90) {$Enbr = $Enbr - 26}
       If ($Enbr -lt 65) {$Enbr = $Enbr + 26}
       Write-Host $Enbr -foregroundcolor "red" #Hide
       [string]$ELetter = [char]$Enbr
       #Write-Host "Lowercase " + $ELetter        
       } 
   
    If ($nbr -ge 91 -and $nbr -le 96) #ASCII Codes
       {
       [string]$Snbr = $nbr
       [int]$Nnbr = $Snbr  
       [int]$Enbr = $Nnbr 
       Write-Host $Enbr -foregroundcolor "red"
       [string]$ELetter = [char]$Enbr
       }
   
    If ($nbr -ge 97 -and $nbr -le 122) #Alphabet Lowercase
       {
       [string]$Snbr = $nbr
       [int]$Nnbr = $Snbr 
       [int]$nkey = $key
       [int]$Enbr = $Nnbr + $nkey 
       If($Enbr -gt 122) {$Enbr = $Enbr - 26}
       If  ($Enbr -lt 97) {$Enbr = $Enbr + 26}
       Write-Host $Enbr -foregroundcolor "red" #Hide
       [string]$ELetter = [char]$Enbr
       #Write-Host "Lowercase " + $ELetter        
       }
        
     If ($nbr -ge 123 -and $nbr -le 127) #ASCII Codes
        {
        [string]$Snbr = $nbr
        [int]$Nnbr = $Snbr  
        [int]$Enbr = $Nnbr 
        Write-Host $Enbr -foregroundcolor "red" #Hide
        [string]$ELetter = [char]$Enbr
        }    

$EMsg = $EMsg + $ELetter 
    
} 
Write-Host "Encrypted Text: " $EMsg -foregroundcolor "red"
Clear-variable EMsg
Write-Host "-------------------------------------------------" 
}
  
#Decrypt
if ($EType -eq "d")
{

Write-Host "Encrypted Message: " $msg -foregroundcolor "red"

$String=[char[]]$msg

Write-Host "Encrypted Text Letter ASCII Values" -foregroundcolor "red" #Hide

foreach ($letter in $String) #Hide (the foreeach cmd)
{
    $nbr = [int[]][char]$letter
    Write-Host $nbr -foregroundcolor "red"
}

Write-Host "PlainText Letter ASCII Values"  -foregroundcolor "green" #Hide

$String=[char[]]$msg
foreach ($letter in $String)
{
    $nbr = [int[]][char]$letter
   
    If ($nbr -ge 0 -and $nbr -le 64) #ASCII Codes
       {
       [string]$Snbr = $nbr
       [int]$Nnbr = $Snbr  
       [int]$Enbr = $Nnbr 
       Write-Host $Enbr -foregroundcolor "green"#Hide
       [string]$ELetter = [char]$Enbr
       }
  
    If ($nbr -ge 65 -and $nbr -le 90) #Alphabet UpperCase
       {
       [string]$Snbr = $nbr
       [int]$Nnbr = $Snbr 
       [int]$nkey = $key
       [int]$Enbr = $Nnbr - $nkey 
       If ($Enbr -gt 90) {$Enbr = $Enbr - 26}
       If ($Enbr -lt 65) {$Enbr = $Enbr + 26}
       Write-Host $Enbr -foregroundcolor "green" #Hide
       [string]$ELetter = [char]$Enbr
       #Write-Host "Lowercase " + $ELetter        
       }  
   
    If ($nbr -ge 91 -and $nbr -le 96) #ASCII Codes
       {
       [string]$Snbr = $nbr
       [int]$Nnbr = $Snbr  
       [int]$Enbr = $Nnbr 
       Write-Host $Enbr -foregroundcolor "green"
       [string]$ELetter = [char]$Enbr
       }
          
    If ($nbr -ge 97 -and $nbr -le 122) #Alphabet LowerCase
       {
       [string]$Snbr = $nbr
       [int]$Nnbr = $Snbr 
       [int]$nkey = $key
       [int]$Enbr = $Nnbr - $nkey 
       If($Enbr -gt 122) {$Enbr = $Enbr - 26}
       If  ($Enbr -lt 97) {$Enbr = $Enbr + 26}
       Write-Host $Enbr -foregroundcolor "green" #Hide
       [string]$ELetter = [char]$Enbr       
       }    
     
    If ($nbr -ge 123 -and $nbr -le 127) #ASCII Codes
       {
       [string]$Snbr = $nbr
       [int]$Nnbr = $Snbr  
       [int]$Enbr = $Nnbr 
       Write-Host $Enbr -foregroundcolor "green" #Hide
       [string]$ELetter = [char]$Enbr
       }    

$EMsg = $EMsg + $ELetter    
} 
Write-Host "PlainText: " $EMsg -foregroundcolor "green"
Clear-variable EMsg
Write-Host "-------------------------------------------------"
}

How To Create an HTML Table from PowerShell

 

PowerShell has made it easy to output data to an HTML table with just a few lines of code. In the examples below I will demonstrate on how to create an HTML table like the one below.

Blogpic1

 

Example 1. Create HTML Table

The ConvertTo-HTML Cmdlet converts the Get-service results to HTML and then the Out-File Cmdlet saves the output to c:\temp\ReportHTML.html. The Invoke-Item Cmdlet is used to open the HTML file. You can Copy & Paste the code below into your favorite PowerShell Editor or open Windows PowerShell ISE. Note if you don’t have SQL Server installed on the computer that your using, you can remove *SQL*.

$GService = Get-service *SQL* | Where-Object {$_.status} | Select Name, Status | ConvertTo-HTML -Fragment 

#Create and Save the HTML Web Page
ConvertTo-HTML -head "SQL Services" -Body "$GService" |Out-File c:\temp\TableHTML.html

#Open TableHTML.html
 Invoke-Item c:\temp\TableHTML.html

Example 2. Create HTML Table With Style

The HTML <style> tag is used to define the look of your HTML web page. The TABLE element is for displaying the tabular data. The TH element is to define the Table Header attributes  and the TD element is to define the Table Cell attributes.  Take a few minutes and change the border-width and the padding pixel (px) size.

$Style = "
<style>
    TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
    TH{border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
    TD{border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
</style>
"
$GService = Get-service *SQL* | Where-Object {$_.status} | Select Name, Status | ConvertTo-HTML -AS Table -Fragment -PreContent '<h2>SQL Services</h2>'|Out-String 

#Save the HTML Web Page
ConvertTo-HTML -head $Style -PostContent $GService|Out-File c:\temp\TableHTML.html

#Open TableHTML.html
Invoke-Item c:\temp\TableHTML.html

Example 3. Background Color

To add a background color to your HTML web page, you can add BODY{background-color:#b0c4de;} to the <style> tag. To view all the HTML color codes you can go to http://html-color-codes.info/.

BODY{background-color:#b0c4de;}

Example 4. Table Header Background Color

To make the table header easier to read you can add a background color, add background-color:#778899 to the end of the TH element.

TH{border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color:#778899}

Example 5. Table Row Color

To make the table rows easier to read you can add alternating row colors by adding tr:nth-child(odd) and tr:nth-child(even) to the <style> tag.

tr:nth-child(odd) { background-color:#d3d3d3;} 
tr:nth-child(even) { background-color:white;}

 

Example 6. Cell Color

In this step I will show you how to change the table cell background color by using some logic and PowerShell scripting. If the SQL Service is Stopped the background color will be Red and Running will be Green. You can add the $StatusColor code right underneath the $Style code.

$StatusColor = @{Stopped = ' bgcolor="RED">Stopped<';Running = ' bgcolor="Green">Running<';}

The following line of code below it will use Find\Replace the HTML source code in the $GService parameter with the background color (<td>Stopped</td> is being replaced with <td bgcolor=”Red”>Stopped</td>). You can add the $StatusColor.keys code below right underneath the $GService parameter.

$StatusColor.Keys | foreach { $GService = $GService -replace ">$_<",($StatusColor.$_) }

Example 7. Multiple Tables

PowerShell has made it very simple to add multiple tables to your HTML web page. As you can see below I have added $Reboots parameter to collect last five entries of Windows Event 6005 from the event log. Event 6005 is logged at boot time noting that the Event Log service was started. You can add the $Reboots parameter right underneath the $StatusColor.keys parameter.

$Reboots = Get-WinEvent -FilterHashtable @{logname='system';id=6005} -MaxEvents 5| Select Message, TimeCreated  |ConvertTo-HTML -AS Table -Fragment -PreContent '<h2>Last 5 Reboots</h2>'|Out-String

To display the $Reboots results you just need to add the $Reboots parameter to the PostContent in the ConvertTo-HTML Cmdlet line of code.

ConvertTo-HTML -head $Style -PostContent $GService, $Reboots -PreContent '<h1>Web Page Title</h1>'|Out-File c:\temp\TableHTML.html

Example 8. Change the Font Attributes

I will show you a couple of ways to change the Font Attribute. In this example I am using the <h1> tag, but this will work in any of the HTML tags that contain text. The first way  is to add the following h1 tag line of code below to the <style> tag. Making the change in the <Style> tag is a global setting.

h1{color:blue;font-family:courier;font-size:300%}

The second way is to change the individual <h1> tag.

<h1 style="color:blue;font-family:courier;font-size:300%">

Full Code

$Style = "
<style>
    BODY{background-color:#b0c4de;}
    TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
    TH{border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color:#778899}
    TD{border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
    tr:nth-child(odd) { background-color:#d3d3d3;} 
    tr:nth-child(even) { background-color:white;}    
</style>
"
#Cell Color - Logic
$StatusColor = @{Stopped = ' bgcolor="Red">Stopped<';Running = ' bgcolor="Green">Running<';}

#SQL Service Status
$GService = Get-service *SQL* | Where-Object {$_.status} | Select Name, Status | ConvertTo-HTML -AS Table -Fragment -PreContent '<h2>SQL Services</h2>'|Out-String 

#Cell Color - Find\Replace
$StatusColor.Keys | foreach { $GService = $GService -replace ">$_<",($StatusColor.$_) }

#Last 5 Reboots
$Reboots = Get-WinEvent -FilterHashtable @{logname='system';id=6005} -MaxEvents 5| Select Message, TimeCreated  |ConvertTo-HTML -AS Table -Fragment -PreContent '<h2>Last 5 Reboots</h2>'|Out-String

#Save the HTML Web Page
ConvertTo-HTML -head $Style -PostContent $GService, $Reboots -PreContent '<h1>Web Page Title</h1>'|Out-File c:\temp\TableHTML.html #Open TableHTML.html Invoke-Item c:\temp\TableHTML.html
#Open TableHTML.html
Invoke-Item c:\temp\TableHTML.html

Part2 Adding a New CheckBox – Create your own SQl tools with powershell and windows forms

 

This blog post is about adding a new CheckBox to the SQL Tool base form from my first blog post. In the first blog post I just gave an introduction on the SQL Tool, now this is where the fun starts getting creative by adding your own SQL queries and PowerShell scripts. How cool is that! You’re making your own SQL tools tailored to your own needs. The CheckBox that I am going to be adding is for an SQL process, but it is universal. The CheckBox is just starting a PowerShell Function, what ever you dream up you can start that process from your own SQL Tool. If you started making your own SQL Tools or you would like for me to cover a topic please leave me a comment.

Add a new CheckBox

Step 1. In this step we will be adding a new CheckBox object called $checkBox3.

Open up Windows PowerShell ISE and then copy the Powershell Script from my first blog post. Then you can follow the next 5 steps to add a new CheckBox to the SQL Tool Form.

Find “$checkBox2 = New-Object System.Windows.Forms.CheckBox”, it is near the top of the script. You can either copy this line of code and paste it right underneath  and change from $checkBox2 to $checkBox3  or copy\paste the $checkBox3 object below. This Step will create a new Checkbox object called $checkBox3.

$checkBox3 = New-Object System.Windows.Forms.CheckBox  

 

Step 2. In this step we will be adding $checkBox3 to the click action of Button 1.

In the Section “#Button 1 Action” find “if ($checkBox2.Checked)    {  LastReboot }”, You can either copy this line of code and paste it right underneath and change from $checkBox2 to $checkBox3 and also change “LastReboot” to “Request” or you can copy\paste the line of code below. When CheckBox3 is checked and you click the Run button, it will run the PowerShell Function called “Requests” that we will create is step 5.

if ($checkBox3.Checked)    {  Requests }

Step 3. Need to Add $checkBox3 to the Button 2 on click action

In the Section “#Button 2 Action” find “if ($checkBox2.Checked) {$checkBox2.CheckState = 0}”, You can either copy this line of code and paste it right underneath and change from $checkBox2 to $checkBox3 or or you can copy\paste the line of code below. When the Clear CheckBox Button is click it will clear all the CheckBox’s if checked.

if ($checkBox3.Checked) {$checkBox3.CheckState = 0}

 

Step 4. This step will add the CheckBox to the SQL Tool form

Copy the entire section called “#LastReBoot” and paste it into NotePad. Then use Find & Replace to find “checkBox2” and replace with “checkBox3”. Next copy the code from Notepad and paste it back into the PowerShell script under the section called  “#LastReBoot”. Name the new section #Request. Change the “$System_Drawing_Point.Y =” value from 80 to 110, this will lower Request Checkbox under the LastReBoot.

#Request
$checkBox3.UseVisualStyleBackColor = $True
$System_Drawing_Size = New-Object System.Drawing.Size
$System_Drawing_Size.Width = 104
$System_Drawing_Size.Height = 24
$checkBox3.Size = $System_Drawing_Size
$checkBox3.TabIndex = 0
$checkBox3.Text = "Requests"
$System_Drawing_Point = New-Object System.Drawing.Point
$System_Drawing_Point.X = 25
$System_Drawing_Point.Y = 110
$checkBox3.Location = $System_Drawing_Point
$checkBox3.DataBindings.DefaultDataSourceUpdateMode = 0
$checkBox3.Name = "checkBox3"
$form1.Controls.Add($checkBox3)

Step 5. Create the PowerShell Function for the new CheckBox

Copy\paste the code below underneath the Section called SQLVersion or you can add your own SQL Script. If you add your own SQL script remember to change Requests to the new name in the PowerShell script.

 

Function Requests
{
[string]$SQLVersion = @"
SELECT
   db_name(r.database_id) as database_name, r.session_id AS SPID,r.status,s.host_name,
     r.start_time,(r.total_elapsed_time/1000) AS 'TotalElapsedTime Sec',
   r.wait_type as current_wait_type,r.wait_resource as current_wait_resource,
   r.blocking_session_id,r.logical_reads,r.reads,r.cpu_time as cpu_time_ms,r.writes,r.row_count,
   substring(st.text,r.statement_start_offset/2,
   (CASE WHEN r.statement_end_offset = -1 THEN len(convert(nvarchar(max), st.text)) * 2 ELSE r.statement_end_offset END - r.statement_start_offset)/2) as statement
FROM
   sys.dm_exec_requests r
      LEFT OUTER JOIN sys.dm_exec_sessions s on s.session_id = r.session_id
      LEFT OUTER JOIN sys.dm_exec_connections c on c.connection_id = r.connection_id       
      CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st 
WHERE r.status NOT IN ('background','sleeping')
"@ 
 $Server = $objTextBox.text
Invoke-Sqlcmd3 -ServerInstance $Server -Database Master -Query $SQLVersion | Out-GridView -Title "$server Request"
}