105 lines
3.9 KiB
Markdown
105 lines
3.9 KiB
Markdown
# LiteLLM Spend Query Patterns (app1 Postgres)
|
|
|
|
Quick-reference for querying today's spend directly from LiteLLM's Postgres database on app1.
|
|
|
|
## SSH Access
|
|
|
|
```bash
|
|
ssh -i /root/.ssh/itpp-infra -o StrictHostKeyChecking=no root@152.53.36.131
|
|
```
|
|
|
|
## Today's Total Spend
|
|
|
|
```bash
|
|
ssh -i /root/.ssh/itpp-infra -o StrictHostKeyChecking=no root@152.53.36.131 \
|
|
'docker exec litellm_postgres psql -U litellm -d litellm_db -c "
|
|
SELECT
|
|
ROUND(SUM(spend)::numeric, 4) as total_spend,
|
|
COUNT(*) as total_requests,
|
|
SUM(total_tokens) as total_tokens
|
|
FROM \"LiteLLM_SpendLogs\"
|
|
WHERE \"startTime\" >= '\''2026-07-14 00:00:00'\''
|
|
AND \"startTime\" < '\''2026-07-15 00:00:00'\'';
|
|
"'
|
|
```
|
|
|
|
## Per-Model Breakdown
|
|
|
|
```bash
|
|
ssh -i /root/.ssh/itpp-infra -o StrictHostKeyChecking=no root@152.53.36.131 \
|
|
'docker exec litellm_postgres psql -U litellm -d litellm_db -c "
|
|
SELECT
|
|
model,
|
|
COUNT(*) as requests,
|
|
ROUND(SUM(spend)::numeric, 4) as spend,
|
|
SUM(total_tokens) as tokens
|
|
FROM \"LiteLLM_SpendLogs\"
|
|
WHERE \"startTime\" >= '\''2026-07-14 00:00:00'\''
|
|
AND \"startTime\" < '\''2026-07-15 00:00:00'\''
|
|
GROUP BY model
|
|
ORDER BY spend DESC;
|
|
"'
|
|
```
|
|
|
|
## Per-User/Key Breakdown
|
|
|
|
```bash
|
|
ssh -i /root/.ssh/itpp-infra -o StrictHostKeyChecking=no root@152.53.36.131 \
|
|
'docker exec litellm_postgres psql -U litellm -d litellm_db -c "
|
|
SELECT
|
|
COALESCE(u.user_email, s.user, s.api_key) as identity,
|
|
COUNT(*) as requests,
|
|
ROUND(SUM(s.spend)::numeric, 4) as spend,
|
|
SUM(s.total_tokens) as tokens
|
|
FROM \"LiteLLM_SpendLogs\" s
|
|
LEFT JOIN \"LiteLLM_UserTable\" u ON s.user = u.user_id
|
|
WHERE s.\"startTime\" >= '\''2026-07-14 00:00:00'\''
|
|
AND s.\"startTime\" < '\''2026-07-15 00:00:00'\''
|
|
GROUP BY identity
|
|
ORDER BY spend DESC;
|
|
"'
|
|
```
|
|
|
|
## Failed vs Successful Requests
|
|
|
|
```bash
|
|
ssh -i /root/.ssh/itpp-infra -o StrictHostKeyChecking=no root@152.53.36.131 \
|
|
'docker exec litellm_postgres psql -U litellm -d litellm_db -c "
|
|
SELECT status, COUNT(*), ROUND(SUM(spend)::numeric, 4) as total_spend
|
|
FROM \"LiteLLM_SpendLogs\"
|
|
WHERE \"startTime\" >= '\''2026-07-14 00:00:00'\''
|
|
AND \"startTime\" < '\''2026-07-15 00:00:00'\''
|
|
GROUP BY status
|
|
ORDER BY count DESC;
|
|
"'
|
|
```
|
|
|
|
## Pitfalls
|
|
|
|
- **Column names are camelCase**: `startTime`, `endTime`, `completionStartTime` — always quoted
|
|
- **Table name is `LiteLLM_SpendLogs`** (capital L in Logs)
|
|
- **Database is `litellm_db`**, not `litellm`
|
|
- **Single-quote escaping** in nested SSH+Docker+psql is tricky: use `'\\''` to break out of the outer single quote, insert a literal `'`, then resume
|
|
- **Failed requests have `spend = 0` and `total_tokens = 0`** — filter with `WHERE spend > 0` for spend-only analysis
|
|
- **The viewer key (`sk-dZ6QafZLLN5Ewl0NxVlRhQ`) only has `llm_api_routes`** — cannot query `/global/spend` or `/spend/logs` via HTTPS. Go through Postgres directly.
|
|
- **NULL-model failure rows are external auth noise.** Rows with empty `model`, `model_group`, and `custom_llm_provider` + `status = 'failure'` are requests rejected before model routing (auth failures from OpenWebUI or stale integrations). Filter them out for Hermes-specific analysis: `WHERE (model != '' AND model IS NOT NULL)`. See `references/litellm-null-model-failures.md`. These rows inflate failure counts in reports — report them separately.
|
|
|
|
## Column Reference
|
|
|
|
Key columns in `LiteLLM_SpendLogs`:
|
|
|
|
| Column | Type | Notes |
|
|
|---|---|---|
|
|
| `startTime` | timestamp | Request time (server local) |
|
|
| `spend` | double precision | Cost in USD |
|
|
| `total_tokens` | integer | Total tokens consumed |
|
|
| `prompt_tokens` | integer | Input tokens |
|
|
| `completion_tokens` | integer | Output tokens |
|
|
| `model` | text | Model name as called |
|
|
| `model_group` | text | Model group (e.g., `deepseek-v4-pro`) |
|
|
| `custom_llm_provider` | text | Provider (e.g., `deepseek`, `openrouter`) |
|
|
| `user` | text | User ID |
|
|
| `api_key` | text | API key hash used |
|
|
| `status` | text | `success` or `failure` |
|
|
| `cache_hit` | text | `true` if cache hit |
|