Using ELC reporting log data

From ELC
Jump to: navigation, search

Go Up to Administering AppWave Enterprise License Center Server


Log data can be imported into multiple tools:

  • Importing into Microsoft Access using XML
  • Importing into Microsoft Excel and Access using CSV format

To import data to Microsoft Access using XML

Importing log data into Microsoft Access is available for Microsoft Office Access 2002 and above.

  1. Start Microsoft Access and create a new database.
  2. Import the ELC export.xml and lm_reporting_access.xsd files located in the directory which you specified when running the exportLogs command. If no directory was specified, the exported data can be found in the <ELC>/export/export_<timestamp>/xml directory.
    1. In Access, choose File|Get External Data|Import.
    2. In the Import dialog box, choose XML (*.xml, *.xsd) as the file type.
    3. Select the lm_reporting_access.xsd file to import. This file contains information regarding the database schema.
    4. Click the Import button.
      1. Example13.jpg
    5. Click OK when the Import XML dialog box appears.
      1. Example14.jpg
    6. The database schema has now been imported.
    7. In Access, choose File|Get External Data|Import (to import the actual data).
    8. Select XML from the Files of Type drop-down list.
    9. Select export.xml as the file to import.
    10. When the Import XML dialog box appears, select Options >> and click the Append Data to Existing Table(s) radio button.
      1. Example151.jpg
    11. Click OK and the data from export.xml is imported into the existing database tables.

    To import data into Microsoft Excel and Access using CSV

    1. Run the ELC exportLogs command with the –csv option.
    2. From the <ELC>\bin directory run the csvConvert script. This script imports all of the exported CSV files into a single Excel workbook:
      > csvConvert <csv_export_directory>
      Output should be similar to the following:
      Embarcadero ExportLogs Conversion Utility v1.0
      Importing files into workbook..............
      Successfully created C:\export\export_2004-2-23_6-29\csv\embarcadero_reporting_logs.xls.
    3. Once the Embarcadero_reporting_logs.xsl file has been created, open the file in Microsoft Excel. Notice that each .csv file has now been imported into a separate Excel worksheet.

    To query the data and create reports, you need to import those worksheets into Microsoft Access.

    1. Start Access and create a new database.
    2. Import the ELC Embarcadero_reporting_logs.xsl file. This file is located in the directory you specified when running the csvConvert utility command.
      1. In Access, choose File|Get External Data|Import.
      2. In the Import dialog box, choose Microsoft Excel as the file type.
      3. Click the Import button.
      4. Example15.jpg

    3. This opens the Import Spreadsheet wizard. The wizard enables you to import any number of worksheets from the Embarcadero_reporting_logs Excel workbook.
    4. Example16.jpg

      1. Choose a worksheet to import to. See Sample Queries. You only need to import tables named lm_product, lm_pool_type_lu, lm_pool_entry, and lm_entry_instance:
      2. Example17.jpg

      3. Click the First Row Contains Column Headings check box.
      4. Click Finish. The worksheet has now been imported into an Access Table of the same name.

    To create Queries in Microsoft Access

    1. In the Database window, click the Queries link.
    2. Double-click Create Query in Design View to create a new query.
      1. Example18.jpg
    3. Select all of the available tables (the example shows four tables imported from the original worksheet).
    4. Click Add.
    5. Right-click the Design Pane in the Query Designer and select SQL View.
    6. Copy and paste the following into the SQL View of the Query Designer window.
      1. SELECT LM_POOL_ENTRY.LM_REQ_USERNAME, LM_POOL_ENTRY.LM_HOST_NAME, LM_PRODUCT.LM_PRODUCT_NAME, LM_PRODUCT.LM_SKU_NAME, LM_POOL_TYPE_LU.LM_POOL_TYPE_DESC, LM_ENTRY_INSTANCE.LM_START_TIME, LM_ENTRY_INSTANCE.LM_FINISH_TIME FROM LM_POOL_ENTRY, LM_ENTRY_INSTANCE, LM_PRODUCT, LM_POOL_TYPE_LU WHERE (((LM_ENTRY_INSTANCE.LM_START_TIME)<[Enter a starting date (ie 2/20/2004) ]) AND ((LM_POOL_ENTRY.LM_REQUESTED_TYPE)=1) AND ((LM_POOL_ENTRY.LM_POOL_ENTRY_ID)=[LM_ENTRY_INSTANCE].[LM_POOL_ENTRY_ID]) AND ((LM_PRODUCT.LM_PRODUCT_ID)=[LM_POOL_ENTRY].[LM_PRODUCT_ID]) AND ((LM_PRODUCT.LM_SKU)=[LM_POOL_ENTRY].[LM_SKU_ID]) AND ((LM_POOL_TYPE_LU.LM_POOL_TYPE)=[LM_POOL_ENTRY].[LM_REQUESTED_TYPE]));
    7. Save this query with the name concurrent_usage.
    8. In the Database window, right-click the newly created concurrent_usage query and click Export. You can now export this query to Excel format by selecting Excel in the Files of Type drop-down list to create charts, reports, and pivot tables.

    You can also quickly create reports for the concurrent_usage query (or any queries you create) using the Access Report Designer or Report Wizard.

    Sample Queries

    1. Query
      1. SELECT LM_POOL_ENTRY.LM_REQ_USERNAME, LM_POOL_ENTRY.LM_HOST_NAME, LM_PRODUCT.LM_PRODUCT_NAME, LM_PRODUCT.LM_SKU_NAME, LM_POOL_TYPE_LU.LM_POOL_TYPE_DESC, LM_ENTRY_INSTANCE.LM_START_TIME, LM_ENTRY_INSTANCE.LM_FINISH_TIME FROM LM_POOL_ENTRY, LM_ENTRY_INSTANCE, LM_PRODUCT, LM_POOL_TYPE_LU WHERE (((LM_ENTRY_INSTANCE.LM_START_TIME)<[Enter a starting date (ie 2/20/2004) ]) AND ((LM_POOL_ENTRY.LM_REQUESTED_TYPE)=1) AND ((LM_POOL_ENTRY.LM_POOL_ENTRY_ID)=[LM_ENTRY_INSTANCE].[LM_POOL_ENTRY_ID]) AND ((LM_PRODUCT.LM_PRODUCT_ID)=[LM_POOL_ENTRY].[LM_PRODUCT_ID]) AND ((LM_PRODUCT.LM_SKU)=[LM_POOL_ENTRY].[LM_SKU_ID]) AND ((LM_POOL_TYPE_LU.LM_POOL_TYPE)=[LM_POOL_ENTRY].[LM_REQUESTED_TYPE]));
        Description - This query usable in Microsoft Access enables a user to select all licensees using concurrent licenses 1 that began using any product before 2-20-2004 (insert your own date). It includes the licensees' username, hostname, product they were using, license type, their starting time, and completion time (when the license was released).
    2. Query 2
      1. SELECT LM_REQ_USERNAME, LM_PRODUCT.LM_PRODUCT_NAME, LM_PRODUCT.LM_SKU_NAME, LM_ENTRY_INSTANCE.LM_START_TIME FROM LM_POOL_ENTRY, LM_ENTRY_INSTANCE, LM_PRODUCT WHERE LM_REQUESTED_TYPE=2 AND LM_DONE IS NULL AND LM_PRODUCT.LM_PRODUCT_ID=LM_POOL_ENTRY.LM_PRODUCT_ID AND ((LM_PRODUCT.LM_SKU)=[LM_POOL_ENTRY].[LM_SKU_ID]) AND LM_POOL_ENTRY.LM_POOL_ENTRY_ID=LM_ENTRY_INSTANCE.LM_POOL_ENTRY_ID
        Description: This query selects all named users that currently have licenses in use.
    3. Query 3
      1. SELECT LM_REQ_USERNAME, LM_PRODUCT.LM_PRODUCT_NAME, LM_PRODUCT.LM_SKU_NAME, LM_ENTRY_INSTANCE.LM_START_TIME FROM LM_POOL_ENTRY, LM_ENTRY_INSTANCE, LM_PRODUCT WHERE LM_REQUESTED_TYPE=1 AND LM_DONE IS NULL AND LM_PRODUCT.LM_PRODUCT_ID=LM_POOL_ENTRY.LM_PRODUCT_ID AND ((LM_PRODUCT.LM_SKU)=[LM_POOL_ENTRY].[LM_SKU_ID]) AND LM_POOL_ENTRY.LM_POOL_ENTRY_ID=LM_ENTRY_INSTANCE.LM_POOL_ENTRY_ID
        Description: This query selects all concurrent users that currently have licenses in use.
    4. Query
      1. SELECT LM_POOL_ENTRY.LM_REQ_USERNAME, LM_POOL_ENTRY.LM_HOST_NAME, LM_PRODUCT.LM_PRODUCT_NAME, LM_PRODUCT.LM_SKU_NAME, LM_ENTRY_INSTANCE.LM_START_TIME, LM_ENTRY_INSTANCE.LM_FINISH_TIME, (DateDiff("n",[LM_START_TIME],[LM_FINISH_TIME]) / 60 ) AS "TIMEUSED IN HOURS" FROM LM_POOL_ENTRY, LM_ENTRY_INSTANCE, LM_PRODUCT WHERE LM_ENTRY_INSTANCE.LM_START_TIME>[Enter a start date (ie 2/20/2004) ] AND LM_ENTRY_INSTANCE.LM_FINISH_TIME<[Enter a finish date (ie 2/20/2004) ] AND LM_PRODUCT.LM_PRODUCT_ID=LM_POOL_ENTRY.LM_PRODUCT_ID AND ((LM_PRODUCT.LM_SKU)=[LM_POOL_ENTRY].[LM_SKU_ID]) AND LM_POOL_ENTRY.LM_POOL_ENTRY_ID=LM_ENTRY_INSTANCE.LM_POOL_ENTRY_ID
        Description: This query concatenates users and products with a summary of the start times, finish times, and time used in hours (the DateDiff expression is usable only with Access).
    5. Query
      1. SELECT LM_POOL_ENTRY.LM_REQ_USERNAME, LM_POOL_ENTRY.LM_HOST_NAME, LM_PRODUCT.LM_PRODUCT_NAME, LM_PRODUCT.LM_SKU_NAME, LM_ENTRY_INSTANCE.LM_START_TIME, LM_ENTRY_INSTANCE.LM_FINISH_TIME, HoursAndMinutes([LM_FINISH_TIME]-[LM_START_TIME]) AS ["TIMEUSED IN HOURS:MINUTES"] FROM LM_POOL_ENTRY, LM_ENTRY_INSTANCE, LM_PRODUCT WHERE LM_ENTRY_INSTANCE.LM_START_TIME>[ENTER A START DATE (ie 2/20/2004) ] AND LM_ENTRY_INSTANCE.LM_FINISH_TIME<[ENTER A FINISH DATE (ie 2/20/2004) ] AND LM_PRODUCT.LM_PRODUCT_ID=[LM_POOL_ENTRY].[LM_PRODUCT_ID] AND ((LM_PRODUCT.LM_SKU)=[LM_POOL_ENTRY].[LM_SKU_ID]) AND LM_POOL_ENTRY.LM_POOL_ENTRY_ID=[LM_ENTRY_INSTANCE].[LM_POOL_ENTRY_ID];
    Description: This query is identical to the one above with the exception that it uses a function HoursAndMinutes which can be created by following instructions found at the following website:

    http://office.microsoft.com/assistance/preview.aspx?AssetID=HA011102181033&CTT=1&Origin=EC010227041033&QueryID=YIx4rFgBb0

    After creating the HoursAndMinutes Function mentioned in the article, you can specify the time difference formatted as "Hours:Minutes" between the start time and finish time.

    1 LM_REQUESTED_TYPE = 1 denotes concurrent and LM_REQUESTED_TYPE=2 denotes network named user

    2 Due to an issue with MS Access xml import, null integer values fail to import properly.
    Workaround: Change the following expression LM_DONE IS NULL to LM_DONE = "" in the query above when using the Access XML Import.
    3 Due to an issue with MS Access xml import, null integer values fail to import properly.
    Workaround: Change the following expression LM_DONE IS NULL to LM_DONE = "" in the query above when using the Access XML Import.

    Database Table Descriptions

    The following table gives the name of each table in the database along with a general description of the data it contains:

    Table name Description

    lm_denial_entry

    Denials refer to attempts to access licenses when all available licenses are currently in use.

    lm_entry_instance

    Tracks timestamp information regarding when license usage begins and ends.

    lm_license

    Contains information regarding currently available licenses.

    lm_license_pool

    Contains information regarding floating and named license pools.

    lm_permission

    Contains named and floating user permissions.

    lm_pool_entry

    Tracks usage data such as user, host, and product which can be linked to timestamp data.

    lm_pool_type_lu

    Contains descriptions of license pool types.

    lm_product

    Contains product information related to available licenses.

    lm_timestamp

    Contains current timestamp.

    lm_user

    Contains user information, only pertains to users entered into userlist.txt and userlist-concurrent.txt.


    See Also