Usage Data Sample Queries

 

Periscope usage data can be used to analyze the SQL queries on a Periscope site, measure runtimes, and audit user activity. Example queries that are ready for use on the usage data dataset can be found below.

Note: These sample queries are written to be compatible with Periscope sites that are set up with a Periscope Redshift warehouse. Sample queries for Periscope sites not set up with the cache can be found here.

Search SQL

The "Search SQL" query can be used to search Periscope items - charts, views, SQL snippets, SQL alerts, and filters - that contain a specified string. In the example below, the query is searching each element for the string "us_states". This can be replaced with any string that may appear in a SQL query such as the name of a database table.

select
 'chart' as item
 , name
from
 periscope_usage_data.charts
where
 sql like '%us_states%'
union all
select
 'view' as item
 , name
from
 periscope_usage_data.sql_views
where
 sql like '%us_states%'
union all
select
 'snippet' as item
 , name
from
 periscope_usage_data.sql_snippets
where
 sql like '%us_states%'
union all
select
 'alert' as item
 , name
from
 periscope_usage_data.sql_alerts
where
 sql like '%us_states%'
union all
select
 'filter' as item
 , name
from
 periscope_usage_data.filters
where
 sql like '%us_states%'

Most Popular Dashboards

The "Most Popular Dashboards" query measures how often and for how long a dashboard has been viewed, as well as the number of refreshes and the total runtime of all charts on the dashboard.

The first CTE, refreshes, finds the number of refreshes per dashboard as well as the total runtime of all charts. The second CTE, views, counts the number of distinct users viewing the dashboard, as well as the total amount of time spent on the dashboard by users. The final query returns the dashboard name, dashboard id, distinct user count, total viewed time in second, and total dashboard runtime in seconds for each dashboard.

with
 refreshes as (
   select
     dashboard_id
    , count(1) as queries
     , sum(runtime_ms) * 1.0 / 1000 as total_runtime
   from
     periscope_usage_data.query_logs
     join periscope_usage_data.charts on
       query_logs.item_id = charts.id
   group by
     1
 )
 , views as (
   select
     dashboard_id
     , sum(seconds) as view_time
     , count(distinct user_id) as view_users
     , listagg(users.first_name || ' ' || users.last_name || ' ')
   from
     periscope_usage_data.time_on_site_logs
     join periscope_usage_data.users on
       time_on_site_logs.user_id = users.id
   where
     focused_tab = true
   group by
     1
 )
select
 dashboards.name
 , dashboards.id
 , views.view_users as users
 , views.view_time as view_time_s
 , refreshes.queries as queries_run
 , refreshes.total_runtime as total_query_time
from
 periscope_usage_data.dashboards
 left join refreshes on
   dashboards.id = refreshes.dashboard_id
 left join views on
   dashboards.id = views.dashboard_id
order by
 view_time_s desc

Top Users

For each user on a Periscope site, the "Top Users" query returns the name, email address, total minutes on site, number of charts created,

with
  view_usage as (
   select
    user_id
    , dashboard_id
    , sum(seconds) as seconds
    , (sum(sum(seconds)) over(partition by user_id)) * 1.0 / 60 as minutes_on_site
    , max(sum(seconds)) over(partition by user_id) as most_used_dash_time
   from
    periscope_usage_data.time_on_site_logs
   where
    focused_tab = true
    and [created_at=7days]
   group by
    1
    , 2
  )
  , query_usage as (
   select
    user_id
    , count(1) as queries_run
    , sum(runtime_ms) * 1.0 / 1000 as query_time_s
   from
    periscope_usage_data.query_logs
   where
    [created_at=7days]
   group by
    1
   )
   , chart_usage as (
   select
    created_by
    , count(1) as charts_created
   from
    periscope_usage_data.charts
   where
    [created_at=7days]
   group by
    1
   )
select
 users.first_name || ' ' || users.last_name
 , users.email_address
 , dashboards.name
 , view_usage.minutes_on_site
 , coalesce(chart_usage.charts_created, 0) as charts_created
 , coalesce(query_usage.queries_run, 0) as queries_run
 , coalesce(query_usage.query_time_s, 0) as query_time_s
 , dashboards.name || '( ' || seconds / 60.0 / minutes_on_site * 100 || '%)' as favorite_dashboard
