Skip to main content

Sample queries

This section lists some sample queries you can run against the metrics to gain meaningful insights.

Unique visitors by week

  • The number of unique visitors that accessed the application. The results are grouped by week, for the past year.

    Note

    How are unique visitors counted?

    Say you have Bob, Jane, and Sarah in your organization and in the past week, Bob was active for 2 days, Jane was active for 3 days, Sarah was not active, the count of unique visitors for the week is 2 as only 2 unique visitors were active for the week.

    Sample query

    SELECT *
    FROM visits_unique_visitors_weekly
    WHERE week >= date_add(now()::date, -1, 'year')
    

Active user days by week

  • Find the number of active user days by week, for the past year. An active user day is counted when a user has had 11 or more interactions with the application in one calendar day. active user day.

    Note

    How are active user days calculated? Say you have Bob, Jane, and Sarah in your organization and Bob logged in for 5 days and had only 4 interactions each day with the application, where are, Jane and Sarah logged in for 5 days and had more than 11 interactions each day, the active user days for that week will be 10.

    Sample query

    SELECT DATE_TRUNC(date, 'week') AS week,
           SUM(user_days) AS weekly_user_days
    FROM visits_adoption_daily
    WHERE date >= DATE_ADD(NOW()::DATE, -1, 'year')
    GROUP BY week
    

Authorization requests by resource

  • Find out the number of times requests are made to access a resource.

    Sample query

    SELECT resource, 
           resourcetype,  
           COUNT(*) AS n_requests
    FROM events_authorization_requests
    GROUP BY resource, resourcetype
    ORDER BY n_requests DESC
    

Authorization requests by user

  • Find out the number of times an agent requested access to resources in the organization.

    Sample query

    SELECT requester, 
           COUNT(*) AS n_requests
    FROM events_authorization_requests
    GROUP BY requester
    ORDER BY n_requests DESC
    

Live catalog (metadata) assets

  • Find the total count of actively available catalog metadata assets per asset type.

    Sample query

    SELECT asset, 
           SUM(n_created) AS n_metadata_resources
    FROM resources_live_metadata_assets_created_by_day
    GROUP BY asset
    ORDER BY n_metadata_resources DESC