Logflare | Cloudflare, Vercel & Elixir Logging

~/ endpoints /get-grouped-stats-events-logs

id: 8fd936a6-d128-42b5-97fc-c3cc257e7ae3

BigQuery SQL


SELECT
        DATE_TRUNC('day', timestamp) AS group_time,
        m.event_id,
        m.event_name,
        m.city,
        m.country,
        m.device,
        m.language,
        m.referrer,
        m.event_type,
        m.utm_params,
        COUNT(*) AS total_hits
    FROM
        "stats.events.logs"
        CROSS JOIN UNNEST(metadata) AS m
    WHERE
        (@level IS NULL OR m.level = @level)
        AND (@search_term IS NULL OR event_message ILIKE '%' || @search_term || '%')
        AND (@user_id IS NULL OR m.user_id = @user_id)
        AND (@profile_id IS NULL OR m.profile_id = @profile_id)
        AND (@view_id IS NULL OR m.view_id = @view_id)
        AND (
            @event_type IS NULL OR
            (@event_type = 'view' AND @include_unique = 'true' AND m.event_type IN ('view', 'unique_view')) OR
            (@event_type = 'view' AND (@include_unique IS NULL OR @include_unique != 'true') AND m.event_type = 'view') OR
            (@event_type = 'click' AND @include_unique = 'true' AND m.event_type IN ('click', 'unique_click')) OR
            (@event_type = 'click' AND (@include_unique IS NULL OR @include_unique != 'true') AND m.event_type = 'click') OR
            (@event_type NOT IN ('view', 'click') AND m.event_type = @event_type)
        )
        AND (@event_target IS NULL OR m.event_target = @event_target)
        AND (@event_ids IS NULL OR m.event_id = ANY(string_to_array(@event_ids, ',')))
        AND (@event_name IS NULL OR m.event_name = @event_name)
        AND (@referrer IS NULL OR m.referrer = @referrer)
        AND (@city IS NULL OR m.city = @city)
        AND (@country IS NULL OR m.country = @country)
        AND (@device IS NULL OR m.device = @device)
        AND (@language IS NULL OR m.language = @language)
        AND (@utm_params IS NULL OR m.utm_params = @utm_params)
        AND (@start_time IS NULL OR timestamp >= CAST(@start_time AS timestamp))
        AND (@end_time IS NULL OR timestamp <= CAST(@end_time AS timestamp))
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
    ORDER BY 1 ASC, 2 ASC

max rows: 1000
caching: 1000 seconds
cache warming: 0 seconds
query sandboxing: disabled


SELECT DATE_TRUNC('day', timestamp) AS group_time, m.event_id, m.event_name, m.city, m.country, m.device, m.language, m.referrer, m.event_type, m.utm_params, COUNT(*) AS total_hits FROM "stats.events.logs" CROSS JOIN UNNEST(metadata) AS m WHERE (@level IS NULL OR m.level = @level) AND (@search_term IS NULL OR event_message ILIKE '%' || @search_term || '%') AND (@user_id IS NULL OR m.user_id = @user_id) AND (@profile_id IS NULL OR m.profile_id = @profile_id) AND (@view_id IS NULL OR m.view_id = @view_id) AND (@event_type IS NULL OR (@event_type = 'view' AND @include_unique = 'true' AND m.event_type IN ('view', 'unique_view')) OR (@event_type = 'view' AND (@include_unique IS NULL OR @include_unique <> 'true') AND m.event_type = 'view') OR (@event_type = 'click' AND @include_unique = 'true' AND m.event_type IN ('click', 'unique_click')) OR (@event_type = 'click' AND (@include_unique IS NULL OR @include_unique <> 'true') AND m.event_type = 'click') OR (@event_type NOT IN ('view', 'click') AND m.event_type = @event_type)) AND (@event_target IS NULL OR m.event_target = @event_target) AND (@event_ids IS NULL OR m.event_id = ANY(string_to_array(@event_ids, ','))) AND (@event_name IS NULL OR m.event_name = @event_name) AND (@referrer IS NULL OR m.referrer = @referrer) AND (@city IS NULL OR m.city = @city) AND (@country IS NULL OR m.country = @country) AND (@device IS NULL OR m.device = @device) AND (@language IS NULL OR m.language = @language) AND (@utm_params IS NULL OR m.utm_params = @utm_params) AND (@start_time IS NULL OR timestamp >= CAST(@start_time AS TIMESTAMP)) AND (@end_time IS NULL OR timestamp <= CAST(@end_time AS TIMESTAMP)) GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ORDER BY 1 ASC, 2 ASC

Call your endpoint


      # By UUID
      curl "https://api.logflare.app/api/endpoints/query/8fd936a6-d128-42b5-97fc-c3cc257e7ae3" \
      -H 'X-API-KEY: YOUR-ACCESS-TOKEN' \
      -H 'Content-Type: application/json; charset=utf-8' \
      -G -d "end_time=VALUE" -d "start_time=VALUE" -d "utm_params=VALUE" -d "language=VALUE" -d "device=VALUE" -d "country=VALUE" -d "city=VALUE" -d "referrer=VALUE" -d "event_name=VALUE" -d "event_ids=VALUE" -d "event_target=VALUE" -d "include_unique=VALUE" -d "event_type=VALUE" -d "view_id=VALUE" -d "profile_id=VALUE" -d "user_id=VALUE" -d "search_term=VALUE" -d "level=VALUE"


      # By name
      curl "https://api.logflare.app/api/endpoints/query/get-grouped-stats-events-logs" \
      -H 'X-API-KEY: YOUR-ACCESS-TOKEN' \
      -H 'Content-Type: application/json; charset=utf-8' \
      -G -d "end_time=VALUE" -d "start_time=VALUE" -d "utm_params=VALUE" -d "language=VALUE" -d "device=VALUE" -d "country=VALUE" -d "city=VALUE" -d "referrer=VALUE" -d "event_name=VALUE" -d "event_ids=VALUE" -d "event_target=VALUE" -d "include_unique=VALUE" -d "event_type=VALUE" -d "view_id=VALUE" -d "profile_id=VALUE" -d "user_id=VALUE" -d "search_term=VALUE" -d "level=VALUE"