Skip to main content

LLM Usage Reporting View

On Waii, you can view the usage of your LLMs in the LLM Usage Reporting View. This view provides you with a detailed breakdown of the usage of your LLMs, including the number of times each LLM has been used, the number of times each LLM has been used by each user, and the number of times each LLM has been used by each user in each month.

When Waii system get started, it will automatically create a new LLM Usage Reporting View and add it to users which have waii-org-admin-user or above.

Accessing the LLM Usage Reporting View

If you are an user with waii-org-admin-user or above, you can access the LLM Usage Reporting View by clicking the top-right corner -> "Manage Warehouses" and find waii-usage-reporting in the list.

If you are using Waii SaaS deployment, and want to access the view, please contact Waii team to enable it for you.

If you are using Waii on-premise deployment (any version after 1.21.0), you should be able to access the view automatically if you login as the default user.

Get usage reporting

You can get usage reporting via UI or API.

From Waii UI​

Once you switch to the LLM Usage Reporting view on UI, you can directly ask natural language questions.

Before running the next example, you need to make sure that you selected the right database connection. You should be able to find the WAII schema, and there're views like TOKEN_USAGE, etc. under it.

usage-view-ui.png

Now if you ask question like:

Give me total tokens consumed by each user every month

You should get a query like:

WITH _access_controlled_token_usage AS (
  SELECT
    *
  FROM waii.token_usage
  WHERE
    user_id IN (
      SELECT
        users.user_id
      FROM waii.users
      WHERE
        users.organization = '<your-domain>'
    )
)
SELECT
  token_usage.user_id,
  EXTRACT(YEAR FROM token_usage.timestamp) AS year,
  EXTRACT(MONTH FROM token_usage.timestamp) AS month,
  SUM(token_usage.total_tokens) AS total_tokens
FROM _access_controlled_token_usage AS token_usage
GROUP BY
  token_usage.user_id,
  EXTRACT(YEAR FROM token_usage.timestamp),
  EXTRACT(MONTH FROM token_usage.timestamp)

