LGTM Enterprise 1.25

Querying the business intelligence database

LGTM Enterprise can be set up to export analysis data to an external SQL database. This allows you to use the data in a business intelligence (BI) tool such as Tableau or QlikView. It also means that—if you have access to this database—you can query it directly using SQL.

This topic is only relevant to you if:

  • Your LGTM Enterprise system exports to a business intelligence database.
  • You have a user account for this database.
  • You are familiar with running SQL queries.

Overview

The BI database is an external database—not part of LGTM Enterprise—into which LGTM can automatically load analysis data. Typically, this data is only accessed from a business intelligence application which pulls information from the database for you and allows you to see graphical representations of aspects of the data that you want to explore.

If you need to run queries on the database directly, you'll be working with the tables in the public schema.

The public schema

The following diagram shows the main tables in the public schema of the BI database.

The tables can be roughly categorized as follows (indicated by the colors in the diagram):

  • Project and metric metadata

    Each row of the projects table contains information about a specific project. For example:

    id lgtm_id display_name repository repository_type
    123 131477296 mygroup/myproject https://example.org/projects/g/mygroup/myproject GIT

    Each row in the metrics table contains information about a pairing of a specific CodeQL query and a metric type. There are also 4 rows for the basic metrics: Added lines, Deleted lines, Net lines, and Churn. There are 8 metric types:

    • Absolute metrics
    • Absolute metrics for excluded files
    • Basic
    • Existing alerts
    • Existing alerts in excluded files
    • Fixed alerts
    • Metric delta
    • New alerts

    Queries are assigned to one or more of these metric types programmatically during the export process. For example:

    id metric_name metric_type display_name description language severity precision lgtm_metric_id
    1 Added lines Basic Total added lines Total number of added lines       OAdded lines
    2 Deleted lines Basic Total deleted lines Total number of deleted lines       ODeleted lines
    3 Net lines Basic Total net lines Total number of net lines       ONet lines
    4 Churn Basic Total churn Total number of changed lines       OChurn
    5 com.lgtm/javascript-queries:js/incomplete-sanitization New alerts Incomplete string escaping or encoding A string transformer that does not replace or escape all occurrences of a meta-character may be ineffective. JavaScript warning HIGH R1000636
    6 com.lgtm/javascript-queries:js/incomplete-sanitization Fixed alerts Incomplete string escaping or encoding A string transformer that does not replace or escape all occurrences of a meta-character may be ineffective. JavaScript warning HIGH R1000636
    7 com.lgtm/javascript-queries:js/incomplete-sanitization Existing alerts Incomplete string escaping or encoding A string transformer that does not replace or escape all occurrences of a meta-character may be ineffective. JavaScript warning HIGH R1000636
    8 com.lgtm/javascript-queries:js/incomplete-sanitization Existing alerts in excluded files Incomplete string escaping or encoding A string transformer that does not replace or escape all occurrences of a meta-character may be ineffective. JavaScript warning HIGH R1000636
    9 com.lgtm/javascript-queries:js/superfluous-trailing-arguments New alerts Superfluous trailing arguments A function is invoked with extra trailing arguments that are ignored. JavaScript warning VERYHIGH R1000598
    10 com.lgtm/javascript-queries:js/superfluous-trailing-arguments Fixed alerts Superfluous trailing arguments A function is invoked with extra trailing arguments that are ignored. JavaScript warning VERYHIGH R1000598
    11 com.lgtm/javascript-queries:js/superfluous-trailing-arguments Existing alerts Superfluous trailing arguments A function is invoked with extra trailing arguments that are ignored. JavaScript warning VERYHIGH R1000598
    12 com.lgtm/javascript-queries:js/superfluous-trailing-arguments Existing alerts in excluded files Superfluous trailing arguments A function is invoked with extra trailing arguments that are ignored. JavaScript warning VERYHIGH R1000598

    The "com.lgtm/" at the start of a metric_name value indicates that the query is one of LGTM's built-in queries.

    Only queries that have generated analysis results are added to this table. The rows in this table are referenced from several other tables. For example, the latest_alerts table references rows with a metric_type of Existing alerts or Existing alerts in excluded files.

    The metric_tag table simply identifies which tags have been assigned to each query in the metrics table.

  • Latest analysis

    These tables contain data for the most recent analysis of projects. For example: the score and grade calculated for each language in each analyzed snapshot, the details of third-party code referenced by code in a specific language in each snapshot, and the number of alerts for each query found in code of a specific language in each snapshot.

  • Commit changes

    These tables record what changed in specific revisions of a project. For example, the committer's name, the revision header, the timestamp of the change, how many lines of code changed, and the number of alerts (if any) generated for specific queries.

  • Manually populated

    These tables are populated by your LGTM Enterprise administrator, or database administrator, with data from your HR system. The committer_author table can be used for author deduplication by mapping committer IDs to unique IDs output from your HR system.

  • LGTM version

    The version number of LGTM Enterprise at the time the BI database schema was created or last changed. This allows you to identify which version of the database schema you are currently using. This is useful information if you need to contact GitHub Support about an issue relating to BI export.