from
 periscope_usage_data.users
 join view_usage on
   users.id = view_usage.user_id
 left join query_usage on
   users.id = query_usage.user_id
 join periscope_usage_data.dashboards on
   view_usage.dashboard_id = dashboards.id
 left join chart_usage on
   users.id = chart_usage.created_by
where
 view_usage.seconds = view_usage.most_used_dash_time
order by
 minutes_on_site desc

Recently Created Charts

The "Recently Created Charts" query returns the dashboard name, dashboard id, chart title, chart created timestamp, and the name of the last user to edit the chart for every chart created in the last seven days. The date range can be adjusted by modifying the where clause condition applied to the created_at field.

select
 dashboards.name as dashboard_name
 , dashboard_id
 , charts.name as chart_title
 , charts.created_at as created_at
 , users.first_name || ' ' || users.last_name as chart_creator
from
 periscope_usage_data.charts
 join periscope_usage_data.dashboards on
  charts.dashboard_id = dashboards.id
 join periscope_usage_data.users on
  charts.created_by = users.id
where
 charts.deleted_at is null
 and [charts.created_at=7days]
order by
 charts.created_at desc

Query Runtimes

The "Query Runtimes" query measures the average, min, max, and sum of all query runtimes for each chart, as well as the number of times that query has been executed in the last seven days. The date range can be adjusted by modifying the where clause condition applied to the created_at field.

select
 dashboards.name as dashboard_name
 , dashboards.id as dashboard_id
 , charts.name as chart_name
 , charts.id as chart_id
 , avg(query_logs.runtime_ms * 1.0) * 1.0 / 1000 as avg_runtime_s
 , min(query_logs.runtime_ms * 1.0) * 1.0 / 1000 as min_runtime_s
 , max(query_logs.runtime_ms * 1.0) * 1.0 / 1000 as max_runtime_s
 , count(1) as run_count
 , sum(query_logs.runtime_ms) * 1.0 / 1000 as total_runtime_s
from
 periscope_usage_data.dashboards
 join periscope_usage_data.charts on
  dashboards.id = charts.dashboard_id
 join periscope_usage_data.query_logs on
  query_logs.item_id = charts.id
where
 [query_logs.created_at=7days]
 and dashboards.deleted_at is null
 and charts.deleted_at is null
group by
 1
 , 2
 , 3
 , 4
order by
 total_runtime_s desc
 , avg_runtime_s desc

Users Who Can Edit

The "Users Who Can Edit" query returns all users on a Periscope site that have edit access. This can be adjusted to show all users without edit access by adjusting the WHERE clause condition applied to the groups.can_edit field to equal "false".

select
 users.first_name || ' ' || users.last_name
 , listagg(groups.name || ', ') as groups_that_can_edit
from
 periscope_usage_data.users
 join periscope_usage_data.user_group_memberships on
  users.id = user_group_memberships.user_id
 join periscope_usage_data.groups on
  user_group_memberships.group_id = groups.id
where
 groups.deleted_at is null
 and user_group_memberships.deleted_at is null
 and groups.can_edit = true
group by
 1

Dashboard Preferences

The “Dashboard Permissions” query can be used to check users’ permissions/access across all dashboards on a RBAC enabled site.

The first CTE returns a list of all the dashboards across a site’s spaces. The second CTE returns all the permissions and whether they have been granted for specific roles on dashboards. The final CTE returns the user ids associated with the roles as mentioned above. The final query returns the user’s name, the space, the dashboard, the role, whether the permission has been granted, and the name of the permission.

with
spaces_dashboards as (
   select
     s.name as space
     , d.name as dashboard
     , d.id
   from
     periscope_usage_data.spaces s
     join periscope_usage_data.dashboards d on
       s.id = d.space_id
   where
     d.name is not null
     and d.archived_at is null
 )
 , permission as (
   select
     sd.space
     , sd.dashboard
     , rp.is_granted
     , rp.role_id
     , rp.permission_name
   from
     spaces_dashboards sd
     left join periscope_usage_data.role_object_permissions rp on
       rp.permissioned_object_id = sd.id
 )
 , user_id as (
   select
     u.user_id
     , r.name as role
     , p.space
     , p.dashboard
     , p.is_granted
     , p.permission_name
   from
     permission p
     left join user_roles u on
       p.role_id = u.role_id
     left join roles r on
       r.id = p.role_id
   where
     u.id is not null
 )
