/*
@RuleName = Tibco
@SoftwareName = Tibco
@RuleStatus = Not Verified
@RuleCategory = Tibco
@Vendor = Tibco
*/

WITH SearchInProcessArguments
AS
(
	SELECT '[^a-z]TIBCO' AS Pattern, 'Tibco' AS Edition, NULL AS AntiPattern1, NULL AS AntiPattern2
	UNION SELECT ALL 'TIBJMS', 'Tibjms', NULL, NULL
	UNION SELECT ALL '[^a-z]TIB', 'Tib', 'TIBCO', 'TIBJMS'
	UNION SELECT ALL 'Activematrix', 'Activematrix', NULL, NULL
	UNION SELECT ALL 'Activespaces', 'Activespaces', NULL, NULL 
	UNION SELECT ALL 'Businessconnect', 'Businessconnect', NULL, NULL
	UNION SELECT ALL 'Be[-]engine', 'Be_engine', NULL, NULL
	UNION SELECT ALL 'Businessevents', 'Businessevents', NULL, NULL 
	UNION SELECT ALL 'Businessworks', 'Businessworks', 'Businessworks%Collaborator', 'Businessworks%Form%Builder'
	UNION SELECT ALL 'Businessworks%Collaborator', 'Businessworks_Collaborator', NULL, NULL 
	UNION SELECT ALL 'Businessworks%Form%Builder', 'Businessworks_Form_Builder', NULL, NULL 
	UNION SELECT ALL 'Cyber%fusion', 'Cyber_Fusion', NULL, NULL 
	UNION SELECT ALL 'Enterprise%message%service', 'Enterprise_Message_Service', NULL, NULL 
	UNION SELECT ALL '[^a-z]EMS[^a-z]', 'EMS', NULL, NULL
	UNION SELECT ALL 'File%Adapter', 'FileAdapter', NULL, NULL 
	UNION SELECT ALL 'Foresight', 'Foresight', NULL, NULL
	UNION SELECT ALL 'Fusion', 'Fusion', 'Cyber%fusion', NULL 
	UNION SELECT ALL '[^a-z]Hawk', 'Hawk', NULL, NULL 
	UNION SELECT ALL 'Managed%File%Transfer', 'Managed_File_Transfer', NULL, NULL
	UNION SELECT ALL 'Master%Data%Management', 'Master_Data_Management', NULL, NULL
	UNION SELECT ALL '[^a-z]MDM', 'MDM', NULL, NULL 
	UNION SELECT ALL '[^a-z]MFT', 'MFT', NULL, NULL 
	UNION SELECT ALL 'MQ%Websphere', 'MQ_Websphere', NULL, NULL 
	UNION SELECT ALL 'Nimbus', 'Nimbus', NULL, NULL 
	UNION SELECT ALL 'Rendezvous', 'Rendezvous', NULL, NULL 
	UNION SELECT ALL 'RDV', 'RDV', NULL, NULL 
	UNION SELECT ALL 'Slingshot', 'Slingshot', NULL, NULL 
	UNION SELECT ALL 'Spotfire', 'Spotfire', NULL, NULL 
	UNION SELECT ALL 'tibemsd', 'tibemsd', NULL, NULL 
), SearchInSoftwareName
AS
(
	SELECT 'TIBCO' AS Pattern, 'Tibco' AS Edition, NULL AS AntiPattern1, NULL AS AntiPattern2
	UNION SELECT ALL 'TIB', 'TIB', 'TIBCO', 'Compatibility'
	UNION SELECT ALL 'Activematrix', 'Activematrix', NULL, NULL
	UNION SELECT ALL 'Activespaces', 'Activespaces', NULL, NULL 
	UNION SELECT ALL 'Businessconnect', 'Businessconnect', NULL, NULL
	UNION SELECT ALL 'Be[-]engine', 'Be_engine', NULL, NULL
	UNION SELECT ALL 'Businessevents', 'Businessevents', NULL, NULL 
	UNION SELECT ALL 'Businessworks', 'Businessworks', 'Businessworks%Collaborator', 'Businessworks%Form%Builder'
	UNION SELECT ALL 'Businessworks%Collaborator', 'Businessworks_Collaborator', NULL, NULL 
	UNION SELECT ALL 'Businessworks%Form%Builder', 'Businessworks_Form_Builder', NULL, NULL 
	UNION SELECT ALL 'Cyber%fusion', 'Cyber_Fusion', NULL, NULL 
	UNION SELECT ALL 'Enterprise%message%service', 'Enterprise_Message_Service', NULL, NULL 
	UNION SELECT ALL '[^a-z]EMS[^a-z]', 'EMS', 'intuit', NULL
	UNION SELECT ALL 'File%Adapter', 'File_Adapter', NULL, NULL 
	UNION SELECT ALL 'Foresight', 'Foresight', NULL, NULL
	UNION SELECT ALL 'Fusion', 'Fusion', 'Cyber%fusion', 'ColdFusion'
	UNION SELECT ALL '[^a-z]Hawk', 'Hawk', NULL, NULL 
	UNION SELECT ALL 'Managed%File%Transfer', 'Managed_File_Transfer', NULL, NULL
	UNION SELECT ALL 'Master%Data%Management', 'Master_Data_Management', NULL, NULL
	UNION SELECT ALL '[^a-z]MDM', 'MDM', NULL, NULL 
	UNION SELECT ALL '[^a-z]MFT', 'MFT', NULL, NULL 
	UNION SELECT ALL 'MQ%Websphere', 'MQ_Websphere', NULL, NULL 
	UNION SELECT ALL 'Nimbus', 'Nimbus', NULL, NULL 
	UNION SELECT ALL 'Rendezvous', 'Rendezvous', NULL, NULL 
	UNION SELECT ALL 'RDV', 'RDV', NULL, NULL 
	UNION SELECT ALL 'Slingshot', 'Slingshot', NULL, NULL 
	UNION SELECT ALL 'Spotfire', 'Spotfire', NULL, NULL 
	UNION SELECT ALL 'tibemsd', 'tibemsd', 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+'%')
		)
		AND lk_soft.[Name] NOT IN (
			 'VMware Fusion PC Migration Agent'
			,'Oracle Hyperion Data Relationship Management, Fusion Edition, Client'
			,'VRTSvcstib'
		)
		AND lk_soft.[Name] NOT LIKE 'link[-]qbo[-]profile[-]adapter%'
		AND lk_soft.[Name] NOT LIKE '%nodejs[-]hawk%'
) t
WHERE t.[Qualifier] = 1
--ORDER BY [DeviceInstanceID]
GO