USE [iQSonarData]
GO
/****** Object:  StoredProcedure [dbo].[ScanningApplicationJobList_se]    Script Date: 04/10/2016 18:14:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ScanningApplicationJobList_se]
(
	@ServerID			INT,
	@MaxFailures		INT = 5,
	@LicensedNodes		INT,
	@ServiceType		NVARCHAR(255),
	@SupressActiveJobs	BIT = 0,
	@IsDebug			BIT = 0
)
WITH RECOMPILE
AS
BEGIN TRANSACTION
BEGIN TRY
	SET NOCOUNT ON;
	DECLARE @ServiceID				INT,
			@ServiceTypeEnumID		TINYINT,
			@DefaultExpiry			SMALLINT, -- default expiry in minutes
			@CurrentDate			DATETIME,
			@ExpiryDate				DATETIME,
			@CredentialTypeEnumID	TINYINT,
			@LockoutDelayMinutes	INT;

	SET @CurrentDate = GETDATE();
	SET @DefaultExpiry = 60;

	-- Get service type
	SELECT @ServiceTypeEnumID = [ServiceTypeEnumID] FROM [ServiceTypeEnum] WHERE [ServiceType] = @ServiceType;

	-- Get service id
	SELECT @ServiceID = [_ServiceID] FROM [_Service] WHERE [_ServerID] = @ServerID AND [ServiceTypeEnumID] = @ServiceTypeEnumID;

	-- Clear expired jobs
	DELETE FROM [FoundApplicationActiveJob] WHERE [ExpiryDate] <= @CurrentDate;
	DELETE FROM [ApplicationActiveJob] WHERE [ExpiryDate] <= @CurrentDate;

	-- Reset any Application that DataTTL has expired since the last scan attempt
	UPDATE dbo.[ApplicationScanHistory]
		SET [AttemptCount] = @MaxFailures-1
	WHERE [ApplicationScanHistoryID]IN
		(
			SELECT [ApplicationScanHistoryID]
			FROM [ApplicationScanHistory] ash WITH( NOLOCK )
			JOIN [Application] a WITH( NOLOCK ) ON a.[ApplicationID] =ash.[ApplicationID]
			WHERE ([ServiceTypeEnumID] = @ServiceTypeEnumID
				AND a.[DataTTL] is not null 
				AND DATEADD(DAY, a.[DataTTL], ash.[LastAttemptDate]) <= @CurrentDate)
				AND ash.[AttemptCount] >= @MaxFailures);	
	
	DECLARE @IPPattern VARCHAR(48);
	
	SET @IPPattern = '\b(?:\d{1,3}\.){3}\d{1,3}\b';

	-- Ensure the IPRange table has binary start and end IP addresses
	UPDATE ir
		SET ir.[StartIPBinary] =
			(
				SELECT CAST( CAST( p.[1] AS TINYINT ) AS BINARY(1) )
					+ CAST( CAST( p.[2] AS TINYINT ) AS BINARY(1) )
					+ CAST( CAST( p.[3] AS TINYINT ) AS BINARY(1) )
					+ CAST( CAST( p.[4] AS TINYINT ) AS BINARY(1) )
				FROM dbo.[SplitWithResultCheck]( ir.[StartIP], N'.', default, '%/%' )
				PIVOT( MAX( [Value] ) FOR [ID] IN ( [1], [2], [3], [4] ) ) p
			),
		ir.[EndIPBinary] =
			(
				SELECT CAST( CAST( p.[1] AS TINYINT ) AS BINARY(1) )
					+ CAST( CAST( p.[2] AS TINYINT ) AS BINARY(1) )
					+ CAST( CAST( p.[3] AS TINYINT ) AS BINARY(1) )
					+ CAST( CAST( p.[4] AS TINYINT ) AS BINARY(1) )
				FROM dbo.[SplitWithResultCheck]( ir.[EndIP], N'.', default, '%/%' )
				PIVOT( MAX( [Value] ) FOR [ID] IN ( [1], [2], [3], [4] ) ) p
			),
		ir.[CachedStartIP] = ir.[StartIP],
		ir.[CachedEndIP] = ir.[EndIP],
		ir.[CachedIPRangeTypeEnum] = e.[IPRangeTypeEnum]
	FROM dbo.[IPRange] ir
	JOIN dbo.[IPRangeTypeEnum] e WITH( NOLOCK ) ON e.[IPRangeTypeEnumID] = ir.[IPRangeTypeEnumID]
	WHERE e.[IPRangeTypeEnum] = N'Range'
		AND dbo.RegExMatch(ir.[StartIP], @IPPattern, 0) = 1
		AND dbo.RegExMatch(ir.[EndIP], @IPPattern, 0) = 1
		AND (
			ir.[StartIP] != ir.[CachedStartIP] OR ir.[CachedStartIP] IS NULL OR
			ir.[EndIP] != ir.[CachedEndIP] OR ir.[CachedEndIP] IS NULL OR
			e.[IPRangeTypeEnum] != ir.[CachedIPRangeTypeEnum] OR ir.[CachedIPRangeTypeEnum] IS NULL
		);

	UPDATE ir2 SET StartIPBinary = t2.StartIPBinaryFinal, EndIPBinary = t2.EndIPBinaryFinal
	FROM dbo.IPRange ir2 INNER JOIN (
		Select t.IPRangeID, 
		CASE WHEN SubnetBinaryCalculated = -1 then  cast ( ( t.StartIPBinaryCalculated & SubnetBinaryCalculated )  as binary(4)) 
		ELSE cast ( ( t.StartIPBinaryCalculated & SubnetBinaryCalculated ) +1  as binary(4))
		end as StartIPBinaryFinal,
		CASE WHEN SubnetBinaryCalculated = -1 then CAST ( ( t.StartIPBinaryCalculated | (~ SubnetBinaryCalculated) )as binary(4))
		ELSE CAST ( ( t.StartIPBinaryCalculated | (~ SubnetBinaryCalculated) ) -1  as binary(4))
		END as EndIPBinaryFinal
		FROM ( SELECT ir.IPRangeID,
			( 
				SELECT CAST ( CAST( CAST( p.[1] AS TINYINT ) AS BINARY(1) )
					+ CAST( CAST( p.[2] AS TINYINT ) AS BINARY(1) )
					+ CAST( CAST( p.[3] AS TINYINT ) AS BINARY(1) )
					+ CAST( CAST( p.[4] AS TINYINT ) AS BINARY(1) ) as int) 
				FROM dbo.[SplitWithResultCheck]( ir.[StartIP], N'.', default, '%/%' )
				PIVOT( MAX( [Value] ) FOR [ID] IN ( [1], [2], [3], [4] ) ) p
			) AS StartIPBinaryCalculated,
			(
				SELECT CAST ( CAST( CAST( p.[1] AS TINYINT ) AS BINARY(1) )
					+ CAST( CAST( p.[2] AS TINYINT ) AS BINARY(1) )
					+ CAST( CAST( p.[3] AS TINYINT ) AS BINARY(1) )
					+ CAST( CAST( p.[4] AS TINYINT ) AS BINARY(1) ) as int )
				FROM dbo.[SplitWithResultCheck]( ir.[EndIP], N'.', default, '%/%' )
				PIVOT( MAX( [Value] ) FOR [ID] IN ( [1], [2], [3], [4] ) ) p
			) AS SubnetBinaryCalculated
			FROM dbo.IPRange ir 
			INNER JOIN dbo.IPRangeTypeEnum e on e.IPRangeTypeEnumID = ir.IPRangeTypeEnumID
			WHERE e.IPRangeTypeEnum = 'Subnet'
			AND dbo.RegExMatch(ir.[StartIP], @IPPattern, 0) = 1
			AND dbo.RegExMatch(ir.[EndIP], @IPPattern, 0) = 1
		) as t ) as t2 ON t2.IPRangeID = ir2.IPRangeID

	-- Create jobs table
	DECLARE @Jobs TABLE
	(
		[FoundApplicationID] INT,
		[ApplicationID] INT,
		[DeviceID] INT,
		[Hostname] NVARCHAR(50),
		[UniqueIdentifier] NVARCHAR(255),
		[ApplicationName] NVARCHAR(127),
		[ApplicationVendor] NVARCHAR(100),
		[Port] INT,
		[DataTTL] INT,
		[Credentials] XML,
		[ScanIP] NVARCHAR(50),
		[VerifiedIP] NVARCHAR(50),
		[ApplicationType] NVARCHAR(50),
        [IsDebugEnabled] BIT,
        [IsToBeScanned] BIT,
        [Reason] NVARCHAR(255),
        [Path] NVARCHAR(2048) NULL,
        [LocationID] INT,
        [ScanStageEnumID] SMALLINT
	);

	DECLARE @CredentialPreTesting TABLE
	(
		[CredentialCount] INT,
		[ApplicationTypeEnumID] INT,
		[LocationID] INT,
		[ServiceTypeEnumID] INT
	)

	-- CredentialPreTesting is related to BUG 4371.
	-- Oracle FoundApplications were not being served because there were a large number of SQLServer FoundApplications and no SQLServer credentials.
	INSERT INTO @CredentialPreTesting 
	SELECT count( c.[CredentialID]) CredentialCount , c.[ApplicationTypeEnumID], mlc.[LocationID], cs.[ServiceTypeEnumID]
	FROM dbo.[Credential] c
	INNER JOIN [MapCredentialTypeServiceTypeEnum] cs ON cs.[CredentialTypeEnumID] = c.[CredentialTypeEnumID] 
	LEFT JOIN dbo.[MapLocationCredential] mlc ON mlc.CredentialID = c.CredentialID 
	WHERE c.[ParentCredentialID] IS NULL
		AND c.[IsDisabled] = 0
		AND c.[ApplicationTypeEnumID] IS NOT NULL 
		AND ISNULL(mlc.IsEnabled, 1) = 1
	GROUP BY c.[ApplicationTypeEnumID], mlc.[LocationID], cs.[ServiceTypeEnumID]

	UPDATE [ApplicationScanHistory] SET [AttemptCount]=0 WHERE [NextScanDate] IS NOT NULL AND [NextScanDate] <= @CurrentDate AND [NextScanDate] > [LastAttemptDate];
	UPDATE [FoundApplicationScanHistory] SET [AttemptCount]=0 WHERE [NextScanDate] IS NOT NULL AND [NextScanDate] <= @CurrentDate AND [NextScanDate] > [LastAttemptDate];

	--Reset lockouts that have elapsed
	SELECT @CredentialTypeEnumID=[CredentialTypeEnumID] FROM [MapCredentialTypeServiceTypeEnum] WHERE [ServiceTypeEnumID]=@ServiceTypeEnumID;

	SELECT @LockoutDelayMinutes = CASE WHEN @IsDebug = 1 THEN 0 ELSE ISNULL( [LockoutDelayMinutes], 0 ) END
	FROM [CredentialTypeEnum] WHERE [CredentialTypeEnumID] = @CredentialTypeEnumID;	
	
	--The subselect on these updates get the last time that username was used on a given host and adds the lockoutdelay
	UPDATE [ApplicationCredentialHistory] SET [LockoutAttemptCount]=0 WHERE GETDATE() > 
		( SELECT DATEADD(mi, @LockoutDelayMinutes, MAX([LastAttemptDate])) FROM [ApplicationCredentialHistory] d 
		JOIN [Credential] c ON d.[CredentialID]=c.[CredentialID]
		WHERE d.[ApplicationID]=[ApplicationCredentialHistory].[ApplicationID] AND c.[Username] = 
			( SELECT [Username] FROM [Credential] WHERE [CredentialID] = [ApplicationCredentialHistory].[CredentialID] ) )
	UPDATE [FoundApplicationCredentialHistory] SET [LockoutAttemptCount]=0 WHERE GETDATE() >
		( SELECT DATEADD(mi, @LockoutDelayMinutes, MAX([LastAttemptDate])) FROM [FoundApplicationCredentialHistory] d 
		JOIN [Credential] c ON d.[CredentialID]=c.[CredentialID]
		WHERE d.[FoundApplicationID]=[FoundApplicationCredentialHistory].[FoundApplicationID] AND c.[Username] = 
			( SELECT [Username] FROM [Credential] WHERE [CredentialID] = [FoundApplicationCredentialHistory].[CredentialID] ) )



	-- Insert job details into temp table
	--Found
	INSERT INTO @Jobs ( [FoundApplicationID], [ApplicationID], [DeviceID], [Hostname], [UniqueIdentifier], [ApplicationName], [ApplicationVendor], [Port],
		[DataTTL], [Credentials], [ScanIP], [VerifiedIP], [ApplicationType], [IsDebugEnabled], [IsToBeScanned], [Reason], [Path], [LocationID], [ScanStageEnumID] )
	SELECT t2.[FoundApplicationID], t2.[ApplicationID], t2.[DeviceID], t2.[Hostname], t2.[UniqueIdentifier], t2.[ApplicationName], t2.[ApplicationVendor],
		t2.[Port], t2.[DataTTL],
		dbo.GetApplicationCredentials( t2.[ApplicationTypeEnumID], @ServiceTypeEnumID, NULL, t2.[FoundApplicationID], t2.[LocationID] ) AS [Credentials],
		t2.[ScanIP], t2.[VerifiedIP], t2.[ApplicationType], t2.[IsDebugEnabled], t2.[IsToBeScanned], t2.[Reason], t2.[Path], t2.[LocationID], t2.[ScanStageEnumID]
	FROM
	(
		SELECT t.[FoundApplicationID], t.[ApplicationID], t.[DeviceID], t.[Hostname], t.[UniqueIdentifier], t.[ApplicationName], t.[ApplicationVendor], t.[Port],
			t.[DataTTL], t.[ScanIP], t.[VerifiedIP], t.[IPAddressBinary], t.[ApplicationType], t.[IsDebugEnabled], t.[IsToBeScanned], t.[Reason], t.[Path],
			t.[LocationID], t.[ApplicationTypeEnumID], t.[ScanStageEnumID]
		FROM
		(
			SELECT TOP 500 
				db.[FoundApplicationID],
				NULL AS [ApplicationID],
				db.[DeviceID],
				db.[Hostname],
				NULL AS [UniqueIdentifier],
				db.[InstanceName] AS [ApplicationName],
				v.[Vendor] AS [ApplicationVendor],
				db.[Port],
				NULL AS [DataTTL],
				db.[ScanIP],
				[FoundApplicationAddressAndLocation].[IPAddressBinary],
				[FoundApplicationAddressAndLocation].[IPAddress] AS [VerifiedIP],
				at.[Name] AS [ApplicationType],
				CASE WHEN db.[DebugExpiryDate] > GETDATE() THEN 1 ELSE 0 END AS [IsDebugEnabled],
				xtst.[Scan] AS [IsToBeScanned], 
				xtst.[Reason],
				db.[Path],
				[FoundApplicationAddressAndLocation].LocationID,
				db.[ApplicationTypeEnumID],
				db.[ScanStageEnumID]
			FROM [FoundApplication] AS db
			INNER JOIN (
							SELECT db2.[FoundApplicationID], 
									ISNULL (
												db2.[LocationID], 
												(
													SELECT TOP 1 ipr.[LocationID] 
													FROM dbo.[IPRange] ipr 
													JOIN [MapIPRange_Server] m 
														ON m.[IPRangeID] = ipr.[IPRangeID] 
														AND m.[_ServerID] = @ServerID
													WHERE i.[IPAddressBinary] >= ipr.[StartIPBinary] 
													AND i.[IPAddressBinary] <= ipr.[EndIPBinary]
													ORDER BY ipr.[IPRangeID]
												)
											) AS [LocationID],
									CASE 
										WHEN dbo.RegExValue( db2.[ScanIP], @IPPattern, 0 ) IS NOT NULL 
										THEN
											(
												SELECT CAST( CAST( p.[1] AS TINYINT ) AS BINARY(1) )
													+ CAST( CAST( p.[2] AS TINYINT ) AS BINARY(1) )
													+ CAST( CAST( p.[3] AS TINYINT ) AS BINARY(1) )
													+ CAST( CAST( p.[4] AS TINYINT ) AS BINARY(1) )
												FROM dbo.Split( db2.[ScanIP], N'.' )
												PIVOT( MAX( [Value] ) FOR [ID] IN ( [1], [2], [3], [4] ) ) p
											)
									END AS [ScanIPAddressBinary],
									i.[IPAddressBinary], 
									i.[IPAddress]
							FROM dbo.[FoundApplication] db2 
							INNER JOIN [MapApplicationTypeServiceTypeEnum] AS st 
								ON st.[ApplicationTypeEnumID] = db2.[ApplicationTypeEnumID] 
								AND st.[ServiceTypeEnumID]=@ServiceTypeEnumID
							LEFT JOIN [IPAddress] i 
								ON i.[DeviceID] = db2.[DeviceID] 
								--Removed as CS-567
								--AND i.[IsVerified] = 1 
								--AND ISNULL( i.[IPAddress], '' ) <> ISNULL( db2.[ScanIP], '' )
						) AS [FoundApplicationAddressAndLocation] ON [FoundApplicationAddressAndLocation].FoundApplicationID = db.FoundApplicationID
			INNER JOIN [ApplicationTypeEnum] at ON db.[ApplicationTypeEnumID] = at.[ApplicationTypeEnumID]
			LEFT JOIN [VendorEnum] AS v ON v.[VendorEnumID] = db.[ApplicationVendorEnumID]
			LEFT JOIN [FoundApplicationScanHistory] h ON h.[FoundApplicationID]=db.[FoundApplicationID] AND h.[ServiceTypeEnumID]=@ServiceTypeEnumID AND h.[ScanStageEnumID]=db.[ScanStageEnumID]
			LEFT JOIN @CredentialPreTesting cpt ON cpt.[ApplicationTypeEnumID] = db.[ApplicationTypeEnumID] 
				AND (cpt.[LocationID] = [FoundApplicationAddressAndLocation].LocationID OR ([FoundApplicationAddressAndLocation].LocationID IS NULL AND cpt.[LocationID] IS NULL))
				AND cpt.[ServiceTypeEnumID] = @ServiceTypeEnumID
			CROSS APPLY dbo.[IsApplicationToBeScanned]( ISNULL( db.[ApplicationID], -1 ), 'Application', @LicensedNodes ) xtst
			WHERE db.[ApplicationID] IS NULL
				AND ISNULL(cpt.[CredentialCount], 0) > 0
				AND COALESCE(db.[OutOfScope],at.[OutOfScope],0) = 0
				AND xtst.[Scan] = 1
				AND db.[FoundApplicationID] NOT IN ( SELECT [FoundApplicationID] FROM [FoundApplicationActiveJob] WHERE [ServiceTypeEnumID] = @ServiceTypeEnumID AND [ScanStageEnumID] = db.[ScanStageEnumID])
				AND (
					( [FoundApplicationAddressAndLocation].[LocationID] IS NULL AND NOT EXISTS( SELECT TOP 1 1 FROM dbo.[MapLocation_Server] WHERE [_ServerID] = @ServerID ) )
					OR
					(
						[FoundApplicationAddressAndLocation].[LocationID] IS NOT NULL
						AND
						EXISTS( SELECT TOP 1 1 FROM dbo.[MapLocation_Server] m WHERE m.[_ServerID] = @ServerID AND m.[LocationID] = [FoundApplicationAddressAndLocation].[LocationID] AND m.IsEnabled = 1 )
					)
				)
				AND
				(
					EXISTS
					(
						SELECT TOP 1 1
						FROM [IPRange] ipr
						JOIN [MapIPRange_Server] AS m ON m.[IPRangeID] = ipr.[IPRangeID]
							AND m.[_ServerID] = @ServerID
						WHERE [FoundApplicationAddressAndLocation].ScanIPAddressBinary >= ipr.[StartIPBinary]
							AND [FoundApplicationAddressAndLocation].ScanIPAddressBinary <= ipr.[EndIPBinary]
							AND ISNULL( ipr.[LocationID], -1 ) = ISNULL( [FoundApplicationAddressAndLocation].[LocationID], -1 )
					)
					AND
					(						
						[FoundApplicationAddressAndLocation].[IPAddress] IS NULL						
						
						OR
						EXISTS
						(
							SELECT TOP 1 1
							FROM [IPRange] ipr
							JOIN [MapIPRange_Server] AS m ON m.[IPRangeID] = ipr.[IPRangeID]
								AND m.[_ServerID] = @ServerID
							WHERE [FoundApplicationAddressAndLocation].[IPAddressBinary] >= ipr.[StartIPBinary]
								AND [FoundApplicationAddressAndLocation].[IPAddressBinary] <= ipr.[EndIPBinary]
								AND ISNULL( ipr.[LocationID], -1 ) = ISNULL( [FoundApplicationAddressAndLocation].[LocationID], -1 )
						)
					)
				)
			GROUP BY db.[FoundApplicationID],db.[ApplicationID] , db.[DeviceID], db.[Hostname], db.[InstanceName], v.[Vendor], db.[Port], db.[ScanIP], [FoundApplicationAddressAndLocation].[IPAddress], at.[Name], db.[DebugExpiryDate],
				xtst.[Scan],xtst.[Reason],db.[FoundApplicationID],db.[Path], db.[ApplicationTypeEnumID], [FoundApplicationAddressAndLocation].[LocationID], [FoundApplicationAddressAndLocation].[IPAddressBinary], db.[ScanStageEnumID]
			HAVING ISNULL(SUM(h.[AttemptCount]),0) < @MaxFailures
			AND (MAX([LastSuccessfulDate]) IS NULL OR 
				(MAX(CASE WHEN [NextScanDate] <= @CurrentDate THEN [NextScanDate] ELSE NULL END) IS NOT NULL 
					AND MAX([LastSuccessfulDate]) < MAX(CASE WHEN [NextScanDate] <= @CurrentDate THEN [NextScanDate] ELSE NULL END)))
			ORDER BY ISNULL(SUM(h.[AttemptCount]),0)
		) t
	) t2;
		
	--Rescan
	INSERT INTO @Jobs ( [FoundApplicationID], [ApplicationID], [DeviceID], [Hostname], [UniqueIdentifier], [ApplicationName], [ApplicationVendor], [Port],
		[DataTTL], [Credentials], [ScanIP], [VerifiedIP], [ApplicationType], [IsDebugEnabled], [IsToBeScanned], [Reason], [Path], [LocationID], [ScanStageEnumID] )
	SELECT t2.[FoundApplicationID], t2.[ApplicationID], t2.[DeviceID], t2.[Hostname], t2.[UniqueIdentifier], t2.[ApplicationName], t2.[ApplicationVendor], t2.[Port],
		t2.[DataTTL],
		dbo.GetApplicationCredentials( t2.[ApplicationTypeEnumID], @ServiceTypeEnumID, t2.[ApplicationID], NULL, t2.[LocationID] ) AS [Credentials],
		t2.[ScanIP], t2.[VerifiedIP], t2.[ApplicationType], t2.[IsDebugEnabled], t2.[IsToBeScanned], t2.[Reason], t2.[Path], t2.[LocationID], t2.[ScanStageEnumID]
	FROM
	(
		SELECT t.[FoundApplicationID], t.[ApplicationID], t.[DeviceID], t.[Hostname], t.[UniqueIdentifier], t.[ApplicationName], t.[ApplicationVendor], t.[Port],
			t.[DataTTL], t.[ScanIP], t.[ScanIPAddressBinary], t.[IPAddressBinary], t.[VerifiedIP], t.[ApplicationType], t.[IsDebugEnabled],
			t.[IsToBeScanned], t.[Reason], t.[Path],
			t.[LocationID], t.[ApplicationTypeEnumID], t.[ScanStageEnumID]
		FROM
		(
			SELECT TOP 500
				NULL AS [FoundApplicationID],
				db.[ApplicationID],
				(Select TOP 1 mad.[DeviceID] from mapApplicationDevice as mad where mad.ApplicationID = db.[ApplicationID]) AS [DeviceID],
				ad.[Hostname],
				db.[UniqueIdentifier],
				ad.[InstanceName] AS [ApplicationName],
				v.[Vendor] AS [ApplicationVendor],
				ad.[Port],
				db.[DataTTL],
				db.[ScanIP],
				[ApplicationLocationAndAddress].[ScanIPAddressBinary],
				[ApplicationLocationAndAddress].[VerifiedIP],
				[ApplicationLocationAndAddress].[IPAddressBinary],
				at.[Name] AS [ApplicationType],
				CASE WHEN db.[DebugExpiryDate] > GETDATE() THEN 1 ELSE 0 END AS [IsDebugEnabled],
				xtst.[Scan] AS [IsToBeScanned], 
				xtst.[Reason],
				[ApplicationLocationAndAddress].[Path],
				[ApplicationLocationAndAddress].[LocationID],
				db.[ApplicationTypeEnumID],
				db.[ScanStageEnumID]
			FROM dbo.[Application] AS db
			INNER JOIN (
			SELECT [ApplicationAddress].[ApplicationID], MAX( [ApplicationAddress].[Path] ) AS [Path], [ApplicationAddress].[VerifiedIP], [ApplicationAddress].[IPAddressBinary],[ApplicationAddress].[ScanIPAddressBinary],
				COALESCE
					(
						[ApplicationAddress].[LocationID],
						(
							SELECT TOP 1 ipr.[LocationID]
							FROM dbo.[IPRange] ipr
							JOIN [MapIPRange_Server] m ON m.[IPRangeID] = ipr.[IPRangeID]
								AND m.[_ServerID] = @ServerID
							WHERE [ApplicationAddress].[ScanIPAddressBinary] >= ipr.[StartIPBinary]
								AND [ApplicationAddress].[ScanIPAddressBinary] <= ipr.[EndIPBinary]
							ORDER BY ipr.[IPRangeID]
						),
						(
							SELECT TOP 1 1
							FROM [IPRange] ipr
							JOIN [MapIPRange_Server] AS m ON m.[IPRangeID] = ipr.[IPRangeID]
								AND m.[_ServerID] = @ServerID
							WHERE [ApplicationAddress].[IPAddressBinary] >= ipr.[StartIPBinary]
								AND [ApplicationAddress].[IPAddressBinary] <= ipr.[EndIPBinary]
								AND [ApplicationAddress].[ScanIP] IS NULL
						)
					) AS [LocationID]
				FROM (
					SELECT db2.ApplicationID, fa.[Path], i.IPAddress AS [VerifiedIP], i.IPAddressBinary, db2.ScanIP, 
						CASE WHEN dbo.RegExValue( db2.[ScanIP], @IPPattern, 0 ) IS NOT NULL THEN
						(
							SELECT CAST( CAST( p.[1] AS TINYINT ) AS BINARY(1) )
								+ CAST( CAST( p.[2] AS TINYINT ) AS BINARY(1) )
								+ CAST( CAST( p.[3] AS TINYINT ) AS BINARY(1) )
								+ CAST( CAST( p.[4] AS TINYINT ) AS BINARY(1) )
							FROM dbo.Split( db2.[ScanIP], N'.' )
							PIVOT( MAX( [Value] ) FOR [ID] IN ( [1], [2], [3], [4] ) ) p
						)
						END AS [ScanIPAddressBinary], db2.[LocationID]
					FROM dbo.[Application] db2
					INNER JOIN [MapApplicationTypeServiceTypeEnum] AS st ON st.[ApplicationTypeEnumID] = db2.[ApplicationTypeEnumID] AND st.[ServiceTypeEnumID]=@ServiceTypeEnumID
					LEFT JOIN [FoundApplication] fa on fa.[ApplicationID] = db2.[ApplicationID]
					LEFT JOIN [IPAddress] i ON i.[DeviceID] = fa.[DeviceID] AND i.[IsVerified] = 1 AND ISNULL( i.[IPAddress], '' ) <> ISNULL( db2.[ScanIP], '' )
				) AS [ApplicationAddress] 
				GROUP BY [ApplicationID], [VerifiedIP], IPAddressBinary, ScanIPAddressBinary, ScanIP, LocationID
			) AS [ApplicationLocationAndAddress] ON [ApplicationLocationAndAddress].ApplicationID = db.ApplicationID
			INNER JOIN [ApplicationTypeEnum] at ON db.[ApplicationTypeEnumID] = at.[ApplicationTypeEnumID]
			LEFT JOIN [VendorEnum] AS v ON v.[VendorEnumID] = db.[ApplicationVendorEnumID]
			LEFT JOIN [ApplicationScanHistory] h ON h.[ApplicationID]=db.[ApplicationID] 
				AND h.[ServiceTypeEnumID] = @ServiceTypeEnumID  AND h.[ScanStageEnumID]=db.[ScanStageEnumID]
			LEFT JOIN [ApplicationDatabase] ad on ad.[ApplicationID] = db.[ApplicationID]
			CROSS APPLY dbo.[IsApplicationToBeScanned]( ISNULL( db.[ApplicationID], -1 ), 'Application', @LicensedNodes ) xtst
			WHERE xtst.[Scan] = 1
				AND COALESCE(db.[OutOfScope],at.[OutOfScope],0) = 0
				AND db.[ApplicationID] NOT IN ( SELECT [ApplicationID] FROM [ApplicationActiveJob] WHERE [ServiceTypeEnumID] = @ServiceTypeEnumID AND [ScanStageEnumID] = db.[ScanStageEnumID] )
				AND (EXISTS
					(
						SELECT TOP 1 1
						FROM [IPRange] ipr
						JOIN [MapIPRange_Server] AS m ON m.[IPRangeID] = ipr.[IPRangeID]
							AND m.[_ServerID] = @ServerID
						WHERE [ApplicationLocationAndAddress].[ScanIPAddressBinary] >= ipr.[StartIPBinary]
							AND [ApplicationLocationAndAddress].[ScanIPAddressBinary] <= ipr.[EndIPBinary]
					)
					OR
					(
						db.[ScanIP] IS NULL
						AND
						EXISTS
						(
							SELECT TOP 1 1
							FROM [IPRange] ipr
							JOIN [MapIPRange_Server] AS m ON m.[IPRangeID] = ipr.[IPRangeID]
								AND m.[_ServerID] = @ServerID
							WHERE [ApplicationLocationAndAddress].[IPAddressBinary] >= ipr.[StartIPBinary]
								AND [ApplicationLocationAndAddress].[IPAddressBinary] <= ipr.[EndIPBinary]
						)
					) )
			GROUP BY db.[ApplicationID], ad.[Hostname], db.[UniqueIdentifier], ad.[InstanceName], v.[Vendor], ad.[Port], db.[DataTTL], db.[ScanIP], [ApplicationLocationAndAddress].[VerifiedIP], at.[Name], db.[DebugExpiryDate],
				xtst.[Scan], xtst.[Reason], [ApplicationLocationAndAddress].[Path], [ApplicationLocationAndAddress].[LocationID], db.[ApplicationTypeEnumID], [ApplicationLocationAndAddress].[ScanIPAddressBinary], [ApplicationLocationAndAddress].[IPAddressBinary], db.[ScanStageEnumID]
			HAVING ISNULL(SUM(h.[AttemptCount]), 0) < @MaxFailures
			AND (MAX([LastSuccessfulDate]) IS NULL OR 
				(db.[DataTTL] IS NOT NULL AND DATEADD(Day, db.[DataTTL], MAX([LastSuccessfulDate]) )<= @CurrentDate) OR
				(MAX(CASE WHEN [NextScanDate] <= @CurrentDate THEN [NextScanDate] ELSE NULL END) IS NOT NULL 
					AND MAX([LastSuccessfulDate]) < MAX(CASE WHEN [NextScanDate] <= @CurrentDate THEN [NextScanDate] ELSE NULL END))
				)
			ORDER BY ISNULL(SUM(h.[AttemptCount]),0)
		) t
	) t2;

	--Delete jobs without credentials
	DELETE FROM @Jobs where [Credentials] IS NULL;

	-- Work out Expiry Date for new jobs
	SET @ExpiryDate = DATEADD(minute, @DefaultExpiry, @CurrentDate);

	IF(@SupressActiveJobs <> 1)
	BEGIN
		INSERT INTO [FoundApplicationActiveJob] ( [FoundApplicationID], [_ServiceID], [ServiceTypeEnumID], [ExpiryDate], [ScanStageEnumID] )
		SELECT [FoundApplicationID], @ServiceID, @ServiceTypeEnumID, @ExpiryDate, [ScanStageEnumID] FROM @Jobs WHERE [FoundApplicationID] IS NOT NULL
			AND [FoundApplicationID] NOT IN ( SELECT [FoundApplicationID] FROM [FoundApplicationActiveJob] WHERE [ServiceTypeEnumID] = @ServiceTypeEnumID)

		INSERT INTO [ApplicationActiveJob] ( [ApplicationID], [_ServiceID], [ServiceTypeEnumID], [ExpiryDate], [ScanStageEnumID] )
		SELECT [ApplicationID], @ServiceID, @ServiceTypeEnumID, @ExpiryDate, [ScanStageEnumID] FROM @Jobs WHERE [ApplicationID] IS NOT NULL
			AND [ApplicationID] NOT IN ( SELECT [ApplicationID] FROM [ApplicationActiveJob] WHERE [ServiceTypeEnumID] = @ServiceTypeEnumID);
	END
	
	COMMIT TRANSACTION;

	SELECT j.[FoundApplicationID], j.[ApplicationID], j.[DeviceID], j.[Hostname], j.[UniqueIdentifier], j.[ApplicationName], j.[ApplicationVendor],
		j.[Port], j.[DataTTL], j.[Credentials], j.[ScanIP], j.[VerifiedIP], j.[ApplicationType], j.[IsToBeScanned], j.[Reason] , j.[IsDebugEnabled], 
		j.[Path], j.[LocationID], j.[ScanStageEnumID],
		(SELECT [ScanStageName] FROM [ScanStageEnum] WHERE [ScanStageEnumID] = j.[ScanStageEnumID]) as [ScanStageName]
	FROM @Jobs j;
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION;

	-- Raise an error with the details of the exception
	DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
	SELECT @ErrMsg = ERROR_MESSAGE(),
		 @ErrSeverity = ERROR_SEVERITY()

	RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH