/*
@RuleName = Safe Net
@SoftwareName = Safe Net
@RuleStatus = Not Verified
@RuleCategory = Safe Net
@Vendor = Safe Net
*/

WITH SearchInProcessArguments
AS
(
	SELECT 'SafeNet' AS Pattern, 'SafeNet' AS Edition, NULL AS AntiPattern1, NULL AS AntiPattern2
	
	   ), 
    
    SearchInSoftwareName
AS
(
	SELECT 'SafeNet' AS Pattern, 'SafeNet' AS Edition, NULL AS AntiPattern1, NULL AS AntiPattern2
	
	   )

SELECT t.[DatasetID], t.[DeviceInstanceID], t.[Version], t.[Edition]

FROM
(
		SELECT 1 AS [Source],
			ROW_NUMBER() OVER( PARTITION BY di.[DatasetID], di.[DeviceInstanceID] ORDER BY lk_proc.[Name] ) AS [Qualifier],
			di.[DatasetID],
			di.[DeviceInstanceID],
			CONVERT(nvarchar(255), lk_arg.Name) AS [Version],
			t.Edition+'_Process' AS [Edition]
		FROM [hub].[AttributeDeviceInstanceProcess] a_dip WITH( NOLOCK )
		JOIN [hub].[DeviceInstance] di WITH( NOLOCK ) ON di.[DeviceInstanceID] = a_dip.[DeviceInstanceID]
		JOIN [hub].[LookupProcess] lk_proc WITH( NOLOCK ) ON lk_proc.[LookupProcessID] = a_dip.[LookupProcessID]
		JOIN [hub].[LookupArgument] lk_arg WITH( NOLOCK ) on lk_arg.[LookupArgumentID] = a_dip.[LookupArgumentID]
		JOIN SearchInProcessArguments t ON 1=1
		WHERE  1=1
			AND	 lk_arg.[Name] LIKE N'%'+t.Pattern+'%'
			AND (t.AntiPattern1 IS NULL OR lk_arg.[Name] NOT LIKE N'%'+t.AntiPattern1+'%')
			AND (t.AntiPattern2 IS NULL OR lk_arg.[Name] NOT LIKE N'%'+t.AntiPattern2+'%')
			 UNION ALL
SELECT 2 AS [Source],
			ROW_NUMBER() OVER( PARTITION BY si.[DatasetID], rds.[DeviceInstanceID] ORDER BY lk_soft.[Name] ) AS [Qualifier],
			si.[DatasetID],
			rds.[DeviceInstanceID],
			lk_ver.[Version],
			lk_soft.[Name] AS [Edition]
		FROM [hub].[SoftwareInstance] si WITH( NOLOCK )
		JOIN [hub].[RelationshipDeviceInstanceSoftwareInstance] rds WITH( NOLOCK ) ON rds.[SoftwareInstanceID] = si.[SoftwareInstanceID]
		JOIN [hub].[LookupProduct] lk_prod WITH( NOLOCK ) ON lk_prod.[LookupProductID] = si.[LookupProductID]
		JOIN [hub].[LookupSoftware] lk_soft WITH( NOLOCK ) ON lk_soft.[LookupSoftwareID] = si.[LookupSoftwareID]
		LEFT JOIN [hub].[LookupVersion] lk_ver WITH( NOLOCK ) ON lk_ver.[LookupVersionID] = si.[LookupVersionID]
		LEFT JOIN [hub].[LookupEdition] lk_ed WITH( NOLOCK ) ON lk_ed.[LookupEditionID] = si.[LookupEditionID]
		JOIN SearchInSoftwareName t ON 1=1
		WHERE lk_prod.[Name] = N'Installed Software'
		AND (
			(lk_soft.[Name] LIKE N'%'+t.Pattern+'%')
			AND (t.AntiPattern1 IS NULL OR lk_soft.[Name] NOT LIKE N'%'+t.AntiPattern1+'%')
			AND (t.AntiPattern2 IS NULL OR lk_soft.[Name] NOT LIKE N'%'+t.AntiPattern2+'%')
		)
		
) t
WHERE t.[Qualifier] = 1
GO