SQL Agent Job Status

Hi ryanjay15 ,

unfortunately, there is no out-of-the-box widget that you can use to get this information. You can add a custom query widget to your summary page and paste the below query into it should get you what you need on a summary page

SELECT n.Caption as [Node], app.Name as [Job Name], app.LastRunDate, app.LastRunDuration, app.JobStatus
FROM Orion.APM.SqlJobInfoAlert app
JOIN Orion.Nodes n on n.nodeid = app.nodeid

Here's a screenshot of it

Let me know how that works for ya

EDIT 1

Did some more tinkering with it and developed a better-looking widget.

SELECT DISTINCT n.Caption as [Node]
, app.Name as [Name]
, concat(app.LastRunDuration, 's') as [Last Run Duration]
, app.JobStatus as [Job Status]
, case when app.JobStatus = 'Up' then '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_succeeded.png' Else '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_failed.png' end as [_Iconfor_Job Status]
, n.DetailsUrl as [_LinkFor_Node]
, sql.DetailsUrl as [_linkfor_Name]
FROM Orion.APM.SqlJobInfoAlert app
JOIN Orion.Nodes n on n.nodeid = app.nodeid
join Orion.apm.SqlJobInfo sql on app.ApplicationID = sql.ApplicationID
ORDER BY app.Jobstatus

Screenshot

Thank you for your response. I'll try what you suggested and get back to

you as soon as possible.

On Sat, Mar 30, 2019, 1:16 AM christopher.t.jones123 <

This worked great, thanks

Thank you, it works. It would be great to make search working as well.

It's fairly simple to create the search query, it's essentially a copy of the above widget with a "WHERE" statement in there. Below is the search query where you can search based off of node caption

SELECT DISTINCT n.Caption as [Node]  
, app.Name as [Name]  
, concat(app.LastRunDuration, 's') as [Last Run Duration]  
, app.JobStatus as [Job Status]  
, case when app.JobStatus = 'Up' then '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_succeeded.png' Else '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_failed.png' end as [_Iconfor_Job Status]  
, n.DetailsUrl as [_LinkFor_Node]  
, sql.DetailsUrl as [_linkfor_Name]  
FROM Orion.APM.SqlJobInfoAlert app  
JOIN Orion.Nodes n on n.nodeid = app.nodeid  
join Orion.apm.SqlJobInfo sql on app.ApplicationID = sql.ApplicationID  
where n.caption = '%${SEARCH_STRING}%'
ORDER BY app.Jobstatus

is it working for you? for some reason it is not searching, or I may have missed something.

I modified it, try it now. I dont have an environment to validate it with currently, but that syntax should be valid

I was also having issues with the search.  I had to track down another custom query I cribbed off of Thwack since my SQL/SQWL skills leave something to be desired, but it looks like

where n.caption =

Should be

where n.caption LIKE

Also, if you want to be able to search the job name as well:

WHERE (Node LIKE '%${SEARCH_STRING}%' OR Name LIKE '%${SEARCH_STRING}%')

Thanks for the code!