MySQL database query to get all users who haven't logged for current date

[Srinivas Acharya M]

Hi,
I'm trying to find all the users who haven't logged for the current day.
I need the MySQL query for JIRA to get all such users? 

I have JIRA 7.9.0 and MySQL 5.7

Does anyone know what the query is?

[Sloan]

Hi @Srinivas Acharya M

The following Knowledgebase page might help you:

• Retrieve last login dates for users from the database

This is the SQL to display all users according to a last login date:

SELECT d.directory_name AS "Directory", 
    u.user_name AS "Username",
    from_unixtime((cast(attribute_value AS UNSIGNED)/1000)) AS "Last Login" 
FROM cwd_user u
JOIN (
    SELECT DISTINCT child_name
    FROM cwd_membership m
    JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID
    ) AS m ON m.child_name = u.user_name
JOIN (
    SELECT * 
    FROM cwd_user_attributes 
    WHERE attribute_name = 'login.lastLoginMillis'
    ) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
ORDER BY "Last Login" DESC;

Niklas

[Sloan]

And for users that have never logged in see this:

• How to check for users who never logged into Jira

Note please that the SQL there is for PostgreSQL and might now work without changes on MySQL.