3

I am having a bit of trouble getting accurate results from a query that looks to me as if it should work. RDBMS is MySQL.

I am trying to get the max last login date from a union select on three databases for Atlassian products JIRA, Confluence and Stash. There should only be a single entry in the results for each user (the max(Last_Login) is supposed to get the maximum last login date and show a result if that max(Last_Login) is older than today - 90 days.

I summarize the output of the union query then select from that aggregate so that I can get the max login date that is 90 days ago or prior.

The problem: The query seems to work for the most part, but there are a handful of cases where a user shows activity in Confluence, Stash or JIRA interfaces that is more recent than what I have here.

Here is the query I am attempting to use:

select
    User_Name,
    First_Name,
    Last_Name,
    max(Last_Login) as Last_Login
FROM
(
SELECT
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(a.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM jira.cwd_user u
  LEFT JOIN jira.cwd_user_attributes a ON u.id = a.user_id AND a.attribute_name = 'login.lastLoginMillis'
  JOIN jira.cwd_directory d ON u.directory_id = d.id
where u.active = 1
UNION
select
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name, 
    ifnull(from_unixtime((cast(ca.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM confluence.cwd_user u
  LEFT JOIN confluence.cwd_user_attribute ca ON u.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis'
  JOIN confluence.cwd_directory d ON u.directory_id = d.id
where u.active = 'T'
UNION
select
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(sa.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM stash.cwd_user u
LEFT JOIN stash.cwd_user_attribute sa ON u.id = sa.user_id AND sa.attribute_name = 'login.lastLoginMillis'
JOIN stash.cwd_directory d ON u.directory_id = d.id
where u.is_active = 'T'
) as z
group by User_Name, First_Name, Last_Name
having (max(Last_Login)) <= date_sub(now(), interval 90 day)
order by User_Name asc, Last_Login desc
  • 1
    Can you give us sample rows for the user that has newer than 90 days activity and does not appear in the result? – ypercubeᵀᴹ Jul 23 '15 at 16:57
  • @ypercube: the user shows in the result, but his last login date shows as '1970-01-01' – amatusko Jul 23 '15 at 17:21
  • 1
    Show us the related rows from the tables (confluence.cwd_user, confluence.cwd_user_attribute, confluence.cwd_directory). – ypercubeᵀᴹ Jul 23 '15 at 17:31
  • @ypercube: So there was a mistake in the query and the confluence query was returning 0 results with u.active = 1. It is stored as "T" or "F" in that database. I changed the query to reflect this and it is tested returning 913 rows now, but the two users who have logged in are still showing last_login of '1970-01-01'. Unfortunately, I can't show the rows themselves because of security. – amatusko Jul 23 '15 at 18:12
1

So it turns out that confluence uses a different structure to manage this data and now the discrepancies appear to be resolved.

Here is the query:

select
    User_Name,
    First_Name,
    Last_Name,
    max(Last_Login) as Last_Login
FROM
(
SELECT
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(a.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM jira.cwd_user u
  LEFT JOIN jira.cwd_user_attributes a ON u.id = a.user_id AND a.attribute_name = 'login.lastLoginMillis'
  JOIN jira.cwd_directory d ON u.directory_id = d.id
where u.active = 1
UNION
 SELECT 
    u.user_name, 
    u.first_name as First_Name, 
    u.last_name as Last_Name, 
    date_format(l.successdate, '%Y-%m-%d') as Last_Login 
  FROM confluence.logininfo l 
  JOIN confluence.user_mapping m ON m.user_key = l.username 
  JOIN confluence.cwd_user u ON m.username = u.user_name 
  JOIN confluence.cwd_directory d ON u.directory_id = d.id and u.active = 'T' 
UNION
select
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(sa.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM stash.cwd_user u
LEFT JOIN stash.cwd_user_attribute sa ON u.id = sa.user_id AND sa.attribute_name = 'login.lastLoginMillis'
JOIN stash.cwd_directory d ON u.directory_id = d.id
where u.is_active = 'T'
) as z
group by User_Name, First_Name, Last_Name
having (max(Last_Login)) <= date_sub(now(), interval 90 day)
order by User_Name asc, Last_Login desc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.