Error loading Risk Fabric SQL job status details in Risk Fabric Health dashboard
Article ID: 188799
Updated On:
Products
Information Centric Analytics
Data Loss Prevention Core Package
Issue/Introduction
When attempting to view the Risk Fabric - SQL Job Status - Detail dashboard widget in the Information Centric Analytics (ICA) console under Dashboards > Risk Fabric Health > Health Summary, the following error is displayed:
Error loading Risk Fabric - SQL Job Status - Detail data
Environment
Version : 6.5.3
Component : Dashboards
Cause
The next run date is not properly configured in a SQL Server Agent job schedule. When the NextRunDate or NextRunTime value is 0 on a recurring job schedule, a conversion error occurs.
Resolution
This limitation is fixed in ICA version 6.5.4.
Additional Information
The following error is captured in the RiskFabric server log:
<date> <time> [93:ERROR] DALException.SaveLog() Error: Conversion failed when converting date and/or time from character string.
Exception at <day>, <month> <date>, <year> <time>
Application: .Net SqlClient Data Provider
Exception Type: System.Data.SqlClient.SqlException
Stack Trace:
at RiskFabric.Web.Library.DALException.ControlThrow(Exception ex)
at RiskFabric.Web.DAL.ExecuteAndLogTime[T](DALTimer timer, SqlCommand sqlCommand, String loggingInfo, Func{{1 command) at RiskFabric.Web.DAL.DataTable(SqlConnection conn, SqlCommand cmd, Int32 timeoutSeconds, Boolean closeConnection) at RiskFabric.Web.DAL.DataTable(String connectionString, SqlCommand cmd, Int32 timeout) at RiskFabric.Web.Classes.Metrics.CustomDashboards.QueryConfiguration.GetSqlDataTable(IEnumerable}}1 inputParameters)
at RiskFabric.Web.Classes.Metrics.CustomDashboards.QueryConfiguration.GetDataTable(IEnumerable{{1 inputParameters) at RiskFabric.Web.Classes.Metrics.CustomDashboards.QueryConfiguration.GetResults(IEnumerable}}1 inputParameters)
at RiskFabric.Web.Classes.Metrics.CustomDashboards.CustomDashboard.GetTableData(DtoWidgetSearchParams searchParams)
at RiskFabric.Web.Controllers.MetricsController.GetTableDataFiltered(DtoWidgetSearchParams searchParams)
Source: Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])
Data Keys
HelpLink.ProdName: Microsoft SQL Server
HelpLink.ProdVer: 13.00.4502
HelpLink.EvtSrc: MSSQLServer
HelpLink.EvtID: 241
HelpLink.BaseHelpUrl: http://go.microsoft.com/fwlink
HelpLink.LinkId: 20476
Command: {"CommandText":" SELECT Distinct SOrgS.originating_server , SJ.name [Job Name]
n , case when SJ.description is null then '' when SJ.description = 'No description available.' then '' else SJ.description
n END Description , SJ.job_id , convert(varchar,(MAX(CONVERT(DATE, CONVERT(varchar(8), run_date), 101))), 101) LastRunDate
n , substring(convert(varchar, msdb.dbo.agent_datetime(run_date, run_time), 108 ), 1, 5) LastRunTime \\n\\t\\t
t , convert(varchar,(MAX(CONVERT(DATE, CONVERT(varchar(8), next_run_date), 101))), 101) NextRunDate \\n\\t\\t
t ,substring(convert(varchar, msdb.dbo.agent_datetime(next_run_date, next_run_time), 108), 1,5) NextRunTime \\n\\t\\t
t ,CASE WHEN SJ.[enabled]=1 THEN 'Enabled' ELSE 'Disabled' END [Job Status] \\n\\t\\t
t ,CASE WHEN SJH.run_status=0 THEN 'Failed' WHEN SJH.run_status=1 THEN 'Succeeded' \\n\\t\\t
t WHEN SJH.run_status=2 THEN 'Retry' WHEN SJH.run_status=3 THEN 'Cancelled' \\n\\t\\t
t ELSE 'Unknown' END [Job Outcome] \\n\\t\\t
t , STUFF( STUFF(RIGHT('000000' + CAST(SJH.[run_duration] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)] \\n\\t\\t
t ,[Occurs] = CASE SCH.[freq_type] \\n\\t\\t\\t\\t\\t
t WHEN 1 THEN 'Once' WHEN 4 THEN 'Daily' \\n\\t\\t\\t\\t\\t
t WHEN 8 THEN 'Weekly' WHEN 16 THEN 'Monthly' \\n\\t\\t\\t\\t\\t
t WHEN 32 THEN 'Monthly relative' WHEN 64 THEN 'When SQL Server Agent starts' \\n\\t\\t\\t\\t\\t
t WHEN 128 THEN 'Start whenever the CPU(s) become idle' ELSE '' END \\n\\t\\t\\t\\t\\t
t , dateadd(s, 1, convert(datetime,(MAX(CONVERT(DATETIME,RTRIM(run_date),126))))) LastRunDateSort \\n\\t\\t\\t\\t\\t\\t
t , dateadd(s, 1, convert(datetime,(MAX(CONVERT(DATETIME,RTRIM(run_date),126))))) NextRunDateSort \\n\\t\\t\\t\\t\\t\\t
t FROM msdb..sysjobhistory SJH JOIN msdb..sysjobs SJ \\n\\t\\t\\t\\t\\t\\t
t ON SJH.job_id=sj.job_id join msdb..sysjobschedules SJS \\n\\t\\t\\t\\t\\t\\t
t on SJH.job_id = SJS.job_id join msdb..sysschedules SCH \\n\\t\\t\\t\\t\\t\\t
t on SJS.schedule_id = SCH.schedule_id join msdb..sysoriginatingservers_view SOrgS \\n\\t\\t\\t\\t\\t\\t
t on SJ.originating_server_id = SOrgS.originating_server_id WHERE step_id=0 \\n\\t\\t\\t\\t\\t\\t\\t
tAND ( (isnull(@d_jobname,'1') = '1' or @d_jobname = '' or SJ.name in (select * from dbo.fnParseDelimitedList(@d_jobname, ','))) and (SJ.name not like 'DBA%' and SJ.name not like 'Sys%') \\n\\t
t \\n\\t\\t\\t\\t\\t\\t\\t
tAND (@d_startdate is null or CONVERT(DATETIME,RTRIM(run_date),113) >= @d_startdate) and (@d_enddate is null or CONVERT(DATETIME,RTRIM(run_date),113) <= @d_enddate) AND ( (isnull(@d_jobstatus,'-1') = '-1' or @d_jobstatus = '' or cast(SJH.run_status as nvarchar) in ( select * from dbo.fnParseDelimitedList(@d_jobstatus, ',')) ))) group by SOrgS.originating_server,SJ.job_id, SJ.name, SJ.description, run_date,run_time,SJ.enabled, SJH.run_status,run_duration,SJS.[next_run_date], SJS.[next_run_time],SCH.[freq_type] ORDER BY SOrgS.originating_server,SJ.name desc, LastRunDate desc, LastRunTime desc ","Parameters":[{"Name":"d_jobname","Direction":"Input","Value":""},{"Name":"d_jobstatus","Direction":"Input","Value":""},{"Name":"d_startdate","Direction":"Input","Value":"<date> <time>"},{"Name":"d_enddate","Direction":"Input","Value":"<date> <time>"}],"ExecutionTime":"00:00:00"}
To identify recurring job schedules that do not have a NextRunDate and NextRunTime scheduled, follow this procedure:
- Open SQL Server Management Studio (SSMS)
- Connect to the Database Engine hosting the RiskFabric database
- Select File > New > Query with Current Connection from the menu bar
A new query editor window will open - Copy the following query and paste it in the new query editor window:
SELECT DISTINCT sosv.originating_server AS "Server",
sj.[name] AS "JobName",
CASE
WHEN sj.[description] IS NULL THEN ''
WHEN sj.[description] = 'No description available' THEN ''
ELSE sj.[description]
END AS "Description",
sj.job_id AS "JobID",
CONVERT(varchar,(MAX(CONVERT(DATE, CONVERT(varchar(8), run_date), 101))), 101) AS "LastRunDate",
SUBSTRING(CONVERT(varchar, msdb.dbo.agent_datetime(run_date, run_time), 108 ), 1, 5) AS "LastRunTime",
CONVERT(varchar,(MAX(CONVERT(DATE, CONVERT(varchar(8), CASE WHEN next_run_date = 0 THEN '19710101' ELSE next_run_date END), 101))), 101) AS "NextRunDate",
SUBSTRING(CONVERT(varchar, msdb.dbo.agent_datetime(CASE WHEN next_run_date = 0 THEN '19710101' ELSE next_run_date END, next_run_time), 108), 1,5) AS "NextRunTime",
CASE
WHEN sj.[enabled]=1 THEN 'Enabled'
ELSE 'Disabled'
END AS "JobStatus",
CASE
WHEN sjh.run_status=0 THEN 'Failed'
WHEN sjh.run_status=1 THEN 'Succeeded'
WHEN sjh.run_status=2 THEN 'Retry'
WHEN sjh.run_status=3 THEN 'Cancelled'
ELSE 'Unknown'
END AS "JobOutcome",
STUFF(STUFF(RIGHT('000000' + CAST(sjh.[run_duration] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)],
"Occurs" = CASE sch.[freq_type]
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 6 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQL Server Agent starts'
WHEN 128 THEN 'Start whenever the CPU(s) become(s) idle'
ELSE ''
END ,
DATEADD(s, 1, CONVERT(datetime,(MAX(CONVERT(DATETIME,RTRIM(run_date),126))))) AS "LastRunDateSort",
DATEADD(s, 1, CONVERT(datetime,(MAX(CONVERT(DATETIME,RTRIM(run_date),126))))) AS "NextRunDateSort"
FROM msdb..sysjobhistory AS sjh
INNER JOIN msdb..sysjobs AS sj
ON sjh.job_id = sj.job_id
INNER JOIN msdb..sysjobschedules AS sjs
ON sjh.job_id = sjs.job_id
INNER JOIN msdb..sysschedules AS sch
ON sjs.schedule_id = sch.schedule_id
INNER JOIN msdb..sysoriginatingservers_view AS sosv
ON sj.originating_server_id = sosv.originating_server_id
WHERE sjh.step_id = 0 AND
(
(
ISNULL(NULL,'1') = '1' OR
NULL = '' OR
sj.[name] IN
(
SELECT *
FROM dbo.fnParseDelimitedList(NULL, ',')
)
) AND
(
sj.[name] NOT LIKE 'DBA%' AND
sj.[name] NOT LIKE 'Sys%'
) AND
(
'2020-04-02 00:00:00' IS NULL OR
CONVERT(DATETIME,RTRIM(run_date),113) >= '2020-04-02 00:00:00'
) AND
(
'2020-04-09 00:00:00' IS NULL OR
CONVERT(DATETIME,RTRIM(run_date),113) <= '2020-04-09 00:00:00'
) AND
(
(
ISNULL(NULL,'-1') = '-1' OR
NULL = '' OR
CAST(sjh.run_status AS nvarchar) IN
(
SELECT *
FROM dbo.fnParseDelimitedList(NULL, ',')
)
)
)
)
GROUP BY sosv.originating_server,
sj.job_id,
sj.[name],
sj.[description],
run_date,
run_time,
sj.[enabled],
sjh.run_status,
run_duration,
sjs.[next_run_date],
sjs.[next_run_time],
sch.[freq_type]
ORDER BY sosv.originating_server,
sj.[name] DESC,
LastRunDate DESC,
LastRunTime DESC; - Execute the query by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Query > Execute from the menu bar
- If any records are returned, note the job name(s)
- In Object Explorer, navigate to SQL Server Agent > Jobs
- Right-click the job matching the job name noted in step 6 and select Properties
The Job Properties - <job-name> window will open - Select the Schedules page and click the Edit button
The Job Schedule Properties - <job-name> Schedule window will open - Assign a schedule to the job and click the OK button to save the schedule and close the Job Schedule Properties window
- Click the OK button to close the Job Properties window
- Repeat steps 7 through 11 for any additional jobs identified in step 6
Feedback
Yes
No
Powered by