Please note that the _access_controlled_token_usage is automatically added to the query to make sure you can only access the data you have permission to. (It is protected by the Waii table access rules: see this doc: https://doc.waii.ai/python/docs/access-rule-module for more details).

If you are using on-prem deployment, you should see the filter of users.organization is my_org_id, that's default organization id of the system.

Even if you directly run the query in the SQL editor, you should get the same result -- The table access rules will be applied to the query automatically in the background.

In addition to the above example which generate a query for you, you can also run the query directly in the SQL editor (just copy and paste the query like below to the SQL editor, and click "Run Query" button to run it):

SELECT
  AVG(query_tokens.input_tokens) AS avg_input_tokens,
  AVG(query_tokens.output_tokens) AS avg_output_tokens
FROM (
  SELECT
    SUM(token_usage.input_tokens) AS input_tokens,
    SUM(token_usage.output_tokens) AS output_tokens
  FROM waii.token_usage
  WHERE
    NOT token_usage.query_uuid IS NULL
  GROUP BY
    token_usage.query_uuid
) AS query_tokens

The above query will give you the average input tokens and output tokens for each query.

From SDK​

You can also run the query via SDK. Here is an example in Python: https://doc.waii.ai/python/docs/sql-query-module#run-a-query-sync

First you need to activate the connection ('waii://waii@host/waii-usage-reporting' is the constant you should always use to access the usage reporting view, no need to change host to the real host name):

WAII.Database.activate_connection('waii://waii@host/waii-usage-reporting')

Then you can run the query and get the result:

WAII.Query.run(RunQueryRequest(query = "SELECT * from WAII.TOKEN_USAGE"))

If you are interested in other SDKs, you can find the query run API in the SDK documentation. For example Java SDK: https://doc.waii.ai/java/docs/sql-query-module#running-a-query-

Data Model​

The data modeling of the LLM Usage Reporting View is as follows:

  • WAII schema, which contains the following views:
    • DB_CONNECTIONS: Contains the database connections that are assigned to each user.
      • It include a key and a user_id, which is used to determine the database connection that is assigned to each user.
    • USERS: For each user, it includes USER_ID, ORGANIZATION, TENANT and NAME.
    • LLMS: Containers LLM_ID (internal_ID), LLM_NAME (such as gpt-4o), and LLM_VERSION (3.5, 4, etc.)
    • TOKEN_USAGE: Contains the following fields
      • DB_CONNECTION_KEY: The key of the database connection that is assigned to the user.
      • TIMESTAMP: The timestamp of the LLM call.
      • ELAPSED_TIME_MS: The elapsed time of the LLM call in milliseconds.
      • USER_ID: The user ID of the user who made the LLM call.
      • QUERY_UUID: The UUID of the query. This can be NULL if the LLM call is not associated with a query generation task (for example, it is associated with a database summarization task).
      • OPERATION_TYPE: The operation type of the LLM call.
      • INPUT_TOKENS: The number of input tokens used in the LLM call.
      • OUTPUT_TOKENS: The number of output tokens used in the LLM call.
      • TOTAL_TOKENS: The total number of tokens used in the LLM call. (This is the sum of the input and output tokens.)
      • LLM_ID: The internal ID of the LLM that was called.

Joins between the views:

  • DB_CONNECTIONS and USERS are joined on USER_ID.
  • TOKEN_USAGE and DB_CONNECTIONS are joined on DB_CONNECTION_KEY.
  • TOKEN_USAGE and LLMS are joined on LLM_ID.
  • TOKEN_USAGE and USERS are joined on USER_ID.

Important notes:

  • For one generated query (which has the same QUERY_UUID), there may be multiple LLM calls, they can have a different OPERATION_TYPE. In order to get the total tokens consumed by each query, you need to sum the TOTAL_TOKENS for each QUERY_UUID, and group by QUERY_UUID. (Make sure exclude QUERY_UUID is NULL)

Example queries​

Here're some example queries you can run in the LLM Usage Reporting View:

Get avg input/output tokens for queries​

SELECT
  AVG(query_tokens.input_tokens) AS avg_input_tokens,
  AVG(query_tokens.output_tokens) AS avg_output_tokens
FROM (
  SELECT
    SUM(token_usage.input_tokens) AS input_tokens,
    SUM(token_usage.output_tokens) AS output_tokens
  FROM WAII.TOKEN_USAGE AS token_usage
  WHERE
    NOT token_usage.query_uuid IS NULL
  GROUP BY
    token_usage.query_uuid
) AS query_tokens

Get total tokens consumed by each user every month​

SELECT
  users.user_id,
  users.user_name,
  SUM(token_usage.total_tokens) AS total_tokens
FROM WAII.TOKEN_USAGE AS token_usage
JOIN WAII.USERS AS users
  ON token_usage.user_id = users.user_id
WHERE
  token_usage.timestamp >= CURRENT_DATE - INTERVAL '7 day'
GROUP BY
  users.user_id,
  users.user_name

how many tokens spent on each database for database indexing tasks​

SELECT
  token_usage.db_connection_key,
  SUM(token_usage.total_tokens) AS total_tokens_spent
FROM waii.token_usage AS token_usage
WHERE
  token_usage.operation_type IN ('db_description_generation', 'fk_generation', 'pk_generation')
GROUP BY
  token_usage.db_connection_key

What's the total amount of llm token usage per day, per llm model?​

SELECT
  DATE_TRUNC('day', token_usage.timestamp) AS day,
  llms.llm_name,
  SUM(token_usage.total_tokens) AS total_tokens
FROM waii.token_usage AS token_usage
JOIN waii.llms
  ON token_usage.llm_id = llms.llm_id
GROUP BY
  DATE_TRUNC('day', token_usage.timestamp),
  llms.llm_name
ORDER BY
  day,
  llms.llm_name

Get aggregated token usage for each query gen, and total token consumed by different operation types​

WITH token_aggregation AS (
  SELECT
    token_usage.query_uuid,
    SUM(token_usage.total_tokens) AS total_tokens_per_query,
    SUM(token_usage.input_tokens) AS total_input_tokens,
    SUM(token_usage.output_tokens) AS total_output_tokens
  FROM waii.token_usage AS token_usage
  WHERE
    NOT token_usage.query_uuid IS NULL
  GROUP BY
    token_usage.query_uuid
), operation_pivot AS (
  SELECT
    token_usage.query_uuid,
    SUM(
      CASE
        WHEN token_usage.operation_type = 'table_selection'
        THEN token_usage.total_tokens
        ELSE 0
      END
    ) AS table_selection_tokens,
    SUM(
      CASE
        WHEN token_usage.operation_type = 'column_selection'
        THEN token_usage.total_tokens
        ELSE 0
      END
    ) AS column_selection_tokens,
    SUM(
      CASE
        WHEN token_usage.operation_type = 'query_generation'
        THEN token_usage.total_tokens
        ELSE 0
      END
    ) AS query_generation_tokens,
    SUM(
      CASE
        WHEN token_usage.operation_type = 'tweak_checker'
        THEN token_usage.total_tokens
        ELSE 0
      END
    ) AS tweak_checker_tokens,
    SUM(
      CASE
        WHEN token_usage.operation_type = 'info_schema_shortcut'
        THEN token_usage.total_tokens
        ELSE 0
      END
    ) AS info_schema_shortcut_tokens,
    SUM(
      CASE
        WHEN token_usage.operation_type = 'rule_selection'
        THEN token_usage.total_tokens
        ELSE 0
      END
    ) AS rule_selection_tokens,
    SUM(
      CASE
        WHEN token_usage.operation_type = 'similar_query_match'
        THEN token_usage.total_tokens
        ELSE 0
      END
    ) AS similar_query_match_tokens
  FROM waii.token_usage AS token_usage
  WHERE
    NOT token_usage.query_uuid IS NULL
  GROUP BY
    token_usage.query_uuid
)
SELECT
  ta.query_uuid,
  ta.total_tokens_per_query,
  ta.total_input_tokens,
  ta.total_output_tokens,
  op.table_selection_tokens,
  op.column_selection_tokens,
  op.query_generation_tokens,
  op.tweak_checker_tokens,
  op.info_schema_shortcut_tokens,
  op.rule_selection_tokens,
  op.similar_query_match_tokens
FROM token_aggregation AS ta
JOIN operation_pivot AS op
  ON ta.query_uuid = op.query_uuid