SQL Server Job Change Auditing

#function: monitor job change and email for any changesimport-module dbatools, sqlserver;
 
#[boolean] $debug = 1; # 0 = $false; , 
$stg_path = 'c:\JobAuditing\Staging'; # better not have \ at the end
$curr_path = 'C:\JobAuditing\Curr';
$hist_path = 'C:\JobAuditing\Hist';
$central_svr = 'localhost\sql2017'; # central sql instance to host monitored job info
$central_db = 'MSSQLTips';
$dba_email = '[email protected]'; # replace it with your own email 
 
$monitored_server = 'localhost\sql2017'; # does NOT need to be the same as $central_svr
 
if (test-path -Path "$stg_path\*.sql")
{ remove-item -Path "$stg_path\*.sql";}
 
$tbl  = new-object "system.data.DataTable";
$col = new-object System.Data.DataColumn ("SQLInstance", [system.string]);
$tbl.Columns.Add($col);
$col = new-object System.Data.DataColumn ("JobName", [system.string]);
$tbl.Columns.Add($col);
$col = new-object System.Data.DataColumn ("FilePath", [system.string]);
$tbl.Columns.Add($col);
$col = new-object System.Data.DataColumn ("Hash", [system.string]);
$tbl.Columns.Add($col);
 
#clean up joblist_staging table
invoke-sqlcmd -ServerInstance $central_svr -Database $central_db -Query "truncate table dbo.JobList_Staging;" -QueryTimeout 120 -ConnectionTimeout 120;
 
    try {
        $jobs = get-dbaAgentJob -SqlInstance $monitored_server;
        foreach ($j in $jobs)
        {
            write-host "Processing job:$($j.Name)" -ForegroundColor Green;
            #replace special chars inside [\/":'] to blank or $
            $fpath="$stg_path\$(($j.sqlInstance).replace('\', '$'))_$(($j.name-replace '[:"'']', '')-replace '[\\\/]','$').sql"
 
            $j |  Export-DbaScript  -NoPrefix -Encoding ASCII -FilePath $fpath  | Out-Null;
            $hs=Get-FileHash -Path $fpath;
            $r = $tbl.NewRow();
            $r.SQLInstance =$j.SqlInstance;
            $r.JobName = $j.Name;
            $r.FilePath = $fpath;
            $r.Hash = $hs.Hash;
            $tbl.Rows.add($r);
          } #foreach $j
          Write-SqlTableData -ServerInstance $central_svr -DatabaseName $central_db -SchemaName dbo -TableName JobList_Staging -InputData $tbl;
          $tbl.Clear();
          $svr = $j.sqlinstance;
          $JobChange_qry = @"
-- check whether there is new job
if exists (
   select Jobname from dbo.JobList_staging where SQLinstance= '$($svr)'
   except
   select Jobname from dbo.JobList_Current where SQLInstance = '$($svr)')
begin
   ; with c as (
   select Jobname from dbo.JobList_staging where SQLInstance= '$($svr)'
   except
   select Jobname from dbo.JobList_Current where SQLInstance = '$($svr)'
   )
   update s set [Status]='N' -- new
   from dbo.JobList_Staging s
   inner join c
   on s.SQLInstance = '$($svr)'
   and s.JobName = c.Jobname;

   insert into dbo.JobList_Current (SQLInstance, JobName, [FilePath], [Hash])
   select s.SQLInstance, s.JobName, replace(s.[FilePath], '$($stg_path)', '$($curr_path)'), s.[Hash]
   from dbo.JobList_Staging s
   where s.SQLInstance = '$($svr)' and s.[Status]='N';
end

-- check whether there is any job changed since last check
if exists (select s.Jobname from dbo.JobList_staging s
inner join dbo.JobList_Current l
on s.SQLInstance = l.SQLInstance and l.SQLInstance = '$($svr)'
and s.JobName = l.JobName and (s.[Status] <> 'N' or s.[Status] is null)
and s.[Hash] <> l.[hash])
begin
   update s set [Status]='U' -- update
   from dbo.JobList_Staging s
   inner join dbo.JobList_Current l
   on s.SQLInstance = l.SQLInstance
   and s.JobName = l.Jobname
   and s.SQLInstance = '$($svr)'
   and s.[Hash] <> l.[hash];

   update l set l.[Hash]=s.[Hash]
   , l.[LogDate]=getdate()
   output deleted.[SQLInstance], deleted.[JobName],
   deleted.[Hash], 'U'
   into dbo.JobList_History (
   [SQLInstance], [JobName],[Hash],[Status])
   from dbo.JobList_Current l
   inner join dbo.JobList_Staging s
   on s.SQLInstance = l.SQLInstance
   and s.JobName = l.Jobname and s.SQLInstance = '$($svr)'
   and s.[Status]='U';
end
-- check whether there is a job deleted since last check
if exists ( select l.SQLInstance, l.Jobname from dbo.JobList_Current l
         where SQLInstance = '$($svr)'
         except
         select s.SQLInstance, s.JobName
         from dbo.JobList_Staging s
           where s.SQLInstance = '$($svr)'
         )
