Analytics Provision Access

[jezzanuena]

Hi! We created an analytic to provision access using below query:

Spoiler

SELECT DISTINCT 'Provision Access' AS Default_Action_For_Analytics,
u.username,
a.NAME AS accName,
e.displayname,
ev.entitlement_valuekey AS entvaluekey,
ev.entitlement_value AS entitlement_value,
a.accountkey AS 'acctKey',
u.userkey AS userKey,
Now() AS runDate
FROM users u,
accounts a,
user_accounts ua,
entitlement_values ev,
entitlement_types et,
endpoints e
WHERE ua.accountkey = a.accountkey
AND ua.userkey = u.userkey
AND ev.entitlementtypekey = et.entitlementtypekey
AND et.endpointkey = e.endpointkey
AND e.endpointkey = a.endpointkey
AND u.statuskey = 1
AND a.status IN ( '1', 'Manually Provisioned' )
AND a.NAME LIKE 'e%'
AND u.userkey IN (SELECT ua1.userkey
FROM user_accounts ua1,
accounts a1
WHERE ua1.accountkey = a1.accountkey
AND a1.endpointkey = 7
AND a1.NAME LIKE 's%'
AND a1.status IN (
1, 'Active', 'Manually Provisioned'
))
AND ( u.orgunitid IS NOT NULL
AND e.endpointname = 'Active Directory US'
AND ( ev.entitlement_value =
'CN=Cyberark - End User'
AND a.accountkey NOT IN (SELECT ae.accountkey
FROM account_entitlements1 ae
INNER JOIN entitlement_values ev
ON ev.entitlement_valuekey = ae.entitlement_valuekey
INNER JOIN entitlement_types et
ON et.endpointkey = (SELECT endpointkey
FROM endpoints
WHERE
endpointname = 'Active Directory US')
WHERE ev.entitlement_value = 'Cyberark - End User'
)
) )
GROUP BY u.username,
a.NAME,
ev.entitlement_valuekey
UNION
SELECT DISTINCT 'Provision Access' AS Default_Action_For_Analytics,
u.username,
a.NAME AS accName,
e.displayname,
ev.entitlement_valuekey AS entvaluekey,
ev.entitlement_value AS entitlement_value,
a.accountkey AS 'acctKey',
u.userkey AS userKey,
Now() AS runDate
FROM users u,
accounts a,
user_accounts ua,
entitlement_values ev,
entitlement_types et,
endpoints e
WHERE ua.accountkey = a.accountkey
AND ua.userkey = u.userkey
AND ev.entitlementtypekey = et.entitlementtypekey
AND et.endpointkey = e.endpointkey
AND e.endpointkey = a.endpointkey
AND u.statuskey = 1
AND a.status IN ( '1', 'Manually Provisioned' )
AND a.NAME LIKE 'e%'
AND u.userkey IN (SELECT ua1.userkey
FROM user_accounts ua1,
accounts a1
WHERE ua1.accountkey = a1.accountkey
AND a1.endpointkey = 207
AND a1.NAME LIKE 's%'
AND a1.status IN (
1, 'Active', 'Manually Provisioned'
))
AND ( u.orgunitid IS NOT NULL
AND e.endpointname = 'Active Directory Canada'
AND ( ev.entitlement_value = 'T_CyberArk_Admins'
AND a.accountkey NOT IN (SELECT ae.accountkey
FROM account_entitlements1 ae
INNER JOIN entitlement_values
ev
ON ev.entitlement_valuekey =
ae.entitlement_valuekey
INNER JOIN entitlement_types et
ON et.endpointkey = (SELECT endpointkey
FROM
endpoints
WHERE
endpointname =
'Active Directory (Medisna)')
WHERE
ev.entitlement_value = 'T_CyberArk_Admins'
) ) )
GROUP BY u.username,
a.NAME,
ev.entitlement_valuekey

But when we run the job wherein Execute Default Action for Analytics is ON, the task is not getting created. IIs there something we missed? Thank you in advance.

[NM · Accepted solution]

Hi @jezzanuena , delete all existing report from analytics history and then try running it.

[Raghu · Accepted solution]

@jezzanuena  delete job and again create it.

[jezzanuena]

Hi @Raghu  and @NM thank you for checking on this. It worked. I did both of your suggestions. Thank you so much!