Heya Tom,
Sure thing, I’ll dump the relevant code below. I use this for displaying the current state of SQL Agent Jobs (and being able to start them form the dashboard). This was put together a while ago originally and I’ve learned a lot about PSU since then so there’s probably loads of stuff I’m doing wrong or not the best way - so if anyone has any suggestions please let me know! Always worth refactoring 
I’ve been using the SQLServer and dbaTools modules so you’ll need them. I’ve found getting SQL job info is much faster using the SQLServer module and I’m changing to that (from dbaTools).
The main PSU dashboard .ps1 file initialises a bunch of variables that are assigned as cache variables based on a .json file like…
$BaseConfigFile = "config-uat.json"
$BaseConfigFolder = (Join-Path $PSScriptRoot "websitename-config")
# Load and parse the JSON configuration file
try {
$Config = Get-Content (Join-Path -Path $BaseConfigFolder -ChildPath $BaseConfigFile) -Raw -ErrorAction:SilentlyContinue -WarningAction:SilentlyContinue | ConvertFrom-Json -ErrorAction:SilentlyContinue -WarningAction:SilentlyContinue
} catch {
Throw -Message "The websitename-config Base configuration file is missing!"
}
# Check the configuration
if (!($Config)) {
Throw "The websitename-config Base configuration file is corrupt! Check for dodgy syntax somewhere."
}
...
...
...
$cache:cfgSQLserver = ($Config.SQL.Settings.SQLserver)
$cache:logoDarkBlue = ($Config.Colors.LogoDarkBlue)
$cache:logoGreen = ($Config.Colours.LogoGreen)
...
...
...
There are 2 relevant functions (Check-SQLJob and Start-SQLJob) that I load (along with many others) using the main PSU dashboard .ps1 file, it dot sources any functions within .ps1 files that are in the \websitename-functions\ folder…
### DOT SOURCE REQUIRED FUNCTIONS/SCRIPTS ###
Get-ChildItem -Path (Join-Path $PSScriptRoot "websitename-functions") -Filter *.ps1 -Recurse -File | Where-Object { $_.FullName -ne $PSCommandPath } | ForEach-Object {
. $_.FullName
}
Check-SQLJob function (gets the job info of the SQL job and assigns values to cache variables then broadcasts an element sync out to the dashboards which updates them) - I’ll probably rename this function to something like Sync-SQLJob:
function Check-SQLJob {
param (
[Parameter()]
[String]$JobName
)
Show-UDToast -Message "Refreshing SQL Job info for: $JobName" -Duration 3000
$JobNameWithSpaces = $JobName -replace '_',' '
#Get-SqlAgentJob is MUCH faster than dbaTools Get-DbaAgentJob PLUS it includes LastRunDuration whereas dbaTools doesn't
#$jobinfo = (Get-DbaAgentJob -SqlInstance $cache:cfgSQLserver -Job "$JobNameWithSpaces" |
$jobinfo = (Get-SqlAgentJob -ServerInstance $cache:cfgSQLserver -Name "$JobNameWithSpaces" |
Select-Object @{Name = 'CurrentRunStatus'; Expression = {$c = $_.CurrentRunStatus; "$c"}},@{Name = 'CurrentRunStep'; Expression = {$step = $_.CurrentRunStep; if($step -eq '0 (unknown)'){"Not running"}else{"$step"}}},Description,IsEnabled,LastRunDate,@{Name = 'LastRunOutcome'; Expression = {$l = $_.LastRunOutcome; "$l"}},NextRunDate,@{Name = 'State'; Expression = {$s = $_.State; "$s"}},LastRunDuration,JobSteps)
Set-Item -PSPath "cache:$($JobName)SQLJobInfo" -Value $jobinfo
if((Get-Item -pspath "cache:$($JobName)SQLJobInfo").CurrentRunStatus -eq "Idle") {
# this is to set jobfinished to true so that the popup "complete" appears (when cache:$($JobName)JobFinished is true)
# silently continue on error because the first time this runs for a job, the varialbe "cache:$($JobName)JobRunning" won't exist so throws an error that is ok to ignore
If((Get-Item -pspath "cache:$($JobName)JobRunning" -ErrorAction SilentlyContinue) -eq $true) {
# job was running, now finished, also sync dynamic sections above jobinfo sections
Set-Item -PSPath "cache:$($JobName)JobFinished" -Value $true
}
Set-Item -PSPath "cache:$($JobName)JobRunning" -Value $false
Set-Item -PSPath "cache:$($JobName)ButtonPointerEvents" -Value 'auto'
Set-Item -PSPath "cache:$($JobName)ButtonColour" -Value $cache:logoGreen
Set-Item -PSPath "cache:$($JobName)ButtonText" -Value ("run sql job ""$JobNameWithSpaces"" on $cache:cfgSQLserver")
} else {
Set-Item -PSPath "cache:$($JobName)JobFinished" -Value $false
Set-Item -PSPath "cache:$($JobName)JobRunning" -Value $true
Set-Item -PSPath "cache:$($JobName)ButtonPointerEvents" -Value 'none'
Set-Item -PSPath "cache:$($JobName)ButtonColour" -Value 'LightGrey'
Set-Item -PSPath "cache:$($JobName)ButtonText" -Value "sql job currently running..."
}
Sync-UDElement -Id "$JobName`JobInfo" -Broadcast
Sync-UDElement -Id "btnstartjob$JobName" -Broadcast
} # end function
Start-SQLJob function (starts the SQL job (if it hasn’t been started already) then loops checking every 3 seconds to see if the SQL job info has changed, like moving on a step, if there is a change then it calls the Check-SQLJob function):
function Start-SQLJob {
[CmdletBinding()]
param (
[Parameter()]
[String]$JobName
)
$JobNameWithSpaces = $JobName -replace '_',' '
# need to update this to use Get-SqlAgentJob as it's MUCH faster than Get-DbaAgentJob
if((Get-DbaAgentJob -SqlInstance $cache:cfgSQLserver -Job "$JobNameWithSpaces").CurrentRunStatus -eq "Idle"){
Start-DbaAgentJob -SqlInstance $cache:cfgSQLserver -Job "$JobNameWithSpaces"
}
$sqlJobTimeoutSeconds = 0
$previousMonitoringSqlJobInfo = $null
Do {
$ts = [timespan]::fromseconds($sqlJobTimeoutSeconds)
Show-UDToast -Message "sqlJob ($JobName) is running: $(("{0:hh\:mm\:ss}" -f $ts))" -duration 3000
# need to update this to use Get-SqlAgentJob as it's MUCH faster than Get-DbaAgentJob, as well as removing a bunch of unrequired properties, only need CurrentRunStatus and CurrentRunStep
Set-Item -PSPath "cache:$($JobName)monitoringSqlJobInfo" -Value (Get-DbaAgentJob -SqlInstance $cache:cfgSQLserver -Job "$JobNameWithSpaces" |
Select-Object @{Name = 'CurrentRunStatus'; Expression = {$c = $_.CurrentRunStatus; "$c"}},@{Name = 'CurrentRunStep'; Expression = {$step = $_.CurrentRunStep; if($step -eq '0 (unknown)'){"Not running"}else{"$step"}}},Description,IsEnabled,LastRunDate,@{Name = 'LastRunOutcome'; Expression = {$l = $_.LastRunOutcome; "$l"}},NextRunDate,@{Name = 'State'; Expression = {$s = $_.State; "$s"}},LastRunDuration,JobSteps)
#only check/sync the job element if anything has changed in the sql job info
if( ($null -eq $previousMonitoringSqlJobInfo) -Or ($previousMonitoringSqlJobInfo.CurrentRunStatus -ne ((Get-Item -pspath "cache:$($JobName)monitoringSqlJobInfo")).CurrentRunStatus) -Or ($previousMonitoringSqlJobInfo.CurrentRunStep -ne ((Get-Item -pspath "cache:$($JobName)monitoringSqlJobInfo")).CurrentRunStep) ) {
Check-SQLJob -JobName $JobName
}
$previousMonitoringSqlJobInfo = Get-Item -pspath "cache:$($JobName)monitoringSqlJobInfo"
Start-Sleep -Seconds 3
$sqlJobTimeoutSeconds += 3
} Until ( ((Get-Item -pspath "cache:$($JobName)monitoringSqlJobInfo").CurrentRunStatus -eq "Idle") -Or $sqlJobTimeoutSeconds -gt 1200)
#dumb timeout to alert user sql job running longer than 20minutes
if($sqlJobTimeoutSeconds -gt 1200){
Show-Popup -type "error" -Message "SQL Job $JobName timed out (ran longer than 20 minutes)" -Duration 10000 -CloseOnClick $true -Broadcast $true -IncludeUserNameAndTime $false -PlayAlertSound $false
}
} # end function
Show-Popup function (just a wrapper around Show-UDToast):
function Show-Popup {
param (
$type = "info",
$closeonclick = $false,
$broadcast = $false,
$message,
$duration = 8000,
$includeusernameandtime = $false,
$playalertsound = $false
)
$toastParamsCommon = @{
Message = If($includeusernameandtime) {"[$(Get-Date -format "HH:mm:ss")] $(If($null -eq $session:UDuser.displayname){$User}else{$session:UDuser.displayname}): $message"}else{$message}
Duration = $duration
CloseOnClick = $closeonclick
Broadcast = $broadcast
Position = 'topCenter'
TransitionIn = 'fadeInDown'
}
switch ($type) {
"error" {
$toastParamsSpecific = @{
BackgroundColor = "pink"
MessageColor = "red"
}
}
"info" {
$toastParamsSpecific = @{
BackgroundColor = "lightblue"
MessageColor = "blue"
}
}
"success" {
$toastParamsSpecific = @{
BackgroundColor = "lightgreen"
MessageColor = "green"
}
}
}
Show-UDToast @toastParamsCommon @toastParamsSpecific
If($playalertsound) {
Invoke-UDJavascript -Javascript "var audio = new Audio('$(("$cache:cfgSiteRootURL/assets/fanfare-trumpet.wav").Replace('//','/'))');audio.play();"
}
} # end function
For the dashboard page, I use the URL parameter to bring in the SQL Job Name, like /JobProcessing?JobName=Run_this_SQL_job so I have $JobName in the page as the name of the SQL Job to start.
Here’s the dashboard code with a lot removed but hopefully enough for the job running part…
...
...
...
If (-not($JobName)) {
Exit
}
$JobNameWithSpaces = $JobName -replace '_',' '
Check-SQLJob -JobName $JobName
...
...
...
New-UDPaper -Content {
New-UDGrid -Container -Content {
Set-Item -PSPath "cache:$($JobName)JobFinished" -Value $false
New-UDDynamic -Id "$JobName`JobInfo" -Content {
if((Get-Item -pspath "cache:$($JobName)JobFinished") -eq $true) {
Show-Popup -type "success" -Message "$JobName Job Complete!" -Duration 30000 -CloseOnClick $true -Broadcast $true -IncludeUserNameAndTime $false -PlayAlertSound $false
Set-Item -PSPath "cache:$($JobName)JobFinished" -Value $false
}
New-UDGrid -Container -Content {
New-UDGrid -Item -ExtraSmallSize 12 -SmallSize 12 -MediumSize 12 -LargeSize 12 -Content {
New-UDTypography -Text $JobName -Variant 'h6'
New-UDTypography -Text ((Get-Item -pspath "cache:$($JobName)SQLJobInfo").Description) -Variant 'body2'
if((Get-Item -pspath "cache:$($JobName)JobRunning") -eq $true) {
New-UDTypography -Text "[running step $( (Get-Item -pspath "cache:$($JobName)SQLJobInfo").CurrentRunStep ) of $(((Get-Item -pspath "cache:$($JobName)SQLJobInfo").JobSteps).Count)]" -Variant 'overline'
} else {
New-UDTypography -Text "[not running - last result: $((Get-Item -pspath "cache:$($JobName)SQLJobInfo").LastRunOutcome)]" -Variant 'overline'
}
}
}
New-UDGrid -Container -Content {
New-UDGrid -Item -ExtraSmallSize 12 -SmallSize 12 -MediumSize 12 -LargeSize 12 -Content {
New-UDList -Content {
New-UDListItem -Label 'CurrentRunStatus' -Icon (New-UDIcon -Icon cogs -Size lg -Color $cache:logoDarkBlue) -SubTitle (Get-Item -pspath "cache:$($JobName)SQLJobInfo").CurrentRunStatus
$maxsteps=((Get-Item -pspath "cache:$($JobName)SQLJobInfo").JobSteps).Count
for($i=1; $i -le $maxsteps; $i++) {
New-UDStyle -Style "
$(if((Get-Item -pspath "cache:$($JobName)SQLJobInfo").CurrentRunStep -ne 'Not running'){
"font-style: italic !important;
color: LightGrey !important;"
if( ((Get-Item -pspath "cache:$($JobName)SQLJobInfo").CurrentRunStep).SubString(0,1) -eq $i ) {
"font-style: underline !important;
font-style: normal !important;
color: Black !important;"
} elseif ( ((Get-Item -pspath "cache:$($JobName)SQLJobInfo").CurrentRunStep).SubString(0,1) -gt $i ) {
"text-decoration: line-through;"
"font-weight: normal;"
}
} else {
"color: LightGrey !important;"
}
)
" -Content {
New-UDListItem -Label "$i`. $((Get-Item -pspath "cache:$($JobName)SQLJobInfo").JobSteps | where {$_.Id -eq $i})" -Icon (New-UDIcon -Icon caret_right -Size lg -Color $cache:logoDarkBlue)
}
}
}
}
}
New-UDGrid -Container -Content {
New-UDGrid -Item -ExtraSmallSize 12 -SmallSize 6 -MediumSize 5 -LargeSize 3 -Content {
New-UDList -Content {
New-UDListItem -Label 'CurrentRunStep' -Icon (New-UDIcon -Icon info -Size lg -Color $cache:logoDarkBlue) -SubTitle (Get-Item -pspath "cache:$($JobName)SQLJobInfo").CurrentRunStep
}
}
New-UDGrid -Item -ExtraSmallSize 12 -SmallSize 6 -MediumSize 5 -LargeSize 3 -Content {
New-UDList -Content {
New-UDListItem -Label 'LastRunDate' -Icon (New-UDIcon -Icon info -Size lg -Color $cache:logoDarkBlue) -SubTitle (Get-Date((Get-Item -pspath "cache:$($JobName)SQLJobInfo").LastRunDate) -Format "dd MMM yyyy HH:mm:ss")
}
}
New-UDGrid -Item -ExtraSmallSize 12 -SmallSize 6 -MediumSize 5 -LargeSize 3 -Content {
New-UDList -Content {
New-UDListItem -Label 'LastRunOutcome' -Icon (New-UDIcon -Icon info -Size lg -Color $cache:logoDarkBlue) -SubTitle (Get-Item -pspath "cache:$($JobName)SQLJobInfo").LastRunOutcome
}
}
New-UDGrid -Item -ExtraSmallSize 12 -SmallSize 6 -MediumSize 5 -LargeSize 3 -Content {
New-UDList -Content {
New-UDListItem -Label 'LastRunDuration' -Icon (New-UDIcon -Icon info -Size lg -Color $cache:logoDarkBlue) -SubTitle $([string](Get-Item -pspath "cache:$($JobName)SQLJobInfo").LastRunDuration)
}
}
}
If((Get-Item -pspath "cache:$($JobName)JobRunning") -eq $true) {
New-UDStyle -Style "
.MuiCircularProgress-colorPrimary {
color: $cache:logoGreen
}" -Content {
New-UDProgress -Circular
}
}
New-UDButton -Id "btnbegin$JobName" -Text (Get-Item -pspath "cache:$($JobName)ButtonText") -OnClick {
If((Get-Item -pspath "cache:$($JobName)JobRunning") -eq $false) {
Show-UDModal -Content {
New-UDTypography -Text "Are you sure you want to start this process?"
} -Footer {
New-UDButton -Text "Close" -Variant 'outlined' -OnClick {
Hide-UDModal
}
New-UDButton -Text "Start" -OnClick {
Hide-UDModal
Show-Popup -type "info" -Message "Started $JobNameWithSpaces sql job!" -Duration 30000 -CloseOnClick $true -Broadcast $true -IncludeUserNameAndTime $true -PlayAlertSound $false
Set-Item -PSPath "cache:$($JobName)ButtonPointerEvents" -Value 'none'
Set-Item -PSPath "cache:$($JobName)ButtonColour" -Value 'LightGrey'
Set-Item -PSPath "cache:$($JobName)ButtonText" -Value "sql job currently running..."
Set-Item -PSPath "cache:$($JobName)JobRunning" -Value $true
Sync-UDElement -Id "$JobName`JobInfo" -Broadcast
Sync-UDElement -Id "btnstartjob$JobName" -Broadcast
# start job as separate PS function
Start-SQLJob -JobName $JobName
}
} -Persistent
} else {
Show-UDToast -Message "job was already running! checking for updated job info" -Duration 10000
Check-SQLJob -JobName $JobName
}
} -Style @{
'background-color' = Get-Item -PSPath "cache:$($JobName)ButtonColour"
'color' = 'White'
'margin' = 'auto'
'pointer-events' = Get-Item -PSPath "cache:$($JobName)ButtonPointerEvents"
}
} #end dynamic job info
}
} -Elevation 4
New-UDElement -Tag 'div' -Attributes @{
style = @{
"position"="relative"
"float" = "right"
"margin-left"="auto"
"margin-top"="-45px"
"margin-right"="-15px"
}
} -Content {
New-UDFloatingActionButton -Icon (New-UDIcon -Icon undo -Size lg) -OnClick {
Check-SQLJob -JobName $JobName
}
}
...
...
...
Hopefully there’s enough there for you to work out what’s going on. Anyway, I might’ve missed some parts out, see how ya go.
Cheers!
Steve
