Generating custom SQL reports

You can query the database to get a custom usage report using the web-based UI or at a command line, as described below. The table reference to the MySQL database is stored in mysql/database_structure.sql.

Generating custom SQL reports using the web-based UI

To run a SQL query on the License Statistics database using the web-based UI:

1. Log into License Statistics as an administrator.
   
   
2. Click Administration at the top of the License Statistics web page.
   
   
3. From the Administration page, click the SQL Console tab. 
   
   
4. Enter your custom SQL query in the edit box and click Submit to generate the report.
   
   The Custom SQL Report appears in the area below the Custom SQL Query edit box. Query results will be returned in a grid only if you enter a SELECT query.

You can export the data returned from the query by clicking on one of the Download options at the top right of the Custom SQL Report. For example, you may want to view the report in Excel or PDF so you can include it in a report or presentation. See Downloading License Statistics data for more information about obtaining data in various formats.

Warning: Use caution when running queries that use UPDATE or DELETE keywords, because using these keywords can cause data loss or corruption.

Saving a query

You can save SQL queries under the Saved Reports area so that you can easily run the query in the future.

To save a query for future use:

1. Click Add New under the Saved Reports area.
2. Enter a name for your report in the Report Name field.
3. Enter the SQL query in the SQL query field.
4. Click Save to save the query.
5. To run the saved query, click on it in the Saved Reports list. The query appears in the Custom SQL Query area.
   
6. Click Submit to submit the query.

You can also save a query as a CSV, Excel, PDF or SQL file using Save As in the Saved Reports area. After selecting the format to which to save the file, you are prompted to either open or save the file.

Running a SQL query from a command line

To run a SQL query from a command line:

1. Stop License Statistics.
   
   
2. From a command line, navigate to the License Statistics installation directory, and then to mysql_x86 for 32-bit databases or mysql_x64 for 64-bit databases.
   
   
3. Start the MySQL server by running mysql_start.bat for Windows or mysql_start.sh for Linux.
   
   
4. Connect to the License Statistics database using the following command:
   
   For Windows: mysql -u root -h localhost -tcp -Dxformation_licstat
   For Linux: ./mysql -u root -h localhost -tcp -Dxformation_licstat --socket=data/mysql.sock
   
   
5. After connecting to the database, run the desired MySQL commands.
   
   
6. Stop the MySQL server by running mysql_stop.bat for Windows or mysql_stop.sh for Linux.
   
   
7. Restart License Statistics.