Custom columns (not custom properties) in FNMS

[jasonlu]

Way back in FNMP days I remember creating custom columns against assets and inventory records. Is this possible in FNMS?

What I would like is the ability to create a column where the value is calculated, not stored in the database.

As a very simple example, have a custom column "Last Inventory Day" where the value is calculated from DATENAME(WEEKDAY, @InventoryDate) This would show "Monday", "Tuesday",  etc based on the value of InventoryDate. 

Obviously the provided document for custom properties does not support this, but can it still be done, and does anyone have code examples of this?

j

[ChrisG · Accepted solution]

While this technique doesn't come with any specific documentation or formal support, custom columns can be configured for use in reports by adding records to the ComplianceSearchTypeColumn view in the compliance database of a FlexNet Manager Suite On Premises system.

Here's a custom stored procedure which can be used as a starting point for this:

CREATE PROCEDURE dbo.CustomCreateComplianceSearchTypeColumn
    @ComplianceSearchTypeName NVARCHAR(64)
    , @ColumnName NVARCHAR(128)
    , @FromTable NVARCHAR(MAX)
    , @SelectName NVARCHAR(MAX)
    , @WhereClause NVARCHAR(MAX)
    , @ColumnNameResourceName NVARCHAR(128) = NULL
    , @FilterGroupType INT = 1 /* string */
    , @RequiresSearchTypeName NVARCHAR(64) = NULL
    , @SelectByDefault BIT = 0
    , @Mandatory BIT = 0
    , @PrimaryKey BIT = 0
AS
-- Purpose: Create a ComplianceSearchTypeColumn record

PRINT 'Configuring custom view column "' + @ColumnName + '"'

DECLARE @ComplianceSearchTypeID INT
DECLARE @RequiresSearchTypeID INT

SELECT @ComplianceSearchTypeID = ComplianceSearchTypeID FROM dbo.ComplianceSearchType WHERE TypeName = @ComplianceSearchTypeName
SELECT @RequiresSearchTypeID = ComplianceSearchTypeID FROM dbo.ComplianceSearchType WHERE TypeName = @RequiresSearchTypeName

-- Remove line breaks (since FNMP de-duplicates identical lines)
SET @FromTable = REPLACE(@FromTable, '
', ' ')

INSERT INTO dbo.ComplianceSearchTypeColumn (
    ColumnName,
    ColumnNameResourceName,
    FromTable,
    SelectName,
    JoinClause,
    WhereClause,
    SelectOptionsSQL,
    FilterGroupType,
    DefaultFilterType,
    ComplianceSearchTypeID,
    RequiresSearchTypeID,
    SelectByDefault,
    Mandatory,
    PrimaryKey
)
SELECT
    @ColumnName,
    @ColumnNameResourceName,
    @FromTable,
    @SelectName,
    '',
    @WhereClause,
    '',
    @FilterGroupType,
    @FilterGroupType,
    @ComplianceSearchTypeID,
    @RequiresSearchTypeID,
    @SelectByDefault,
    @Mandatory,
    @PrimaryKey
WHERE
    NOT EXISTS (
        SELECT 1
        FROM dbo.ComplianceSearchTypeColumn
        WHERE ColumnName = @ColumnName AND ComplianceSearchTypeID = @ComplianceSearchTypeID
    )

UPDATE dbo.ComplianceSearchTypeColumn
SET    ColumnNameResourceName = @ColumnNameResourceName,
    FromTable = @FromTable,
    SelectName = @SelectName,
    JoinClause = '',
    WhereClause = @WhereClause,
    SelectOptionsSQL = '',
    FilterGroupType = @FilterGroupType,
    DefaultFilterType = @FilterGroupType,
    RequiresSearchTypeID = @RequiresSearchTypeID,
    SelectByDefault = @SelectByDefault,
    Mandatory = @Mandatory,
    PrimaryKey = @PrimaryKey
WHERE    ColumnName = @ColumnName
    AND ComplianceSearchTypeID = @ComplianceSearchTypeID
    AND (
        ISNULL(ColumnNameResourceName, '') != ISNULL(@ColumnNameResourceName, '')
        OR ISNULL(FromTable, '') != ISNULL(@FromTable, '')
        OR SelectName != @SelectName
        OR JoinClause != ''
        OR WhereClause != @WhereClause
        OR SelectOptionsSQL != ''
        OR FilterGroupType != @FilterGroupType
        OR DefaultFilterType != @FilterGroupType
        OR ISNULL(RequiresSearchTypeID, -1) != ISNULL(@RequiresSearchTypeID, -1)
        OR SelectByDefault != @SelectByDefault
        OR Mandatory != @Mandatory
        OR PrimaryKey != @PrimaryKey
    )

IF @@ROWCOUNT > 0
BEGIN
    -- Force saved queries using this column to be recalculated
    UPDATE    css
    SET    SearchSQL = NULL
    FROM    (
            SELECT    css.ComplianceSavedSearchID
            FROM    dbo.ComplianceSavedSearch AS css
                CROSS APPLY css.SearchXML.nodes('//column[@name]') T(cols)
                INNER JOIN dbo.ComplianceSearchTypeColumn AS cstc
                    ON cstc.ColumnName = cols.value('@name', 'nvarchar(max)')
                    AND cstc.ColumnName = @ColumnName
                    AND cstc.ComplianceSearchTypeID = @ComplianceSearchTypeID

            UNION

            SELECT    css.ComplianceSavedSearchID
            FROM    dbo.ComplianceSavedSearch AS css
                CROSS APPLY css.SearchXML.nodes('//condition[@column]') T(cols)
                INNER JOIN dbo.ComplianceSearchTypeColumn AS cstc
                    ON cstc.ColumnName = cols.value('@column', 'nvarchar(max)')
                    AND cstc.ColumnName = @ColumnName
                    AND cstc.ComplianceSearchTypeID = @ComplianceSearchTypeID
        ) AS cols
        INNER JOIN dbo.ComplianceSavedSearch css ON css.ComplianceSavedSearchID = cols.ComplianceSavedSearchID
    WHERE    css.SearchSQL IS NOT NULL
END
GO

 With this stored procedure configured you can configure custom columns that are available in reports like the following:

EXEC CustomCreateComplianceSearchTypeColumn
    @ColumnName = 'Days Since Inventory Received'
    , @FromTable = NULL
    , @SelectName = 'DATEDIFF(d, {NAMESPACE}ComplianceComputer.InventoryDate, GETUTCDATE())'
    , @WhereClause = 'DATEDIFF(d, {NAMESPACE}ComplianceComputer.InventoryDate, GETUTCDATE())'
    , @FilterGroupType = 2 /* number */
    , @ComplianceSearchTypeName = 'Computer'