Determine scheduled execution status and review data source query history

book

Article ID: 171436

calendar_today

Updated On:

Products

Information Centric Analytics Data Loss Prevention Core Package

Issue/Introduction

Data Source Query jobs to pull data into the ICA Staging area are will be executed on a set schedule defined by the end-user.  The data source query scheduled can be set to run on an hourly, daily or weekly basis.  The SQL Server Agent job ‘RiskFabric – Integration Wizard DataSource Query Manager’ job is tasked with querying the ‘IW_DataSourceQuery’ table to determine if it is time to bring data into the ICA staging area.  When the When the Column DisabledDate is null, the JobIntervalMinutes > 0, the JobStatusFlag <> ‘R’ and the JobLastRunDate, JobStartDate are compared against the GetDate() function to determine if the difference exceeds the defined in the column JobIntervalMinutes column.  When these conditions are met, the the JobIsRunnable flag will be set to 1.  When the JobIsRunnable field is set to 1 the data source query will be invoked by the RiskFabric – Integration Wizard DataSource Query Manager job. 

To Determine the status of a data source query job, you will need to evaluate the value set in the JobStatusFlag field for the data source query.  There are three potential statuses that the JobStatusFlag can contain.  The status flag will either be set to a value of ‘R’ for running, ‘C’ for complete or a value of ‘F’ for failure.  In the event that the data source query results in a JobStatusFlag=’F’, you will need to execute the following queries against the ‘IW_DataSourceQuery’ table to review the status of the query and then you will need to query the ‘vIW_DataSourceQueryHistory’ view to obtain a complete history of the data source query.

Resolution

  1. Obtain the DataSourceQueryID from the IW_DataSourceQuery table to review the history of the data source query.  The SQL below can be used to review the current status of each data source query job. 
    • Select DataSourceQueryID, DataSourceQueryName, DataSourceQueryStatement, DestinationTableName, JobStartDate, JobLastRunDate, JobIntervalMinutes,JobStatusFlag, JobIsRunnable, JobRunCount From IW_DataSourceQuery
  2. ​​Once you have Obtained the datasourcequeryID from the previous query, you will use the view vIW_DataSourceQueryHistory to obtain the status of the DataSourceQuery 
  3. Manager job each time it has been executed.  The where Clause can be modified to look for failures of a job.  The LogDescription column can be used to evaluate and document the exact SQL used to execute the data source query. 
    • Select DataSourceQueryID, DataSourceQueryName, StartDate, Run Minutes, Rows Affected, StatusFlag,ErrorDesc,LogDescription from vIW_DataSourceQueryHistory Where  DataSourceQueryID=X Order by Start Date DESC

 

Attachments