SQL Script Catalog¶
Reference for the ready-made SQL scripts that ship with MetaMiner and the example scripts you can download from this site.
Where the scripts are¶
MetaMiner installs a library of saved scripts named [IK-nnn] <title> in <install folder>\server\scripts. You open them from:
- the Script Catalog of the Meta Query tab in the MetaMiner Client (MMC);
- the saved queries of Meta Query on the Web, which imports the same library when the MetaMiner Server (MMS) starts.
You can save your own scripts to the Script Catalog next to them. All scripts run against the MetaMiner Database (MMDB); see MetaMiner Database Schema.
Image of the Meta Query tab with the Script Catalog open and the [IK-0xx] scripts listed here
../../images/reports-data/meta-query-script-catalog.png
Scripts that write to BusinessObjects
The bulk update scripts (IK-027, IK-028) produce input for MetaMiner automation that changes BusinessObjects objects. Review the result set before you use it, and back up the affected content first.
BusinessObjects scripts¶
| Script | What it returns |
|---|---|
| IK-001 Document Listing by Size (kb) and Folder Path | All CMS documents with file size, by folder path |
| IK-002 Document Listing by Size (kb) | All CMS documents with their size in the File Repository Server |
| IK-003 List Access Levels and Included Rights by User Group for specific object types | Folder-to-group access levels and the rights they include |
| IK-004 Webi / MetaMiner Stale Metadata Listing | Web Intelligence documents whose MetaMiner metadata should be reset |
| IK-007 WebI Unused Objects | Data provider objects that a Web Intelligence report does or does not use |
| IK-008 Audit Events - Daily Count | Audit events per day per environment |
| IK-009 Audit - Last 24 hours of activity | Who did what in the last 24 hours, scheduled or interactive |
| IK-011 Top 3 Reports per Universe per Folder | The most used reports per universe, per folder |
| IK-012 Top 3 Reports per Universe GLOBALLY | The most used reports per universe across all folders |
| IK-014 Crystal Command Text Table Column SQL | Crystal Reports command text split into tables and columns |
| IK-015 Audit - Last 12 Month Usage | Report usage over the last 12 months |
| IK-016 Audit Last 12 Months UNIVERSE Usage | Universe usage over the last 12 months |
| IK-017 Audit query to view objects right modification/custom access level modification | Audit trail of rights and custom access level changes |
| IK-018 UNX Impact Analysis (Universe to Webi Report) | UNX universe objects and the Web Intelligence reports that use them |
| IK-019 UNV Impact Analysis (Universe to Webi Report) | UNV universe objects and the Web Intelligence reports that use them |
| IK-020 Last 12 months audit usage of documents and instances | Usage of documents and instances over 12 months |
| IK-021 List users preferences setting(default/custom) | User preference settings |
| IK-022 List all inbox documents updated in a date range from users in the XYZ group | Inbox documents changed in a date range by members of a group |
| IK-023, IK-024 Users in group A and group B | Users who belong to two group hierarchies at once |
| IK-025 List all reports where the last 20 instances were NON-interactive ... | Reports used only by schedules that deliver to the platform or inbox |
| IK-027 Crystal Logons Bulk Update Query | Input for a bulk update of Crystal Reports logons |
| IK-028, IK-028b, IK-028c Email and File Destination Bulk Update Query | Input for a bulk update of email and file destinations, including notification addresses |
| IK-029 Universe usage based on Audit events including instance activity | Universe usage including scheduled instances |
| IK-033 Complete information about recurring schedules | Recurring schedules with their details |
| IK-034 Custom User Attributes Listing | Users and their custom attributes |
| IK-040 BO Security Objects Diagnostic | Checks for problems collecting security metadata |
| IK-042 UNX DataSecurityProfile & BusinessSecurityProfile Listing | Data and business security profiles of UNX universes |
| IK-052 UNX Universe Parameters Listing | UNX universe parameters (for example FULL OUTER JOIN) |
| IK-053 UNX Universe Business Views Listing | UNX business layer views, folders and objects |
| IK-054 Security View (access levels and rights) | Security metadata for all secured objects down to the individual right |
| IK-059 Migration Queries - SAP BO | Deltas between two BusinessObjects environments |
MetaMiner diagnostic scripts¶
| Script | What it returns |
|---|---|
| IK-005 Diagnostic Audit Summary Counts (MMDB) | Audit events captured by MetaMiner, by event type |
| IK-010 MetaMiner DB - active SQLs (diagnostic) | SQL statements currently running in the MMDB |
| IK-013 Webi Processing Progress % Complete | How many Web Intelligence documents have been fully processed |
| IK-032 Webi Deep vs. Shallow Counts Consistency | Web Intelligence documents collected in summary versus in detail |
The library also contains Tableau scripts (IK-006, IK-026, IK-031, IK-035 to IK-039, IK-041, IK-043 to IK-051, IK-055, IK-056). They return results only when MetaMiner collects Tableau metadata.
Downloadable examples¶
These files are examples from earlier use cases. Open them in Meta Query or any SQL client.
| File | What it returns |
|---|---|
| a1-webi-dataproviders-by-database.sql | Web Intelligence documents by database engine across their data providers |
| reports-to-datasources.sql | Reports and their data sources |
| webi-impact-analysis-report-only.sql | Web Intelligence impact analysis, report level |
| webi-impact-analysis-with-unx.sql | Web Intelligence impact analysis including UNX universes |
| webi-hana-olap-impact.sql | Web Intelligence documents on SAP HANA and OLAP sources |
| universe-document-audit-usage.sql | Universe and document usage from audit data |
| interactive-vs-scheduled-refresh.sql | Interactive versus scheduled refreshes |
| document_and_last_known_audit_action.sql | Each document with its last audited action |
| last_12_months_documents_instances_metaminer.postgresql.sql | Documents and instances used in the last 12 months |
| documents-inbox-destionations.sql | Documents scheduled to inbox destinations |
| who-can-view-object.sql | Users and groups that can view an object |
| included-rights-diff.sql | Differences in included rights |
| roles-diff.sql | Differences between custom roles |
| server-availability.sql | Server availability history |
TODO: re-test the downloadable examples against the current MMDB
These files predate several schema updates. Confirm each still runs, or retire it.
Example: long-running queries¶
The query below lists documents by number of refreshes over the last 90 days, with average refresh and run times. Event type 1011 is a scheduled run and 1003 an interactive refresh.
SELECT f.folderpathcomplete AS document_folder
, v.name AS document_name
, v.cmsid
, count(a.event_id) AS nbr_runs
, round(avg(refresh_ms) / 1000, 2) AS avg_refresh_sec
, round(avg(run_ms) / 1000, 2) AS avg_run_sec
FROM audit_summary2 a
JOIN documents v ON a.object_cuid = v.cuid AND a.cmsid = v.cmsid
JOIN v_folders2a f ON f.cmsid = v.cmsid AND f.cuid = v.parentfoldercuid
WHERE a.audit_date_time > current_date - interval '90 days'
AND a.event_type_id IN (1011, 1003)
GROUP BY v.name, v.cmsid, f.folderpathcomplete
ORDER BY nbr_runs DESC;
Add AND a.refresh_ms > 100000 to keep only refreshes longer than 100 seconds.