-1

I'm trying to create a table which contains all the failed SQL agent jobs from all the servers. I'm facing difficulties in getting all the jobs from all the servers into one table

sscsql
  • 1
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Jan 09 '23 at 02:27
  • On the server that you want the table to live, do you have Linked Servers setup to the other servers? – J.D. Jan 09 '23 at 02:41
  • Yes, I have linked servers – sscsql Jan 09 '23 at 02:43
  • Ok, so what have you tried and what are you stuck on? – J.D. Jan 09 '23 at 14:56
  • I can get the failed jobs from only current server. I dont know how to proceed further – sscsql Jan 09 '23 at 19:41
  • Can you provide the script you are using for the current server. Do you want to have the other servers dynamically or are they fixed? – Peter Jan 10 '23 at 07:14

1 Answers1

1

If you have a linked server already setup them and the security setup correctly then you should be able to pull the job information with the below query. This one is configured to pull for one day but can be changed.

SELECT MSDB.dbo.agent_datetime(jh.run_date,jh.run_time) as date_time
    ,j.name as job_name,js.step_id as job_step
    ,jh.message as error_message
    FROM [LINKED_SERVER_NAME].msdb.dbo.sysjobs AS j
    INNER JOIN [LINKED_SERVER_NAME].msdb.dbo.sysjobsteps AS js ON js.job_id = j.job_id
    INNER JOIN [LINKED_SERVER_NAME].msdb.dbo.sysjobhistory AS jh ON jh.job_id = j.job_id AND jh.step_id = js.step_id
    WHERE jh.run_status = 0 AND MSDB.dbo.agent_datetime(jh.run_date,jh.run_time) >= GETDATE()-1
    ORDER BY MSDB.dbo.agent_datetime(jh.run_date,jh.run_time) DESC

Credit to https://dba.stackexchange.com/a/249624/262264

  • Sorry I don't understand this. I have two or three servers from which i want to get the details of the failed jobs – sscsql Jan 12 '23 at 01:41