List of Jira Dashboards that contain an EazyBI gadget

[Kris Dewachter]

Hi Community,

I'm looking for a way to make a list of Jira Dashboards, that contain at least 1 EazyBI gadget (report or dashboard). Is there a way to do this through a DB query, or the usage statistics in EazyBI ?

Best regards,

Kris

[Steven Schouppe · Accepted solution]

@Kris Dewachter 

This query seems to work on Postgres: 

SELECT pagename AS "Dashboard Name", username AS "Owner", gadget_xml AS "Gadget Type"
FROM portalpage
JOIN portletconfiguration ON portalpage.id = portalpage
WHERE gadget_xml LIKE '%eazybi%'

Cheers,
Steven 

[Zane eazyBI Support]

Hi @Kris Dewachter

eazyBI usage statistics can answer which reports and dashboards are published on Jira or Confluence, but not from the opposite. For example, the dimensions "Report Location" and "Report" used with the measure "Report Executions" would show where users see those reports. However, this does not include the Jira dashboard name, as eazyBI does not have this information.

If you want to know the Jira dashboards, the best option is to search in the Jira database (for Data Centers), as @Steven Schouppe suggested.

A more advanced query for Postgres below returns the Jira dashboard name, eazyBI report or dashboard ID, and eazyBI account ID to find those reports in eazyBI.

SELECT 
  portal.pagename AS jira_dashboardname,
  gadgetpref.userprefkey AS eazyBI_gadget_type,
  gadgetpref.userprefvalue AS eazybi_report_or_dashboard_id,
  -- Include account_id using a subquery
  (SELECT userprefvalue 
  FROM gadgetuserpreference 
  WHERE portletconfiguration = pgadget.id 
    AND userprefkey = 'account_id') AS eazybi_account_id,
  1 AS rowcounter
FROM
  portletconfiguration pgadget
  JOIN portalpage portal ON portal.id = pgadget.portalpage
  JOIN gadgetuserpreference gadgetpref ON gadgetpref.portletconfiguration = pgadget.id
WHERE pgadget.gadget_xml LIKE '%eazybi-jira%'
  AND (gadgetpref.userprefkey = 'dashboard_id' OR gadgetpref.userprefkey = 'report_id');

Best regards,

Zane / support@eazyBI.com