Error loading Risk Fabric SQL job status details in Risk Fabric Health dashboard
search cancel

Error loading Risk Fabric SQL job status details in Risk Fabric Health dashboard

book

Article ID: 188799

calendar_today

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:

  1. Open SQL Server Management Studio (SSMS)
  2. Connect to the Database Engine hosting the RiskFabric database
  3. Select File > New > Query with Current Connection from the menu bar
    A new query editor window will open
  4. 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;
  5. 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
  6. If any records are returned, note the job name(s)
  7. In Object Explorer, navigate to SQL Server Agent > Jobs
  8. Right-click the job matching the job name noted in step 6 and select Properties
    The Job Properties - <job-name> window will open
  9. Select the Schedules page and click the Edit button
    The Job Schedule Properties - <job-name> Schedule window will open
  10. Assign a schedule to the job and click the OK button to save the schedule and close the Job Schedule Properties window
  11. Click the OK button to close the Job Properties window
  12. Repeat steps 7 through 11 for any additional jobs identified in step 6