Custom Query - SQL Job Status (Last 5)

CjWig

I'm looking to create custom query on SolarWinds to show the status of a SQL job, but I want to be able to view the last 5 times (if that makes sense)

I have found this prebuilt query on THWACK (SQL Agent Job Status - Forum - Server & Application Monitor (SAM) - THWACK (solarwinds.com))  already but I'm struggling to get it to bring more than one day's worth of data.

SELECT DISTINCT n.Caption AS [Node]
     , app.Name AS [Name]
     , app.LastRunDate AS [Last Run Date]
     , 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 app.name = 'XXX'
     AND app.name = 'XXX'
     AND n.caption = 'XXX'
ORDER BY app.Jobstatus

We regularly have jobs fail due to memory and we monitor this over a 4-day period and if on the 3rd day its failed again we would investigate. We want a simple way to check if the job has been failing more than 3 days.

KMSigma.SWI

Logically, lines 17 and 18 contradict each other, but I'm guessing that was a bad copy/paste.

The logic is good, you've just got to change the way you query the information.

I'm not 100% sure if the Orion.APM.SqlJobInfoAlert maintains historical information or just the last poll.  (I don't have a good instance in my lab at the moment).

I did take the liberty of re-writing your query to use [mention:0d6836b12ccc4811bb673b3ebf92ca65:6b577b8c04704e209d29b6772bf67243] instead of a bunch of JOINs.

SELECT DISTINCT [JIA].SqlJobInfo.SqlApplication.Node.Caption AS [Node]
     , [JIA].SqlJobInfo.SqlApplication.Node.DetailsUrl AS [_LinkFor_Node]
     , [JIA].SqlJobInfo.SqlApplication.Name AS [Name]
     , [JIA].SqlJobInfo.SqlApplication.DetailsUrl AS [_LinkFor_Name]
     , [JIA].LastRunDate AS [Last Run Date]
     , [JIA].JobStatus AS [Job Status]
     , CASE [JIA].JobStatus
          WHEN 'Up'
               THEN '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_succeeded.png'
          ELSE '/Orion/APM/SqlBlackBox/Images/StatusIcons/job_failed.png'
          END AS [_IconFor_Job Status]
--WHERE [JIA].SqlJobInfo.SqlApplication.Name = 'Your Application Name Here'
--  AND [JIA].SqlJobInfo.SqlApplication.Node.Caption = 'Your Node Caption Here'
FROM Orion.APM.SqlJobInfoAlert AS [JIA]
ORDER BY [JIA].JobStatus

Might make it easier to parse out.