Queries

[Kapil Lohia]

Hi All,

I need to get some data from my database and i am attaching my requirements below:

can anyone help

1)List of users with last login date for the Confluence

Required Columns

user_name directory_name SUCCESSDATE

Query

select cu.user_name, cua.attribute_value from cwd_user_attribute cua left join cwd_user cu on cua.user_id=cu.id where attribute_name='lastAuthenticated';

2)List of User who never logged in the confluence for Confluence

Required Columns

user_name created_date first_name last_name display_name

Query

select user_name from cwd_user where user_name not in
(SELECT cwd_user.user_name
FROM cwd_user, cwd_user_attribute
WHERE cwd_user_attribute.user_id = cwd_user.id
AND cwd_user_attribute.attribute_name = 'lastAuthenticated');

3)List of active users with last login date for the URL For JIRA

Required Columns

Directory Username Last Login

Query

SELECT d.directory_name AS "Directory",
u.user_name AS "Username",
DATEADD(second, cast(attribute_value as bigint)/1000,{d '1970-01-01'}) AS "Last Login"
FROM dbo.cwd_user u
JOIN (
SELECT DISTINCT child_name
FROM dbo.cwd_membership m
JOIN dbo.globalpermissionentry gp ON m.parent_name = gp.GROUP_ID
WHERE gp.PERMISSION IN ('ADMINISTER', 'USE', 'SYSTEM_ADMIN')
) AS m ON m.child_name = u.user_name
LEFT JOIN (
SELECT *
FROM dbo.cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) AS a ON a.user_id = u.ID
JOIN dbo.cwd_directory d ON u.directory_id = d.ID
ORDER BY "Last Login" DESC;

4)List of active User who never logged in theJIRA 

Required Columns
Directory Username Last Login

Query

select * from cwd_user_attributes where attribute_name = 'login.count'and attribute_value='0';

5)List of all the inactive users for the JIRA

Required Columns

user_name created_date first_name last_name display_name
Query

SELECT u.user_name,u.first_name,u.last_name,u.email_address,d.directory_name from cwd_user u join cwd_directory d on u.directory_id = d.id where u.active = 0;

6)List of all the active users for the JIRA

Required Columns

user_name created_date first_name last_name display_name

select count(*) from cwd_user where active = 1

[Nic Brough -Adaptavist- · Accepted solution]

There's no question here.  What are you asking us to help with?