Hopefoley.files.wordpress.com



#************************# & "C:\PSScripts\Test\DemoScript1.ps1" # Author: Hope Foley - PTI# Runs script against SQL to find failed jobs#************************$computer = "localhost"$cnxt = "server= $computer;database=master;Integrated Security=sspi"$command = "use msdb-- Variable DeclarationsDECLARE @PreviousDate datetimeDECLARE @Year VARCHAR(4)DECLARE @Month VARCHAR(2)DECLARE @MonthPre VARCHAR(2)DECLARE @Day VARCHAR(2)DECLARE @DayPre VARCHAR(2)DECLARE @FinalDate INT-- Initialize VariablesSET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days SET @Year = DATEPART(yyyy, @PreviousDate) SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) SET @FinalDate = CAST(@Year + @Month + @Day AS INT)-- Final LogicSELECT j.[name], s.step_name, h.step_id, h.step_name, h.run_date, h.run_time, h.sql_severity, h.message, h.serverFROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id AND h.step_id = s.step_idWHERE h.run_status = 0 -- Failure AND h.run_date > @FinalDateORDER BY h.instance_id DESC "$db = new-object System.Data.SqlClient.SqlDataAdapter ($command, $cnxt)$db.mandTimeout = 30$du = new-object System.Data.DataTable$db.fill($du) | out-null$du | format-table#************************# & "C:\PSScripts\Test\DemoScript2.ps1" # Author: Hope Foley - PTI# Runs script against SQL to find failed jobs#************************$computer = "localhost"$cnxt = "server= $computer;database=master;Integrated Security=sspi"$now = Get-Date$command = "use msdb-- Variable DeclarationsDECLARE @PreviousDate datetimeDECLARE @Year VARCHAR(4)DECLARE @Month VARCHAR(2)DECLARE @MonthPre VARCHAR(2)DECLARE @Day VARCHAR(2)DECLARE @DayPre VARCHAR(2)DECLARE @FinalDate INT-- Initialize VariablesSET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days SET @Year = DATEPART(yyyy, @PreviousDate) SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) SET @FinalDate = CAST(@Year + @Month + @Day AS INT)-- Final LogicSELECT j.[name], s.step_name, h.step_id, h.step_name, h.run_date, h.run_time, h.sql_severity, h.message, h.serverFROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id AND h.step_id = s.step_idWHERE h.run_status = 0 -- Failure AND h.run_date > @FinalDateORDER BY h.instance_id DESC "$db = new-object System.Data.SqlClient.SqlDataAdapter ($command, $cnxt)$db.mandTimeout = 30$du = new-object System.Data.DataTabletry{$db.fill($du) | out-null$du | format-table}catch{write-host "Cannot connect to $computer at $now"}#************************# & "C:\PSScripts\Test\DemoScript3.ps1" # Author: Hope Foley - PTI# Runs script against SQL to find failed jobs#************************$computers = get-content "C:\PSScripts\AllServers.txt"foreach ($computer in $computers) {$computer = "$computer"$cnxt = "server= $computer;database=master;Integrated Security=sspi"$now = Get-Date$command = "use msdb-- Variable DeclarationsDECLARE @PreviousDate datetimeDECLARE @Year VARCHAR(4)DECLARE @Month VARCHAR(2)DECLARE @MonthPre VARCHAR(2)DECLARE @Day VARCHAR(2)DECLARE @DayPre VARCHAR(2)DECLARE @FinalDate INT-- Initialize VariablesSET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days SET @Year = DATEPART(yyyy, @PreviousDate) SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) SET @FinalDate = CAST(@Year + @Month + @Day AS INT)-- Final LogicSELECT j.[name], s.step_name, h.step_id, h.step_name, h.run_date, h.run_time, h.sql_severity, h.message, h.serverFROM msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id AND h.step_id = s.step_idWHERE h.run_status = 0 -- Failure AND h.run_date > @FinalDateORDER BY h.instance_id DESC "$db = new-object System.Data.SqlClient.SqlDataAdapter ($command, $cnxt)$db.mandTimeout = 30$du = new-object System.Data.DataTabletry{$db.fill($du) | out-null$du | format-table}catch{write-host "Cannot connect to $computer at $now"}}#************************# & "C:\PSScripts\Test\DemoScript4.ps1" # Author: Hope Foley - PTI# Runs script against SQL to find failed jobs#************************$computers = get-content "C:\PSScripts\AllServers.txt"$FailedJobQueryPath = "C:\PSScripts\Test\FailedJobs.sql"foreach ($computer in $computers) {$computer = "$computer"$cnxt = "server= $computer;database=master;Integrated Security=sspi"$now = Get-Date$command = [string]::join([environment]::newline, (get-content $FailedJobQueryPath ))$db = new-object System.Data.SqlClient.SqlDataAdapter ($command, $cnxt)$db.mandTimeout = 30$du = new-object System.Data.DataTabletry{$db.fill($du) | out-null$du | format-table}catch{write-host "Cannot connect to $computer at $now"}}#************************# & "C:\PSScripts\Test\DemoScript5.ps1" # Author: Hope Foley - PTI# Runs script against SQL to find failed jobs#************************function FailedJobs{$computers = get-content "C:\PSScripts\AllServers.txt"$FailedJobQueryPath = "C:\PSScripts\Test\FailedJobs.sql"foreach ($computer in $computers) {$computer = "$computer"$cnxt = "server= $computer;database=master;Integrated Security=sspi"$now = Get-Date$command = [string]::join([environment]::newline, (get-content $FailedJobQueryPath ))$db = new-object System.Data.SqlClient.SqlDataAdapter ($command, $cnxt)$db.mandTimeout = 30$du = new-object System.Data.DataTabletry{$db.fill($du) | out-null$du }catch{write-host "Cannot connect to $computer at $now"}}}FailedJobs | convertto-html | out-file "C:\PSScripts\Test\Output.html"#************************# & "C:\PSScripts\Test\DemoScript6.ps1" # Author: Hope Foley - PTI# Runs script against SQL to find failed jobs#************************function FailedJobs{$computers = get-content "C:\PSScripts\AllServers.txt"$FailedJobQueryPath = "C:\PSScripts\Test\FailedJobs.sql"foreach ($computer in $computers) {$computer = "$computer"$cnxt = "server= $computer;database=master;Integrated Security=sspi"$now = Get-Date$command = [string]::join([environment]::newline, (get-content $FailedJobQueryPath ))$db = new-object System.Data.SqlClient.SqlDataAdapter ($command, $cnxt)$db.mandTimeout = 30$du = new-object System.Data.DataTabletry{$db.fill($du) | out-null$du }catch{write-host "Cannot connect to $computer at $now"}}}$head = '<style>BODY{font-family:Calibri; background-color:white;}TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}TH{font-size:1.3em; border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color:#5FB404}TD{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color:#E6E6E6}</style>'$header = "<H1>Failed Jobs</H1>"FailedJobs | ConvertTo-HTML -head $head -body $header | Out-File "C:\PSScripts\Test\SnazzyOutput.html"$now = Get-Date$htmlfile = get-content C:\PSScripts\Test\SnazzyOutput.html$htmlfile | foreach {$_ -replace "</body></html>", ""} | set-content C:\PSScripts\Test\SnazzyOutput.html$dateinfo = "<br>Report ran at " + $now + ".</i></body></html>" add-content -path C:\PSScripts\Test\SnazzyOutput.html -Value $dateinfo#************************# & "C:\PSScripts\Test\DemoScript7.ps1" # Author: Hope Foley - PTI# Runs script against SQL to find failed jobs#************************$Outputfile = "C:\PSScripts\Test\MoreSnazzierOutput.html"function FailedJobs{$computers = get-content "C:\PSScripts\AllServers.txt"$FailedJobQueryPath = "C:\PSScripts\Test\FailedJobs.sql"foreach ($computer in $computers) {$computer = "$computer"$cnxt = "server= $computer;database=master;Integrated Security=sspi"$now = Get-Date$command = [string]::join([environment]::newline, (get-content $FailedJobQueryPath ))$db = new-object System.Data.SqlClient.SqlDataAdapter ($command, $cnxt)$db.mandTimeout = 30$du = new-object System.Data.DataTabletry{$db.fill($du) | out-null$du | select server, name, step_name, step_id, step_name1, run_date, run_time, sql_severity, message}catch{write-host "Cannot connect to $computer at $now"}}}$head = '<style>BODY{font-family:Calibri; background-color:white;}TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}TH{font-size:1.3em; border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color:#5FB404}TD{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color:#E6E6E6}</style>'$header = "<H1>Failed Jobs</H1>"FailedJobs | ConvertTo-HTML -head $head -body $header | Out-File $Outputfile$now = Get-Date$htmlfile = get-content $Outputfile$htmlfile | foreach {$_ -replace "</body></html>", ""} | set-content $Outputfile$dateinfo = "<br>Report ran at " + $now + ".</i></body></html>" add-content -path $Outputfile -Value $dateinfo#************************# & "C:\PSScripts\Test\DemoScript8.ps1" # Author: Hope Foley - PTI# DBA check that writes report file#************************# Change the following variables based on your environment$html_file_dir = "C:\PSScripts\Test"$server_file = "C:\PSScripts\AllServers.txt"$FailedJobQueryPath = "C:\PSScripts\Test\FailedJobs.sql"# Change the following variables for the style of the report.$background_color = "#FFFFFF" # can be in rgb format (rgb(0,0,0)) or hex format (#FFFFFF)$server_name_font = "Arial"$server_name_font_size = "20px"$server_name_bg_color = "rgb(77,108,145)" # can be in rgb format (rgb(0,0,0)) or hex format (#FFFFFF)$heading_font = "Arial"$heading_font_size = "14px"$heading_name_bg_color = "rgb(95,130,169)" # can be in rgb format (rgb(0,0,0)) or hex format (#FFFFFF)$data_font = "Arial"$data_font_size = "11px"$ErrorActionPreference = "Continue"$date = Get-Date -UFormat "%Y%m%d"$html_file = New-Item -ItemType File -Path "$html_file_dir\FailedJobs_$date.html" -Force# Create the file$html_file# Create the header and footer contents of the html page for output$html_header = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" ""><html lang="en-US" xml:lang="en-US" xmlns=""><head><title>Server Drive Space</title><style type="text/css">.serverName { text-align:center; font-family:"' + $server_name_font + '"; font-size:' + $server_name_font_size + `'; font-weight:bold; background-color: ' + $server_name_bg_color + '; border: 1px solid black; width: 150px; }.headings { text-align:center; font-family:"' + $heading_font + '"; font-size:' + $heading_font_size + `'; font-weight:bold; background-color: ' + $heading_name_bg_color + '; border: 1px solid black; width: 150px; }.data { font-family:"' + $data_font + '"; font-size:' + $data_font_size + '; border: 1px solid black; width: 150px; }#dataTable { border: 1px solid black; border-collapse:collapse; }body { background-color: ' + $background_color + '; }#legend { border: 1px solid black; position:absolute; right:500px; top:10px; }</style><script language="JavaScript" type="text/javascript"><!--function zxcWWHS(){ if (document.all){ zxcCur=''hand''; zxcWH=document.documentElement.clientHeight; zxcWW=document.documentElement.clientWidth; zxcWS=document.documentElement.scrollTop; if (zxcWH==0){ zxcWS=document.body.scrollTop; zxcWH=document.body.clientHeight; zxcWW=document.body.clientWidth; } } else if (document.getElementById){ zxcCur=''pointer''; zxcWH=window.innerHeight-15; zxcWW=window.innerWidth-15; zxcWS=window.pageYOffset; } zxcWC=Math.round(zxcWW/2); return [zxcWW,zxcWH,zxcWS];}window.onscroll=function(){ var img=document.getElementById(''legend''); if (!document.all){ img.style.position=''fixed''; window.onscroll=null; return; } if (!img.pos){ img.pos=img.offsetTop; } img.=(zxcWWHS()[2]+img.pos)+''px'';}//--></script></head><body>'$html_footer = '</body></html>'# Start to create the reports fileAdd-Content $html_file $html_header$computers = get-content $server_fileforeach ($computer in $computers) {$computer = "$computer"$cnxt = "server= $computer;database=master;Integrated Security=sspi"$now = Get-Date$command = [string]::join([environment]::newline, (get-content $FailedJobQueryPath ))$db = new-object System.Data.SqlClient.SqlDataAdapter ($command, $cnxt)$db.mandTimeout = 30$du = new-object System.Data.DataTabletry{$db.fill($du) | out-null$du | select server, name, step_name, step_id, step_name1, run_date, run_time, sql_severity, message}catch{write-host "Cannot connect to $computer at $now"}Add-Content $html_file ('<Table id="dataTable"><tr><td colspan="3" class="serverName">' + $computer + '</td></tr><tr><td class="headings">Server</td><td class="headings">Job Name</td><td class="headings">Step Name</td><td class="headings">Step ID</td><td class="headings">Step Name</td><td class="headings">Run Date</td><td class="headings">RunTime</td><td class="headings">Sql Severity</td><td class="headings">Message</td></tr>')foreach ($row in $du) { $server = $row["server"] $name = $row["name"] $step_name = $row["step_name"] $step_id = $row["step_id"] $step_name1 = $row["step_name1"] $run_date = $row["run_date"] $run_time = $row["run_time"] $severity = $row["sql_severity"] $message = $row["message"]Add-Content $html_file ('<tr><td class="data">' + $server + '</td><td class="data">' + $name + `'</td><td class="data">' + $step_name + '</td><td class="data">' + $step_id + `'</td><td class="data">' + $step_name1 + '</td><td class="data">' + $run_date + '</td><td class="data">' + $run_time + '</td><td class="data">' + $severity + '</td><td class="data">' + $message + '</td></tr>')}}# End the reports fileAdd-Content $html_file $html_footer ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download