Highlighted

Report on History tab

Jump to solution

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

Accepted Solutions
Highlighted
Flexera
Flexera

Re: Report on History tab

Jump to solution

@rajeshponnala - 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

Highlighted
Intrepid explorer

Re: Report on History tab

Jump to solution

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
Highlighted
Flexera
Flexera

Re: Report on History tab

Jump to solution

@rajeshponnala - 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

Highlighted

Re: Report on History tab

Jump to solution

@kclausen Thanks for your super quick response :)

0 Kudos
Highlighted
Intrepid explorer

Re: Report on History tab

Jump to solution

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

Highlighted
Community Manager Community Manager
Community Manager

Re: Report on History tab

Jump to solution

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

(Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".)
0 Kudos