Analytics Plus Inventory

Aim:

To have the detailed list of all the Reports, Databases created in Analytics

Open Local & Cloud Databases and fill the entries password = Postgres and if any error occurs stating something like "check the DB connection" change the Port to "33376" and try again.
In the next page select "Single table" and select the table "iamemail".
Name the Database as "Inventory of Analytics".
In the final import tab, schedule it for whichever frequency is required.
Inside the Database "Inventory of Analytics", click on Create new table -> Local & Cloud Databases and follow the steps followed in Step 1, but change the Database Name as "zreportsdb".
Click on Multiple tables and select the following tables - zdbdbobject, zdbmailscheduleobjects, zdbscheduledemails.
In the final import tab, schedule it for the frequency you scheduled the earlier table.

Now create the following query tables(in the same order)

Query table 1 :-

Name it as - Reports Created

SELECT
"zdbdbobject"."displayname" "Report Name",
"iamemail"."email_id" "Created By",
"zdbdbobject"."description" "Description",
FROM_UNIXTIME("zdbdbobject"."createdtime" / 1000) "Created Time",
"zdbdbobject"."id" "ObjID"
FROM "iamemail",
"zdbdbobject"
WHERE "iamemail"."zuid" = "zdbdbobject"."owner_zuid"
AND "iamemail"."zuid" > 107
AND "zdbdbobject"."type" NOT IN ( ('0') , ('6') , ('7') )

This Query table will contain the report name, report description, user who created it, created time.

Query table 2 :-

Name it as - Dashboards Created

SELECT
"iamemail"."email_id" "Created By",
"zdbdbobject"."displayname" "Dashboard Name",
"zdbdbobject"."description" "Description",
FROM_UNIXTIME("zdbdbobject"."createdtime" / 1000) "Created Time",
"zdbdbobject"."id" "ObjID"
FROM "iamemail",
"zdbdbobject"
WHERE "iamemail"."zuid" = "zdbdbobject"."owner_zuid"
AND "iamemail"."zuid" > 107
AND "zdbdbobject"."type" IN ( ('7') )

This Query table will contain the Dashboard name, Dashboard description, user who created it, created time.

Query table 3 :-

Name it as - Reports Scheduled

SELECT
"Reports Created"."Created By" "Created By",
"Reports Created"."Report Name" "Report Name",
"Reports Created"."Description" "Description",
"zdbscheduledemails"."emailid" "Receiver Email Address"
FROM "Reports Created" JOIN "zdbmailscheduleobjects" ON "zdbmailscheduleobjects"."objid" = "Reports Created"."ObjID" INNER JOIN "zdbscheduledemails" ON "zdbscheduledemails"."instance_id" = "zdbmailscheduleobjects"."instance_id"

This Query table will contain the Report Name, Created by and Email receiving user's email addresses.

Query table 4:-

Name it as - Dashboard Scheduled

SELECT
"Dashboards Created"."Created By",
"Dashboards Created"."Dashboard Name",
"Dashboards Created"."Description",
"zdbscheduledemails"."emailid" "Receiver Email Address"
FROM "Dashboards Created" JOIN "zdbmailscheduleobjects" ON "zdbmailscheduleobjects"."objid" = "Dashboards Created"."ObjID" INNER JOIN "zdbscheduledemails" ON "zdbscheduledemails"."instance_id" = "zdbmailscheduleobjects"."instance_id"

This Query table will contain the Dashboard Name, Created by and Email receiving user's email addresses.

Query Table 5:-

Name it as - Database Created

SELECT
"zdbdatabase"."name" "Database Name",
FROM_UNIXTIME("zdbdatabase"."createdtime" / 1000) "Created on",
"iamemail"."email_id" " Created By"
FROM "zdbdatabase" LEFT JOIN "iamemail" ON "iamemail"."zuid" = "zdbdatabase"."owner_zuid"
WHERE "iamemail"."zuid" > 107