Set Up A Preemptive Auto Archive Alert

[intapiuser]

With Auto Archive, unused dashboards, views, and uploaded CSVs will automatically be archived after a number of days defined in site settings by an administrator. To make sure nothing gets accidentally archived by this, we can set up an Alert that will warn a day in advance of all the content that will be archived the next day if not used!

First, let's create a new Dashboard with a title like "Archivable Content".

In that dashboard, let's just create one large table chart with the following query:

-- --yes_cache
select
  coalesce(nullif(split_part(spaces.name, ':', 2),''), spaces.name) as Space
  , 'Dashboard'
  , '[' || coalesce(dashboards.name, 'Untitled Dashboard')  || '](' || url || ')' as Dashboard
  , coalesce(first_name || ' ' || last_name, 'Periscope') as creator
  , coalesce([last_used_at:date], '2018-09-04') as last_used_on
  , DATEDIFF(day, coalesce([last_used_at:date], '2018-09-04'), sysdate) as days_since_last_used
from
  periscope_usage_data.dashboards
left join periscope_usage_data.users
on dashboards.created_by = periscope_usage_data.users.id
left join periscope_usage_data.spaces
on dashboards.space_id = periscope_usage_data.spaces.id
left join urls
on dashboards.id = urls.item_id
where
  (
    dashboards.deleted_at < dashboards.last_used_at
    or dashboards.deleted_at is null
  )
  and (
    dashboards.archived_at < dashboards.last_used_at
    or dashboards.archived_at is null
  )

union all

select
  coalesce(nullif(split_part(spaces.name, ':', 2),''), spaces.name) as Space
  , 'CSV'
  , coalesce(csvs.name, 'Untitled Dashboard') as View
  , coalesce(first_name || ' ' || last_name, 'Periscope') as creator
  , coalesce([last_used_at:date], '2018-09-04') as last_used_on
  , DATEDIFF(day, coalesce([last_used_at:date], '2018-09-04'), sysdate) as days_since_last_used
from
  periscope_usage_data.csvs
left join periscope_usage_data.users
on csvs.owner_id = periscope_usage_data.users.id
left join periscope_usage_data.spaces
on csvs.space_id = periscope_usage_data.spaces.id
left join urls
on csvs.id = urls.item_id
where
  (
    csvs.deleted_at < csvs.last_used_at
    or csvs.deleted_at is null
  )
  and (
    csvs.archived_at < csvs.last_used_at
    or csvs.archived_at is null
  )

union all

select
  coalesce(nullif(split_part(spaces.name, ':', 2),''), spaces.name) as Space
  , 'View'
  , coalesce(sql_views.name, 'Untitled Dashboard') as View
  , coalesce(first_name || ' ' || last_name, 'Periscope') as creator
  , coalesce([last_used_at:date], '2018-09-04') as last_used_on
  , DATEDIFF(day, coalesce([last_used_at:date], '2018-09-04'), sysdate) as days_since_last_used
from
  periscope_usage_data.sql_views
left join periscope_usage_data.users
on sql_views.owner_id = periscope_usage_data.users.id
left join periscope_usage_data.spaces
on sql_views.space_id = periscope_usage_data.spaces.id
left join urls
on sql_views.id = urls.item_id
where
  (
    sql_views.deleted_at < sql_views.last_used_at
    or sql_views.deleted_at is null
  )
  and (
    sql_views.archived_at < sql_views.last_used_at
    or sql_views.archived_at is null
  )
order by days_since_last_used desc

This will show all the dashboards, views, and csvs that can be archived, but haven't yet. It should look something like this:

With that done, it's very easy to set up an alert based on that query. For example, if your site is set to archive after 30 days without use, you might have something like the following:

select count(*)
from (query)
where 30 - days_since_last_used = 1

If your dashboards and views archive at different rates, you'll need to break the alert into multiple pieces. I'd also recommend including a link to the dashboard in the alert description!