Coverage for .venv/lib/python3.13/site-packages/litellm/proxy/management_endpoints/user_agent_analytics_endpoints.py: 86%
267 statements
« prev ^ index » next coverage.py v7.15.2, created at 2026-10-10 12:01 +0000
1"""
2User Agent Analytics Endpoints
4This module provides optimized endpoints for tracking user agent activity metrics including:
5- Daily Active Users (DAU) by tags for configurable number of days
6- Weekly Active Users (WAU) by tags for configurable number of weeks
7- Monthly Active Users (MAU) by tags for configurable number of months
8- Summary analytics by tags
10These endpoints use optimized single SQL queries with joins to efficiently calculate
11user metrics from tag activity data and return time series for dashboard visualization.
12"""
14from collections.abc import Mapping, Sequence
15from datetime import datetime, timedelta
16from typing import TYPE_CHECKING, Final, Protocol, TypeVar, overload
18from fastapi import APIRouter, Depends, HTTPException, Query
19from pydantic import BaseModel, TypeAdapter
21if TYPE_CHECKING: 21 ↛ 22line 21 didn't jump to line 22 because the condition on line 21 was never true
22 from prisma.models import LiteLLM_DailyTagSpend as PrismaDailyTagSpendRow
23 from prisma.models import LiteLLM_UserTable as PrismaUserRow
24 from prisma.models import LiteLLM_VerificationToken as PrismaVerificationTokenRow
26 from litellm.proxy.utils import PrismaClient
28from litellm.proxy._types import CommonProxyErrors, UserAPIKeyAuth
29from litellm.proxy.auth.user_api_key_auth import user_api_key_auth
30from litellm.repositories.table_repositories import DailyTagSpendRepository
31from litellm.repositories.user_repository import UserRepository
32from litellm.repositories.verification_token_repository import (
33 VerificationTokenRepository,
34)
36# Constants for analytics periods
37MAX_DAYS: Final = 7 # Number of days to show in DAU analytics
38MAX_WEEKS: Final = 7 # Number of weeks to show in WAU analytics
39MAX_MONTHS: Final = 7 # Number of months to show in MAU analytics
40MAX_TAGS: Final = 250 # Maximum number of distinct tags to return
42router: Final = APIRouter()
45class TagActiveUsersResponse(BaseModel):
46 """Response for tag active users metrics"""
48 tag: str
49 active_users: int
50 date: str # The specific date or period identifier
51 period_start: str | None = None # For WAU/MAU, this will be the start of the period
52 period_end: str | None = None # For WAU/MAU, this will be the end of the period
55class ActiveUsersAnalyticsResponse(BaseModel):
56 """Response for active users analytics"""
58 results: list[TagActiveUsersResponse]
61class TagSummaryMetrics(BaseModel):
62 """Summary metrics for a tag"""
64 tag: str
65 unique_users: int
66 total_requests: int
67 successful_requests: int
68 failed_requests: int
69 total_tokens: int
70 total_spend: float
73class TagSummaryResponse(BaseModel):
74 """Response for tag summary analytics"""
76 results: list[TagSummaryMetrics]
79class DistinctTagResponse(BaseModel):
80 """Response for distinct user agent tags"""
82 tag: str
85class DistinctTagsResponse(BaseModel):
86 """Response for all distinct user agent tags"""
88 results: list[DistinctTagResponse]
91class PerUserMetrics(BaseModel):
92 """Metrics for individual user"""
94 user_id: str
95 user_email: str | None = None
96 user_agent: str | None = None
97 successful_requests: int = 0
98 failed_requests: int = 0
99 total_requests: int = 0
100 total_tokens: int = 0
101 spend: float = 0.0
104class PerUserAnalyticsResponse(BaseModel):
105 """Response for per-user analytics"""
107 results: list[PerUserMetrics]
108 total_count: int
109 page: int
110 page_size: int
111 total_pages: int
114class _DistinctTagRow(BaseModel):
115 tag: str
118class _ActiveUsersRow(BaseModel):
119 tag: str
120 active_users: int
121 date: str
122 period_start: str | None = None
123 period_end: str | None = None
126class _TagSummaryRow(BaseModel):
127 tag: str
128 unique_users: int | None = None
129 total_requests: float | int | str | None = None
130 successful_requests: float | int | str | None = None
131 failed_requests: float | int | str | None = None
132 total_tokens: float | int | str | None = None
133 total_spend: float | int | str | None = None
136_DISTINCT_TAG_ROWS: Final = TypeAdapter(list[_DistinctTagRow])
137_ACTIVE_USERS_ROWS: Final = TypeAdapter(list[_ActiveUsersRow])
138_TAG_SUMMARY_ROWS: Final = TypeAdapter(list[_TagSummaryRow])
140_RowT_co: Final = TypeVar("_RowT_co", covariant=True)
142if TYPE_CHECKING: 142 ↛ 144line 142 didn't jump to line 144 because the condition on line 142 was never true
144 class _TableOps(Protocol[_RowT_co]):
145 async def find_many(self, where: Mapping[str, object] | None = None) -> Sequence[_RowT_co]: ...
148@overload
149def _typed_table(repo: DailyTagSpendRepository) -> "_TableOps[PrismaDailyTagSpendRow]": ... 149 ↛ exitline 149 didn't return from function '_typed_table' because
150@overload
151def _typed_table(repo: VerificationTokenRepository) -> "_TableOps[PrismaVerificationTokenRow]": ... 151 ↛ exitline 151 didn't return from function '_typed_table' because
152@overload
153def _typed_table(repo: UserRepository) -> "_TableOps[PrismaUserRow]": ... 153 ↛ exitline 153 didn't return from function '_typed_table' because
154def _typed_table(repo: DailyTagSpendRepository | VerificationTokenRepository | UserRepository) -> object:
155 return repo.table
158async def _query_raw(prisma_client: "PrismaClient", sql_query: str, *params: object) -> object:
159 return await prisma_client.db.query_raw(sql_query, *params)
162@router.get(
163 "/tag/distinct",
164 response_model=DistinctTagsResponse,
165 tags=["tag management", "user agent analytics"],
166 dependencies=[Depends(user_api_key_auth)],
167)
168async def get_distinct_user_agent_tags(
169 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
170):
171 """
172 Get all distinct user agent tags up to a maximum of {MAX_TAGS} tags.
174 This endpoint returns all unique user agent tags found in the database,
175 sorted by frequency of usage.
177 Returns:
178 DistinctTagsResponse: List of distinct user agent tags
179 """
180 from litellm.proxy.proxy_server import prisma_client
182 if prisma_client is None: 182 ↛ 183line 182 didn't jump to line 183 because the condition on line 182 was never true
183 raise HTTPException(
184 status_code=500,
185 detail={"error": CommonProxyErrors.db_not_connected_error.value},
186 )
188 try:
189 sql_query: Final = f"""
190 SELECT
191 dts.tag,
192 COUNT(*) as usage_count
193 FROM "LiteLLM_DailyTagSpend" dts
194 WHERE dts.tag LIKE 'User-Agent:%' OR dts.tag NOT LIKE '%:%'
195 GROUP BY dts.tag
196 ORDER BY usage_count DESC
197 LIMIT {MAX_TAGS}
198 """
200 db_response: Final = _DISTINCT_TAG_ROWS.validate_python(await _query_raw(prisma_client, sql_query))
202 results: Final = [DistinctTagResponse(tag=row.tag) for row in db_response]
204 return DistinctTagsResponse(results=results)
206 except Exception as e:
207 raise HTTPException(
208 status_code=500,
209 detail=f"Failed to fetch distinct user agent tags: {e}",
210 )
213@router.get(
214 "/tag/dau",
215 response_model=ActiveUsersAnalyticsResponse,
216 tags=["tag management", "user agent analytics"],
217 dependencies=[Depends(user_api_key_auth)],
218)
219async def get_daily_active_users(
220 tag_filter: str | None = Query(
221 default=None,
222 description="Filter by specific tag (optional)",
223 ),
224 tag_filters: list[str] | None = Query(
225 default=None,
226 description="Filter by multiple specific tags (optional, takes precedence over tag_filter)",
227 ),
228 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
229):
230 """
231 Get Daily Active Users (DAU) by tags for the last {MAX_DAYS} days ending on UTC today + 1 day.
233 This endpoint efficiently calculates unique users per tag for each of the last {MAX_DAYS} days
234 using a single optimized SQL query, perfect for dashboard time series visualization.
236 Args:
237 tag_filter: Optional filter to specific tag (legacy)
238 tag_filters: Optional filter to multiple specific tags (takes precedence over tag_filter)
240 Returns:
241 ActiveUsersAnalyticsResponse: DAU data by tag for each of the last {MAX_DAYS} days
242 """
243 from litellm.proxy.proxy_server import prisma_client
245 if prisma_client is None: 245 ↛ 246line 245 didn't jump to line 246 because the condition on line 245 was never true
246 raise HTTPException(
247 status_code=500,
248 detail={"error": CommonProxyErrors.db_not_connected_error.value},
249 )
251 try:
252 # Calculate end_date as UTC today + 1 day
253 from datetime import timezone
255 end_dt = datetime.now(timezone.utc).replace(hour=0, minute=0, second=0, microsecond=0) + timedelta(days=1)
256 end_date: Final = end_dt.strftime("%Y-%m-%d")
258 # Calculate date range (last MAX_DAYS days)
259 start_dt: Final = end_dt - timedelta(days=MAX_DAYS)
260 start_date: Final = start_dt.strftime("%Y-%m-%d")
262 # Build SQL query with optional tag filter(s)
263 where_clause = "WHERE dts.date >= $1 AND dts.date <= $2 AND vt.user_id IS NOT NULL"
264 params: Final = [start_date, end_date]
266 # Handle multiple tag filters (takes precedence over single tag filter)
267 if tag_filters and len(tag_filters) > 0:
268 tag_conditions: Final = []
269 for i, tag in enumerate(tag_filters):
270 param_index = len(params) + 1
271 tag_conditions.append(f"dts.tag = ${param_index}")
272 params.append(tag)
273 where_clause += f" AND ({' OR '.join(tag_conditions)})"
274 elif tag_filter:
275 where_clause += " AND dts.tag ILIKE $3"
276 params.append(f"%{tag_filter}%")
278 sql_query: Final = f"""
279 SELECT
280 dts.tag,
281 dts.date,
282 COUNT(DISTINCT vt.user_id) as active_users
283 FROM "LiteLLM_DailyTagSpend" dts
284 INNER JOIN "LiteLLM_VerificationToken" vt ON dts.api_key = vt.token
285 {where_clause}
286 GROUP BY dts.tag, dts.date
287 ORDER BY dts.date DESC, active_users DESC
288 """
290 db_response: Final = _ACTIVE_USERS_ROWS.validate_python(await _query_raw(prisma_client, sql_query, *params))
292 results: Final = [
293 TagActiveUsersResponse(tag=row.tag, active_users=row.active_users, date=row.date) for row in db_response
294 ]
296 return ActiveUsersAnalyticsResponse(results=results)
298 except Exception as e:
299 raise HTTPException(
300 status_code=500,
301 detail=f"Failed to fetch DAU analytics: {e}",
302 )
305@router.get(
306 "/tag/wau",
307 response_model=ActiveUsersAnalyticsResponse,
308 tags=["tag management", "user agent analytics"],
309 dependencies=[Depends(user_api_key_auth)],
310)
311async def get_weekly_active_users(
312 tag_filter: str | None = Query(
313 default=None,
314 description="Filter by specific tag (optional)",
315 ),
316 tag_filters: list[str] | None = Query(
317 default=None,
318 description="Filter by multiple specific tags (optional, takes precedence over tag_filter)",
319 ),
320 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
321):
322 """
323 Get Weekly Active Users (WAU) by tags for the last {MAX_WEEKS} weeks ending on UTC today + 1 day.
325 Shows week-by-week breakdown:
326 - Week 1 (Jan 1): Earliest week (7 weeks ago)
327 - Week 2 (Jan 8): Next week (6 weeks ago)
328 - Week 3 (Jan 15): Next week (5 weeks ago)
329 - ... and so on for {MAX_WEEKS} weeks total
330 - Week 7: Most recent week ending on UTC today + 1 day
332 Args:
333 tag_filter: Optional filter to specific tag (legacy)
334 tag_filters: Optional filter to multiple specific tags (takes precedence over tag_filter)
336 Returns:
337 ActiveUsersAnalyticsResponse: WAU data by tag for each of the last {MAX_WEEKS} weeks with descriptive week labels (e.g., "Week 1 (Jan 1)")
338 """
339 from litellm.proxy.proxy_server import prisma_client
341 if prisma_client is None: 341 ↛ 342line 341 didn't jump to line 342 because the condition on line 341 was never true
342 raise HTTPException(
343 status_code=500,
344 detail={"error": CommonProxyErrors.db_not_connected_error.value},
345 )
347 try:
348 # Calculate end_date as UTC today + 1 day
349 from datetime import timezone
351 end_dt = datetime.now(timezone.utc).replace(hour=0, minute=0, second=0, microsecond=0) + timedelta(days=1)
352 end_date: Final = end_dt.strftime("%Y-%m-%d")
354 # Calculate date range for all weeks (49 days total)
355 # Start from 48 days before end_date to cover exactly MAX_WEEKS complete weeks
356 start_dt: Final = end_dt - timedelta(days=(MAX_WEEKS * 7 - 1)) # MAX_WEEKS weeks * 7 days - 1
357 start_date: Final = start_dt.strftime("%Y-%m-%d")
359 # Build SQL query with optional tag filter(s)
360 where_clause = "WHERE dts.date >= $1 AND dts.date <= $2 AND vt.user_id IS NOT NULL"
361 params: Final = [start_date, end_date]
363 # Handle multiple tag filters (takes precedence over single tag filter)
364 if tag_filters and len(tag_filters) > 0:
365 tag_conditions: Final = []
366 for i, tag in enumerate(tag_filters):
367 param_index = len(params) + 1
368 tag_conditions.append(f"dts.tag = ${param_index}")
369 params.append(tag)
370 where_clause += f" AND ({' OR '.join(tag_conditions)})"
371 elif tag_filter:
372 where_clause += " AND dts.tag ILIKE $3"
373 params.append(f"%{tag_filter}%")
375 # Use window function to group by weeks with clear week numbering
376 sql_query: Final = f"""
377 WITH weekly_data AS (
378 SELECT
379 dts.tag,
380 dts.date,
381 vt.user_id,
382 -- Calculate week number (0 = Week 1 most recent, 1 = Week 2, etc.)
383 FLOOR((DATE '{end_date}' - dts.date::date) / 7) as week_offset
384 FROM "LiteLLM_DailyTagSpend" dts
385 INNER JOIN "LiteLLM_VerificationToken" vt ON dts.api_key = vt.token
386 {where_clause}
387 )
388 SELECT
389 tag,
390 COUNT(DISTINCT user_id) as active_users,
391 -- Week identifier with month and day (Week 1 (earliest), Week 2, etc.)
392 'Week ' || ({MAX_WEEKS} - week_offset)::text || ' (' ||
393 TO_CHAR(DATE '{end_date}' - (week_offset * 7 || ' days')::interval - '6 days'::interval, 'Mon DD') || ')' as date,
394 -- Calculate week start and end dates for each week
395 (DATE '{end_date}' - (week_offset * 7 || ' days')::interval - '6 days'::interval)::text as period_start,
396 (DATE '{end_date}' - (week_offset * 7 || ' days')::interval)::text as period_end,
397 week_offset
398 FROM weekly_data
399 WHERE week_offset < {MAX_WEEKS}
400 GROUP BY tag, week_offset
401 ORDER BY week_offset DESC, active_users DESC
402 """
404 db_response: Final = _ACTIVE_USERS_ROWS.validate_python(await _query_raw(prisma_client, sql_query, *params))
406 results: Final = [
407 TagActiveUsersResponse(
408 tag=row.tag,
409 active_users=row.active_users,
410 date=row.date, # This will be "Week 1 (Jan 15)", "Week 2 (Jan 8)", etc.
411 period_start=row.period_start,
412 period_end=row.period_end,
413 )
414 for row in db_response
415 ]
417 return ActiveUsersAnalyticsResponse(results=results)
419 except Exception as e:
420 raise HTTPException(
421 status_code=500,
422 detail=f"Failed to fetch WAU analytics: {e}",
423 )
426@router.get(
427 "/tag/mau",
428 response_model=ActiveUsersAnalyticsResponse,
429 tags=["tag management", "user agent analytics"],
430 dependencies=[Depends(user_api_key_auth)],
431)
432async def get_monthly_active_users(
433 tag_filter: str | None = Query(
434 default=None,
435 description="Filter by specific tag (optional)",
436 ),
437 tag_filters: list[str] | None = Query(
438 default=None,
439 description="Filter by multiple specific tags (optional, takes precedence over tag_filter)",
440 ),
441 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
442):
443 """
444 Get Monthly Active Users (MAU) by tags for the last {MAX_MONTHS} months ending on UTC today + 1 day.
446 Shows month-by-month breakdown:
447 - Month 1 (Nov): Earliest month (7 months ago, 30-day period)
448 - Month 2 (Dec): Next month (6 months ago)
449 - Month 3 (Jan): Next month (5 months ago)
450 - ... and so on for {MAX_MONTHS} months total
451 - Month 7: Most recent month ending on UTC today + 1 day
453 Args:
454 tag_filter: Optional filter to specific tag (legacy)
455 tag_filters: Optional filter to multiple specific tags (takes precedence over tag_filter)
457 Returns:
458 ActiveUsersAnalyticsResponse: MAU data by tag for each of the last {MAX_MONTHS} months with descriptive month labels (e.g., "Month 1 (Nov)")
459 """
460 from litellm.proxy.proxy_server import prisma_client
462 if prisma_client is None: 462 ↛ 463line 462 didn't jump to line 463 because the condition on line 462 was never true
463 raise HTTPException(
464 status_code=500,
465 detail={"error": CommonProxyErrors.db_not_connected_error.value},
466 )
468 try:
469 # Calculate end_date as UTC today + 1 day
470 from datetime import timezone
472 end_dt = datetime.now(timezone.utc).replace(hour=0, minute=0, second=0, microsecond=0) + timedelta(days=1)
473 end_date: Final = end_dt.strftime("%Y-%m-%d")
475 # Calculate date range for all months (210 days total)
476 # Start from 209 days before end_date to cover exactly MAX_MONTHS complete months
477 start_dt: Final = end_dt - timedelta(days=(MAX_MONTHS * 30 - 1)) # MAX_MONTHS months * 30 days - 1
478 start_date: Final = start_dt.strftime("%Y-%m-%d")
480 # Build SQL query with optional tag filter(s)
481 where_clause = "WHERE dts.date >= $1 AND dts.date <= $2 AND vt.user_id IS NOT NULL"
482 params: Final = [start_date, end_date]
484 # Handle multiple tag filters (takes precedence over single tag filter)
485 if tag_filters and len(tag_filters) > 0:
486 tag_conditions: Final = []
487 for i, tag in enumerate(tag_filters):
488 param_index = len(params) + 1
489 tag_conditions.append(f"dts.tag = ${param_index}")
490 params.append(tag)
491 where_clause += f" AND ({' OR '.join(tag_conditions)})"
492 elif tag_filter:
493 where_clause += " AND dts.tag ILIKE $3"
494 params.append(f"%{tag_filter}%")
496 # Use window function to group by months (30-day periods) with clear month numbering
497 sql_query: Final = f"""
498 WITH monthly_data AS (
499 SELECT
500 dts.tag,
501 dts.date,
502 vt.user_id,
503 -- Calculate month number (0 = Month 1 most recent, 1 = Month 2, etc.)
504 FLOOR((DATE '{end_date}' - dts.date::date) / 30) as month_offset
505 FROM "LiteLLM_DailyTagSpend" dts
506 INNER JOIN "LiteLLM_VerificationToken" vt ON dts.api_key = vt.token
507 {where_clause}
508 )
509 SELECT
510 tag,
511 COUNT(DISTINCT user_id) as active_users,
512 -- Month identifier with month name (Month 1 (earliest), Month 2, etc.)
513 'Month ' || ({MAX_MONTHS} - month_offset)::text || ' (' ||
514 TO_CHAR(DATE '{end_date}' - (month_offset * 30 || ' days')::interval - '29 days'::interval, 'Mon') || ')' as date,
515 -- Calculate month start and end dates for each month
516 (DATE '{end_date}' - (month_offset * 30 || ' days')::interval - '29 days'::interval)::text as period_start,
517 (DATE '{end_date}' - (month_offset * 30 || ' days')::interval)::text as period_end,
518 month_offset
519 FROM monthly_data
520 WHERE month_offset < {MAX_MONTHS}
521 GROUP BY tag, month_offset
522 ORDER BY month_offset DESC, active_users DESC
523 """
525 db_response: Final = _ACTIVE_USERS_ROWS.validate_python(await _query_raw(prisma_client, sql_query, *params))
527 results: Final = [
528 TagActiveUsersResponse(
529 tag=row.tag,
530 active_users=row.active_users,
531 date=row.date, # This will be "Month 1 (Jan)", "Month 2 (Dec)", etc.
532 period_start=row.period_start,
533 period_end=row.period_end,
534 )
535 for row in db_response
536 ]
538 return ActiveUsersAnalyticsResponse(results=results)
540 except Exception as e:
541 raise HTTPException(
542 status_code=500,
543 detail=f"Failed to fetch MAU analytics: {e}",
544 )
547@router.get(
548 "/tag/summary",
549 response_model=TagSummaryResponse,
550 tags=["tag management", "user agent analytics"],
551 dependencies=[Depends(user_api_key_auth)],
552)
553async def get_tag_summary(
554 start_date: str = Query(description="Start date in YYYY-MM-DD format"),
555 end_date: str = Query(description="End date in YYYY-MM-DD format"),
556 tag_filter: str | None = Query(
557 default=None,
558 description="Filter by specific tag (optional)",
559 ),
560 tag_filters: list[str] | None = Query(
561 default=None,
562 description="Filter by multiple specific tags (optional, takes precedence over tag_filter)",
563 ),
564 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
565):
566 """
567 Get summary analytics for tags including unique users, requests, tokens, and spend.
569 Args:
570 start_date: Start date for the analytics period (YYYY-MM-DD)
571 end_date: End date for the analytics period (YYYY-MM-DD)
572 tag_filter: Optional filter to specific tag (legacy)
573 tag_filters: Optional filter to multiple specific tags (takes precedence over tag_filter)
575 Returns:
576 TagSummaryResponse: Summary analytics data by tag
577 """
578 from litellm.proxy.proxy_server import prisma_client
580 if prisma_client is None: 580 ↛ 581line 580 didn't jump to line 581 because the condition on line 580 was never true
581 raise HTTPException(
582 status_code=500,
583 detail={"error": CommonProxyErrors.db_not_connected_error.value},
584 )
586 try:
587 # Validate date format
588 datetime.strptime(start_date, "%Y-%m-%d")
589 datetime.strptime(end_date, "%Y-%m-%d")
591 # Build SQL query with optional tag filter(s)
592 where_clause = "WHERE dts.date >= $1 AND dts.date <= $2"
593 params: Final = [start_date, end_date]
595 # Handle multiple tag filters (takes precedence over single tag filter)
596 if tag_filters and len(tag_filters) > 0:
597 tag_conditions: Final = []
598 for i, tag in enumerate(tag_filters):
599 param_index = len(params) + 1
600 tag_conditions.append(f"dts.tag = ${param_index}")
601 params.append(tag)
602 where_clause += f" AND ({' OR '.join(tag_conditions)})"
603 elif tag_filter:
604 where_clause += " AND dts.tag ILIKE $3"
605 params.append(f"%{tag_filter}%")
607 sql_query: Final = f"""
608 SELECT
609 dts.tag,
610 COUNT(DISTINCT vt.user_id) as unique_users,
611 SUM(dts.api_requests) as total_requests,
612 SUM(dts.successful_requests) as successful_requests,
613 SUM(dts.failed_requests) as failed_requests,
614 SUM(dts.prompt_tokens + dts.completion_tokens) as total_tokens,
615 SUM(dts.spend) as total_spend
616 FROM "LiteLLM_DailyTagSpend" dts
617 LEFT JOIN "LiteLLM_VerificationToken" vt ON dts.api_key = vt.token
618 {where_clause}
619 GROUP BY dts.tag
620 ORDER BY total_requests DESC
621 """
623 db_response: Final = _TAG_SUMMARY_ROWS.validate_python(await _query_raw(prisma_client, sql_query, *params))
625 results: Final = [
626 TagSummaryMetrics(
627 tag=row.tag,
628 unique_users=row.unique_users or 0,
629 total_requests=int(row.total_requests or 0),
630 successful_requests=int(row.successful_requests or 0),
631 failed_requests=int(row.failed_requests or 0),
632 total_tokens=int(row.total_tokens or 0),
633 total_spend=float(row.total_spend or 0.0),
634 )
635 for row in db_response
636 ]
638 return TagSummaryResponse(results=results)
640 except ValueError as e:
641 raise HTTPException(
642 status_code=400,
643 detail=f"Invalid date format. Use YYYY-MM-DD: {e}",
644 )
645 except Exception as e:
646 raise HTTPException(
647 status_code=500,
648 detail=f"Failed to fetch tag summary analytics: {e}",
649 )
652@router.get(
653 "/tag/user-agent/per-user-analytics",
654 response_model=PerUserAnalyticsResponse,
655 tags=["tag management", "user agent analytics"],
656 dependencies=[Depends(user_api_key_auth)],
657)
658async def get_per_user_analytics(
659 tag_filter: str | None = Query(
660 default=None,
661 description="Filter by specific tag (optional)",
662 ),
663 tag_filters: list[str] | None = Query(
664 default=None,
665 description="Filter by multiple specific tags (optional, takes precedence over tag_filter)",
666 ),
667 page: int = Query(default=1, description="Page number for pagination", ge=1),
668 page_size: int = Query(default=50, description="Items per page", ge=1, le=1000),
669 user_api_key_dict: UserAPIKeyAuth = Depends(user_api_key_auth),
670):
671 """
672 Get per-user analytics including successful requests, tokens, and spend by individual users.
674 This endpoint provides usage metrics broken down by individual users based on their
675 tag activity during the last 30 days ending on UTC today + 1 day.
677 Args:
678 tag_filter: Optional filter to specific tag (legacy)
679 tag_filters: Optional filter to multiple specific tags (takes precedence over tag_filter)
680 page: Page number for pagination
681 page_size: Number of items per page
683 Returns:
684 PerUserAnalyticsResponse: Analytics data broken down by individual users for the last 30 days
685 """
686 from litellm.proxy.proxy_server import prisma_client
688 if prisma_client is None: 688 ↛ 689line 688 didn't jump to line 689 because the condition on line 688 was never true
689 raise HTTPException(
690 status_code=500,
691 detail={"error": CommonProxyErrors.db_not_connected_error.value},
692 )
694 try:
695 # Calculate end_date as UTC today + 1 day
696 from datetime import timezone
698 end_dt = datetime.now(timezone.utc).replace(hour=0, minute=0, second=0, microsecond=0) + timedelta(days=1)
699 end_date: Final = end_dt.strftime("%Y-%m-%d")
701 # Calculate date range (last 30 days)
702 start_dt: Final = end_dt - timedelta(days=30)
703 start_date: Final = start_dt.strftime("%Y-%m-%d")
705 # Build where clause with date range
706 where_clause: Final[dict[str, object]] = {"date": {"gte": start_date, "lte": end_date}}
708 # Add tag filtering if provided
709 if tag_filters and len(tag_filters) > 0:
710 where_clause["tag"] = {"in": tag_filters}
711 elif tag_filter:
712 where_clause["tag"] = {"contains": tag_filter}
714 # Get all tag records in the date range with optional tag filtering
715 tag_records: Final = await _typed_table(DailyTagSpendRepository(prisma_client)).find_many(where=where_clause)
717 # Get unique api_keys
718 api_keys: Final = set(record.api_key for record in tag_records if record.api_key)
720 if not api_keys:
721 return PerUserAnalyticsResponse(
722 results=[],
723 total_count=0,
724 page=page,
725 page_size=page_size,
726 total_pages=0,
727 )
729 # Lookup user_id for each api_key
730 api_key_records: Final = await _typed_table(VerificationTokenRepository(prisma_client)).find_many(
731 where={"token": {"in": list(api_keys)}}
732 )
734 # Create mapping from api_key to user_id
735 api_key_to_user_id: Final = {record.token: record.user_id for record in api_key_records if record.user_id}
737 # Get user emails for the user_ids
738 user_ids: Final = list(set(api_key_to_user_id.values()))
739 user_records: Final = await _typed_table(UserRepository(prisma_client)).find_many(
740 where={"user_id": {"in": user_ids}}
741 )
743 # Create mapping from user_id to user_email
744 user_id_to_email: Final = {record.user_id: record.user_email for record in user_records}
746 # Aggregate metrics by user
747 user_metrics: Final[dict[str, PerUserMetrics]] = {}
749 for record in tag_records:
750 if record.api_key in api_key_to_user_id: 750 ↛ 751line 750 didn't jump to line 751 because the condition on line 750 was never true
751 user_id = api_key_to_user_id[record.api_key]
752 tag = record.tag # Use the full tag as user_agent
754 if user_id not in user_metrics:
755 user_metrics[user_id] = PerUserMetrics(
756 user_id=user_id,
757 user_email=user_id_to_email.get(user_id),
758 user_agent=tag,
759 )
760 else:
761 # If tag is different, keep the first one or prioritize certain ones
762 if tag and not user_metrics[user_id].user_agent:
763 user_metrics[user_id].user_agent = tag
765 # Aggregate metrics
766 user_metrics[user_id].successful_requests += record.successful_requests or 0
767 user_metrics[user_id].failed_requests += record.failed_requests or 0
768 user_metrics[user_id].total_requests += record.api_requests or 0
769 # Calculate total_tokens from prompt_tokens + completion_tokens
770 prompt_tokens = record.prompt_tokens or 0
771 completion_tokens = record.completion_tokens or 0
772 user_metrics[user_id].total_tokens += int(prompt_tokens + completion_tokens)
773 user_metrics[user_id].spend += record.spend or 0.0
775 # Convert to list and sort by successful requests (descending)
776 results: Final = sorted(
777 list(user_metrics.values()),
778 key=lambda x: x.successful_requests,
779 reverse=True,
780 )
782 # Apply pagination
783 total_count: Final = len(results)
784 total_pages: Final = (total_count + page_size - 1) // page_size
785 start_idx: Final = (page - 1) * page_size
786 end_idx: Final = start_idx + page_size
787 paginated_results: Final = results[start_idx:end_idx]
789 return PerUserAnalyticsResponse(
790 results=paginated_results,
791 total_count=total_count,
792 page=page,
793 page_size=page_size,
794 total_pages=total_pages,
795 )
797 except Exception as e:
798 raise HTTPException(
799 status_code=500,
800 detail=f"Failed to fetch per-user analytics: {e}",
801 )