Data Analyzer Query Assistance

[ewalton]

Hello, I am hoping someone can help me with the query below. I would like to include a specific entitlement from Active Directory in the query but I am unsure of the syntax. The query below works but I would like to include a condition where the AD group name is "myADgroup". Thank you in advance!

SELECT
u.username,
u.firstname,
u.lastname,
u.employeetype,
u.termdate,
u.leavestatus,
u.customproperty48,
u.statuskey AS status_key,
accounts.name AS account_name,
endpoints.ENDPOINTNAME,
accounts.STATUS AS account_status
FROM
accounts
LEFT JOIN
user_accounts ON accounts.ACCOUNTKEY = user_accounts.ACCOUNTKEY
LEFT JOIN
endpoints ON endpoints.ENDPOINTKEY = accounts.ENDPOINTKEY
LEFT JOIN
users u ON u.userkey = user_accounts.userkey
WHERE
endpoints.ENDPOINTNAME IN ('Active Directory')
AND u.leavestatus != 'active'
AND u.termdate <= CURDATE()
AND accounts.STATUS LIKE '1'
AND u.statuskey = 1;

[Raghu · Accepted solution]

@ewalton  you can use below

ev.ENTITLEMENT_VALUE and ev.displayname  not same sometime based on group name  you can pass and  use it.

SELECT
u.username,
u.firstname,
u.lastname,
u.employeetype,
u.termdate,
u.leavestatus,
u.customproperty48,
u.statuskey AS status_key,
accounts.name AS account_name,
endpoints.ENDPOINTNAME,
accounts.STATUS AS account_status
FROM
accounts
LEFT JOIN
user_accounts ON accounts.ACCOUNTKEY = user_accounts.ACCOUNTKEY
LEFT JOIN
endpoints ON endpoints.ENDPOINTKEY = accounts.ENDPOINTKEY
LEFT JOIN
users u ON u.userkey = user_accounts.userkey
LEFT JOIN
entitlement_types et on endpoints.ENDPOINTKEY = et.ENDPOINTKEY
LEFT JOIN
entitlement_values ev on ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY
WHERE
endpoints.ENDPOINTNAME IN ('ISC AD')
AND u.leavestatus != 'active'
AND u.termdate <= CURDATE()
AND accounts.STATUS LIKE '1'
AND u.statuskey = 1
and ev.ENTITLEMENT_VALUE="myADgroup"
and ev.DISPLAYNAME="myADgroup"

[ewalton]

Thank you for your assistance! 

[rushikeshvartak · Accepted solution]

SELECT u.username,
       u.firstname,
       u.lastname,
       u.employeetype,
       u.termdate,
       u.leavestatus,
       u.customproperty48,
       u.statuskey     AS status_key,
       accounts.NAME   AS account_name,
       endpoints.endpointname,
       accounts.status AS account_status
FROM   accounts
       LEFT JOIN user_accounts
              ON accounts.accountkey = user_accounts.accountkey
       LEFT JOIN endpoints
              ON endpoints.endpointkey = accounts.endpointkey
       LEFT JOIN users u
              ON u.userkey = user_accounts.userkey
       LEFT JOIN entitlement_types et
              ON endpoints.endpointkey = et.endpointkey
       LEFT JOIN entitlement_values ev
              ON ev.entitlementtypekey = et.entitlementtypekey
WHERE  endpoints.endpointname IN ( 'Active Directory' )
       AND u.leavestatus != 'active'
       AND u.termdate <= Curdate()
       AND accounts.status LIKE '1'
       AND u.statuskey = 1
       AND ev.entitlement_value IN ( 'myADgroup' ) 

[ewalton]

This worked great. Thank you so much for your help!