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

List of deactivated users

  • Find the list of deactivated users in the platform.

    Sample query

    SELECT * 
FROM agents
WHERE allowedroles LIKE '%deactivated%';

  • Find the list of users that are not deactivated.

    Sample query

    SELECT * 
FROM agents
WHERE allowedroles NOT LIKE '%deactivated%';

In this section: