Using ELC reporting log data
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
Contents
To import data to Microsoft Access using XML
Importing log data into Microsoft Access is available for Microsoft Office Access 2002 and above.
- Start Microsoft Access and create a new database.
- Import the ELC
export.xml
andlm_reporting_access.xsd
files located in the directory which you specified when running theexportLogs
command. If no directory was specified, the exported data can be found in the<ELC>/export/export_<timestamp>/xml
directory. - In Access, choose File|Get External Data|Import.
- In the Import dialog box, choose XML (
*.xml, *.xsd
) as the file type. - Select the
lm_reporting_access.xsd
file to import. This file contains information regarding the database schema. - Click the Import button.
- Click OK when the Import XML dialog box appears.
- The database schema has now been imported.
- In Access, choose File|Get External Data|Import (to import the actual data).
- Select XML from the Files of Type drop-down list.
- Select
export.xml
as the file to import. - When the Import XML dialog box appears, select Options >> and click the Append Data to Existing Table(s) radio button.
- Click OK and the data from
export.xml
is imported into the existing database tables. - Run the ELC
exportLogs
command with the –csv option. - 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.
- 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. - Start Access and create a new database.
- Import the ELC Embarcadero_reporting_logs.xsl file. This file is located in the directory you specified when running the csvConvert utility command.
- In Access, choose File|Get External Data|Import.
- In the Import dialog box, choose Microsoft Excel as the file type.
- Click the Import button.
- This opens the Import Spreadsheet wizard. The wizard enables you to import any number of worksheets from the Embarcadero_reporting_logs Excel workbook.
- 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:
- Click the First Row Contains Column Headings check box.
- Click Finish. The worksheet has now been imported into an Access Table of the same name.
- In the Database window, click the Queries link.
- Double-click Create Query in Design View to create a new query.
- Select all of the available tables (the example shows four tables imported from the original worksheet).
- Click Add.
- Right-click the Design Pane in the Query Designer and select SQL View.
- Copy and paste the following into the SQL View of the Query Designer window.
- Save this query with the name concurrent_usage.
- 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.
- Query
- 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).
- Query 2
- Description: This query selects all named users that currently have licenses in use.
- Query 3
- Description: This query selects all concurrent users that currently have licenses in use.
- Query
- 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).
- Query
- 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:
- Starting AppWave Enterprise License Center
- Using AppWave Enterprise License Center commands
- Setting up a client for named user licensing
- Setting up a client for concurrent licensing
- Interpreting the logs
- Troubleshooting the server
- Troubleshooting the client
- Generating usage report logs
- Questions and Answers
To import data into Microsoft Excel and Access using CSV
To query the data and create reports, you need to import those worksheets into Microsoft Access.
To create Queries in Microsoft Access
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]));
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
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]));
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
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
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
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];
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 andLM_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 expressionLM_DONE IS NULL
toLM_DONE = ""
in the query above when using theAccess XML Import
.
3 Due to an issue with MS Access xml import, null integer values fail to import properly.
Workaround: Change the following expressionLM_DONE IS NULL
toLM_DONE = ""
in the query above when using theAccess 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 |