[Prototype] User Report Ver 6.3

Sure, if you can do it easily, it'd be nice to have. It could be interesting to look at upload tags and try to see who is uploading what.

Sure, no problem... :)

I'll probably have something up by tomorrow (it being late in the states and all)...

The following is about issue #2647, i.e. making a user's report for all users.

albert left the following post on that issue about a week ago:

albert said:

Any feedback about the current report?

The report he's referring to is located at the following link: Moderator Dashboard

I'd say contrast and compare how the current report looks with the user report located on the first post in this thread.

Any feedback you have will go towards making the report more like what you want. Thanks.

@evazion

I uploaded the JSON files. The main difference is the site files that combine all of the users' tags into one object, e.g. {"1girl":20,"solo":15}.

• All Site Tags
• All User Tags
• Upload Site Tags
• Upload User Tags
• Post Site Tags
• Post User Tags

It probably comes as no suprise that the Top 10 upload tags...

jq-win32 "to_entries | sort_by(.value) | reverse | [limit(10;.[])] | from_en
tries" uploadsitetags.json

...were the following...

{
  "1girl": 19821,
  "solo": 14897,
  "long_hair": 14012,
  "highres": 13985,
  "breasts": 10994,
  "looking_at_viewer": 10027
  "blush": 9608,
  "smile": 9533,
  "short_hair": 8121,
  "open_mouth": 8068
}

Thanks, I'll check it out. If we could filter out the gentags it might be interesting to see what the top uploaded artists/copyrights/characters are. And I've been playing around more with BigQuery; I figured out how to import your tag gardening dump from yesterday to it. Here's the link: https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru.user_added_tags. A few more stats I found:

In a one month period...

• 1774 users added a tag.
• Of those, only 163 added more than 100 tags.
• 15054 distinct tags were used.
• 320160 total tags were added.
• But the top ten taggers added 58% of those 320160 tags.

So relatively few people tag at all. And of those, only a very small group tags in any significant amount.

Hmmm... I could create modified JSONs so that they include tag type, similar to how the POSTS JSON does it....

Maybe something like..

[{"id":1,"gentags":{"solo":1},"arttags":{"nullken":10}, "copytags":{"touhou":23}, "chartags":{"pixiv-tan":4}}]

Since I can't think of any easy way to combine the different types for comparison, I'd leave the untyped versions as as they currently are as well...

Edit:

Nevermind, I just realized that...

.[] | .copytags + .chartags + .gentags + .arttags

...would combine all of the tagtypes into one.

Okay, so I imported uploadusertags.json into BigQuery here, along with a dump of the users table and the tags table. I did this in BigQuery because I'm finding it's easier for me to do this kind of analysis in SQL, since you can do things like filter by tag type, or match user IDs to names, by JOINing the tables all together into one big table.

Here are the results:

SELECT
  tags_used.id AS id,
  users.name AS name,
  users.level_string AS level,
  tags.name as tag_name,
  tags.category as tag_type,
  tags_used.tags.count AS tag_count
FROM FLATTEN([turing-zone-143603:danbooru.user_upload_tags], tags) AS tags_used
JOIN [turing-zone-143603:danbooru.tags]  AS tags  ON tags_used.tags.name = tags.name
JOIN [turing-zone-143603:danbooru.users] AS users ON tags_used.id        = users.id
WHERE tags.category = 1
GROUP BY id, name, level, tag_name, tag_type, tag_count
ORDER BY tag_count DESC
LIMIT 15000

SELECT
  tags_used.id AS id,
  users.name AS name,
  users.level_string AS level,
  tags.name as tag_name,
  tags.category as tag_type,
  tags_used.tags.count AS tag_count
FROM FLATTEN([turing-zone-143603:danbooru.user_upload_tags], tags) AS tags_used
JOIN [turing-zone-143603:danbooru.tags]  AS tags  ON tags_used.tags.name = tags.name
JOIN [turing-zone-143603:danbooru.users] AS users ON tags_used.id        = users.id
WHERE tags.category = 4
GROUP BY id, name, level, tag_name, tag_type, tag_count
ORDER BY tag_count DESC
LIMIT 15000

SELECT
  tags_used.id AS id,
  users.name AS name,
  users.level_string AS level,
  tags.name as tag_name,
  tags.category as tag_type,
  tags_used.tags.count AS tag_count
FROM FLATTEN([turing-zone-143603:danbooru.user_upload_tags], tags) AS tags_used
JOIN [turing-zone-143603:danbooru.tags]  AS tags  ON tags_used.tags.name = tags.name
JOIN [turing-zone-143603:danbooru.users] AS users ON tags_used.id        = users.id
WHERE tags.category = 3
GROUP BY id, name, level, tag_name, tag_type, tag_count
ORDER BY tag_count DESC
LIMIT 15000

