Data Explorer Plugin

official

(Kane York) #1

The Data Explorer plugin allows you to make SQL queries against your live database, allowing for up-to-the-minute stats reporting.

Usage

After installing the plugin, head to /admin/plugins. Click on the “Settings” button, and turn on “data explorer enabled”, then return and reload the page.

There should now be a tab in the stacked nav called “Data Explorer”. Select it, then use the + and Import buttons to create or import queries.

For an example, let’s import one that I wrote. Download this file, then open the import dialog and drag the file onto the dialog.

most-common-likers.dcquery (1).json (442 Bytes)


Click Import to save it to the server. Select the query from the drop-down box, and try running it with the Run Query button. (Note: For the purposes of demonstration, I checked the “Include query plan” box.)

Looks like all of the Discourse developers like each other quite a bit.

Writing Queries

When you click any of the “Edit” buttons on a query view, or start a new Explorer Query, you are presented with a screen like the one below:

The top two boxes are the name and description of the query. The name is what you’re selecting in the dropdown box, and the description should be used for things like explaining what to put in the query parameters or to describe what data is being queried.

The left pane is where you write the SQL query. Minor syntax highlighting and checking is provided.

The schema explorer is on the right side of the writing pane. Note the red column names with a warning symbol, such as email, password_hash, salt - these are “Sensitive” columns. Try to avoid querying them if possible, reconsider querying them if you are in a public location, and definitely do not post screenshots of queries checking those columns without thinking about what you’re doing first.

Declaring Parameters

TODO

-- [params]
-- null string_list :words
-- null string_list :categories
-- user_id :user_id
-- int :limit = 150

WITH words AS (
 SELECT unnest(string_to_array( :words, ',' )) word
),
cats1 AS (
 SELECT unnest(string_to_array( :categories, ',')) cat
),
-- ...
AND p.user_id = :user_id
-- ...
LIMIT :limit

Installation

If you’re on a hosted Discourse forum and the data explorer doesn’t show up at /admin/plugins, email us at team@discourse.org to ask us to add it for you.

For self-hosted discourse forums, follow the Install A Plugin guide using GitHub - discourse/discourse-data-explorer: SQL Queries for admins in Discourse .


Daily graph of user time online
Visual forum statistics
Network chart of your forum - data visualization
What cool data explorer queries have you come up with?
Extracting email addresses for a group
Visual map of users
Can I see in the statistics which post received the most likes
Can staff be able to see real author of anonymous posts
How do I download a record of top posts or most clicked links?
How to look up a user based on a user_id?
Discourse view file update does not reflect in browser
How do I get a list of which users use which login methods?
Discourse Narrative Bot Data Explorer Queries :robot:
Who messaged the most?
Who messaged the most?
How to open hyperlinks in a new tab
Working with plugin_store_rows with API, Possible?
Run report of digest Unsubscribes
Need log the who downloaded attachments
Creating Point/Reward System for users
Redesign of use profile
Poll problem - exact vote numbers needed
Telegram Notifications Plugin (discourse-telegram-notifications)
Get last :heart: via API
Is it possible to keep the votes history of a poll?
Monitoring Moderator Activity
See what categories new users (& other trust levels) post in most
What plugins do you use - and why?
What cool badge queries have you come up with?
CRM Integration
Can I access my Discourse forum database?
Counting and reporting post views for commercial forum users (so we can charge them per-view)
Top user list public
How do I audit votes for fraudulent behavior
See All User Activity As An Admin
Create report for amount of interactions per user
What would you like to see on your Discourse Admin Dashboard?
How do I clear Warnings?
Bug with [code]anything[/code] on a single line by itself
Exporting all Topic titles and IDs?
Is there a Data Explorer for Dummies article somewhere?
Display Data Explorer query results in WordPress (with TwigAnything)
Tracking referrals? Encouraging users to invite others?
Query to find out who marked the solution as solved
Add an Admin subtab for Inactive users
Get infromation from data explorer and put on user card
Beginners Guide to Install Discourse on Ubuntu for Development
SQL: The most N used words per user (speak their language!)
User Summary - Most Liked By user attributions are incorrect
Maximum Number of Search Results?
Data analytics by complex networks and text mining
Error thrown when importing a query in Data Explorer
Zero search results searching private messages for specific keyword
How to measure active users?
Where to find User ID information
(David García-Navas) #4

We’ve upgraded the plugin with your last commits and now works perfect! Thanks!

Btw, the “Most Common Likers” query is a very very useful stat for communities! :smile:
It makes us think on a “tinder”-like badge: when two users give each other n likes, could be a match!
*perhaps a feature for Helloforos? cc/ @charleswalter


(Felix Freiberger) #5

Should moderators be able to use this plugin?

