Analytics Report to Revoke Access

[ejeong]

We have requirement to make Remove Access task by checking Last Login date from another endpoint? is this feasible? 

If datediff is greater than 90 days in Endpoint A, then we need to create remove access tasks for Endpoint B. Could you please guide us how to make query for this?

[sundas7]

Hi,

Can you please check this query and tweak based on your requirement.

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', case when (a.status=1) then 'Active' else a.status end AS 'Account Status' FROM accounts a, user_accounts ua, users u, endpoints e where ua.accountkey = a.accountkey and u.userkey = ua.userkey and a.endpointkey = a.endpointkey and a.status IN ('1' , 'Manually Provisioned') AND DATEDIFF(CURDATE(), lastlogondate) >= 60 AND lastlogondate is not null;

Thanks

Shyam

[ejeong]

Thanks for your feedback. I am aware of this query and using it already.. 

I want to create revoke access tasks based on datediff in another endpoint endpoint 

[ejeong]

This is query I am trying to do but it didn't work as I expected. 

select ev.entitlement_valuekey as entvaluekey, a.accountkey as acctKey,a.name as accName,u.userKey,'Deprovision Access' as 'Default_Action_For_Analytics'
from entitlement_values ev, accounts a,user_accounts ua,users u,endpoints e,account_entitlements1 ae1
where
A.ENDPOINTKEY = E.ENDPOINTKEY
AND ua.accountkey = a.accountkey
AND u.userkey = ua.userkey
AND ae1.accountkey = a.accountkey
AND ae1.entitlement_valuekey = ev.entitlement_valuekey
AND u.email like '%abc.com%'
and e.endpointkey= 1
and u.userkey in (select ua.userkey from user_accounts ua,accounts a,users u where ua.accountkey = a.accountkey
AND u.userkey = ua.userkey and datediff(curdate(),lastlogon) > 90)
and ev.customproperty2 in (select e.endpointname from entitlement_values ev, accounts a,user_accounts ua,users u,endpoints e,account_entitlements1 ae1 where A.ENDPOINTKEY = E.ENDPOINTKEY
AND ua.accountkey = a.accountkey
AND u.userkey = ua.userkey
AND ae1.accountkey = a.accountkey
AND ae1.entitlement_valuekey = ev.entitlement_valuekey and datediff(curdate(),lastlogon) > 90)

[rushikeshvartak]

what is wrong in output in query.  for other endpoint where is lastlogin stored? how to identify ?

[ejeong]

Lastlogondate for endpointkey = 1

Make deprovision access for endpointkey = 2

We want to revoke access all if this meet condition like below

Ev.customproperty2 = endpointname for endpointkey1

[rushikeshvartak]

SELECT distinct a.name AS AccountName, U.USERNAME, u.FirstName, u.LastName, A.STATUS AS AccountStatus, a.accountkey AS acctKey,ev.entitlement_valuekey AS entvaluekey, 'Active_Directory_Remove_Access' as Application, 'Deprovision Access' AS Default_Action_For_Analytics FROM users u, user_accounts ua, accounts a, endpoints ep,entitlement_types et,account_entitlements1 ae1,entitlement_values ev WHERE u.userkey = ua.userkey AND ua.accountkey = a.accountkey AND a.endpointkey = ep.endpointkey AND a.accountkey=ae1.accountkey AND ae1.entitlement_valuekey=ev.entitlement_valuekey AND ep.endpointname = '<EndpointNameTaskToBeCreated>' AND a.name in ( select pa.name from accounts pa,endpoints ep where ep.endpointkey=pa.endpointkey and a.status IN ('1' , 'Manually Provisioned') and DATEDIFF(CURDATE(), lastlogondate) >= 90 AND lastlogondate is not null and ev.customproperty2=ep.endpointname)

[ejeong]

Thanks for your response! I thnk I didn't explain clearly about requirement. And I made one query like below and there is no data found.. 

select ev.entitlement_valuekey as entvaluekey, a.accountkey as acctKey,a.name as accName,u.userKey,'Deprovision Access' as 'Default_Action_For_Analytics'
from entitlement_values ev, accounts a,user_accounts ua,users u,endpoints e,account_entitlements1 ae1
where
A.ENDPOINTKEY = E.ENDPOINTKEY
AND ua.accountkey = a.accountkey
AND u.userkey = ua.userkey
AND ae1.accountkey = a.accountkey
AND e.endpointkey = 477
AND ae1.entitlement_valuekey = ev.entitlement_valuekey
and u.userkey in (select ua.userkey from user_accounts ua,accounts a,users u where ua.accountkey = a.accountkey
AND u.userkey = ua.userkey and datediff(curdate(),a.lastlogondate) > 90 and a.endpointkey = 1303)
and ev.customproperty2 in (select SUBSTRING_INDEX(SUBSTRING_INDEX(e.endpointname,'_', 1),'_', -1) from entitlement_values ev, accounts a,user_accounts ua,users u,endpoints e,account_entitlements1 ae1 where A.ENDPOINTKEY = E.ENDPOINTKEY
AND ua.accountkey = a.accountkey
AND u.userkey = ua.userkey
AND ae1.accountkey = a.accountkey
AND ae1.entitlement_valuekey = ev.entitlement_valuekey and datediff(curdate(),a.lastlogondate) > 90 and a.endpointkey = 1303)

