report of the users who haven't logged in to the content server

Hi,

I have the report to generate the users with last login date but I am unable to get the report of the users who haven't logged in to the content server.

Can you please with the report.

Thanks you,

Shefali shireen

Find more posts tagged with

Comments

Greg Griffiths

if you have a list of users who have logged in, you should be able to just use that to remove from the list of all users and whoever is left has not logged in.

At the simplest level, something like the following should work :

SELECT * FROM kuaf WHERE id NOT IN

(

SELECT kuaf.id of users who have logged in

)

Aseveli

I usually join with the Audit table to gather this information, please note that this query could take some time to complete based on the size of your Audit table.

SELECT * 
FROM (
	SELECT 
	K.ID, 
	K.Name, 
	K.FirstName, 
	K.LastName, 
	K.MailAddress,  
	K.UserPrivileges & 1 AS Enabled,
	(SELECT MAX(DAN1.AuditDate) FROM DAuditNew DAN1 WHERE DAN1.AuditID = 1 AND DAN1.UserID = K.ID) AS "AccountCreateDate",  
	(SELECT MAX(DAN1.AuditDate) FROM DAuditNew DAN1 WHERE DAN1.AuditID = 2 AND DAN1.UserID = K.ID) AS "AccountDeleteDate",	
	(SELECT MAX(DAN1.AuditDate) FROM DAuditNew DAN1 WHERE DAN1.AuditID = 23 AND DAN1.PerformerID = K.ID) AS "LastLoginDate" 
	FROM KUAF K 
	WHERE K.Type = 0
	AND K.Deleted = 0
) KI

If you are only after the Last Login Date, you can probably make this query more efficient by doing a LEFT OUTER JOIN with the DAuditNew table along with using a PARITION BY clause to bubble up the last login audit event.

Todd.

Greg Griffiths

@toddpinel suspect a simple

SELECT DISTINCT(performerid) FROM dauditnew WHERE auditstr='login'

and then use that in a NOT clause selecting all users from KUAF may be a little more efficient as we dont care when the users last logged in, we just want to exclude any that have from the results.

For a complete list of all users, indicating their last login or not, then a PARTITION BY would help.

Aseveli

@Greg Griffiths good point! Much more direct approach for finding that information.