Currently on discourse-data-explorer d4d5417 and Discourse ad8f2cb, admins and mods can see the link to the data explorer page, but it fails to load for mods.


(Jeff Atwood) #6

No, this is definitely an Admin thing. Very low level access to the database.


(Felix Freiberger) #7

I agree – so having this show up to moderators is a bug, then?

(Of course, it would be cool if admins could allow mods to run certain pre-defined queries – but that certainly is an advanced feature.)


(Jeff Atwood) #8

This should not show up for mods can you check into this @riking?


(Anton) #9

An example of a query that I use for a simple monthly contest in our community:

Fetch top 10 posts by likes received in the last month, excluding administrators. If the likes count equals, prioritize the posts that were created earlier.

SELECT p.id as post_id, p.like_count as like_count
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN users u ON u.id = p.user_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '1 month'
  AND NOT u.admin
  AND NOT u.blocked
ORDER BY p.like_count DESC, p.created_at ASC
LIMIT 10

Should I also add clauses to filter out archived and deleted topics?


(Gerhard Schlager) #10

Am I doing something wrong or is the plugin not compatible with the master branch?
I’m getting the following error in the browser console after enabling the plugin in my development environment:

Error: Assertion Failed: The attempt to link-to route 'adminPlugins.explorer' failed. The router did not find 'adminPlugins.explorer' in its possible routes: 'loading', 'error', 'exception', 'about', 'topic.loading', 'topic.error', 'topic.fromParams', 'topic', 'topic.fromParamsNear', 'topicBySlug', 'topicUnsubscribe', 'discovery.loading', 'discovery.error', 'discovery.top', 'discovery.topParentCategory', 'discovery.topCategoryNone', 'discovery.topCategory', 'discovery.topAll', 'discovery.topAllParentCategory', 'discovery.topAllCategoryNone', 'discovery.topAllCategory', 'discovery.topYearly', 'discovery.topYearlyParentCategory', 'discovery.topYearlyCategoryNone', 'discovery.topYearlyCategory', 'discovery.topQuarterly', 'discovery.topQuarterlyParentCategory', 'discovery.topQuarterlyCategoryNone', 'discovery.topQuarterlyCategory', 'discovery.topMonthly', 'discovery.topMonthlyParentCategory', 'discovery.topMonthlyCategoryNone', 'discovery.topMonthlyCategory', 'discovery.topWeekly', 'discovery.topWeeklyParentCategory', 'discovery.topWeeklyCategoryNone', 'discovery.topWeeklyCategory', 'discovery.topDaily', 'discovery.topDailyParentCategory', 'discovery.topDailyCategoryNone', 'discovery.topDailyCategory', 'discovery.latest', 'discovery.latestParentCategory', 'discovery.latestCategoryNone', 'discovery.latestCategory', 'discovery.unread', 'discovery.unreadParentCategory', 'discovery.unreadCategoryNone', 'discovery.unreadCategory', 'discovery.new', 'discovery.newParentCategory', 'discovery.newCategoryNone', 'discovery.newCategory', 'discovery.read', 'discovery.readParentCategory', 'discovery.readCategoryNone', 'discovery.readCategory', 'discovery.posted', 'discovery.postedParentCategory', 'discovery.postedCategoryNone', 'discovery.postedCategory', 'discovery.bookmarks', 'discovery.bookmarksParentCategory', 'discovery.bookmarksCategoryNone', 'discovery.bookmarksCategory', 'discovery.categories', 'discovery.parentCategory', 'discovery.categoryNone', 'discovery.category', 'discovery', 'application', 'group.loading', 'group.error', 'group.members', 'group.index', 'group', 'users', 'user.loading', 'user.error', 'userActivity.loading', 'userActivity.error', 'userActivity.likes_given', 'userActivity.likes_received', 'userActivity.bookmarks', 'userActivity.topics', 'userActivity.posts', 'userActivity.replies', 'userActivity.mentions', 'userActivity.quotes', 'userActivity.edits', 'userActivity.messages_sent', 'userActivity.messages_received', 'userActivity.pending', 'userActivity.index', 'userActivity', 'user.badges', 'user.notifications', 'user.flaggedPosts', 'user.deletedPosts', 'userPrivateMessages.loading', 'userPrivateMessages.error', 'userPrivateMessages.mine', 'userPrivateMessages.unread', 'userPrivateMessages.index', 'userPrivateMessages', 'preferences.loading', 'preferences.error', 'preferences.username', 'preferences.email', 'preferences.about', 'preferences.badgeTitle', 'preferences.card-badge', 'preferences.index', 'preferences', 'userInvited.loading', 'userInvited.error', 'userInvited.show', 'userInvited.index', 'userInvited', 'user.index', 'user', 'signup', 'login', 'login-preferences', 'forgot-password', 'faq', 'tos', 'privacy', 'guidelines', 'new-topic', 'badges.loading', 'badges.error', 'badges.show', 'badges.index', 'badges', 'queued-posts', 'full-page-search', 'admin.loading', 'admin.error', 'admin.dashboard', 'admin', 'adminSiteSettings.loading', 'adminSiteSettings.error', 'adminSiteSettingsCategory', 'adminSiteSettings.index', 'adminSiteSettings', 'adminEmail.loading', 'adminEmail.error', 'adminEmail.all', 'adminEmail.sent', 'adminEmail.skipped', 'adminEmail.previewDigest', 'adminEmail.index', 'adminEmail', 'adminCustomize.loading', 'adminCustomize.error', 'adminCustomize.colors', 'adminCustomizeCssHtml.loading', 'adminCustomizeCssHtml.error', 'adminCustomizeCssHtml.show', 'adminCustomizeCssHtml.index', 'adminCustomizeCssHtml', 'adminSiteText.loading', 'adminSiteText.error', 'adminSiteText.edit', 'adminSiteText.index', 'adminSiteText', 'adminUserFields', 'adminEmojis', 'adminPermalinks', 'adminEmbedding', 'adminCustomize.index', 'adminCustomize', 'admin.api', 'admin.backups.loading', 'admin.backups.error', 'admin.backups.logs', 'admin.backups.index', 'admin.backups', 'adminReports', 'adminFlags.loading', 'adminFlags.error', 'adminFlags.list', 'adminFlags.index', 'adminFlags', 'adminLogs.loading', 'adminLogs.error', 'adminLogs.staffActionLogs', 'adminLogs.screenedEmails', 'adminLogs.screenedIpAddresses', 'adminLogs.screenedUrls', 'adminLogs.index', 'adminLogs', 'adminGroups.loading', 'adminGroups.error', 'adminGroupsType.loading', 'adminGroupsType.error', 'adminGroup', 'adminGroupsType.index', 'adminGroupsType', 'adminGroups.index', 'adminGroups', 'adminUsers.loading', 'adminUsers.error', 'adminUser.loading', 'adminUser.error', 'adminUser.badges', 'adminUser.tl3Requirements', 'adminUser.index', 'adminUser', 'adminUsersList.loading', 'adminUsersList.error', 'adminUsersList.show', 'adminUsersList.index', 'adminUsersList', 'adminUsers.index', 'adminUsers', 'adminBadges.loading', 'adminBadges.error', 'adminBadges.show', 'adminBadges.index', 'adminBadges', 'adminPlugins.loading', 'adminPlugins.error', 'adminPlugins.index', 'adminPlugins', 'unknown'

