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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You can parse the logs in DC to see when each dashboard and associated filters were accessed. I wish there was a better way
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Matt Doar,
Thank you,
What are steps followed login into db, can u provide sql query
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.