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
Jun 02, 2021 07:58 PM
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'
Jun 03, 2021 02:09 AM
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'
Jun 03, 2021 02:09 AM