(Kane York) #11

I’m able to load it here on Meta.


#12

I want to be able to change registration dates of my forum users.
Can i make this possible with this plugin?


(Gerhard Schlager) #13

Found the problem. I forgot to delete the content of the tmp folder after installing it in my development environment. Now it’s working.


(Tobias Eigen) #14

the explorer just lets you see into the data - you can’t change anything. but if you know what you’re doing you could do sql queries from the command line that change the database.


(Charles Walter) #15

I’m trying to figure out how to export the number of topics and posts that our users have created. Is there a variable that I can add to this query?

SELECT
    u.id,
    u.username_lower AS "username",
    u.email,
    u.created_at,
    u.last_seen_at,
   ? ... posts_count
FROM users u
WHERE u.id > 0

(Kane York) #16

Join on the user_stats table, it has posts_count and topics_count.


(Charles Walter) #17

Thx. Glad I was able to figure out how to apply based on another sample I had.

SELECT
    u.id,
    u.username_lower AS "username",
    u.email,
    u.created_at,
    u.last_seen_at,
    us.topic_count,
    us.days_visited,
    us.post_count,
    u.last_seen_at
FROM users u
LEFT JOIN user_stats us ON u.id = us.user_id
WHERE u.id > 0

(Bart) #18

Hey guys, does the Data Explorer plugin run all queries, or will it protect users from bringing their databases to their knees with complex joins etc? Can you only run select statements, or also insert, delete etc?

Thanks!


(Mittineague) #19

AFAIK the plugin does only Read (i.e. SELECT queries)
If you need otherwise the CLI should work.

As far as protecting users from shooting themselves in the foot. I don’t think so. I would hope that anyone using it would know enough to not do a SELECT * FROM * type of query


(Bart) #20

That’s what I figured. Thanks!


(Anton) #21

if I’m not mistaken, if you do SELECT * FROM *, it would limit the request by default to ~1000 items or something like that.


(Kane York) #22

Queries are limited to a 10 second runtime, plus SET TRANSACTION READ ONLY.