How To Get of Report ALL Jobs Status In MS SQL Server | use case 2

Join this channel to get access to perks:
/ @mssqldbatechsupport
Join the Telegram Group for the scripts and assistance:
t.me/+sDwstoLc...
You Can Also Download the scripts from below folder
drive.google.c...
We strongly believe there is always chance of betterment, so suggestions are most welcome.
Happy learning, and All the Best in your professional journey!
The journey of improvement is ongoing and never be an end.
Connect With me,
LinkedIn Page --- / mssqldbatechteam
Facebook Page --- www.facebook.c...
Twitter --- / mssqldbasupport
Instagram --- / mssqldbatechsupport
Email --- [e-mail]
Telegram --- t.me/mssqldbaw...
#azuresql #azure #sqldba #sqlserverdba #sql #sqlserver #sqlserverdeveloper #performance #performancetuning #performanceoptimization #mssql #mssqlserver #mssqlserverdba
Thank you!
MS SQL DBA Tech Support
[e-mail]

[@MSSQLDBATechSupport]

##################### #Daily Job Moniter Report Status #Indivial HTML files will Get Generated per server ###################### $starttime = Get-Date $formattedDate = $starttime.ToString("-yyyy-MM-dd_HH-mm-ss") $servernames = Get-Content -Path "C:\HealthcheckReports\serverlist.txt" foreach ($servername in $servernames) { Write-Host "Starting Server $servername" $dataSource = $servername $database = "master" $path = "C:\HealthcheckReports" $name = $dataSource -replace "\\","_" $OutputFile_new = Join-Path -Path $path -ChildPath ($name + $formattedDate + '.html') $style = @" BODY { background-color: #e0e0e0; height: 100%; width: 100%; transform: scale(0.8); transform-origin: top left; } TABLE { border-width: 1px; border: 2px solid black; } TH { border-width: 2px; padding: 5px; border: 2px solid black;background-color: pink; font-family: 'Arial Narrow', Arial, sans-serif; } TD { border-width: 2px; padding: 5px; border: 2px solid black; BGCOLOR = LIGHTGREEN } tr:nth-child(even) { background-color: #f2f2f2 } .ENABLED { color: green; font-weight: bold; } .DISABLED { color: red; font-weight: bold; } .SUCCEEDED { color: green; font-weight: bold; } .FAILED { color: red; font-weight: bold; } .NEVER { color: Orange; font-weight: bold; } "@ $connectionDetails = "Provider=sqloledb; Data Source=$dataSource; Initial Catalog=$database; Integrated Security=SSPI;" $Jobreport = @" SELECT @@SERVERNAME as SERVERNAME, [SJOB].[NAME] AS [JOBNAME], CASE WHEN [SJOBH].[RUN_DATE] IS NULL OR [SJOBH].[RUN_TIME] IS NULL THEN NULL ELSE CAST( CAST([SJOBH].[RUN_DATE] AS CHAR(8)) + ' ' + STUFF( STUFF(RIGHT('000000' + CAST([SJOBH].[RUN_TIME] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS DATETIME) END AS [LASTRUNDATETIME], CASE [SJOBH].[RUN_STATUS] WHEN 0 THEN 'FAILED' WHEN 1 THEN 'SUCCEEDED' WHEN 2 THEN 'RETRY' WHEN 3 THEN 'CANCELLED' WHEN 4 THEN 'RUNNING' END AS [LASTRUNSTATUS], STUFF( STUFF(RIGHT('000000' + CAST([SJOBH].[RUN_DURATION] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS [LASTRUNDURATION], CASE WHEN ENABLED = 1 THEN 'ENABLED' ELSE 'DISABLED' END AS [JOBSTATUS], CASE [SJOBSCH].[NEXTRUNDATE] WHEN 0 THEN NULL ELSE CAST( CAST([SJOBSCH].[NEXTRUNDATE] AS CHAR(8)) + ' ' + STUFF( STUFF(RIGHT('000000' + CAST([SJOBSCH].[NEXTRUNTIME] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS DATETIME) END AS [NEXTRUNDATETIME] INTO #AGENT_JOBSTAUS FROM [MSDB].[DBO].[SYSJOBS] AS [SJOB] LEFT JOIN ( SELECT [JOB_ID], MIN([NEXT_RUN_DATE]) AS [NEXTRUNDATE], MIN([NEXT_RUN_TIME]) AS [NEXTRUNTIME] FROM [MSDB].[DBO].[SYSJOBSCHEDULES] GROUP BY [JOB_ID] ) AS [SJOBSCH] ON [SJOB].[JOB_ID] = [SJOBSCH].[JOB_ID] LEFT JOIN ( SELECT [JOB_ID], [RUN_DATE], [RUN_TIME], [RUN_STATUS], [RUN_DURATION], [MESSAGE], ROW_NUMBER() OVER ( PARTITION BY [JOB_ID] ORDER BY [RUN_DATE] DESC, [RUN_TIME] DESC ) AS ROWNUMBER FROM [MSDB].[DBO].[SYSJOBHISTORY] WHERE [STEP_ID] = 0 ) AS [SJOBH] ON [SJOB].[JOB_ID] = [SJOBH].[JOB_ID] AND [SJOBH].[ROWNUMBER] = 1 ORDER BY [JOBNAME]; SELECT SERVERNAME, JOBNAME, COALESCE(CONVERT(VARCHAR(20), LASTRUNDATETIME, 120), 'NEVER') AS LASTRUNDATETIME, COALESCE(CONVERT(VARCHAR(20), LASTRUNSTATUS, 120), 'NEVER') AS LASTRUNSTATUS, COALESCE(CONVERT(VARCHAR(20), LASTRUNDURATION, 120), 'NEVER') AS LASTRUNDURATION, COALESCE(CONVERT(VARCHAR(20), JOBSTATUS, 120), 'NEVER') AS JOBSTATUS, COALESCE(CONVERT(VARCHAR(20), NEXTRUNDATETIME, 120), 'NEVER') AS NEXTRUNDATETIME FROM #AGENT_JOBSTAUS; DROP TABLE #AGENT_JOBSTAUS; "@ $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command = New-Object System.Data.OleDb.OleDbCommand $Jobreport, $connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command $dataSet = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet) | Out-Null $connection.Close() $rows = foreach ($row in $dataSet.Tables[0].Rows) { $lastRunStatus = $row.LASTRUNSTATUS $statusClass = switch ($lastRunStatus) { 'NEVER' { 'class="NEVER"' } 'FAILED' { 'class="FAILED"' } 'SUCCEEDED' { 'class="SUCCEEDED"' } default { '' } } $JOBSTATUS = $row.JOBSTATUS $JOBSTATUSClass = switch ($JOBSTATUS) { 'ENABLED' { 'class="ENABLED"' } 'DISABLED' { 'class="DISABLED"' } default { '' } } $LASTRUNDURATION = $row.LASTRUNDURATION $lastSTATUSClass = switch ($LASTRUNDURATION) { 'NEVER' { 'class="NEVER"' } default { '' } } $LASTRUNDATETIME = $row.LASTRUNDATETIME $lastLASTRUNDATETIMEClass = switch ($LASTRUNDATETIME) { 'NEVER' { 'class="NEVER"' } default { '' } } " $($row.SERVERNAME) $($row.JOBNAME) $LASTRUNDATETIME $lastRunStatus $LASTRUNDURATION $JOBSTATUS $($row.NEXTRUNDATETIME) " } $htmlContent = @" $style Daily Job Monitor Report SERVERNAMEJOBNAMELASTRUNDATETIMELASTRUNSTATUSLASTRUNDURATIONJOBSTATUSNEXTRUNDATETIME $($rows -join "`r`n") "@ $htmlContent | Out-File $OutputFile_new Write-Host "Report generated: $OutputFile_new" } $Stoptime = Get-Date Write-Host "Script completed at $Stoptime" ================================================

