Usage Data Sample Queries
Sisense for Cloud Data Teams usage data can be used to analyze the SQL queries on the 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 sites that are set up with a Sisense Redshift warehouse. Sample queries for sites not set up with the cache can be found here.
Search SQL
The "Search SQL" query can be used to search Sisense 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 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 site that have edit access. The query differs depending on whether the site has RBAC enabled or legacy group permissions.
Legacy Group Permissions
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
RBAC
select
users.first_name || ' ' || users.last_name as user_name
, listagg(roles.name, ',') as role_name
from
periscope_usage_data.user_roles
left join periscope_usage_data.users on
users.id = user_roles.user_id
left join periscope_usage_data.roles on
roles.id = user_roles.role_id
left join periscope_usage_data.role_privileges on
roles.id = role_privileges.role_id
where
((permission_name = 'create_sql_charts' and role_privileges.is_granted = true)
or roles.name = 'Admin')
and roles.space_id = /*(get space id from spaces table)*/
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
