/*
@RuleName = Symantec
@SoftwareName = Symantec Veritas Storage Foundation
@RuleStatus = Not Verified
@RuleCategory = Symantec Veritas Storage Foundation
@Vendor = Symantec
*/

WITH SearchInProcessArguments
AS
(
	SELECT 'VERITAS' AS Pattern, 'VERITAS' AS Edition, NULL AS AntiPattern1, NULL AS AntiPattern2
	UNION SELECT ALL 'veritas%storage', 'veritas%storage', NULL, NULL
	UNION SELECT ALL 'Vrtsvcs', 'Vrtsvcs', NULL, NULL
	UNION SELECT ALL 'Vrtsvxvm', 'Vrtsvxvm', NULL, NULL
	
), SearchInSoftwareName
AS
(
	SELECT 'Veritas Storage Foundation' AS Pattern, 'Veritas Storage Foundation' AS Edition, NULL AS AntiPattern1, NULL AS AntiPattern2
	UNION SELECT ALL 'veritas storage%server', 'veritas storage%server', NULL, NULL
	UNION SELECT ALL 'veritas%HA', 'veritas%HA', NULL, NULL
	UNION SELECT ALL 'veritas', 'veritas%HA', NULL, NULL
	UNION SELECT ALL 'sfw', 'sfw', NULL, NULL

)
SELECT t.[DatasetID], t.[DeviceInstanceID], t.[Version], t.[Edition]
--SELECT COUNT(DISTINCT [DeviceInstanceID]) AS DeviceCount
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],
			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