Basically, we have endpoint 1303 and endpoint 477 

We want to check lastlogindate in endpont 1303 

and make revoke access all entitlement that has endpointname in ev.customproperty2

for example, I made test data in dev like below 

And we have appName in custom property 2 of entitlement_value 

As I can't can't make endpoint with same name, I put suffix '_IC' and substring this in query to make it same as ev.customproperty2

ev.customproperty2 in (select SUBSTRING_INDEX(SUBSTRING_INDEX(e.endpointname,'_', 1),'_', -1)

[ejeong]

entitlements customproperty2 in another endpoint has endpoint name for one endpoint with last login date, so I added follwoing condition at the end 

and ev.customproperty2 in (select e.endpointname from entitlement_values ev, accounts a,user_accounts ua,users u,endpoints e,account_entitlements1 ae1 where A.ENDPOINTKEY = E.ENDPOINTKEY
AND ua.accountkey = a.accountkey
AND u.userkey = ua.userkey
AND ae1.accountkey = a.accountkey
AND ae1.entitlement_valuekey = ev.entitlement_valuekey and datediff(curdate(),lastlogon) > 90)

[sundas7]

Hi ,

I just reconstructed the highlighted part of your query and tested. I can see there is a lastlogondate in accounts table.

Can you please check if this helps.

select e.endpointname from entitlement_values ev, accounts a,user_accounts ua,users u,endpoints e,account_entitlements1 ae1 where A.ENDPOINTKEY = E.ENDPOINTKEY
AND ua.accountkey = a.accountkey
AND u.userkey = ua.userkey
AND ae1.accountkey = a.accountkey
AND ae1.entitlement_valuekey = ev.entitlement_valuekey and datediff(curdate(),lastlogon) > 90

select acc.lastlogondate AS 'Last Logon',ev.entitlement_value,ep.endpointname as "ENDPOINTNAME", u.username as "USERNAME"
from users u
left join user_Accounts ua on
u.userkey = ua.userkey
left join accounts acc on
acc.accountkey = ua.accountkey
left join account_entitlements1 ae1 on
ae1.accountkey=acc.accountkey
left join entitlement_values ev on
ae1.accountkey=ev.entitlement_valuekey
left join endpoints ep on
ep.endpointkey = acc.endpointkey
AND DATEDIFF(CURDATE(), lastlogondate) >= 90;

Thanks

Shyam

[ejeong]

I made query like below 

select ev.entitlement_valuekey as entvaluekey, a.accountkey as acctKey,a.name as accName,u.userKey,'Deprovision Access' as 'Default_Action_For_Analytics'
from entitlement_values ev, accounts a,user_accounts ua,users u,endpoints e,account_entitlements1 ae1
where
A.ENDPOINTKEY = E.ENDPOINTKEY
AND ua.accountkey = a.accountkey
AND u.userkey = ua.userkey
AND ae1.accountkey = a.accountkey
AND e.endpointkey = 477
AND ae1.entitlement_valuekey = ev.entitlement_valuekey
and u.userkey in (select ua.userkey from user_accounts ua,accounts a,users u where ua.accountkey = a.accountkey
AND u.userkey = ua.userkey and datediff(curdate(),a.lastlogondate) > 90 and a.endpointkey = 1303)
and ev.customproperty2 in (select SUBSTRING_INDEX(SUBSTRING_INDEX(e.endpointname,'_', 1),'_', -1) from entitlement_values ev, accounts a,user_accounts ua,users u,endpoints e,account_entitlements1 ae1 where A.ENDPOINTKEY = E.ENDPOINTKEY
AND ua.accountkey = a.accountkey
AND u.userkey = ua.userkey
AND ae1.accountkey = a.accountkey
AND ae1.entitlement_valuekey = ev.entitlement_valuekey and datediff(curdate(),a.lastlogondate) > 90 and a.endpointkey = 1303)

But I found no data found message even if I have one test data I created. 

Basically, I want to check lastlogindate in endpointkey = 1303 and remove all access that has that endpoint name in endpointkey = 477

[ejeong]

I resolved this issue. 

[Dave]

@ejeong - What is the solution for others who may have a similar question?