-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

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