Derived Software Rules Overview

Overview:

Derived Software is a module that allows iQSonar DataHub to discover software through a set of rules. iQSonar collects the data and applies logic to determine information about the data.  

The presence of an installed package, service or process is used to infer the presence of a piece of software or a suit. This differs from the typical discovery methods which scan an actual device or application. It is important to know that all derived software rules can be performed in iQSonar DataHub.

Details:

Derived Software are implemented in iQSonar DataHub as follows:

• 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.

Example below provides a template:

/*
@RuleName = Microsoft Windows
@SoftwareName = Microsoft Windows
@RuleStatus = Not Verified
@RuleCategory = Microsoft Windows
@Vendor = Microsoft
*/

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 the select statement that follows it.

Derived Software rules are implemented as an ETL script that is provided in the DerivedSoftware.dtsx SSIS package. For each file ending in .sql in Rules folder do following.

• Execute script to read in the SQL from the file and store in a variable.

For each SQL rule found do following.

• Populate Lookup tables:
• Populate LookupProduct with a row where Name = Derived Software if does not already exist.
• Populate LookupVendor with new row if appropriate row does not already exist.
• Populate LookupSoftware with new row if appropriate row does not already exist.

Populate data for a single dataset:

• If a matching row does not exist in SoftwareInstance populate it. Record SoftwareInstanceID generated.
• If a matching row does not exist in RelationshipDeviceInstanceSoftwareInstance populate it.
• Populate data across datasets.  

The following are being handled outside the context of derived software ETL:

• If instance with relevant LookupProductID and LookupSoftwareID does not exist in Software table, populate new row. Record relevant SoftwareID.
• Populate ResolveSoftwareInstanceToSoftware with new row. This maps the corresponding SoftwareInstance to Software rows.
• Query ResolveDeviceInstanceToDevice to obtain DeviceID. o Populate RelationshipDeviceSoftware with new row.

Practical Example:

The example below is built using the template and the only changes needed is as follows:

Header:

/*
@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:

WITH SearchInProcessArguments
AS
(
    SELECT 'jboss-eap' AS Pattern, 'jboss-eap' AS Edition, 'payment' AS AntiPattern1, NULL AS AntiPattern2

You do not need to change anything else rest of the rule will support the search you have created. Please see full rule below.

/*
@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.