Asset Statistics

Analyzes specific assets of a specific node, single or multiple assets. Display graphically and in a table the download-, metadata updated-, asset version activated- and asset version added- count. The results are also downloadable as an Excel file. Result grouping by day, asset or user or PIN. Because MS SQL has a limit of 2100 parameters per query, For MS SQL Databass the number of assets in one query is limited to 2090. The limit is also configurable.

Database View

Before installation of the extension, it is necessary to create the view asset_statistic manually by executing the following command on your CELUM database.

If you update from a version below 1.4.0 to 1.4.0 or above, you have to delete old view and create it new, because of changes in the columns.

The same applies when updating to 3.x

MS SQL

CREATE VIEW asset_statistic2 as
SELECT stats.ims_id, stats.ims_time, stats.ims_user_id,
stats.ims_event_type, stats.ims_entity_id, attr.ims_value_string,  cast(stats.ims_time as date) as ims_day,
stats.IMS_CONTEXT, asset.ims_name as asset_name, authorizable.ims_name as user_name, ims_pin.ims_pin_name,  ims_pin_relation.ims_container_id
FROM IMS_STATS stats
LEFT JOIN ims_asset asset ON (stats.ims_entity_id = asset.ims_id )
LEFT JOIN ims_stats_attributes attr ON (stats.ims_id = attr.ims_stats_id AND attr.ims_attribute = 12)
LEFT JOIN ims_authorizable authorizable ON (stats.ims_user_id = authorizable.ims_id)
LEFT JOIN ims_pin ON (attr.ims_value_string = cast(ims_pin.ims_pin_code COLLATE SQL_Latin1_General_CP1_CI_AS as nvarchar(150)))
LEFT JOIN ims_pin_relation ON (ims_pin.ims_id = ims_pin_relation.ims_pin_id)
WHERE stats.ims_entity_type = 1

MySQL

CREATE VIEW asset_statistic2 as
SELECT stats.ims_id, stats.ims_time, stats.ims_user_id,
stats.ims_event_type, stats.ims_entity_id, attr.ims_value_string,  cast(stats.ims_time as date) as ims_day,
stats.IMS_CONTEXT, asset.ims_name as asset_name, authorizable.ims_name as user_name, ims_pin.ims_pin_name,  ims_pin_relation.ims_container_id
FROM IMS_STATS stats
LEFT JOIN ims_asset asset ON (stats.ims_entity_id = asset.ims_id )
LEFT JOIN ims_stats_attributes attr ON (stats.ims_id = attr.ims_stats_id AND attr.ims_attribute = 12)
LEFT JOIN ims_authorizable authorizable ON (stats.ims_user_id = authorizable.ims_id)
LEFT JOIN ims_pin ON (attr.ims_value_string = ims_pin.ims_pin_code)
LEFT JOIN ims_pin_relation ON (ims_pin.ims_id = ims_pin_relation.ims_pin_id)
WHERE stats.ims_entity_type = 1

Oracle

CREATE VIEW asset_statistic2 as
SELECT stats.ims_id, stats.ims_time, stats.ims_user_id,
stats.ims_event_type, stats.ims_entity_id, attr.ims_value_string,  cast(stats.ims_time as date) as ims_day,
stats.IMS_CONTEXT, asset.ims_name as asset_name, authorizable.ims_name as user_name, ims_pin.ims_pin_name,  ims_pin_relation.ims_container_id
FROM IMS_STATS stats
LEFT JOIN ims_asset asset ON (stats.ims_entity_id = asset.ims_id )
LEFT JOIN ims_stats_attributes attr ON (stats.ims_id = attr.ims_stats_id AND attr.ims_attribute = 12)
LEFT JOIN ims_authorizable authorizable ON (stats.ims_user_id = authorizable.ims_id)
LEFT JOIN ims_pin ON (attr.ims_value_string = ims_pin.ims_pin_code)
LEFT JOIN ims_pin_relation ON (ims_pin.ims_id = ims_pin_relation.ims_pin_id)
WHERE stats.ims_entity_type = 1

It is also recommended to execute the following command once in order to correct inconsistent statistic entries:

UPDATE IMS_STATS SET IMS_USER_ID = 0 where IMS_USER_ID IS NULL

The performance can be increased by creating indexes:
CREATE INDEX assetstatistics_index_stats ON ims_stats (ims_time, ims_event_type, ims_entity_id);
CREATE INDEX assetstatistics_index_stats_id ON ims_stats (ims_id);
CREATE INDEX assetstatistics_index_stats_user ON ims_stats (ims_user_id);
CREATE INDEX assetstatistics_index_stats_attributes ON ims_stats_attributes (ims_stats_id, ims_attribute);

Properties

To be configured in {home}/appserver/conf/custom.properties

assetStatistic.license

type: String, required: yes, default: -

license key (delivered by brix IT Solutions)

assetStatistic.visibleForGroup

type: long, required: no, default: -

Restrict the use of the plugin to this user group ID (superadmins always see it in any case)

assetStatistic.maxAssets

type: number, required: no, default: 2090 (only for MS SQL databases)

Limits the number of assets that can be processed in one query (needed if the database has a query parameter limit).

Screenshots

Compatibility Matrix

Release Notes

3.1

Released 2022-04-14

• PIN name and PIN container in group by pin table

3.0

Released 2022-08-16

• Initial Version