iQDatahub Derived Software rules by process

Owned by Alex Stibayev
Last updated: Jul 25, 2017

Derived Software are implemented in iQDataHub as follows:

  • Derived Software rules are implemented as select statements in SQL. 
  • An ETL script parses these SQL files.
  • Appropriate inserts are performed of rows in the lookup, SoftwareInstance and other tables as appropriate by the execution of the ETL.
  • Following must be included within a comment at the start of the SQL file. The Derived Software ETL will parse these and insert appropriate entries into the database. This will be used by iQExplore to show summary counts of number of matches by rule name and vendor.
    • RuleName
    • SoftwareName
    • RuleStatus: Not Verified by default
    • RuleCategory
    • Vendor
  • Users of iQSonar Explore can make selections within QlikView, for example selecting process names, etc. Such selections can be saved as a standard QlikView bookmark.



  • Examples of Derived Software rules already available: 

Derived Software library



Step-by-step guide

Lets take example of Golden Gate. In order to build any rule at least 1 or more signature in form of process name/argument/path will be required to identify a specific software. 


  1. Follow the green comments. All fields with comments are mandatory. 
  2. Save as .sql 
  3. Copy .sql file to C:\Program Files (x86)\iQuate\iQDataHub\ETL\Derived Software
  4. Run  Datahub import
/*
@RuleName = Golden Gate -- ==>  Fill out as applicable       
@SoftwareName = Golden Gate-- ==>  Fill out as applicable
@RuleStatus = Not Verified -- ==> Fill out as applicable
@RuleCategory = Oracle -- ==> Fill out as applicable
@Vendor = Oracle -- ==>  Fill out as applicable
*/

WITH SearchInProcessArguments
AS
(
	SELECT '%oracle%gg%' AS Pattern,  --  ==>    Fill out as applicable
		   '%oracle%gg%' AS Edition,   -- ==> Process name you have confirmed or verified with customer 
 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+'%')		
) t
WHERE t.[Qualifier] = 1
GO