SUmmary of NCM job status - number of errors
SOmething like this:
SELECT
jv.NCMJobName
--,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate) as [StartJob]
,'StartJob: '+format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy') as [StartJob]
--,substring(JobLog,charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),joblog),9999) as [x]
----,substring(Replace(Joblog, '''', ''),charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),Replace(Joblog, '''', '')),9999) as [y]
--,cast (((datalength(substring(Replace(Joblog, '''', ''),charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),Replace(Joblog, '''', '')),9999)) - datalength(replace(substring(Replace(Joblog, '''', ''),charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),Replace(Joblog, '''', '')),9999),'ERROR:','')))/datalength('ERROR:')/2) as varchar) as [Error Count]
FROM NCM_JobLogs jl
left join NCM_NCMJobsView jv on jv.ncmjobid = jl.NCMJobId
inner join (select max(rundate) as [md] from NCM_JobLogs join NCM_NCMJobsView on NCM_NCMJobsView.ncmjobid = NCM_JobLogs.NCMJobId where NCMJobName='Nightly Network Inventory') a on a.md=jl.rundate
where jv.NCMJobName='Nightly Network Inventory'
group by jl.JobLog, jl.RunDate, jl.NCMJobId, jv.NCMJobName
union all
SELECT
jv.NCMJobName
--,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate) as [StartJob]
--,format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy') as [StartJob]
--,substring(JobLog,charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),joblog),9999) as [x]
----,substring(Replace(Joblog, '''', ''),charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),Replace(Joblog, '''', '')),9999) as [y]
,'Error: ' + cast (((datalength(substring(Replace(Joblog, '''', ''),charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),Replace(Joblog, '''', '')),9999)) - datalength(replace(substring(Replace(Joblog, '''', ''),charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),Replace(Joblog, '''', '')),9999),'ERROR:','')))/datalength('ERROR:')/2) as varchar) as [Error Count]
FROM NCM_JobLogs jl
left join NCM_NCMJobsView jv on jv.ncmjobid = jl.NCMJobId
inner join (select max(rundate) as [md] from NCM_JobLogs join NCM_NCMJobsView on NCM_NCMJobsView.ncmjobid = NCM_JobLogs.NCMJobId where NCMJobName='Nightly Network Inventory') a on a.md=jl.rundate
where jv.NCMJobName='Nightly Network Inventory'
group by jl.JobLog, jl.RunDate, jl.NCMJobId, jv.NCMJobName
-----------------------
union all
SELECT
jv.NCMJobName
--,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate) as [StartJob]
,'StartJob: '+format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy') as [StartJob]
--,substring(JobLog,charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),joblog),9999) as [x]
----,substring(Replace(Joblog, '''', ''),charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),Replace(Joblog, '''', '')),9999) as [y]
--,cast (((datalength(substring(Replace(Joblog, '''', ''),charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),Replace(Joblog, '''', '')),9999)) - datalength(replace(substring(Replace(Joblog, '''', ''),charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),Replace(Joblog, '''', '')),9999),'ERROR:','')))/datalength('ERROR:')/2) as varchar) as [Error Count]
FROM NCM_JobLogs jl
left join NCM_NCMJobsView jv on jv.ncmjobid = jl.NCMJobId
inner join (select max(rundate) as [md] from NCM_JobLogs join NCM_NCMJobsView on NCM_NCMJobsView.ncmjobid = NCM_JobLogs.NCMJobId where NCMJobName='Nightly Config Backup') a on a.md=jl.rundate
where jv.NCMJobName='Nightly Config Backup'
group by jl.JobLog, jl.RunDate, jl.NCMJobId, jv.NCMJobName
union all
SELECT
jv.NCMJobName
--,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate) as [StartJob]
--,format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy') as [StartJob]
--,substring(JobLog,charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),joblog),9999) as [x]
----,substring(Replace(Joblog, '''', ''),charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),Replace(Joblog, '''', '')),9999) as [y]
,'Error: ' + cast (((datalength(substring(Replace(Joblog, '''', ''),charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),Replace(Joblog, '''', '')),9999)) - datalength(replace(substring(Replace(Joblog, '''', ''),charindex(format(DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), jl.RunDate),'M/d/yyyy'),Replace(Joblog, '''', '')),9999),'ERROR:','')))/datalength('ERROR:')/2) as varchar) as [Error Count]
FROM NCM_JobLogs jl
left join NCM_NCMJobsView jv on jv.ncmjobid = jl.NCMJobId
inner join (select max(rundate) as [md] from NCM_JobLogs join NCM_NCMJobsView on NCM_NCMJobsView.ncmjobid = NCM_JobLogs.NCMJobId where NCMJobName='Nightly Config Backup') a on a.md=jl.rundate
where jv.NCMJobName='Nightly Config Backup'
group by jl.JobLog, jl.RunDate, jl.NCMJobId, jv.NCMJobName
union all
SELECT
jv.NCMJobName
,concat('SuccessRate: ',isNULL(SuccessRate,0)) as [SuccessRate]
--,concat('SubJobsCount: ',isNULL(SubJobsCount,0)) as [SubJobsCount]
--,concat('DurationInSec: ',isNULL(DurationInSec,0)) as [DurationInSec]
FROM NCM_JobStatistics js
join NCM_NCMJobsView jv on jv.NCMJobID = js.JobId
where NCMJobName='Nightly Config Backup'
and timestamp=(select max(TimeStamp) as [md] from NCM_JobStatistics join NCM_NCMJobsView on NCM_NCMJobsView.NCMJobID = NCM_JobStatistics.JobId where NCMJobName='Nightly Config Backup')
union all
SELECT
jv.NCMJobName
--,concat('SuccessRate: ',isNULL(SuccessRate,0)) as [SuccessRate]
,concat('SubJobsCount: ',isNULL(SubJobsCount,0)) as [SubJobsCount]
--,concat('DurationInSec: ',isNULL(DurationInSec,0)) as [DurationInSec]
FROM NCM_JobStatistics js
join NCM_NCMJobsView jv on jv.NCMJobID = js.JobId
where NCMJobName='Nightly Config Backup'
and timestamp=(select max(TimeStamp) as [md] from NCM_JobStatistics join NCM_NCMJobsView on NCM_NCMJobsView.NCMJobID = NCM_JobStatistics.JobId where NCMJobName='Nightly Config Backup')
union all
SELECT
jv.NCMJobName
--,concat('SuccessRate: ',isNULL(SuccessRate,0)) as [SuccessRate]
--,concat('SubJobsCount: ',isNULL(SubJobsCount,0)) as [SubJobsCount]
,concat('DurationInSec: ',isNULL(DurationInSec,0)) as [DurationInSec]
FROM NCM_JobStatistics js
join NCM_NCMJobsView jv on jv.NCMJobID = js.JobId
where NCMJobName='Nightly Config Backup'
and timestamp=(select max(TimeStamp) as [md] from NCM_JobStatistics join NCM_NCMJobsView on NCM_NCMJobsView.NCMJobID = NCM_JobStatistics.JobId where NCMJobName='Nightly Config Backup')
