Menu With SQL Queries
. .\CustomFunctions.ps1
#. .\MySQL.ps1
$weekday = (get-date).DayOfWeek
$URI = “http://www.holidaywebservice.com/HolidayService_v2/HolidayService2.asmx”
$proxy = New-WebServiceProxy -Uri $URI -Class holiday -Namespace webservice
$date=get-date -format “dd/MM/yyyy”
$first=get-date $date -Day 1
$last=get-date $first.addmonths(1).addseconds(-1)
$next=get-date $first.addmonths(1) -Day 1
$count1 = -3
$count2 = -4
$menu=@”
1. Collect Initial Daily Backup Data
2. Update Daily Backup Data
3. Show Scheduled Jobs That Failed
4. Show All Jobs That Failed
5. Show All Backup Jobs
6. Collect Holiday Backup Data
7. Generate Random Password
Q. Quit
Select a task by number or Q to quit
“@
Clear-Host
Do {
#use a Switch construct to take action depending on what menu choice
#is selected.
Switch (Invoke-Menu -menu $menu -title “Backup / Tools Menu” -clear) {
“1” {Write-Host “Collecting Daily Backup Data …” -ForegroundColor Yellow
If ($weekday -eq “Monday”) {
While ($count2 -ne -1) {
$yesterday = [DateTime]::Today.AddDays($count1).ToString(“ddMMyyyy”)
$archive = [DateTime]::Today.AddDays($count2).ToString(“ddMMyyyy”)
DailyBackup
$count1++
$count2++
}
}
else {
$yesterday = [DateTime]::Today.AddDays(-1).ToString(“ddMMyyyy”)
$archive = [DateTime]::Today.AddDays(-2).ToString(“ddMMyyyy”)
DailyBackup
}
}
“2” {Write-Host “Updating Daily Backup Data …” -ForegroundColor Green
DailyBackupUpdate
}
“3” {Write-Host “Show Scheduled Jobs That Failed …” -ForegroundColor Green
.\MySQL.ps1 -Query “SELECT TBL_Date, HostName, JobName, LastRunTime, IsScheduled, Status FROM daily_backup WHERE Status LIKE ‘%Failed%’ AND IsScheduled = ‘True'” | Out-GridView
}
“4” {Write-Host “Show All Jobs That Failed …” -ForegroundColor Green
.\MySQL.ps1 -Query “SELECT TBL_Date, HostName, JobName, LastRunTime, IsScheduled, Status FROM daily_backup WHERE Status LIKE ‘%Failed%'” | Out-GridView
}
“5” {Write-Host “Show All Backup Jobs …” -ForegroundColor Green
.\MySQL.ps1 -Query “SELECT TBL_Date, HostName, JobName, JobCreated, LastRunTime, RetentionPeriod, NextRunTime, IsScheduled, LastResult, Status FROM daily_backup” | Out-GridView
}
“6” {Write-Host “Updating Holiday Backup Data …” -ForegroundColor Green
PopUpBox
If ($weekday -eq “Monday”) {
$count1=$count1-$result
$count2=$count2-$result
While ($count2 -ne -1) {
$yesterday = [DateTime]::Today.AddDays($count1).ToString(“ddMMyyyy”)
$archive = [DateTime]::Today.AddDays($count2).ToString(“ddMMyyyy”)
# DailyBackup
$count1++
$count2++
}
}
else {
$count1=$count1-$result
$count2=$count2-$result
While ($count2 -ne -1) {
$yesterday = [DateTime]::Today.AddDays($count1).ToString(“ddMMyyyy”)
$archive = [DateTime]::Today.AddDays($count2).ToString(“ddMMyyyy”)
# DailyBackup
$count1++
$count2++
}
}
}
“7” {Write-Host “Generating Password …” -ForegroundColor Green
#Static settings
#Minimum length of random word
$minWordLen = 8
#Minimum length of random numbers
$numcount = 1
#Minimum length of random special characters
$speccount = 1
[System.Reflection.Assembly]::LoadWithPartialName(“System.Windows.Forms”) | Out-Null
$WebClient = New-Object System.Net.WebClient
$WebClient.Encoding = [System.Text.Encoding]::”Default”
# Password list based on http://blogs.technet.com/b/heyscriptingguy/archive/2013/06/03/generating-a-new-password-with-windows-powershell.aspx
#Numbers characters only
$a=$NULL
$ASCIINumbers=$NULL;For ($a=48;$a –le 57;$a++) {$ASCIINumbers+=,[char][byte]$a }
#Special are in multiple places in the table
#To include space, start at 32
# !” # $ % & ‘ ( ) * + , – . /
$ASCIISpecial=$NULL;
$a=$Null
For ($a=33;$a –le 47;$a++) {$ASCIISpecial+=,[char][byte]$a }
#http://sqlchow.wordpress.com/2013/03/04/shuffle-the-deck-using-powershell/
do {
$NewPass = Main
$result = [System.Windows.Forms.MessageBox]::Show(“Accept and Copy $NewPass to Clipboard?”,”Review”, “YesNo” , “Question” , “Button1”)
}
while ($result.value__ -ne 6)
#Use function because Clip adds a CR
$NewPass | Out-ClipboardText
$wshShell = new-object -comobject wscript.shell
$r=$wshShell.popup(“Password copied”,3,”Done”,1)
}
“Q” {Write-Host “Goodbye” -ForegroundColor Cyan
Return
}
Default {Write-Warning “Invalid Choice. Try again.”
sleep -milliseconds 750}
} #switch
} While ($True)
Original Menu
. .\CustomFunctions.ps1
$weekday = (get-date).DayOfWeek
$count1 = -3
$count2 = -4
$menu=@”
1. Collect Initial Daily Backup Data
2. Update Daily Backup Data
3. Collect Holiday Backup Data
4. Generate Random Password
Q. Quit
Select a task by number or Q to quit
“@
Clear-Host
Do {
#use a Switch construct to take action depending on what menu choice
#is selected.
Switch (Invoke-Menu -menu $menu -title “Backup / Tools Menu” -clear) {
“1” {Write-Host “Collecting Daily Backup Data …” -ForegroundColor Yellow
If ($weekday -eq “Monday”) {
While ($count2 -ne -1) {
$yesterday = [DateTime]::Today.AddDays($count1).ToString(“ddMMyyyy”)
$archive = [DateTime]::Today.AddDays($count2).ToString(“ddMMyyyy”)
DailyBackup
$count1++
$count2++
}
}
else {
$yesterday = [DateTime]::Today.AddDays(-1).ToString(“ddMMyyyy”)
$archive = [DateTime]::Today.AddDays(-2).ToString(“ddMMyyyy”)
DailyBackup
}
}
“2” {Write-Host “Updating Daily Backup Data …” -ForegroundColor Green
DailyBackupUpdate
}
“3” {Write-Host “Updating Holiday Backup Data …” -ForegroundColor Green
PopUpBox
If ($weekday -eq “Monday”) {
$count1=$count1-$result
$count2=$count2-$result
While ($count2 -ne -1) {
$yesterday = [DateTime]::Today.AddDays($count1).ToString(“ddMMyyyy”)
$archive = [DateTime]::Today.AddDays($count2).ToString(“ddMMyyyy”)
# DailyBackup
$count1++
$count2++
}
}
else {
$count1=$count1-$result
$count2=$count2-$result
While ($count2 -ne -1) {
$yesterday = [DateTime]::Today.AddDays($count1).ToString(“ddMMyyyy”)
$archive = [DateTime]::Today.AddDays($count2).ToString(“ddMMyyyy”)
# DailyBackup
$count1++
$count2++
}
}
}
“4” {Write-Host “Generating Password …” -ForegroundColor Green
#Static settings
#Minimum length of random word
$minWordLen = 8
#Minimum length of random numbers
$numcount = 3
#Minimum length of random special characters
$speccount = 2
[System.Reflection.Assembly]::LoadWithPartialName(“System.Windows.Forms”) | Out-Null
$WebClient = New-Object System.Net.WebClient
$WebClient.Encoding = [System.Text.Encoding]::”Default”
# Password list based on http://blogs.technet.com/b/heyscriptingguy/archive/2013/06/03/generating-a-new-password-with-windows-powershell.aspx
#Numbers characters only
$a=$NULL
$ASCIINumbers=$NULL;For ($a=48;$a –le 57;$a++) {$ASCIINumbers+=,[char][byte]$a }
#Special are in multiple places in the table
#To include space, start at 32
# !” # $ % & ‘ ( ) * + , – . /
$ASCIISpecial=$NULL;
$a=$Null
For ($a=33;$a –le 47;$a++) {$ASCIISpecial+=,[char][byte]$a }
#http://sqlchow.wordpress.com/2013/03/04/shuffle-the-deck-using-powershell/
do {
$NewPass = Main
$result = [System.Windows.Forms.MessageBox]::Show(“Accept and Copy $NewPass to Clipboard?”,”Review”, “YesNo” , “Question” , “Button1”)
}
while ($result.value__ -ne 6)
#Use function because Clip adds a CR
$NewPass | Out-ClipboardText
$wshShell = new-object -comobject wscript.shell
$r=$wshShell.popup(“Password copied”,3,”Done”,1)
}
“Q” {Write-Host “Goodbye” -ForegroundColor Cyan
Return
}
Default {Write-Warning “Invalid Choice. Try again.”
sleep -milliseconds 750}
} #switch
} While ($True)