Get user incative and never connect

[salim_testing]

Hello 

I have a lot of users in my crowd and now i need to clean users never connected and inactive since 6 months.

Can you tell me what is the request SQL i need to use for i can have this please ?

and i need also a request api for i can to delete all groups for each users.

Thanks in advance

[Fabio Racobaldo _Catworkx_]

Hi @salim_testing ,

if you are on Postgres DB try to use the following query :

SELECT cu.id, 

 cu.user_name, 

 cu.display_name, 

 cu.active, 

 cu.email_address, 

 cd.directory_name, 

 to_timestamp(CAST(cua.attribute_value as double precision)/1000) AS LastAuthenticated

FROM cwd_user cu 

 INNER JOIN cwd_directory cd ON (cu.directory_id=cd.id)  

 LEFT OUTER JOIN cwd_user_attribute cua ON (cua.user_id=cu.id AND cua.attribute_name = 'lastAuthenticated')  

WHERE 

   cua.attribute_value IS NULL OR to_timestamp(CAST(cua.attribute_value as double precision)/1000) < (NOW() - INTERVAL ' 6 months')

GROUP BY cu.id,cu.display_name, cu.active, cu.email_address, cd.directory_name, to_timestamp(CAST(cua.attribute_value as double precision)/1000);

Hope this helps,

Fabio

[Tinker Fadoua]

@salim_testing 

What is your DB? MySQL? MS SQL? Postgres?

Best

Fadoua