8 Replies Latest reply on Dec 6, 2019 5:22 PM by matth37

    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?

        • Re: SQL Agent Job Status
          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

           

          3 of 3 people found this helpful
          • Re: SQL Agent Job Status
            olessandr

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

              • Re: SQL Agent Job Status
                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
                1 of 1 people found this helpful