Need to fetch dormant account details from analytical reports

[anjali_5]

I am trying to fetch dormant account details for an endpoint using OOTB analytical reports with the help of below query  but the output is showing as "NOT FOUND"

SELECT u.username AS 'Username', u.FIRSTNAME AS 'User First Name', u.LASTNAME AS 'User Last Name', e.DISPLAYNAME AS 'Application Name', name AS 'Account Name', a.lastlogondate AS 'Last Logon', monthname(a.lastlogondate) as lastlogonmonth, CASE WHEN (a.status = 1) THEN 'Active' ELSE a.status END AS 'Account Status', u.EMAIL FROM users u inner join user_accounts ua on ua.USERKEY= u.USERKEY AND u.statuskey <> 0 inner join accounts a on a.ACCOUNTKEY=ua.ACCOUNTKEY inner join endpoints e on a.ENDPOINTKEY = e.ENDPOINTKEY WHERE a.status IN ('1' , 'Manually Provisioned','Active') AND DATEDIFF(CURDATE(), lastlogondate) >= 1 AND DATEDIFF(CURDATE(), lastlogondate) <= 15 AND lastlogondate IS Not NULL AND e.DISPLAYNAME = 'xxxxxxxx'

Then I checked in data analyser using the below query and got the output .

SELECT LASTLOGONDATE ,DATEDIFF(CURDATE(), lastlogondate) ,STATUS,NAME
from accounts
where endpointkey=xx AND lastlogondate IS NOT NULL

Can you please help me to understand where the problem is with the first query?

[NM]

Hi @anjali_5 , 

1 point to check if endpoint display name mentiond is right

2) any account where difference between today's date and lastlogin date is between 1 and 15 will be included

[rushikeshvartak]

SELECT 
    u.username AS 'Username', 
    u.FIRSTNAME AS 'User First Name', 
    u.LASTNAME AS 'User Last Name', 
    e.DISPLAYNAME AS 'Application Name', 
    a.name AS 'Account Name', 
    a.lastlogondate AS 'Last Logon', 
    MONTHNAME(a.lastlogondate) AS lastlogonmonth, 
    CASE 
        WHEN a.status = 1 THEN 'Active' 
        ELSE a.status 
    END AS 'Account Status', 
    u.EMAIL 
FROM 
    users u 
INNER JOIN 
    user_accounts ua ON ua.USERKEY = u.USERKEY 
    AND u.statuskey <> 0 
INNER JOIN 
    accounts a ON a.ACCOUNTKEY = ua.ACCOUNTKEY 
INNER JOIN 
    endpoints e ON a.ENDPOINTKEY = e.ENDPOINTKEY 
WHERE 
    a.status IN ('1', 'Manually Provisioned', 'Active') 
    AND DATEDIFF(CURDATE(), a.lastlogondate) BETWEEN 1 AND 15 
    AND a.lastlogondate IS NOT NULL 
    AND e.DISPLAYNAME = 'xxxxxxxx';

Additional Debugging Steps:

1. Run the Query Without Filters:

   • Try running the query without the date difference condition (DATEDIFF(CURDATE(), a.lastlogondate) BETWEEN 1 AND 15) to see if you get any results.

2. Check Individual Filters:

   • Test the status condition separately to confirm the status values in your database match what you expect.