AppInsight for SQL Job Report in SWQL Custom Query Resource

Works pretty well, but you commented out most recently run jobs? I found myself changing that.

Agreed - I noted that in the post - also I use it differently.
This is on it's on custom left hand page under Application>SQL Server summary page to roll up all SQL Agent jobs from all instances

-I built another smaller one which I put on the SQL server summary page itself which is only failed jobs

-Same could be put on a SQL server detail page if you had enough jobs to need it.

-------Begin Code-------

SELECT

i.Name

,i.DetailsURL AS [_LinkFor_Name]

,ToLocal(i.LastRunDate) AS [Last Run]

,CASE WHEN (i.LastRunDuration / 60) > 0 THEN (ToString((i.LastRunDuration / 60)) + 'm ' + ToString((i.LastRunDuration -(i.LastRunDuration / 60) * 60)) + 's') ELSE (ToString((i.LastRunDuration -(i.LastRunDuration / 60) * 60)) + 's') END AS [Duration]

, CASE WHEN i.LastRunStatus = 0 THEN 'Failed'

             WHEN i.LastRunStatus = 1 THEN 'Succeeded'

             WHEN i.LastRunStatus = 2 THEN 'Retry'

             WHEN i.LastRunStatus = 3 THEN 'Cancelled'

             WHEN i.LastRunStatus = 4 THEN 'In Progress'

             END AS [Outcome]

,'/Orion/APM/SqlBlackBox/Images/StatusIcons/job_' +

  CASE WHEN i.LastRunStatus = 0 THEN 'failed'

             WHEN i.LastRunStatus = 1 THEN 'succeeded'

             WHEN i.LastRunStatus = 2 THEN 'retry'

             WHEN i.LastRunStatus = 3 THEN 'cancelled'

             WHEN i.LastRunStatus = 4 THEN 'inprogress'

             END + '.png' AS [_IconFor_Outcome]

FROM Orion.APM.SqlJobInfo i

INNER JOIN Orion.Nodes n on i.sqlApplication.nodeid=n.nodeid

-------Filter by unsuccessful last run jobs status-------

WHERE  i.LastRunStatus <> 1

AND i.sqlApplication.unmanaged <> 'True'

AND n.status <> 9

-------Sort by status and then run date-------

ORDER BY i.LastRunStatus,i.LastRunDate desc

Thanks a lot for sharing this. I'm trying to use the query in the files you've attached but I keep getting an error. The steps I've followed are:

1. Add a customer query resource to a view of type "Summary":
2. Edit the resource and copy-pasted the query:
3. Save and I get an error:

Am I using the wrong type of custom resource?

Thanks!

‌no the right one- I wonder if the quotes got replaced again?

I'll try again when I get in but it's a straight copy out of my working custom query.

Getting the same error here as well, would love to see this work, client just asked for this.

Thanks much

Got it working, thanks a ton.

orioncrack wrote: Got it working, thanks a ton.

Any chance you could list the steps you took to get this working?

I got the altrrego XML report working in new NPM but not previous version. I am still stuck on making this work for a client in previous version. Need it more in my old environment.

Hi bluefunelemental​​ first of all, great report query, this helped me a lot. Two questions:

• How can use this to only check the status of backup jobs? Currently I added the following in the WHERE clause, but what if a backup job name is not really well set: WHERE i.Name LIKE '%back%'
  • EDIT: I checked the orion database, but I didn't find a way that it flags a specific job as backup job. I am now using a work-around where I added the following (and joined the necessary tables), it works fine for us but I am aware that this might not work everywhere:
    WHERE i.LastRunDate < a.LastBackup AND a.LastBackup < ADDDATE('SECOND',i.LastRunDuration,i.LastRunDate)
• Every node in our environment has the custom property "Production_Server" (True/False or Yes/No). How do I specify that this query only runs for our production servers?
  • EDIT: Answered myself by joining the table Orion.NodesCustomProperties and then adding necessary where clause

Thanks in advanced...

How can I retrieve the error message for each failed job?