...
- Derived Software rules are implemented as select statements in SQL
- An ETL script parses these SQL files.
- Appropriate inserts are performed on rows in the lookup, SoftwareInstance and other tables as appropriate by the execution of the ETL.
- A “Rule Status” flag is included in Derived Software rules, to allow an indicative status of a rule to be specified; For example, “Unverified” or “Accepted by Vendor”.
- 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. o RuleName o SoftwareName o RuleStatus: Not Verified by default o RuleCategory o 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.
- Users can subsequently choose to share such selections with iQuate.
- iQuate will review such selections and build the corresponding SQL manually corresponding to the selections made by the user.
...
In this example the Rule name will appear as Microsoft Windows, Software name will be Microsoft Windows and the vendor will be Microsoft. For each match of on the select statement that follows it.
...
| Code Block |
|---|
/* @RuleName = JBoss Enterprise Application Platform @SoftwareName = JBoss Enterprise Application Platform @RuleStatus = Not Verified @RuleCategory = JBoss Enterprise Application Platform @Vendor = RedHat */ |
Search parameters:
In the example below we are searching for the process that has 'jboss-eap' in the process arguments. But does not contain string 'payment'. For this purpose we are employing pattern and anti pattern below:
...
You do not need to change anything else, rest of the rule will support the search you have created. Please see full rule below.
| Code Block |
|---|
/*
@RuleName = JBoss Enterprise Application Platform
@SoftwareName = JBoss Enterprise Application Platform
@RuleStatus = Not Verified
@RuleCategory = JBoss Enterprise Application Platform
@Vendor = RedHat
*/
WITH SearchInProcessArguments
AS
(
SELECT 'jboss-eap' AS Pattern, 'jboss-eap' AS Edition, 'payment' 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
|
Installation:
Once a new derived software rule is created place the SQL file into the derived software folder.
These rules are located in the iQSonar DataHub folder on your directory.
iQuate > iQSonar DataHub > ETL > DerivedSoftware > *.sql
Once there on the next datahub import the rule will create extra application rows in the Datahub database.