cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Report on History tab

ImIronMan
By Level 6 Flexeran
Level 6 Flexeran

Hello,

Can we run a report on 'History tab' for licenses or devices etc., ? I do not see this in existing "reports" and neither can really download each device's or license's history one by one, if I'm looking for 100+ devices history, to find out who and what has been updated to what .?

Regards

Rajesh Ponnala

@erwinlindemann 

 

(2) Solutions

@ImIronMan - Due to the volume of data, the Audit Log of History is not exposed in the UI for reporting or queries.  If you have FNMS On-Premises, then you could build a SQL Report against the "FNMS Compliance" database.

The underlying Table\View you're looking for is ComplianceHistory. This table has the details and links back to the various objects and lookup tables.

View solution in original post

Hi Rajesh,

To add some details to the information that "Captain Kirk" already provided: There is no simple OOTB report for history data.

Obviously, in the on-premises version of FNMS only, you can extract these data using SQL based on the [ComplianceHistory] table in the [FNMSCompliance] database.

For building and exposing a report in the FNMS Web UI, there is a number of "gotchas" though:

  • The [ComplianceHistory] table contains information for different object types, like Inventories ([ComplianceComputer]), Assets ([Asset]), Contracts ([Contract]) etc. This table is not highly normalized. Based on the object type you are interested in, you have to join this table with different tables from the [FNMSCompliance] database to obtain all columns that you are interested in.
  • Data are not deleted from the [ComplianceHistory] table automatically. This means the [ComplianceHistory] will grow over time. In an FNMS environment that has been running for a longer time, this table will usually take 50%+ of disk space in the [FNMSCompliance] database. To obtain a meaninful data and not exceed the 80.000 row/report limit in FNMS, you have to filter the data.

Filtering can be done based on a substring in a text fields (Computer Name, Purchase Order Name ...), a date range ([HistoryDate] field) the event type ([ComplianceHistoryTypeID] field) or a combination of these fields.

Building a report based on SQL is not complicated. It generally requires wrapping your SELECT statement into a simple stored procedure, and then calling the 'ComplianceCustomViewRegister' stored procedure for exposing your own stored procedure as a report in the FNMS Web UI.

As a reference, you can look at the SQL code used for creating the various standard reports available in FNMS. All of these reports are created in the 'ComplianceProcedures.sql' file that is used when creating an empty [FNMSCompliance] database.

 

View solution in original post

(4) Replies

@ImIronMan - Due to the volume of data, the Audit Log of History is not exposed in the UI for reporting or queries.  If you have FNMS On-Premises, then you could build a SQL Report against the "FNMS Compliance" database.

The underlying Table\View you're looking for is ComplianceHistory. This table has the details and links back to the various objects and lookup tables.

@kclausen Thanks for your super quick response 🙂

Hi Rajesh,

To add some details to the information that "Captain Kirk" already provided: There is no simple OOTB report for history data.

Obviously, in the on-premises version of FNMS only, you can extract these data using SQL based on the [ComplianceHistory] table in the [FNMSCompliance] database.

For building and exposing a report in the FNMS Web UI, there is a number of "gotchas" though:

  • The [ComplianceHistory] table contains information for different object types, like Inventories ([ComplianceComputer]), Assets ([Asset]), Contracts ([Contract]) etc. This table is not highly normalized. Based on the object type you are interested in, you have to join this table with different tables from the [FNMSCompliance] database to obtain all columns that you are interested in.
  • Data are not deleted from the [ComplianceHistory] table automatically. This means the [ComplianceHistory] will grow over time. In an FNMS environment that has been running for a longer time, this table will usually take 50%+ of disk space in the [FNMSCompliance] database. To obtain a meaninful data and not exceed the 80.000 row/report limit in FNMS, you have to filter the data.

Filtering can be done based on a substring in a text fields (Computer Name, Purchase Order Name ...), a date range ([HistoryDate] field) the event type ([ComplianceHistoryTypeID] field) or a combination of these fields.

Building a report based on SQL is not complicated. It generally requires wrapping your SELECT statement into a simple stored procedure, and then calling the 'ComplianceCustomViewRegister' stored procedure for exposing your own stored procedure as a report in the FNMS Web UI.

As a reference, you can look at the SQL code used for creating the various standard reports available in FNMS. All of these reports are created in the 'ComplianceProcedures.sql' file that is used when creating an empty [FNMSCompliance] database.

 

For another thread asking a similar question (and with a sample query attached in a response), see: Report on History

(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)