cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

SQL Agent Job Status

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?

8 Replies
Level 8

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

0 Kudos

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.

0 Kudos

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

0 Kudos

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!

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

pastedImage_2.png

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

pastedImage_3.png

pastedImage_0.png

This worked great, thanks

0 Kudos

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 <

0 Kudos