[@MSSQLDBATechSupport]

############################################ #Daily Job Moniter Report Status #Singel Html Will Get created for all Servers ############################################# $starttime = Get-Date # Format the date and time to remove invalid characters $formattedDate = $starttime.ToString("-yyyy-MM-dd_HH-mm-ss") $servernames = Get-Content -Path "C:\HealthcheckReports\serverlist.txt" # Initialize an empty string to store combined HTML content $combinedHtmlContent = "" foreach ($servername in $servernames) { Write-Host "Starting Server $servername" $dataSource = $servername $database = "master" $path = "C:\HealthcheckReports" $name = $dataSource -replace "\\","_" $OutputFile_new = Join-Path -Path $path -ChildPath ($name + $formattedDate + '.html') $style = @" BODY { background-color: #e0e0e0; height: 100%; width: 100%; transform: scale(0.8); transform-origin: top left; } TABLE { border-width: 1px; border: 2px solid black; } TH { border-width: 2px; padding: 5px; border: 2px solid black;background-color: pink; font-family: 'Arial Narrow', Arial, sans-serif; } TD { border-width: 2px; padding: 5px; border: 2px solid black; BGCOLOR = LIGHTGREEN } tr:nth-child(even) { background-color: #f2f2f2 } .ENABLED { color: green; font-weight: bold; } .DISABLED { color: red; font-weight: bold; } .SUCCEEDED { color: green; font-weight: bold; } .FAILED { color: red; font-weight: bold; } .NEVER { color: Orange; font-weight: bold; } "@ $connectionDetails = "Provider=sqloledb; Data Source=$dataSource; Initial Catalog=$database; Integrated Security=SSPI;" $Jobreport = @" SELECT @@SERVERNAME as SERVERNAME, [SJOB].[NAME] AS [JOBNAME], CASE WHEN [SJOBH].[RUN_DATE] IS NULL OR [SJOBH].[RUN_TIME] IS NULL THEN NULL ELSE CAST( CAST([SJOBH].[RUN_DATE] AS CHAR(8)) + ' ' + STUFF( STUFF(RIGHT('000000' + CAST([SJOBH].[RUN_TIME] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS DATETIME) END AS [LASTRUNDATETIME], CASE [SJOBH].[RUN_STATUS] WHEN 0 THEN 'FAILED' WHEN 1 THEN 'SUCCEEDED' WHEN 2 THEN 'RETRY' WHEN 3 THEN 'CANCELLED' WHEN 4 THEN 'RUNNING' END AS [LASTRUNSTATUS], STUFF( STUFF(RIGHT('000000' + CAST([SJOBH].[RUN_DURATION] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS [LASTRUNDURATION], CASE WHEN ENABLED = 1 THEN 'ENABLED' ELSE 'DISABLED' END AS [JOBSTATUS], CASE [SJOBSCH].[NEXTRUNDATE] WHEN 0 THEN NULL ELSE CAST( CAST([SJOBSCH].[NEXTRUNDATE] AS CHAR(8)) + ' ' + STUFF( STUFF(RIGHT('000000' + CAST([SJOBSCH].[NEXTRUNTIME] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') AS DATETIME) END AS [NEXTRUNDATETIME] INTO #AGENT_JOBSTAUS FROM [MSDB].[DBO].[SYSJOBS] AS [SJOB] LEFT JOIN ( SELECT [JOB_ID], MIN([NEXT_RUN_DATE]) AS [NEXTRUNDATE], MIN([NEXT_RUN_TIME]) AS [NEXTRUNTIME] FROM [MSDB].[DBO].[SYSJOBSCHEDULES] GROUP BY [JOB_ID] ) AS [SJOBSCH] ON [SJOB].[JOB_ID] = [SJOBSCH].[JOB_ID] LEFT JOIN ( SELECT [JOB_ID], [RUN_DATE], [RUN_TIME], [RUN_STATUS], [RUN_DURATION], [MESSAGE], ROW_NUMBER() OVER ( PARTITION BY [JOB_ID] ORDER BY [RUN_DATE] DESC, [RUN_TIME] DESC ) AS ROWNUMBER FROM [MSDB].[DBO].[SYSJOBHISTORY] WHERE [STEP_ID] = 0 ) AS [SJOBH] ON [SJOB].[JOB_ID] = [SJOBH].[JOB_ID] AND [SJOBH].[ROWNUMBER] = 1 ORDER BY [JOBNAME]; SELECT SERVERNAME, JOBNAME, COALESCE(CONVERT(VARCHAR(20), LASTRUNDATETIME, 120), 'NEVER') AS LASTRUNDATETIME, COALESCE(CONVERT(VARCHAR(20), LASTRUNSTATUS, 120), 'NEVER') AS LASTRUNSTATUS, COALESCE(CONVERT(VARCHAR(20), LASTRUNDURATION, 120), 'NEVER') AS LASTRUNDURATION, COALESCE(CONVERT(VARCHAR(20), JOBSTATUS, 120), 'NEVER') AS JOBSTATUS, COALESCE(CONVERT(VARCHAR(20), NEXTRUNDATETIME, 120), 'NEVER') AS NEXTRUNDATETIME FROM #AGENT_JOBSTAUS; DROP TABLE #AGENT_JOBSTAUS; "@ $connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails $command = New-Object System.Data.OleDb.OleDbCommand $Jobreport, $connection $connection.Open() $dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command $dataSet = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet) | Out-Null $connection.Close() # Generate HTML rows with conditional coloring for LASTRUNSTATUS $rows = foreach ($row in $dataSet.Tables[0].Rows) { $lastRunStatus = $row.LASTRUNSTATUS $statusClass = switch ($lastRunStatus) { 'NEVER' { 'class="NEVER"' } 'FAILED' { 'class="FAILED"' } 'SUCCEEDED' { 'class="SUCCEEDED"' } default { '' } } $JOBSTATUS = $row.JOBSTATUS $JOBSTATUSClass = switch ($JOBSTATUS) { 'ENABLED' { 'class="ENABLED"' } 'DISABLED' { 'class="DISABLED"' } default { '' } } $LASTRUNDURATION = $row.LASTRUNDURATION $lastSTATUSClass = switch ($LASTRUNDURATION) { 'NEVER' { 'class="NEVER"' } default { '' } } $LASTRUNDATETIME = $row.LASTRUNDATETIME $lastLASTRUNDATETIMEClass = switch ($LASTRUNDATETIME) { 'NEVER' { 'class="NEVER"' } default { '' } } " $($row.SERVERNAME) $($row.JOBNAME) $LASTRUNDATETIME $lastRunStatus $LASTRUNDURATION $JOBSTATUS $($row.NEXTRUNDATETIME) " } # Append the rows to the combined HTML content $combinedHtmlContent += @" Job Monitor Report for Server: $($dataSource) SERVERNAMEJOBNAMELASTRUNDATETIMELASTRUNSTATUSLASTRUNDURATIONJOBSTATUSNEXTRUNDATETIME $($rows -join "`n") "@ } # Wrap all server reports in a single HTML structure $htmlContent = @" $a Combined Job Monitor Report $combinedHtmlContent "@ # Save the combined HTML content to a file $outputFileCombined = Join-Path -Path $path -ChildPath ("Combined_Report" + $formattedDate + '.html') $htmlContent | Out-File $outputFileCombined Write-Host "Combined Report generated: $outputFileCombined" $Stoptime = Get-Date Write-Host "Script completed at $Stoptime"

[@damodhardaamu9925]

Well said sir

[@MSSQLDBATechSupport]

Yup

[@vijayangovindaraj5124]

Thank you

[@MSSQLDBATechSupport]

Welcome!

[@chindamparamsh6091]

Good Bro...

[@MSSQLDBATechSupport]

Thanks

[@aniruddhabera6049]

Nice bro...one request from my end can u plzzz make one video on how to configure distributed AG

[@MSSQLDBATechSupport]

I have already done and sharing the link kzbin.info/www/bejne/jqbGZ6FurNehjsUsi=saejMtHvCJCbCTsX

[@HelmanNgu]

Nice! Can you please share the script?

[@MSSQLDBATechSupport]

you can get in pinned comments

[@monikanailwal]

Hi @harsha..can it be possible to get healthcheck script for multiple servers in one consolidated report?

[@MSSQLDBATechSupport]

Hi i have already uploaded in pinned comment... and also in google drive as well. in the comment we can uploaded only 5000k words... thats reason i was commented under pinned comment for multiple servers health script