![Advanced-UI](https://img.shields.io/static/v1?label=UI&message=Advanced&color=lightgrey) ![NOVA-UI](https://img.shields.io/static/v1?label=UI&message=NOVA&color=blue) ![DB-View](https://img.shields.io/static/v1?label=DB&message=View&color=red) 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. [MINITOC] ## 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). ##### assetStatistic.excludedUsers > type: list of user ids, required: no, default: - User ids that will be excluded from the statistics. ##### assetStatistic.excludedUserGroups > type: list of user group ids, required: no, default: - User group ids that will be excluded from the statistics. ##### assetStatistic.customizeExcludedUserGroupsEnabled= > type: boolean, required: no, default: false If true, users can customize the excluded user groups in the front-end. Normal users can remove user groups from the excluded ones, users with user management permissions can also add additional user groups to the excluded ones. ##### assetStatistic.anonymizedUsersUserGroups= > type: list of user group ids, required: no, default: - User in these groups will see an anonymized version of the statistic whitout any usernames and no user filter neither the group by user option ## Screenshots ![Asset Statistic](asset_statistic.png) ![Asset Statistic Metadata-Filter](asset_statistic_metadata_filter.png) ## Compatibility Matrix | assetStatistic | CELUM (min. version) | |:-------------- |:-------------------- | | 3.1 | 6.4.0 | | Nova Plugin | CELUM (min. version) | |-------------|----------------------| | 3.0-6.12 | 6.12 | | 3.0-6.14 | 6.14 | | 3.2-6.13 | 6.13 | ## 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 #### 3.2 > Released 2023-08-16 + Exclude User and user groups feature #### 3.3 > Released 2023-08-17 + Exclude User groups customizable in frontend #### 3.9 > Released 2023-11-02 + Anonymized mode feature