Page MenuHomeDevCentral
Files F3769967

No OneTemporary
Actions

View Options

diff --git a/README.md b/README.md
new file mode 100644
index 0000000..a835f80
--- /dev/null
+++ b/README.md
@@ -0,0 +1,16 @@
+## Internal reports
+
+Reports about Nasqueron internal data.
+
+This repository can host:
+
+ - SQL queries to get report data
+ - Tools to produce reports
+
+### SQL queries
+
+Queries are organized by cluster/server name, then by service:
+
+ - acquisitariat/ contains the queries for MySQL Docker container
+ used by dev & community services like DevCentral
+
diff --git a/sql/acquisitariat/devcentral/tokens-language-models.sql b/sql/acquisitariat/devcentral/tokens-language-models.sql
new file mode 100644
index 0000000..3f38780
--- /dev/null
+++ b/sql/acquisitariat/devcentral/tokens-language-models.sql
@@ -0,0 +1,24 @@
+-- DevCentral
+-- What revisions have the "Yo so serious" token awarded?
+--
+-- Notes:
+-- This token allows to tag revisions where ChatGPT was useful.
+-- {{D}} and {{Repo}} are MediaWiki templates used on Agora.
+--
+-- Published to https://agora.nasqueron.org/AI_content
+
+SELECT
+ CONCAT("{{D|", rev.id, "}}") as revision,
+ title,
+ DATE_FORMAT(FROM_UNIXTIME(rev.dateCreated), '%Y-%m-%d') as `date`,
+ userName,
+ CONCAT("{{Repo|", repositorySlug, "}}") as repository
+FROM devcentral_differential.differential_revision rev
+ LEFT JOIN devcentral_repository.repository repo ON repo.phid = rev.repositoryPHID
+ LEFT JOIN devcentral_user.user ON user.phid = authorPHID
+WHERE rev.phid IN (
+ SELECT DISTINCT objectPHID
+ FROM devcentral_token.token_given
+ WHERE tokenPHID = "PHID-TOKN-emoji-3"
+)
+ORDER BY rev.id DESC;
diff --git a/sql/acquisitariat/devcentral/users-2FA.sql b/sql/acquisitariat/devcentral/users-2FA.sql
new file mode 100644
index 0000000..84af9b4
--- /dev/null
+++ b/sql/acquisitariat/devcentral/users-2FA.sql
@@ -0,0 +1,14 @@
+-- DevCentral
+-- Who still need to enable a second factor?
+
+SELECT DISTINCT userName
+FROM devcentral_project.edge
+LEFT JOIN devcentral_user.user ON user.phid = edge.dst
+WHERE src IN (
+ "PHID-PROJ-2gmvzczbipg5amctzsjd", -- Trusted users
+ "PHID-PROJ-puoemrjignrbd2eilwpo" -- Ops
+)
+ AND type = 13 -- project has member (PhabricatorProjectProjectHasMemberEdgeType)
+ AND isSystemAgent = 0 -- avoid bot accounts like Alken-Orin
+ AND isEnrolledInMultiFactor = 0
+ORDER BY userName;

File Metadata

Mime Type
text/x-diff
Expires
Mon, Nov 25, 18:34 (10 h, 5 m)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
2258866
Default Alt Text
(2 KB)

Event Timeline

Nasqueron DevCentral · If it had been much bigger the moon would have had a core of ice. · Powered by Phabricator