Query DB for deactivated users who's not been updated for 5+ years

Question

Hi community,

I'm trying to find a way to extract users who's been deactivated for over 5 years.

We need to find users despite them never being logged in, so ideally the query should be based on the last updated date, since the deactivation should count as the last update on the accounts (if I'm not mistaken).

I'm seeing posts with query options for last login date, but is it possible to query deactivated users for last updated date?

To put this into context, we need to anonymize these users in bulk to begin with, and then create a scheduled script that runs weekly and anonymizes any users that's been deactivated for more than 5 years as a GDPR requirement.

We're on a PostgreSQL DB, and on Jira DC version 8.20.12.
Ideally we'd like to do this without 3rd party add-ons.

Thanks in advance!

Dang Thi Thuy Tien · Answer

Hi @Jakob KN,

We've noticed that many users have been requesting an "Inactive User Anonymizer" feature for anonymizing users who have been inactive for a certain time. I’m pleased to inform you that our app, User Data Cleanup for Jira, includes this functionality. It allows you to easily anonymize inactively or never-logged-in users based on your specified time frame.

Key features of this app include:

* Highly customizable and modular: Configure multiple cleanup rules that can be applied to a single or bulk of users. Maximize control over user-specific data cleansing.

* Two-Stage Cleanup: Leaving Day & Permanent: Apply a two-stage cleanup process: deactivate, remove, or transfer specific user data on the leaving day, retain necessary information for traceability, and execute a permanent cleanup.

* Preview & Retrospect: Cleanup Control: Before performing cleanup actions for single or multiple users, use the Preview to check all planned actions. Use the Retrospect to check previously cleaned data for specific users.

We believe UDC can save you time and effort, and we’d love for you to give it a try. You can find more details and download our app here.
Our docs: https://mgm-atlassian-apps.mgm-tp.com/user-data-cleanup/latest/overview

https://marketplace.atlassian.com/apps/1234248/user-data-cleanup-for-jira?hosting=datacenter&tab=overview

Payne · Answer

Here's a MySQL query to identify the user set you describe; I'm sure it needs to be altered a bit for Postgres, but maybe it will at least get you going in the right direction.

SELECT * FROM jira_test.cwd_user where updated_date < DATE_ADD(now(),INTERVAL -5 year)