how to get my sql jobs using SQL Query

Question

I want to get the SQL Jobs with the Following Columns:

• Name
• Enabled
• Status
• Last Run Date
• Next Run Date
• Last Run Outcome
• Category ID
• Category
• Runnable
• Scheduled
• Created Date
• Modified Date

I tried using the following Query, But I could not get the proper answer.

USE MSDB
SELECT
@@servername as [ServerName]
,SJ.job_id AS [Job Id]
,NAME AS [Job Name]
,iif(max(enabled) = 1, 'Yes', 'No')
--,SJH.run_status as [Job Status]
--,SJH.[run_status]
--,SJH.[Last Run]
--,[Next Run]
,max(category_id) as [Category Id]
--,SJ.Category
--,Runnable
--,Scheduled
,max(date_created) AS [Job Created Date]
,max(date_modified) AS [Job Modified Date]
FROM sysjobhistory SJH
JOIN sysjobs SJ
ON SJH.job_id=sj.job_id
GROUP BY @@servername
,SJ.job_id
,NAME
,enabled
--,SJH.run_status as [Job Status]
--,SJH.[run_status]
--,SJH.[Last Run]
--,[Next Run]
,category_id
--,SJ.Category
--,Runnable
--,Scheduled
,date_created
,date_modified
ORDER BY date_created

If jobs in the Jobs section in the SQL server agent is 12, it is showing like 12* no of history for each particular job.

Please help me out.

Thanks in Advance.

Answer 1

Hi @Navinkumar Kanagaraj
It seems you don't need that much columns in your Group By clause.
How about change the Group By clause to GROUP BY @@servername,SJ.job_id,NAME ?

Best regards,
LiHong

Answer 2

You don't need JobHistory in your query for the fields you are using.

select   
	@@servername as [ServerName]  
	,SJ.job_id AS [Job Id]  
	,NAME AS [Job Name]  
	,CASE WHEN enabled=1 THEN 'Yes' ELSE 'No' END AS [Enabled]  
	,category_id  
	,date_created as [Job Created Date]  
	,date_modified AS [Job Modified Date]  
from msdb..sysjobs SJ