These tables show which people are the top uploaders for which things. So for example, we can see that Dbx was the top hatsune miku uploader, Qpax was the top overwatch uploader, Sacriven was the top touhou uploader, c-button was the most uploaded artist, etc.

Ah, that's pretty cool. I figured I was the top Girls und Panzer uploader.

This all got me thinking though about supply vs demand, and so I took the data from Popular Searches and compiled the following table:

It's not surprising that most of the top spots are artists, as their output would be on average much lower than a character or copyright.

OOT, but anyone knows why Provence deleting himself? :/

No idea... so that's what an account looks like after it's deleted...? I wonder if it was just a mistake? I remember a few months back he momentarily changed his name, only to change it back again...

Sacriven said:

OOT, but anyone knows why Provence deleting himself? :/

BrokenEagle98 said:

No idea... so that's what an account looks like after it's deleted...? I wonder if it was just a mistake? I remember a few months back he momentarily changed his name, only to change it back again...

Looks like account deletion to me. The account name is a generic string and all favorites have been removed.

Can we undeleted an account though? Maybe if we can, that means he's taking a momentary leave for personal reasons.

It can be undeleted up until the moment it gets purged, although I don't know what the timeout for that is...? Even then it might be able to be restored if there are regular periodic backups to the Danbooru data that could be pulled from. Otherwise he'd have to start up a new account...

BrokenEagle98 said:

It can be undeleted up until the moment it gets purged, although I don't know what the timeout for that is...? Even then it might be able to be restored if there are regular periodic backups to the Danbooru data that could be pulled from. Otherwise he'd have to start up a new account...

Didn't find anything that indicates a timer based account purge.

The Ruby code in danbooru/app/logical/user_deletion.rb does the following:

• validate (validate given password)
• clear_user_settings
• remove_favorites
• clear_tag_subscriptions
• rename (replace user name with user id)
• reset_password
• create_mod_action (report for mods?)

This means that a deleted account continues to exist, but the password is changed to something random.

(Sorry for abusing this thread.)

It can be undeleted by albert, by restoring password hash from the backup DB, but requesting that after deleting the account yourself would be pretty bold. Either way, vanishing without saying a word, mid-discussion (he sent me a dmail prompting for response hours before) seems pretty strange, if not stupid. Whatever the circumstances, you can spend 30 seconds and say that you quit before deleting the account.

Type-kun said:

It can be undeleted by albert, by restoring password hash from the backup DB, but requesting that after deleting the account yourself would be pretty bold. Either way, vanishing without saying a word, mid-discussion (he sent me a dmail prompting for response hours before) seems pretty strange, if not stupid. Whatever the circumstances, you can spend 30 seconds and say that you quit before deleting the account.

He might have been hacked and someone deleted him out of spite.

I kind of wish we had two-factor authentication, but I'm not sure how it'd be managed.

Jarlath said:

He might have been hacked and someone deleted him out of spite.

I kind of wish we had two-factor authentication, but I'm not sure how it'd be managed.

I contacted Provence to find out what happened.

A moderator or admin could meanwhile check his account's IP history for irregularities. If the last recorded IP address was a non German one, this could be a good indicator for unauthorized account activity.

reiyasona said:

I contacted Provence to find out what happened.

A moderator or admin could meanwhile check his account's IP history for irregularities. If the last recorded IP address was a non German one, this could be a good indicator for unauthorized account activity.

Wait, you have Provence's contact? So you're his collaborator all this time? xD
That aside, I'm agree that this is strange. His last activities didn't show any sign of quitting, after all.

Sacriven said:

Wait, you have Provence's contact? So you're his collaborator all this time? xD
That aside, I'm agree that this is strange. His last activities didn't show any sign of quitting, after all.

Well ... yeah, we have been working together since the introduction of the medium_breasts tag (see forum #116365 -> press "Show").

I have sent him a message on Pixiv.

Type-kun said:
but requesting that after deleting the account yourself would be pretty bold.

This guy did it, so I'd guess it's not so bold. Especially if it's some external cause.

reiyasona said:
A moderator or admin could meanwhile check his account's IP history for irregularities. If the last recorded IP address was a non German one, this could be a good indicator for unauthorized account activity.

On a rather stacked list of IP adresses by their name, none seem to be not following some recursive patterns (and there are 4-5 specific patterns in there). However, it's not out of question that the deletion might NOT be a recorded action for that purpose (and neither is account creation, this one I'm sure of, for comparison's sake).