Template

Template below was extensively tested and is a robust script that will allow most users to create their own rules with very few changes only changes to the wildcards in the test strings to.

Please note that the you are not limited to the template below only to the output format and the header MUST follow the convention defined in the template:

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

Example above provides the complex set or arguments for both process arguments patters and anti-patters as well as software names.

When looking at the example above what you need to understand is what you are trying to achieve is to find software or process that has certain arguments or the software names and does not contains any exclusions.

So to develop your own version you will need to do the following:

Change the header:

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

Populate the header with the correct information for the software title you are looking for.

2. Define fingerprint (Search parameter)

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

You can have multiple or a single row for the software title you are looking for. In the example above we are searching for processes that have Tibco as a pattern we then define the edition as Tibco. There is no exclusions (anti pattern) so we insert nulls.

On the next line we are specifying the next set of parameters.

We follow the same principal for the software titiles:

AS
(
    SELECT 'TIBCO' AS Pattern, 'Tibco' AS Edition, NULL AS AntiPattern1, NULL AS AntiPattern2
    UNION SELECT ALL 'TIB', 'TIB', 'TIBCO', 'Compatibility'

There can be as many or as few patterns as long as the results match your desired software. And do not include any false positives.

You can filter out undesirable results using anti patterns and regular expression in the search string you are using for fingerprinting the software.