I am trying to figure out a way to monitor SQL Jobs on multiple SQL Servers and add the details to a web form. I have done this with App Pools, Services, and Tasks but now I need to monitor jobs as well. I have a grid that I have created to store things like job name, run time, run date, status, etc.
I found this Question:
How to monitor SQL Server Agent Job info in C#
string sqlJobQuery = "select j.job_id, j.name, j.enabled, jh.run_status," +
" js.last_outcome_message, jh.run_date, jh.step_name, jh.run_time" +
" from sysjobs j left join sysjobhistory jh on (j.job_id = jh.job_id)" +
" left join sysjobservers js on (j.job_id = js.job_id)" +
" where jh.run_date = (select Max(run_date) from sysjobhistory)" +
" and jh.run_time = (select Max(run_time) from sysjobhistory)";
His query looks good but I need to join one more table from my database (dbo.Monitor) which includes a server IP so I can monitor multiple servers. I have tried adding LEFT JOIN Monitor.dbo.SQLJobs in multiple places but it never works. Here is the query I am running:
@serverIP nvarchar(20),
@JobID nvarchar(100)
SELECT Monitor.dbo.SQLJobs.ServerIP, msdb.dbo.sysjobs.job_id, msdb.dbo.sysjobs.name,
msdb.dbo.sysjobhistory.run_date, msdb.dbo.sysjobhistory.run_time, msdb.dbo.sysjobhistory.run_status, msdb.dbo.sysjobhistory.step_name
FROM msdb.dbo.sysjobs LEFT JOIN msdb.dbo.sysjobhistory
ON (msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobhistory.job_id)
WHERE Monitor.dbo.SQLJobs.ServerIP = @serverIP
AND msdb.dbo.sysjobs.job_id = @JobID
AND msdb.dbo.sysjobhistory.run_date = (SELECT MAX(run_date) FROM msdb.dbo.sysjobhistory)
AND msdb.dbo.sysjobhistory.run_time = (SELECT MAX(run_time) FROM msdb.dbo.sysjobhistory)
AND msdb.dbo.sysjobhistory.step_name <> '(Job outcome)'
Where could I add the other JOIN to make this query work? Please let me know if I need to add any more information before down voting me.
Sorry guys I have not checked this in a while. My query would not work because I am trying to query sys jobs on two servers in different domains.
I had to link the servers in order to get data from sys jobs from both servers and display the data in one form.