select
 (
   users.first_name || ' ' || users.last_name
 )
 as user_name
 , u.space
 , u.dashboard
 , u.role
 , u.is_granted
 , u.permission_name
from
 user_id u
 left join periscope_usage_data.users on
   u.user_id = users.id
where
 user_name is not null
group by
 1 , 2, 3, 4, 5, 6
order by
 1, 2, 3, 4, 5, 6

Able to Create SQL Charts on Dashboards

This “Able to Create SQL Charts on Dashboards” query specifically lists the users, their roles, and whether they have the ability to create SQL charts on specific dashboards for RBAC enabled sites. This query is a modified version of the “Dashboard Permissions” query.

with
 spaces_dashboards as (
   select
     s.name as space
     , d.name as dashboard
     , d.id
   from
     periscope_usage_data.spaces s
     join periscope_usage_data.dashboards d on
       s.id = d.space_id
   where
     d.name is not null
     and d.archived_at is null
 )
 , permission as (
   select
     sd.space
     , sd.dashboard
     , rp.is_granted
     , rp.role_id
     , rp.permission_name
   from
     spaces_dashboards sd
     left join periscope_usage_data.role_object_permissions rp on
       rp.permissioned_object_id = sd.id
   where
     rp.is_granted = 't'
     and rp.permission_name = 'create_sql_charts'
 )
 , user_id as (
   select
     u.user_id
     , p.space
     , p.dashboard
   from
     permission p
     left join user_roles u on
       p.role_id = u.role_id
     left join roles r on
       r.id = p.role_id
   where
     u.id is not null
 )
select
 (
   users.first_name || ' ' || users.last_name
 )
 as user_name
 , u.space
 , u.dashboard
from
 user_id u
 left join periscope_usage_data.users on
   u.user_id = users.id
where
 user_name is not null
 -- and user_name like ''
group by
 1, 2, 3
order by
 1, 2, 3


Creator vs. Explorer

This “Creator vs. Explorer” query lists all of the users, and whether the user is a creator or an explorer on RBAC enabled sites.

with
 user_permissions as (
   select
     (
       users.first_name || ' ' || users.last_name
     )
     as user_name
     , (
       case
         when r.is_granted = 't'
         and r.permission_name = 'create_sql_charts'
           then 1
         when r.is_granted = 'f'
         and r.permission_name = 'create_sql_charts'
           then 0
       end
     )
     as role
   from
     periscope_usage_data.role_privileges r
     left join periscope_usage_data.user_roles u on
       r.role_id = u.role_id
     left join periscope_usage_data.users on
       u.user_id = users.id
   where
     user_name is not null
     and role is not null
   order by
     1
 )
select
 user_name
 , (
   case
     when sum(role) > 0
       then 'Creator'
     when sum(role) = 0
       then 'Explorer'
   end
 )
 as Creator_Explorer
from
 user_permissions
group by
 1
order by
 2 , 1


Role Permissions in Spaces

The “Role Permissions in Spaces” query lists all of the roles in a space, and the permissions that have been granted to that role on RBAC enabled sites. This query can be adjusted to list the permissions that haven’t been granted to the role by adjusting the where clause applied to the rp.is_granted to equal “f.”

with
 spaces_dashboards as (
   select
     s.name as space
     , d.name as dashboard
     , d.id
   from
     periscope_usage_data.spaces s
     join periscope_usage_data.dashboards d on
       s.id = d.space_id
   where
     d.name is not null
     and d.archived_at is null
 )
 , permission as (
   select
     sd.space
     , rp.role_id
     , rp.permission_name
   from
     spaces_dashboards sd
     left join periscope_usage_data.role_object_permissions rp on
       rp.permissioned_object_id = sd.id
   where
     rp.is_granted = 't'
 )
select
 p.space
 , r.name as role_name
 , p.permission_name
from
 permission p
 left join roles r on
   r.id = p.role_id
where
 role_name is not null
group by
 1 , 2, 3
order by
 1 , 2, 3

Our support team is ready to help