How to get last active dates in dashboards

satya October 29, 2024

Hi .,

 

we have 16000 dashboards now I want to get last active dates in dashboards 

please help me out

Thanks in advance

3 answers

1 vote
Ravi Sagar _Sparxsys_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 29, 2024

Hi @satya 

When you say last active date. Are you trying to find our when the dashboard was used?

Dashboards are usually based on filters, which have issues under it. If you objective is to clean up old dashboards then there is no straight forward way of doing it. You will have to check the filters, their owners, and also activity in the issues.

Best would be to talk to the owners or stakeholders of various projects and come up with a strategy to maintain the dashboards.

Ravi

sai satyendra November 1, 2024

Hi Ravi Sagar, 

I followed your youtube videos it's very helpful, here the objective is clean up activity so we have 18000 dashboards so we don't no last view details My stakeholders said run SQL in db, can you please describe SQL query for last view details and db login process steps can you describe 

Thanks in advance 

0 votes
Doreen Undiri
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 29, 2024

Hi @satya ,

Try to use the Jira REST API , to access the API, you’ll need an API token (for Jira Cloud) and the relevant permissions based on the actions you want to perform. For example, to view dashboards, you’ll need the "Browse Projects" permission, while more advanced API actions may require "Jira Administrator" or "System Administrator" permissions.

If you are using Jira Server or Data Center, a direct database query is also an option(available only in Jira Server or Data Center) , you can run a query to get the last-viewed dates directly the  query will provide you with a list of dashboards along with their last active dates.

Goodluck!!


Dd

sai satyendra November 1, 2024

Hi doreen

Thanks for your valuable ans 

We have data centre can you provide database query, what are the steps to be followed while login in db

Thanks in advance 

Doreen Undiri
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 4, 2024

Hi @sai satyendra 

you need to access the database directly.Depending on your database type—like PostgreSQL, MySQL, or Oracle—you can use either a database client or command line to connect. After connecting , you can run a query to fetch the last active dates

SELECT

    d.ID AS Dashboard_ID,

    d.NAME AS Dashboard_Name,

    MAX(v.LAST_VIEWED) AS Last_Active_Date

FROM

    AO_<unique code>_DASHBOARD d

JOIN

    AO_<unique code>_VIEWED_DASHBOARDS v ON d.ID = v.DASHBOARD_ID

GROUP BY

    d.ID, d.NAME

ORDER BY

    Last_Active_Date DESC;

This SQL query pulls information from the AO_<unique code>_DASHBOARD table (which stores dashboard data) and joins it with AO_<unique code>_VIEWED_DASHBOARDS (which logs views). The result will be a list of dashboards sorted by their most recent active date. Just make sure you have the necessary read permissions for these tables, and adjust any table or column names if your Jira schema differs slightly. Always run read-only queries on a live system to prevent accidental data changes.

Note:

In the sql code the part with <unique code> is where you write the code/ name of your table which can be found using a Database Browser or Query Tool:

If you have a SQL client (like pgAdmin for PostgreSQL, MySQL Workbench for MySQL, or Oracle SQL Developer for Oracle databases), you can open it to browse the list of tables in your Jira database.

Look for tables with names that follow this pattern: AO_<unique_code>_DASHBOARD and AO_<unique_code>_VIEWED_DASHBOARDS.

The unique code will be consistent across tables for related data. So, if your DASHBOARD table is AO_12345_DASHBOARD, the corresponding view table would likely be AO_12345_VIEWED_DASHBOARDS.

Goodluck...!!


Dd

sai satyendra November 4, 2024

hi thanks for your information 

 

i execute sql query in my oracle db i have getting an error can you please help me out

 

the error is 

ORA-00933: SQL command not properly ended

thanks in advnace

sai satyendra November 5, 2024

how to get unique code details in db

0 votes
Matt Doar _Adaptavist_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 29, 2024

You can parse the logs in DC to see when each dashboard and associated filters were accessed. I wish there was a better way

sai satyendra November 1, 2024

Hi Matt Doar,

Thank you,

What are steps followed login into db, can u provide sql query 

Suggest an answer

Log in or Sign up to answer