Show the SQL job error message?

Question

Hi guys, The following SWQL query shows the Failed SQL Jobs. 1) I would like to show the user also the error message. In which column can I find it? 2) Also, the user would like to see only the jobs that have failed twice in a row (doesn't matter how much time between them). Any ideas? SELECT i.Name ,i.DetailsURL AS [_LinkFor_Name] ,'' + i.sqlApplication.FullyQualifiedName + '' as Application ,'/Orion/APM/SqlBlackBox/Images/StatusIcons/application_small_' + CASE WHEN i.sqlApplication.StatusDescription = 'Critical' THEN 'up-' ELSE '' END + ToLower(i.sqlApplication.StatusDescription) + '.png' AS [_IconFor_Application] ,i.sqlApplication.InstanceName ,i.JobInfoID ,i.ApplicationID ,i.sqlJobInfoAlert.Nodeid ,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 WHERE i.LastRunStatus <> 1 ORDER BY i.LastRunStatus,i.LastRunDate desc

mesverrum · Answer

So this is a bit tricky.  Appinsight does collect the values from the error log, but it doesn't tie them to the job normally.  Depending on the details of the job error messages if you can find something in the string you could so something like joining all the error messages that were logged after the start time of the job that contain that job name, and then get the count of those to filter to only jobs that have 2 matches.  I don't have any failed jobs in my DB to compare their messages against so I can't be sure that the job name will show up, but thats what I'd be looking for if I had that request.