Write Query for User with email but no AD Account or users has Alpha username without email and no A

[Pratham]

 We have to create an analytics report for User with email but no AD Account or users has Alpha username without email and no AD Account (for endpointname=XYZ)

[rushikeshvartak]

Share your draft query also confirm what do you mean by alpha username ?

[Pratham]

Hi @rushikeshvartak ,

alpha username means all username in a numeric form.

select username,firstname,lastname,email,systemusername,
CASE statuskey
WHEN '0' THEN 'INACTIVE'
WHEN '1' THEN 'ACTIVE'
END AS USERSTATUS, employeeid
from users 
where username not in (SELECT u.username FROM users u join user_accounts ua on u.userkey = ua.userkey join accounts a on a.accountkey = ua.accountkey join endpoints e on e.endpointkey = a.endpointkey AND e.endpointname = 'XYZ')
AND ((email IS NOT NULL OR COALESCE(email,'')!='') OR 
((email is null or COALESCE(email,'')='') AND username NOT REGEXP '[A-Za-z]'))

[rushikeshvartak]

SELECT username,
firstname,
lastname,
email,
systemusername,
CASE
WHEN statuskey = '0' THEN 'INACTIVE'
WHEN statuskey = '1' THEN 'ACTIVE'
ELSE 'UNKNOWN'
END AS USERSTATUS,
employeeid
FROM users
WHERE username NOT IN (SELECT u.username
FROM users u
JOIN user_accounts ua ON u.userkey = ua.userkey
JOIN accounts a ON a.accountkey = ua.accountkey
JOIN endpoints e ON e.endpointkey = a.endpointkey
WHERE e.endpointname = 'XYZ')
AND (
(email IS NOT NULL OR COALESCE(email, '') != '')
OR
((email IS NULL OR COALESCE(email, '') = '')
AND username REGEXP '[A-Za-z]')
)

[stalluri]

@Pratham 

SELECT U.USERNAME,
       U.CREATEDATE,
       U.DISPLAYNAME
FROM users u
WHERE (
          u.USERKEY NOT IN (
                               SELECT ua.USERKEY
                               FROM user_accounts ua
                                   JOIN accounts a
                                       ON ua.ACCOUNTKEY = a.ACCOUNTKEY
                               WHERE a.ENDPOINTKEY = <endpointkey>
                           )
          AND U.STATUSKEY = 1 --Checking only for active users only
          AND U.CREATEDATE > '2024-01-01'
          AND (
                  (
                      email IS NOT NULL
                      OR COALESCE(email, '') != ''
                  )
                  OR (
                  (
                      email IS NULL
                      OR COALESCE(email, '') = ''
                  )
                AND username REGEXP '[A-Za-z]'
                     )
              )
      )

[Pratham]

Hi @stalluri , @rushikeshvartak 

Can you check this query

select firstname,lastname,username,email,systemusername,employeeid,CASE statuskey WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' ELSE 'UNKNOWN' END AS USERSTATUS 
from users
where username not in (SELECT u.username FROM users u join user_accounts ua on u.userkey = ua.userkey join accounts a on a.accountkey = ua.accountkey join endpoints e on e.endpointkey = a.endpointkey AND e.endpointname = 'XYZ' and a.status not in ('SUSPENDED FROM IMPORT SERVICE')) 
AND ((email IS NOT NULL OR COALESCE(email,'')!='')
OR ((email is null or COALESCE(email,'')='') AND username REGEXP '[A-Za-z]'))

 we have to show users for only endpoint XYZ, but it will show all the other endpoints also

can you make some changes in this query?

we have to write query to identify User with email, but no AD Account or users has Alpha username w/o email and no AD Account.(except SUSPENDED FROM IMPORT SERVICE)

[stalluri]

@Pratham 

SELECT 
    u.firstname,
    u.lastname,
    u.username,
    u.email,
    u.systemusername,
    u.employeeid,
    CASE 
        WHEN u.statuskey = '0' THEN 'INACTIVE'
        WHEN u.statuskey = '1' THEN 'ACTIVE'
        ELSE 'UNKNOWN' 
    END AS USERSTATUS
FROM 
    users u
WHERE 
    u.username NOT IN (
        SELECT ua.username 
        FROM user_accounts ua
        JOIN accounts a ON ua.accountkey = a.accountkey
        JOIN endpoints e ON a.endpointkey = e.endpointkey
        WHERE e.endpointname = 'XYZ'
        AND a.status NOT IN ('SUSPENDED FROM IMPORT SERVICE')
    )
    AND (
        (u.email IS NOT NULL OR COALESCE(u.email, '') != '')
        OR (
            (u.email IS NULL OR COALESCE(u.email, '') = '')
            AND u.username REGEXP '[A-Za-z]'
        )
    )

[rushikeshvartak]

SELECT u.firstname,
       u.lastname,
       u.username,
       u.email,
       u.systemusername,
       u.employeeid,
       CASE u.statuskey
         WHEN '0' THEN 'INACTIVE'
         WHEN '1' THEN 'ACTIVE'
         ELSE 'UNKNOWN'
       end AS USERSTATUS
FROM   users u
       LEFT JOIN user_accounts ua
              ON u.userkey = ua.userkey
       LEFT JOIN accounts a
              ON a.accountkey = ua.accountkey
       LEFT JOIN endpoints e
              ON e.endpointkey = a.endpointkey
                 AND e.endpointname = 'XYZ'
WHERE  (( u.email IS NOT NULL
          AND Coalesce(u.email, '') != ''
          AND a.accountkey IS NULL ))
        OR ( ( u.email IS NULL
                OR Coalesce(u.email, '') = '' )
             AND u.username REGEXP '^[A-Za-z]+$'
             AND a.accountkey IS NULL )
           AND a.status NOT IN ( 'SUSPENDED FROM IMPORT SERVICE' )
           AND e.endpointname = 'XYZ'