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 N_ B_

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:

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

Niklas