Analytics for active users with inactive active directory accounts

[soumik_das]

Hi,

I am trying to create an analytics which would generate a report having users who are active with inactive active directory accounts. But the report which I am getting with my query consists of users who have both inactive or suspended from import service active directory accounts along with accounts which are active. So basically users having both inactive and active ad accounts are coming up in my report. Please can you rectify my query which I am attaching below.

SELECT username, createdate, startdate AS 'Start Date', enddate AS 'End Date', firstname, lastname, displayname AS "Display Name", email, customproperty11 , customproperty6 , customproperty13 , customproperty1  , customproperty2  , customproperty5  , customproperty3 , departmentname , jobdescription, Country, statuskey , entity, customproperty3 , orgunitid , employeetype  FROM users WHERE statuskey = 1 AND userkey IN (SELECT U.userkey FROM users U, user_accounts UA, accounts A, endpoints E WHERE U.userkey = UA.userkey AND UA.accountkey = A.accountkey AND A.endpointkey = E.endpointkey AND E.endpointname = 'Active Directory' and ((A.status="Inactive" OR A.status="Suspended From Import Service") AND A.status!="Active"))

[NM]

@soumik_das 

SELECT username, createdate, startdate AS 'Start Date', enddate AS 'End Date', firstname, lastname, displayname AS "Display Name", email, customproperty11 , customproperty6 , customproperty13 , customproperty1 , customproperty2 , customproperty5 , customproperty3 , departmentname , jobdescription, Country, statuskey , entity, customproperty3 , orgunitid , employeetype FROM users u, user_accounts ua, accounts a, endpoints e WHERE u.userkey=ua.userkey and ua.accountkey=a.accountkey and a.endpointkey=e.endpointkey and e.endpointname='Active Directory' and u.statuskey=1 and a.status in ('Inactive')

For all property of select add prefix u.

[rushikeshvartak]

SELECT 
    u.username, 
    u.createdate, 
    u.startdate AS 'Start Date', 
    u.enddate AS 'End Date', 
    u.firstname, 
    u.lastname, 
    u.displayname AS "Display Name", 
    u.email, 
    u.customproperty11, 
    u.customproperty6, 
    u.customproperty13, 
    u.customproperty1,  
    u.customproperty2,  
    u.customproperty5,  
    u.customproperty3, 
    u.departmentname, 
    u.jobdescription, 
    u.Country, 
    u.statuskey, 
    u.entity, 
    u.orgunitid, 
    u.employeetype  
FROM 
    users u 
WHERE 
    u.statuskey = 1 
    AND EXISTS (
        SELECT 1
        FROM user_accounts ua
        JOIN accounts a ON ua.accountkey = a.accountkey
        JOIN endpoints e ON a.endpointkey = e.endpointkey
        WHERE 
            ua.userkey = u.userkey
            AND e.endpointname = 'Active Directory'
            AND a.status IN ('Inactive', 'Suspended From Import Service')
            AND NOT EXISTS (
                SELECT 1
                FROM user_accounts ua2
                JOIN accounts a2 ON ua2.accountkey = a2.accountkey
                JOIN endpoints e2 ON a2.endpointkey = e2.endpointkey
                WHERE 
                    ua2.userkey = u.userkey
                    AND e2.endpointname = 'Active Directory'
                    AND a2.status = 'Active'
            )
    );

[soumik_das]

Hi @rushikeshvartak ,

I have tried you query but i am getting users who have both suspended and active ad accounts.
So please can you help out here.

Thanks and regards,
Soumik Das

[PremMahadikar]

Hi @soumik_das ,

Can you try below query:

SELECT 
    u.username, 
    u.createdate, 
    u.startdate AS 'Start Date', 
    u.enddate AS 'End Date', 
    u.firstname, 
    u.lastname, 
    u.displayname AS 'Display Name', 
    u.email, 
    u.customproperty11, 
    u.customproperty6, 
    u.customproperty13, 
    u.customproperty1,  
    u.customproperty2,  
    u.customproperty5,  
    u.customproperty3, 
    u.departmentname, 
    u.jobdescription, 
    u.Country, 
    u.statuskey, 
    u.entity, 
    u.orgunitid, 
    u.employeetype ,
	a.name, 
	a.status
FROM 
    users u inner join user_accounts ua on ua.userkey=u.userkey
	inner join accounts a ON ua.accountkey = a.accountkey
	inner join endpoints e ON a.endpointkey = e.endpointkey
where
	u.statuskey=1
    AND e.endpointname = 'Active Directory'
    AND a.status NOT IN ('Active','1','Manually Provisioned')

If this helps your question, please consider selecting Accept As Solution and hit Kudos

[soumik_das]

Hi @PremMahadikar ,

I tried your query in data analyzer but very sorry to say  I am getting accounts with both active and suspended from import service status.

Please refer to the image above for a user which came up on data anlayzer following your query.
Please if you can suggest any more tweaks.

Thanks and regards,
Soumik Das

[rushikeshvartak]

What are account status you need in report ?

[soumik_das]

Hi @rushikeshvartak ,

I need users who have only inactive or suspended from import service ad accounts. But I am getting users with both inactive or suspended from import service ad accounts and active ones too.

Thanks and regards,
Soumik Das

[rushikeshvartak · Accepted solution]

SELECT u.username,
       u.createdate,
       u.startdate   AS 'Start Date',
       u.enddate     AS 'End Date',
       u.firstname,
       u.lastname,
       u.displayname AS 'Display Name',
       u.email,
       u.customproperty11,
       u.customproperty6,
       u.customproperty13,
       u.customproperty1,
       u.customproperty2,
       u.customproperty5,
       u.customproperty3,
       u.departmentname,
       u.jobdescription,
       u.country,
       u.statuskey,
       u.entity,
       u.orgunitid,
       u.employeetype,
       a.NAME,
       a.status
FROM   users u
       INNER JOIN user_accounts ua
               ON ua.userkey = u.userkey
       INNER JOIN accounts a
               ON ua.accountkey = a.accountkey
       INNER JOIN endpoints e
               ON a.endpointkey = e.endpointkey
WHERE  u.statuskey = 1
       AND e.endpointname = 'Active Directory'
       AND u.userkey NOT IN (SELECT ua.userkey
                             FROM   user_accounts ua
                                    INNER JOIN accounts a
                                            ON ua.accountkey = a.accountkey
                                    INNER JOIN endpoints e
                                            ON a.endpointkey = e.endpointkey
                             WHERE  e.endpointname = 'Active Directory'
                                    AND a.status IN ( 'Active', '1',
                                                      'Manually Provisioned'
                                                    ))
       AND a.status NOT IN ( 'Active', '1', 'Manually Provisioned' ); 

[soumik_das]

Thank you very much @rushikeshvartak