The following answer has been tested in SQL 2008R2 to 2016 (Enterprise Edition, but I don't think that matters)
Extended events are not available for jobs. This solution provides specific version information that is available for searching across multiple servers in the form of a Job step name (sysjobsteps.step_name). It also provides space to track complete versioning history. It can be used with or without a versioning tools like github.
It assumes all the jobs you are interested in, are owned by the SA account and the job names begins with 'Custom'. These assumptions can be modified as needed. The approach below is 3 fold, Steps 1 and 2 should be run against "Central Management Servers" or 'Local Server Groups"
- Identify servers with "Custom%' jobs that have not had any version tracking appied
- Identify Servers that do not have the current version applied
- Where no version tracking is present, update existing step one to the correct version, and add version tracking as a new step one. A slightly different approach would be required for versions updates after v1.0.0.
Notice that new version tracking step is added after the exiting step has been modified to give durability. If there are multiple steps, they are all migrated one step by the insertion of the new step one. There are 3 levels of redundancy in the area (SQL Command) used to track version history;
- On both success and failure of step 1, the action is go to next step
- Block comments (Slash star) are used to isolate the entire body of version history
- Line Comments are used on each line.
Identify servers with "Custom%' jobs that have not had any version tracking appied
-- Identify servers that have not had all versions of our custom jobs applied
-- If the counts are not equal they are missing a version identifier.
-- Count Custom and count Correct version
-- Excludes servers that don't have any jobs beginning Custom
SELECT COUNT ( DISTINCT sysjobs.name)
WHERE owner_sid = 0x01 -- SA owns the Job
AND enabled = 1 -- It is enabled
AND name like 'Custom%' -- name starts with custom
) AS CountCustomJobs
SELECT COUNT ( DISTINCT sysjobsteps.step_id)
WHERE step_id = 1 --Limit to step 1
AND sysjobsteps.step_name like 'v1%' -- Name starts with v1
) AS CountVersionSteps
Identify Servers that do not have the current version applied
--Run against CMS to find jobs that have not been updated to current version
LEFT JOIN (
SELECT sysjobs.name as 'Job_Name'
) AS JN
ON sysjobsteps.job_id = JN.job_id
WHERE step_id = 1
AND sysjobsteps.step_name = 'Old Wrong Name'
OR (sysjobsteps.step_name like 'v1%'
AND sysjobsteps.step_name <> 'v1.0.0 - Custom - <jobname>'
Where no version tracking is present, update existing step one to the correct version, and add version tracking as a new step one
--Update Custom - <jobname> to v1.0.0
--Author: James Jenkins Feb 6, 2018
--Puts the version number in the title of step 1, includes details in the command as comments,
--Updates step 1 and moves orginal step 1 to step 2 with updates
--It does not modify schedules or existing descriptions on the job
--Update existing step 1 to current standard parameters
EXEC msdb.dbo.sp_update_jobstep @job_name=N'Custom - <jobname>', @step_id=1 ,
@V1 = N''<value>''
--Create new step 1 (creates as step 1 and SQL pushes old step one to step 2)
EXEC msdb.dbo.sp_add_jobstep @job_name=N'Custom - <jobname>', @step_name=N'v1.0.0 - Custom - <jobname>',
--Step exists to track verision history
--v1.0.0 - Change made documented here