SQL Agent Job Status

ryanjay15

Hi Everyone,

I'm new here. Can you help me how to add sql agent job status to a customize page or in NOC view mode?

Find more posts tagged with

Accepted answers

All comments

christopher.t.jones123

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

ryanjay15

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 <

Prineel

This worked great, thanks

olessandr

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

christopher.t.jones123

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

olessandr

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

christopher.t.jones123

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