Hi All,
Here is a widget for NCM job status:
SELECT
NCMJobName as [Job]
,case when nj.NCMJobType=0 then 'Execute Script'
when nj.NCMJobType=4 then 'Reboot'
when nj.NCMJobType=5 then 'Export Configs'
when nj.NCMJobType=6 then 'Maintenance'
when nj.NCMJobType=7 then 'Inventory'
when nj.NCMJobType=8 then 'Config Change'
when nj.NCMJobType=9 then 'Policy Report'
when nj.NCMJobType=10 then 'Purge Configs'
when nj.NCMJobType=11 then 'Download Configs'
else '?'
end as [TypeDesc]
,substring(NCMJobSchedule,charindex('<TriggerType>',NCMJobSchedule)+13,charindex('</TriggerType>',NCMJobSchedule)-charindex('<TriggerType>',NCMJobSchedule)-13) as [Schedule]
,convert(varchar, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), LastDateRunUtc),0) as [LastDateRun]
,a.q as [Error]
FROM NCM_NCMJobs nj
join (select
a.ncmjobid
,jv.NCMJobType
,case when jv.NCMJobType=11 and charindex('Run job error',jl.JobLog)>0
then 'Run Job Error'
when jv.NCMJobType=11 and charindex('Run job error',jl.JobLog)=0
then substring(substring(jl.JobLog,charindex(replace(convert(varchar, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), a.maxrundate) ,101),'/0','/') ,jl.JobLog)+11,999999),charindex('errors:',substring(jl.JobLog,charindex(replace(convert(varchar, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), a.maxrundate) ,101),'/0','/') ,jl.JobLog)+11,999999)),11)
when jv.NCMJobType=7 then concat('Errors: ',(DATALENGTH(substring(jl.JobLog,charindex(replace(convert(varchar, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), a.maxrundate) ,101),'/0','/') ,jl.JobLog)+11,999999))-DATALENGTH(REPLACE(substring(jl.JobLog,charindex(replace(convert(varchar, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), a.maxrundate) ,101),'/0','/') ,jl.JobLog)+11,999999),' error: ','')))/DATALENGTH(' error: ')/2)
when jv.NCMJobType=10 then '-'
end as [q]
from NCM_JobLogs jl
join (SELECT NCMJobId,max(RunDate) as [MaxRunDate] FROM NCM_JobLogs group by ncmjobid) a on a.NCMJobId=jl.NCMJobId and a.MaxRunDate=jl.RunDate
join NCM_NCMJobsView jv on jv.NCMJobID=jl.NCMJobId
) a on a.NCMJobId=nj.NCMJobID
where enabled=1
Thank you,
Amit
Loop1
