Splunk test query succeeds but job fails

Products

Information Centric Analytics

Issue/Introduction

A Splunk query successfully returns data when executed using the Test Query function in the Integration Wizard (IW) but fails when running its IW job. Indications of failure include the following:

In the Risk Fabric console, on the page Admin > Integration > Data Sources > Choose Data Source | User Defined, the Type / Status of the data source query will be 'Failed'
In the Risk Fabric console, on the page Admin > Integration > Job Status, the Last Run Outcome of the IW data source query job (RiskFabric_IW_DataSourceQueryID_<ID>) will be 'Failed'
NOTE: to identify the IW job for the Splunk data source query, execute the following query in SQL Server Management Studio (SSMS):
```
USE RiskFabric;
GO

SELECT     DataSourceQueryName,
           DataSourceQueryDescription,
           JobName
FROM       dbo.IW_DataSourceQuery AS dsq
INNER JOIN dbo.LinkedServers AS ls
        ON dsq.LinkedServerID = ls.LinkedServerID
WHERE      LinkedServerTypeID = 32;
GO
```
In the Risk Fabric console, on the page Dashboards > Risk Fabric Health > Health Summary > Risk Fabric - SQL Job Status - Detail, the Job Status of the data source query job (RiskFabric_IW_DataSourceQueryID_<ID>) will be 'Failed'
Error messages similar to either of the following are also captured in the SQL Server Agent history log and the Splunk importer log:

[1:ERROR] SplunkApi.Login() Error while executing Login  System.Net.Http.HttpRequestException: An error occurred while sending the request. ---> System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond <IP-address>:<port-number>

[1:ERROR] SplunkApi.Login() Error while executing Login
System.Net.Http.HttpRequestException: An error occurred while sending the request. ---> System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it <IP-address>:<port-number>

Environment

Version : 6.x

Component : Splunk Import Utility

Topology : Two- or three-server (tier) architecture in which Internet Information Services (IIS) and Microsoft SQL Server (MSSQL) are not co-hosted on the same server

Cause

The Splunk server's firewall has been configured to allow connections from the IIS server but not from the MSSQL server.

The Test Query function in ICA initiates a Splunk search through an API call directly from the IIS server, whereas data source queries are initiated through an IW job that executes ICA's Splunk Import Utility. The Splunk Import Utility is installed by default with ICA's Database Utilities on the MSSQL server.

Resolution

Create a rule on the Splunk server's firewall to allow connections from the MSSQL server. The default Splunk REST API port is 8089.