begin
   ; with c as (
             select l.SQLInstance, l.JobName from dbo.JobList_Current l
             where SQLInstance = '$($svr)'
             except
             select s.SQLInstance, s.JobName
             from dbo.JobList_Staging s
                where s.SQLInstance = '$($svr)'
            )
      delete dbo.JobList_Current
      output deleted.[SQLInstance], deleted.[JobName],
      deleted.[Hash], 'D'
      into dbo.JobList_History ([SQLInstance], [JobName], [Hash], [Status])
      from dbo.JobList_Current l
      inner join c
      on c.SQLInstance = l.SQLInstance
      and c.Jobname = l.Jobname;
end
-- we need to report the change
select SQLInstance, JobName, [Status]='Deleted'
from dbo.JobList_History
where [Status] ='D'
and LogDate > dateadd(minute, -1, getdate()) -- to avoid reporting previous records
and SQLInstance = '$($svr)'
union all
select SQLInstance, JobName, [Status]= case [status] when 'U' then 'Updated' else 'New' end
from dbo.JobList_Staging
where [status] in ('N', 'U')
and SQLInstance = '$($svr)';
"@;
    $rslt = Invoke-Sqlcmd -ServerInstance $central_svr -Database $central_db -Query $JobChange_qry -OutputAs DataRows;
 
    if ($rslt -ne $null)
    {   foreach ($r in $rslt)
        {
            $fp="$($svr.replace('\', '$'))_$(($r.jobname-replace '[:"'']', '')-replace '[\\\/]','$').sql";
            switch ($r.Status)
            {
                'New' { Move-Item -path "$stg_path\$fp" -Destination $curr_path -Force; break;}
                'Updated' { # we need to find the original file and rename it according to its original write time 
                    $fn = dir -path "$curr_path\$fp";
                    $fpath= $fn.fullname.substring(0, $fn.fullname.Length-4) + '_' + ($fn.LastWriteTime).tostring('yyyyMMdd') +'_original.sql';
                    $fname = Split-Path $fpath -leaf;
                    Move-Item -Path $($fn.FullName) -Destination $hist_path\$fname;
                    Copy-Item -Path  "$stg_path\$fp" -Destination $curr_path -Force; break;
                } # 'update'
                #we need to mark the file that is deleted
                'Deleted' {
                 $fn = dir -path "$curr_path\$fp";
                 $fpath= $fn.fullname.substring(0, $fn.fullname.Length-4) + '_' + ($fn.LastWriteTime).tostring('yyyyMMdd') +'_deleted.sql'; 
                 $fname = Split-Path $fpath -leaf;
                 Move-Item -Path $($fn.FullName) -Destination $hist_path\$fname -force;
                 break;
                }# 'deleted'
            } #switch
        } #foreach ($r in $rslt)
    } #if $rslt -ne null
    }#try
    catch {
        write-error $_;
    }#catch
 
$qry = @"
drop table if exists #tmp;
declare @minutes int = -1;
select SQLInstance, JobName, [Status]='Deleted'
, [OriginalFile]= '$($hist_path)' + replace(SQLInstance,'\', '$')
+ '_' +replace(Jobname, ':', '') + '_' + format(logdate, 'yyyyMMdd') +'_deleted.sql'
into #tmp
from dbo.JobList_History
where [Status] = 'D'
and LogDate > dateadd(minute, @minutes, getdate())
union all
select SQLInstance, Jobname, [Status]= 'Update'
, [FilePath]= '$($hist_path)' + replace(SQLInstance,'\', '$')
+ '_' +replace(Jobname, ':', '') + '_' + format(getdate(), 'yyyyMMdd') +'_original.sql'
from dbo.JobList_Staging
where [status] in ('U')
union all -- new jobs for non-first loading
select SQLInstance, Jobname, [Status]= 'New'
, [FilePath]= replace(FilePath, '$($stg_path)','$($curr_path)')
from dbo.JobList_Staging
where [status] in ('N ')
and SQLInstance in (select distinct SQLInstance from dbo.JobList_Current where LogDate < dateadd(minute, @minutes, getdate()));

DECLARE @xml NVARCHAR(MAX);
DECLARE @body NVARCHAR(MAX);

SET @xml = CAST(( select SQLInstance as 'td','',
      Jobname as 'td','',
      Status as 'td','',
      OriginalFile as 'td'
from #tmp t
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body><H3>Job Change Notification</H3>
<table border = 1>
<tr><th> SQLInstance </th> <th> Job </th> <th> Status </th> <th> FilePath </th></tr>'   

SET @body = @body + @xml +'</table></body></html>';

if exists (select * from #tmp)
begin
   EXEC msdb.dbo.sp_send_dbmail
   @profile_name = 'SQLAgent',
   @body = @body,
   @body_format ='HTML',
   @recipients = '$($dba_email)',
   @subject = 'Job Change History on$((Get-Date).toString('yyyy-MMM-dd')) ' ;
end
"@;
invoke-sqlcmd -ServerInstance $central_svr -Database $central_db -Query $qry;
;