This article will provide you with the SQL necessary to troubleshoot the IW_DataSourceQuery that will pull data in from the source into the Staging environment.
1. The following query may be used to identify the DataSource Query that has failed during the nighty process. The DataSourceQuery ID will be used to take a closer look at the failure in an effort to further troubleshoot this issue.
- Execute this query to identify the DataSourceQueryID’s that currently have a status of 'F'. These DataSourceQueryID’s will be used to take a closer look into the IW_DataSourceQuery History.
SELECT * FROM IW_DataSourceQuery WHERE JobStatusFlag='F'
- In this phase of the process, we will use the DataSourceQueryID identified in step 1.a to take a closer look at the Log Description for the error being produced. StatusFlag will communicate to you the current status of the Job, C=Complete, R=Run and F=Failed. The Log Description will provide you with the exact statement that was executed at the time of failure. Error Description will provide you with the Description of the error thrown by SQL Server at the time of failure.
SELECT * FROM vIW_DataSourceQueryHistory WHERE DataSourceQueryID=<XX>
The entity creation process is a two-step process that will move the data from Staging into the pre-processing area. Once the data hits the Processing Area, the data will then flow into the logical data ware house via the Entity Creation phase of the process.
1. Identify the Import Rule Mapping that is experiencing a process failure by executing the query in a. Note the LogGroupID as it will be used in query b when the Log_DataTransformation Query is executed
SELECT * FROM vIW_ImportRuleMappingHistory WHERE StatusFlag = 'F' ORDER BY EndDate DESC
SELECT * FROM Log_DataTransformation WHERE LogGroupID = 49
- StoredProcedureName will identify the process that is running at the time of failure.
- LogDescription will contain the exact SQL Statement that was being executed at the time of failure.
- StatusFlag will communicate to you the current status of the Job, C=Complete, R=Run and F=Failed.
- ErrorCode and Error Description will provide the details of the error that occurred while the process was running.
There are certain instances within Risk Fabric where we would like to disable a Data Source Query from running. To do this, simply navigate to the Data Source tab in Admin > Integrations, right-click on a data source query and select Disable Query:
To delete a previously created Integration Pack, follow the steps below:
1. Open the ICA console.
2. Navigate to the Data Integrations tab under Admin > Integrations.
3. Delete existing Import Rule Mappings under the Integration Pack you want to remove. Before deleting an Integration Pack, all Import Rules under the Integration Pack need to be deleted. Similarly, deleting an Import Rule, all Import Rule Mappings under the Import Rule need to be deleted. To delete Import Rule Mappings, simply right-click on the Import Rule Mapping you want to delete and select Delete Import Rule Mapping.
4. After deleting all the Import Rule Mappings under all the Import Rules included in the Integration Pack you want to delete, open SQL Management Studio and open a new query against the RiskFabric database.
5. Execute the query below and note the ID of the Integration Pack you want to remove.
SELECT [ID], [Name] FROM IntegrationPacks
6. Execute the query below to delete all the Import Rules under the Integration Pack you want to remove, replacing <xx> with the value you retrieved in step
DELETE FROM IW_ImportRule
WHERE IntegrationPackID = <xx>
7. Finally, execute the query below to delete the Integration Pack you want to remove, again replacing <xx> with the value you retrieved in step 5.
DELETE FROM IntegrationPacks
WHERE ID = <xx>
As part of clean up, you may want to delete formulas that a deleted Import Rule Mapping was using. To do this, follow to steps below to identify those formulas and delete them from the RiskFabric database.
1. Open SQL Management Studio.
2. Connect to the SQL server hosting the RiskFabric (ICA) database and open a new query against the RiskFabric database.
3. Execute the query below to identify formulas that are associated to Import Rule Mappings that no longer exist. Take note of the FormulaIDs of the formulas you want to delete.
SELECT * FROM IW_Formula
WHERE FormulaID NOT IN
(SELECT FormulaID FROM IW_ImportRuleMappingPreProcessColumnFormula
)
AND FormulaID > 1000000
4. Execute the query below to delete the formulas, replacing <xx> with the list of FormulaIDs retrieved from the previous step separated by commas.
DELETE FROM IW_Formula
WHERE FormulaID IN (<xx>)
To delete a previously created Data Source, follow the steps below:
1. Open the ICA console.
2. Navigate to the Data Integrations tab under Admin > Data Sources.
3. Delete existing Data Source Queries under the Integration Pack you want to remove. Before deleting an Integration Pack, all Data Source Queries under the Integration Pack need to be deleted. To delete a Data Source Query, simply right-click on the Data Source Query you want to delete and select Delete Query.
4. After deleting all the Data Source Queries under the Data Source you want to remove, note the name/label of the Data Source you want to remove.
5. Open SQL Management Studio and open a new query against the RiskFabric database.
6. Execute the following SQL query to retrieve the LinkedServerID which will be used to delete the Data Source in the next step. Replace <Data Source Label> with the value of the Data Source Label you noted in step 4.
SELECT LinkedServerID, LinkedServerLabel, Host FROM LinkedServers
WHERE LinkedServerLabel = '<Data Source Label>'
NOTE: The query above may return more than one row. If this is the case, locate the correct one by checking the Host column.
1. Execute the following SQL query to delete the Data Source, replacing <xx> with the LinkedServerID value retrieved in the previous step:
DELETE FROM LinkedServers WHERE LinkedServerID = <xx>
For more best practice articles on Symantec Information Centric Analytics see the following posts: