Modules
Browse queries by module.
All Modules
ARS Requests ARS Tasks Accounts Accounts, Endpoint Accounts, Endpoints Analytics Audit Logs Audit Trail Campaign Certification Connections Connections, Job Control Panel Endpoints Job Control Panel Roles Rules Security Systems User Group Users Users, Accounts Users, Accounts, Endpoint Users, Entitlements Users, Roles Users, SAV ROLESTask that got finished after Multiple Retries
Module: ARS Tasks
Description
Get all tasks that Task that got finished after multiple retries- provisioning tries are not null
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT
ar.taskkey,
ar.accountname,
ar.endpoint as Endpoint_Key,
e.endpointname,
CASE
WHEN ar.tasktype = 1 THEN 'Add Access'
WHEN ar.tasktype = 2 THEN 'Remove Access'
WHEN ar.tasktype = 3 THEN 'New Account'
WHEN ar.tasktype = 4 THEN 'New Role Request'
WHEN ar.tasktype = 5 THEN 'Change Password'
WHEN ar.tasktype = 6 THEN 'Enable Account'
WHEN ar.tasktype = 7 THEN 'Proposed Account Owners'
WHEN ar.tasktype = 8 THEN 'Delete Account'
WHEN ar.tasktype = 9 THEN 'Update User'
WHEN ar.tasktype = 12 THEN 'Update Account'
WHEN ar.tasktype = 13 THEN 'Proposed Entitlement Owners'
WHEN ar.tasktype = 14 THEN 'Disable Account'
WHEN ar.tasktype = 23 THEN 'Modify Privilege'
WHEN ar.tasktype = 24 THEN 'Create Entitlement'
WHEN ar.tasktype = 25 THEN 'Update Entitlement- Add Access'
WHEN ar.tasktype = 26 THEN 'Update Entitlement- Remove Access'
WHEN ar.tasktype = 27 THEN 'Update Entitlement'
WHEN ar.tasktype = 28 THEN 'Delete Entitlement'
WHEN ar.tasktype = 29 THEN 'Fire Fighter ID Grant Access'
WHEN ar.tasktype = 30 THEN 'Fire Fighter ID Revoke Access'
WHEN ar.tasktype = 31 THEN 'Extend Access- Update Access End Date'
WHEN ar.tasktype = 32 THEN 'Lock Account'
WHEN ar.tasktype = 33 THEN 'Unlock Account'
WHEN ar.tasktype = 34 THEN 'Fire Fighter Instance Grant Access'
WHEN ar.tasktype = 35 THEN 'Fire Fighter Instance Revoke Access'
ELSE 'Unknown Task Type'
END AS Task_Type,
CASE
WHEN ar.status = 1 THEN 'New'
WHEN ar.status = 2 THEN 'In Progress'
WHEN ar.status = 3 THEN 'Complete'
WHEN ar.status = 4 THEN 'Discontinue'
WHEN ar.status = 5 THEN 'Pending Create'
WHEN ar.status = 6 THEN 'Pending Provisioning'
WHEN ar.status = 7 THEN 'Provisioning Failed'
WHEN ar.status = 8 THEN 'Error'
WHEN ar.status = 9 THEN 'No Action Required'
ELSE 'Unknown Status'
END AS Task_Status,
ar.provisioningTries,
ar.provisioningcomments AS Provisioning_Comments,
ar.startDate,
ar.taskdate,
ar.updateDate,
u.username AS BENEFICIARY_USER
FROM arstasks ar
LEFT JOIN users u
ON u.userkey = ar.userkey
LEFT JOIN endpoints e
ON ar.endpoint = e.endpointkey
WHERE ar.status = 3
AND ar.provisioningTries IS NOT NULL
AND ar.provisioningTries > 1
ORDER BY ar.updateDate DESC;
Comments
nan
Provisioning Failed Tasks
Module: ARS Tasks
Description
Get all tasks that has status as Provisioning Failed
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT
ar.taskkey,
CASE
WHEN ar.tasktype = 1 THEN 'Add Access'
WHEN ar.tasktype = 2 THEN 'Remove Access'
WHEN ar.tasktype = 3 THEN 'New Account'
WHEN ar.tasktype = 4 THEN 'New Role Request'
WHEN ar.tasktype = 5 THEN 'Change Password'
WHEN ar.tasktype = 6 THEN 'Enable Account'
WHEN ar.tasktype = 7 THEN 'Proposed Account Owners'
WHEN ar.tasktype = 8 THEN 'Delete Account'
WHEN ar.tasktype = 9 THEN 'Update User'
WHEN ar.tasktype = 12 THEN 'Update Account'
WHEN ar.tasktype = 13 THEN 'Proposed Entitlement Owners'
WHEN ar.tasktype = 14 THEN 'Disable Account'
WHEN ar.tasktype = 23 THEN 'Modify Privilege'
WHEN ar.tasktype = 24 THEN 'Create Entitlement'
WHEN ar.tasktype = 25 THEN 'Update Entitlement- Add Access'
WHEN ar.tasktype = 26 THEN 'Update Entitlement- Remove Access'
WHEN ar.tasktype = 27 THEN 'Update Entitlement'
WHEN ar.tasktype = 28 THEN 'Delete Entitlement'
WHEN ar.tasktype = 29 THEN 'Fire Fighter ID Grant Access'
WHEN ar.tasktype = 30 THEN 'Fire Fighter ID Revoke Access'
WHEN ar.tasktype = 31 THEN 'Extend Access- Update Access End Date'
WHEN ar.tasktype = 32 THEN 'Lock Account'
WHEN ar.tasktype = 33 THEN 'Unlock Account'
WHEN ar.tasktype = 34 THEN 'Fire Fighter Instance Grant Access'
WHEN ar.tasktype = 35 THEN 'Fire Fighter Instance Revoke Access'
ELSE 'Unknown Task Type'
END AS Task_Type,
ar.userkey,
u.username AS Username,
ar.accountname AS Account_Name,
ar.endpoint,
e.endpointname AS Endpoint_Name,
CASE
WHEN ar.status = 1 THEN 'New'
WHEN ar.status = 2 THEN 'In Progress'
WHEN ar.status = 3 THEN 'Complete'
WHEN ar.status = 4 THEN 'Discontinue'
WHEN ar.status = 5 THEN 'Pending Create'
WHEN ar.status = 6 THEN 'Pending Provisioning'
WHEN ar.status = 7 THEN 'Provisioning Failed'
WHEN ar.status = 8 THEN 'Error'
WHEN ar.status = 9 THEN 'No Action Required'
ELSE 'Unknown Status'
END AS Task_Status,
ar.provisioningtries AS Provisioning_Tries,
ar.provisioningcomments AS Provisioning_Comments,
CASE
WHEN ar.comments IS NOT NULL AND ar.comments != ''
THEN REPLACE(REPLACE(REPLACE(ar.comments, '<span class="busjustformat">', ''), '</span>', ''), '<br>', CHAR(10))
ELSE NULL
END AS Comments,
ar.taskdate,
ar.startdate,
ar.updatedate,
ar.enddate
FROM arstasks ar
LEFT JOIN users u ON ar.userkey = u.userkey
LEFT JOIN endpoints e ON ar.endpoint = e.endpointkey
LEFT JOIN accounts a ON ar.accountname = a.name AND ar.endpoint = a.endpointkey
WHERE ar.status = 7
ORDER BY ar.taskdate DESC;
Comments
nan
Discontinued Tasks
Module: ARS Tasks
Description
Get all tasks that has status as Discontinue
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT
ar.taskkey,
CASE
WHEN ar.tasktype = 1 THEN 'Add Access'
WHEN ar.tasktype = 2 THEN 'Remove Access'
WHEN ar.tasktype = 3 THEN 'New Account'
WHEN ar.tasktype = 4 THEN 'New Role Request'
WHEN ar.tasktype = 5 THEN 'Change Password'
WHEN ar.tasktype = 6 THEN 'Enable Account'
WHEN ar.tasktype = 7 THEN 'Proposed Account Owners'
WHEN ar.tasktype = 8 THEN 'Delete Account'
WHEN ar.tasktype = 9 THEN 'Update User'
WHEN ar.tasktype = 12 THEN 'Update Account'
WHEN ar.tasktype = 13 THEN 'Proposed Entitlement Owners'
WHEN ar.tasktype = 14 THEN 'Disable Account'
WHEN ar.tasktype = 23 THEN 'Modify Privilege'
WHEN ar.tasktype = 24 THEN 'Create Entitlement'
WHEN ar.tasktype = 25 THEN 'Update Entitlement- Add Access'
WHEN ar.tasktype = 26 THEN 'Update Entitlement- Remove Access'
WHEN ar.tasktype = 27 THEN 'Update Entitlement'
WHEN ar.tasktype = 28 THEN 'Delete Entitlement'
WHEN ar.tasktype = 29 THEN 'Fire Fighter ID Grant Access'
WHEN ar.tasktype = 30 THEN 'Fire Fighter ID Revoke Access'
WHEN ar.tasktype = 31 THEN 'Extend Access- Update Access End Date'
WHEN ar.tasktype = 32 THEN 'Lock Account'
WHEN ar.tasktype = 33 THEN 'Unlock Account'
WHEN ar.tasktype = 34 THEN 'Fire Fighter Instance Grant Access'
WHEN ar.tasktype = 35 THEN 'Fire Fighter Instance Revoke Access'
ELSE 'Unknown Task Type'
END AS Task_Type,
ar.userkey,
u.username AS Username,
ar.accountname AS Account_Name,
ar.endpoint,
e.endpointname AS Endpoint_Name,
CASE
WHEN ar.status = 1 THEN 'New'
WHEN ar.status = 2 THEN 'In Progress'
WHEN ar.status = 3 THEN 'Complete'
WHEN ar.status = 4 THEN 'Discontinue'
WHEN ar.status = 5 THEN 'Pending Create'
WHEN ar.status = 6 THEN 'Pending Provisioning'
WHEN ar.status = 7 THEN 'Provisioning Failed'
WHEN ar.status = 8 THEN 'Error'
WHEN ar.status = 9 THEN 'No Action Required'
ELSE 'Unknown Status'
END AS Task_Status,
ar.provisioningtries AS Provisioning_Tries,
ar.provisioningcomments AS Provisioning_Comments,
CASE
WHEN ar.comments IS NOT NULL AND ar.comments != ''
THEN REPLACE(REPLACE(REPLACE(ar.comments, '<span class="busjustformat">', ''), '</span>', ''), '<br>', CHAR(10))
ELSE NULL
END AS Comments,
ar.taskdate,
ar.startdate,
ar.updatedate,
ar.enddate
FROM arstasks ar
LEFT JOIN users u ON ar.userkey = u.userkey
LEFT JOIN endpoints e ON ar.endpoint = e.endpointkey
LEFT JOIN accounts a ON ar.accountname = a.name AND ar.endpoint = a.endpointkey
WHERE ar.status = 4
ORDER BY ar.taskdate DESC;
Comments
nan
Tasks with No Action Required Status
Module: ARS Tasks
Description
Get all tasks that has status as No Action Required
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT
ar.taskkey,
CASE
WHEN ar.tasktype = 1 THEN 'Add Access'
WHEN ar.tasktype = 2 THEN 'Remove Access'
WHEN ar.tasktype = 3 THEN 'New Account'
WHEN ar.tasktype = 4 THEN 'New Role Request'
WHEN ar.tasktype = 5 THEN 'Change Password'
WHEN ar.tasktype = 6 THEN 'Enable Account'
WHEN ar.tasktype = 7 THEN 'Proposed Account Owners'
WHEN ar.tasktype = 8 THEN 'Delete Account'
WHEN ar.tasktype = 9 THEN 'Update User'
WHEN ar.tasktype = 12 THEN 'Update Account'
WHEN ar.tasktype = 13 THEN 'Proposed Entitlement Owners'
WHEN ar.tasktype = 14 THEN 'Disable Account'
WHEN ar.tasktype = 23 THEN 'Modify Privilege'
WHEN ar.tasktype = 24 THEN 'Create Entitlement'
WHEN ar.tasktype = 25 THEN 'Update Entitlement- Add Access'
WHEN ar.tasktype = 26 THEN 'Update Entitlement- Remove Access'
WHEN ar.tasktype = 27 THEN 'Update Entitlement'
WHEN ar.tasktype = 28 THEN 'Delete Entitlement'
WHEN ar.tasktype = 29 THEN 'Fire Fighter ID Grant Access'
WHEN ar.tasktype = 30 THEN 'Fire Fighter ID Revoke Access'
WHEN ar.tasktype = 31 THEN 'Extend Access- Update Access End Date'
WHEN ar.tasktype = 32 THEN 'Lock Account'
WHEN ar.tasktype = 33 THEN 'Unlock Account'
WHEN ar.tasktype = 34 THEN 'Fire Fighter Instance Grant Access'
WHEN ar.tasktype = 35 THEN 'Fire Fighter Instance Revoke Access'
ELSE 'Unknown Task Type'
END AS Task_Type,
ar.userkey,
u.username AS Username,
ar.accountname AS Account_Name,
ar.endpoint,
e.endpointname AS Endpoint_Name,
CASE
WHEN ar.status = 1 THEN 'New'
WHEN ar.status = 2 THEN 'In Progress'
WHEN ar.status = 3 THEN 'Complete'
WHEN ar.status = 4 THEN 'Discontinue'
WHEN ar.status = 5 THEN 'Pending Create'
WHEN ar.status = 6 THEN 'Pending Provisioning'
WHEN ar.status = 7 THEN 'Provisioning Failed'
WHEN ar.status = 8 THEN 'Error'
WHEN ar.status = 9 THEN 'No Action Required'
ELSE 'Unknown Status'
END AS Task_Status,
ar.provisioningtries AS Provisioning_Tries,
ar.provisioningcomments AS Provisioning_Comments,
CASE
WHEN ar.comments IS NOT NULL AND ar.comments != ''
THEN REPLACE(REPLACE(REPLACE(ar.comments, '<span class="busjustformat">', ''), '</span>', ''), '<br>', CHAR(10))
ELSE NULL
END AS Comments,
ar.taskdate,
ar.startdate,
ar.updatedate,
ar.enddate
FROM arstasks ar
LEFT JOIN users u ON ar.userkey = u.userkey
LEFT JOIN endpoints e ON ar.endpoint = e.endpointkey
LEFT JOIN accounts a ON ar.accountname = a.name AND ar.endpoint = a.endpointkey
WHERE ar.status = 9
ORDER BY ar.taskdate DESC;
Comments
nan
Tasks with Error Status
Module: ARS Tasks
Description
Get all tasks that got error out
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT
ar.taskkey,
CASE
WHEN ar.tasktype = 1 THEN 'Add Access'
WHEN ar.tasktype = 2 THEN 'Remove Access'
WHEN ar.tasktype = 3 THEN 'New Account'
WHEN ar.tasktype = 4 THEN 'New Role Request'
WHEN ar.tasktype = 5 THEN 'Change Password'
WHEN ar.tasktype = 6 THEN 'Enable Account'
WHEN ar.tasktype = 7 THEN 'Proposed Account Owners'
WHEN ar.tasktype = 8 THEN 'Delete Account'
WHEN ar.tasktype = 9 THEN 'Update User'
WHEN ar.tasktype = 12 THEN 'Update Account'
WHEN ar.tasktype = 13 THEN 'Proposed Entitlement Owners'
WHEN ar.tasktype = 14 THEN 'Disable Account'
WHEN ar.tasktype = 23 THEN 'Modify Privilege'
WHEN ar.tasktype = 24 THEN 'Create Entitlement'
WHEN ar.tasktype = 25 THEN 'Update Entitlement- Add Access'
WHEN ar.tasktype = 26 THEN 'Update Entitlement- Remove Access'
WHEN ar.tasktype = 27 THEN 'Update Entitlement'
WHEN ar.tasktype = 28 THEN 'Delete Entitlement'
WHEN ar.tasktype = 29 THEN 'Fire Fighter ID Grant Access'
WHEN ar.tasktype = 30 THEN 'Fire Fighter ID Revoke Access'
WHEN ar.tasktype = 31 THEN 'Extend Access- Update Access End Date'
WHEN ar.tasktype = 32 THEN 'Lock Account'
WHEN ar.tasktype = 33 THEN 'Unlock Account'
WHEN ar.tasktype = 34 THEN 'Fire Fighter Instance Grant Access'
WHEN ar.tasktype = 35 THEN 'Fire Fighter Instance Revoke Access'
ELSE 'Unknown Task Type'
END AS Task_Type,
ar.userkey,
u.username AS Username,
ar.accountname AS Account_Name,
ar.endpoint,
e.endpointname AS Endpoint_Name,
CASE
WHEN ar.status = 1 THEN 'New'
WHEN ar.status = 2 THEN 'In Progress'
WHEN ar.status = 3 THEN 'Complete'
WHEN ar.status = 4 THEN 'Discontinue'
WHEN ar.status = 5 THEN 'Pending Create'
WHEN ar.status = 6 THEN 'Pending Provisioning'
WHEN ar.status = 7 THEN 'Provisioning Failed'
WHEN ar.status = 8 THEN 'Error'
WHEN ar.status = 9 THEN 'No Action Required'
ELSE 'Unknown Status'
END AS Task_Status,
ar.provisioningtries AS Provisioning_Tries,
ar.provisioningcomments AS Provisioning_Comments,
CASE
WHEN ar.comments IS NOT NULL AND ar.comments != ''
THEN REPLACE(REPLACE(REPLACE(ar.comments, '<span class="busjustformat">', ''), '</span>', ''), '<br>', CHAR(10))
ELSE NULL
END AS Comments,
ar.taskdate,
ar.startdate,
ar.updatedate,
ar.enddate
FROM arstasks ar
LEFT JOIN users u ON ar.userkey = u.userkey
LEFT JOIN endpoints e ON ar.endpoint = e.endpointkey
LEFT JOIN accounts a ON ar.accountname = a.name AND ar.endpoint = a.endpointkey
WHERE ar.status = 8
ORDER BY ar.taskdate DESC;
Comments
nan
Completed tasks of Specific Task Type and Endpoint
Module: ARS Tasks
Description
Get provisioning comments, no. of provtries, comments etc from the completed tasks for an endpoint and specific tasktype
Dynamic Input
Endpoint Name and Task Type
Execute in
Data Analyzer, Analytics
SQL Query
SELECT
ar.taskkey,
ar.userkey,
u.username AS Username,
CASE
WHEN ar.tasktype = 1 THEN 'Add Access'
WHEN ar.tasktype = 2 AND ar.entitlement_valuekey IS NULL THEN 'Remove Account'
WHEN ar.tasktype = 2 AND ar.entitlement_valuekey IS NOT NULL THEN 'Remove Access'
WHEN ar.tasktype = 3 THEN 'New Account'
WHEN ar.tasktype = 4 THEN 'New Role Request'
WHEN ar.tasktype = 5 THEN 'Change Password'
WHEN ar.tasktype = 6 THEN 'Enable Account'
WHEN ar.tasktype = 7 THEN 'Proposed Account Owners'
WHEN ar.tasktype = 8 THEN 'Delete Account'
WHEN ar.tasktype = 9 THEN 'Update User'
WHEN ar.tasktype = 12 THEN 'Update Account'
WHEN ar.tasktype = 13 THEN 'Proposed Entitlement Owners'
WHEN ar.tasktype = 14 THEN 'Disable Account'
WHEN ar.tasktype = 23 THEN 'Modify Privilege'
WHEN ar.tasktype = 24 THEN 'Create Entitlement'
WHEN ar.tasktype = 25 THEN 'Update Entitlement- Add Access'
WHEN ar.tasktype = 26 THEN 'Update Entitlement- Remove Access'
WHEN ar.tasktype = 27 THEN 'Update Entitlement'
WHEN ar.tasktype = 28 THEN 'Delete Entitlement'
WHEN ar.tasktype = 29 THEN 'Fire Fighter ID Grant Access'
WHEN ar.tasktype = 30 THEN 'Fire Fighter ID Revoke Access'
WHEN ar.tasktype = 31 THEN 'Extend Access- Update Access End Date'
WHEN ar.tasktype = 32 THEN 'Lock Account'
WHEN ar.tasktype = 33 THEN 'Unlock Account'
WHEN ar.tasktype = 34 THEN 'Fire Fighter Instance Grant Access'
WHEN ar.tasktype = 35 THEN 'Fire Fighter Instance Revoke Access'
ELSE 'Unknown'
END AS Task_Type,
CASE
WHEN ar.status = 1 THEN 'New'
WHEN ar.status = 2 THEN 'In Progress'
WHEN ar.status = 3 THEN 'Complete'
WHEN ar.status = 4 THEN 'Discontinue'
WHEN ar.status = 5 THEN 'Pending Create'
WHEN ar.status = 6 THEN 'Pending Provisioning'
WHEN ar.status = 7 THEN 'Provisioning Failed'
WHEN ar.status = 8 THEN 'Error'
WHEN ar.status = 9 THEN 'No Action Required'
ELSE 'Unknown'
END AS Task_Status,
ar.endpoint AS Endpoint_Key,
e.endpointname AS Endpoint_Name,
ar.accountname AS Account_Name,
ar.taskdate,
ar.startdate,
ar.updatedate,
ar.provisioningcomments AS Provisioning_Comments,
ar.provisioningtries AS Provisioning_Tries,
CASE
WHEN ar.comments IS NOT NULL AND ar.comments != ''
THEN REPLACE(REPLACE(REPLACE(ar.comments, '<span class="busjustformat">', ''), '</span>', ''), '<br>', CHAR(10))
ELSE NULL
END AS Comments
FROM arstasks ar
JOIN users u ON ar.userkey = u.userkey
JOIN endpoints e ON ar.endpoint = e.endpointkey
WHERE ar.status=3
AND e.endpointname LIKE '%AWSl100%'
AND ar.tasktype=1
ORDER BY ar.taskdate DESC;
Comments
nan
Pending tasks of Specific Task Type and Endpoint
Module: ARS Tasks
Description
Get provisioning comments, no. of provtries, comments etc from the pending tasks for an endpoint and specific tasktype
Dynamic Input
Endpoint Name and Task Type
Execute in
Data Analyzer, Analytics
SQL Query
SELECT
ar.taskkey,
ar.userkey,
u.username AS Username,
CASE
WHEN ar.tasktype = 1 THEN 'Add Access'
WHEN ar.tasktype = 2 AND ar.entitlement_valuekey IS NULL THEN 'Remove Account'
WHEN ar.tasktype = 2 AND ar.entitlement_valuekey IS NOT NULL THEN 'Remove Access'
WHEN ar.tasktype = 3 THEN 'New Account'
WHEN ar.tasktype = 4 THEN 'New Role Request'
WHEN ar.tasktype = 5 THEN 'Change Password'
WHEN ar.tasktype = 6 THEN 'Enable Account'
WHEN ar.tasktype = 7 THEN 'Proposed Account Owners'
WHEN ar.tasktype = 8 THEN 'Delete Account'
WHEN ar.tasktype = 9 THEN 'Update User'
WHEN ar.tasktype = 12 THEN 'Update Account'
WHEN ar.tasktype = 13 THEN 'Proposed Entitlement Owners'
WHEN ar.tasktype = 14 THEN 'Disable Account'
WHEN ar.tasktype = 23 THEN 'Modify Privilege'
WHEN ar.tasktype = 24 THEN 'Create Entitlement'
WHEN ar.tasktype = 25 THEN 'Update Entitlement- Add Access'
WHEN ar.tasktype = 26 THEN 'Update Entitlement- Remove Access'
WHEN ar.tasktype = 27 THEN 'Update Entitlement'
WHEN ar.tasktype = 28 THEN 'Delete Entitlement'
WHEN ar.tasktype = 29 THEN 'Fire Fighter ID Grant Access'
WHEN ar.tasktype = 30 THEN 'Fire Fighter ID Revoke Access'
WHEN ar.tasktype = 31 THEN 'Extend Access- Update Access End Date'
WHEN ar.tasktype = 32 THEN 'Lock Account'
WHEN ar.tasktype = 33 THEN 'Unlock Account'
WHEN ar.tasktype = 34 THEN 'Fire Fighter Instance Grant Access'
WHEN ar.tasktype = 35 THEN 'Fire Fighter Instance Revoke Access'
ELSE 'Unknown'
END AS Task_Type,
CASE
WHEN ar.status = 1 THEN 'New'
WHEN ar.status = 2 THEN 'In Progress'
WHEN ar.status = 3 THEN 'Complete'
WHEN ar.status = 4 THEN 'Discontinue'
WHEN ar.status = 5 THEN 'Pending Create'
WHEN ar.status = 6 THEN 'Pending Provisioning'
WHEN ar.status = 7 THEN 'Provisioning Failed'
WHEN ar.status = 8 THEN 'Error'
WHEN ar.status = 9 THEN 'No Action Required'
ELSE 'Unknown'
END AS Task_Status,
ar.endpoint AS Endpoint_Key,
e.endpointname AS Endpoint_Name,
ar.accountname AS Account_Name,
ar.taskdate,
ar.startdate,
ar.updatedate,
ar.provisioningcomments AS Provisioning_Comments,
ar.provisioningtries AS Provisioning_Tries,
CASE
WHEN ar.comments IS NOT NULL AND ar.comments != ''
THEN REPLACE(REPLACE(REPLACE(ar.comments, '<span class="busjustformat">', ''), '</span>', ''), '<br>', CHAR(10))
ELSE NULL
END AS Comments
FROM arstasks ar
JOIN users u ON ar.userkey = u.userkey
JOIN endpoints e ON ar.endpoint = e.endpointkey
WHERE ar.status IN (1)
AND e.endpointname LIKE '%AWSl100%'
AND ar.tasktype=1
ORDER BY ar.taskdate DESC;
Comments
nan
Future Dated Tasks
Module: ARS Tasks
Description
Get future dated tasks
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT
ar.taskkey,
CASE
WHEN ar.tasktype = 1 THEN 'Add Access'
WHEN ar.tasktype = 2 AND ar.entitlement_valuekey IS NULL THEN 'Remove Account'
WHEN ar.tasktype = 2 AND ar.entitlement_valuekey IS NOT NULL THEN 'Remove Access'
WHEN ar.tasktype = 3 THEN 'New Account'
WHEN ar.tasktype = 4 THEN 'New Role Request'
WHEN ar.tasktype = 5 THEN 'Change Password'
WHEN ar.tasktype = 6 THEN 'Enable Account'
WHEN ar.tasktype = 7 THEN 'Proposed Account Owners'
WHEN ar.tasktype = 8 THEN 'Delete Account'
WHEN ar.tasktype = 9 THEN 'Update User'
WHEN ar.tasktype = 12 THEN 'Update Account'
WHEN ar.tasktype = 13 THEN 'Proposed Entitlement Owners'
WHEN ar.tasktype = 14 THEN 'Disable Account'
WHEN ar.tasktype = 23 THEN 'Modify Privilege'
WHEN ar.tasktype = 24 THEN 'Create Entitlement'
WHEN ar.tasktype = 25 THEN 'Update Entitlement- Add Access'
WHEN ar.tasktype = 26 THEN 'Update Entitlement- Remove Access'
WHEN ar.tasktype = 27 THEN 'Update Entitlement'
WHEN ar.tasktype = 28 THEN 'Delete Entitlement'
WHEN ar.tasktype = 29 THEN 'Fire Fighter ID Grant Access'
WHEN ar.tasktype = 30 THEN 'Fire Fighter ID Revoke Access'
WHEN ar.tasktype = 31 THEN 'Extend Access- Update Access End Date'
WHEN ar.tasktype = 32 THEN 'Lock Account'
WHEN ar.tasktype = 33 THEN 'Unlock Account'
WHEN ar.tasktype = 34 THEN 'Fire Fighter Instance Grant Access'
WHEN ar.tasktype = 35 THEN 'Fire Fighter Instance Revoke Access'
ELSE 'UNKNOWN'
END AS Task_Type,
CASE
WHEN ar.status = 1 THEN 'New'
WHEN ar.status = 2 THEN 'In Progress'
WHEN ar.status = 3 THEN 'Complete'
WHEN ar.status = 4 THEN 'Discontinue'
WHEN ar.status = 5 THEN 'Pending Create'
WHEN ar.status = 6 THEN 'Pending Provisioning'
WHEN ar.status = 7 THEN 'Provisioning Failed'
WHEN ar.status = 8 THEN 'Error'
WHEN ar.status = 9 THEN 'No Action Required'
ELSE 'Unknown Status'
END AS Task_Status,
ar.startdate,
NOW() AS Current_DateTime,
CONCAT(
FLOOR(TIMESTAMPDIFF(SECOND, NOW(), ar.startdate) / 86400), ' Days ', ' , ',
SEC_TO_TIME(MOD(TIMESTAMPDIFF(SECOND, NOW(), ar.startdate), 86400))
) AS Days_Until_Start,
ar.taskdate,
ar.updatedate,
ar.userkey,
u.username,
ar.endpoint AS Endpoint_Key,
e.endpointname AS Endpoint_Name,
ar.accountname AS Account_Name
FROM arstasks ar
LEFT JOIN users u
ON ar.userkey = u.userkey
LEFT JOIN endpoints e
ON ar.endpoint = e.endpointkey
WHERE ar.startdate > NOW()
ORDER BY ar.startdate ASC;
Comments
nan
Tasks Triggered for a User
Module: ARS Tasks
Description
Get all pending and completed tasks that got triggered or executed for a user on a particular endpoint
Dynamic Input
Endpoint Name, User Name
Execute in
Data Analyzer, Analytics
SQL Query
SELECT
ar.taskkey,
CASE
WHEN ar.tasktype = 1 THEN 'Add Access'
WHEN ar.tasktype = 2 AND ar.entitlement_valuekey IS NULL THEN 'Remove Account'
WHEN ar.tasktype = 2 AND ar.entitlement_valuekey IS NOT NULL THEN 'Remove Access'
WHEN ar.tasktype = 3 THEN 'New Account'
WHEN ar.tasktype = 4 THEN 'New Role Request'
WHEN ar.tasktype = 5 THEN 'Change Password'
WHEN ar.tasktype = 6 THEN 'Enable Account'
WHEN ar.tasktype = 7 THEN 'Proposed Account Owners'
WHEN ar.tasktype = 8 THEN 'Delete Account'
WHEN ar.tasktype = 9 THEN 'Update User'
WHEN ar.tasktype = 12 THEN 'Update Account'
WHEN ar.tasktype = 13 THEN 'Proposed Entitlement Owners'
WHEN ar.tasktype = 14 THEN 'Disable Account'
WHEN ar.tasktype = 23 THEN 'Modify Privilege'
WHEN ar.tasktype = 24 THEN 'Create Entitlement'
WHEN ar.tasktype = 25 THEN 'Update Entitlement- Add Access'
WHEN ar.tasktype = 26 THEN 'Update Entitlement- Remove Access'
WHEN ar.tasktype = 27 THEN 'Update Entitlement'
WHEN ar.tasktype = 28 THEN 'Delete Entitlement'
WHEN ar.tasktype = 29 THEN 'Fire Fighter ID Grant Access'
WHEN ar.tasktype = 30 THEN 'Fire Fighter ID Revoke Access'
WHEN ar.tasktype = 31 THEN 'Extend Access- Update Access End Date'
WHEN ar.tasktype = 32 THEN 'Lock Account'
WHEN ar.tasktype = 33 THEN 'Unlock Account'
WHEN ar.tasktype = 34 THEN 'Fire Fighter Instance Grant Access'
WHEN ar.tasktype = 35 THEN 'Fire Fighter Instance Revoke Access'
ELSE 'UNKNOWN'
END AS Task_Type,
CASE
WHEN ar.status = 1 THEN 'New'
WHEN ar.status = 2 THEN 'In Progress'
WHEN ar.status = 3 THEN 'Complete'
WHEN ar.status = 4 THEN 'Discontinue'
WHEN ar.status = 5 THEN 'Pending Create'
WHEN ar.status = 6 THEN 'Pending Provisioning'
WHEN ar.status = 7 THEN 'Provisioning Failed'
WHEN ar.status = 8 THEN 'Error'
WHEN ar.status = 9 THEN 'No Action Required'
ELSE 'Unknown'
END AS Task_Status,
ar.userkey,
u.username AS Username,
ar.accountname as Account_Name,
CASE
WHEN u.statuskey = 0 THEN 'Inactive'
WHEN u.statuskey = 1 THEN 'Active'
ELSE 'Unknown'
END AS User_Status,
ar.endpoint AS Endpoint_Key,
e.endpointname AS Endpoint_Name,
ar.taskdate
FROM arstasks ar
JOIN users u ON ar.userkey = u.userkey
JOIN endpoints e ON ar.endpoint = e.endpointkey
JOIN user_accounts ua ON ar.userkey = ua.userkey AND ar.endpoint = e.endpointkey
JOIN accounts a ON ua.accountkey = a.accountkey AND a.endpointkey = ar.endpoint
WHERE ar.status IN (1, 3, 4, 5, 7, 8, 9)
AND e.endpointname LIKE '%AWSl100%'
AND u.username LIKE '%PamTrainingAdmin%'
ORDER BY ar.taskdate DESC;
Comments
nan
Completed Task Count of Task Types
Module: ARS Tasks
Description
Get the count of all Completed tasks, grouped by tasktype(print tasktype,count(*))
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT
TASKTYPE,
CASE
WHEN TASKTYPE = 1 THEN 'Add Access'
WHEN TASKTYPE = 2 AND ENTITLEMENT_VALUEKEY IS NULL THEN 'Remove Account'
WHEN TASKTYPE = 2 AND ENTITLEMENT_VALUEKEY IS NOT NULL THEN 'Remove Access'
WHEN TASKTYPE = 3 THEN 'New Account'
WHEN TASKTYPE = 4 THEN 'New Role Request'
WHEN TASKTYPE = 5 THEN 'Change Password'
WHEN TASKTYPE = 6 THEN 'Enable Account'
WHEN TASKTYPE = 7 THEN 'Proposed Account Owners'
WHEN TASKTYPE = 8 THEN 'Delete Account'
WHEN TASKTYPE = 9 THEN 'Update User'
WHEN TASKTYPE = 12 THEN 'Update Account'
WHEN TASKTYPE = 13 THEN 'Proposed Entitlement Owners'
WHEN TASKTYPE = 14 THEN 'Disable Account'
WHEN TASKTYPE = 23 THEN 'Modify Privilege'
WHEN TASKTYPE = 24 THEN 'Create Entitlement'
WHEN TASKTYPE = 25 THEN 'Update Entitlement- Add Access'
WHEN TASKTYPE = 26 THEN 'Update Entitlement- Remove Access'
WHEN TASKTYPE = 27 THEN 'Update Entitlement'
WHEN TASKTYPE = 28 THEN 'Delete Entitlement'
WHEN TASKTYPE = 29 THEN 'Fire Fighter ID Grant Access'
WHEN TASKTYPE = 30 THEN 'Fire Fighter ID Revoke Access'
WHEN TASKTYPE = 31 THEN 'Extend Access- Update Access End Date'
WHEN TASKTYPE = 32 THEN 'Lock Account'
WHEN TASKTYPE = 33 THEN 'Unlock Account'
WHEN TASKTYPE = 34 THEN 'Fire Fighter Instance Grant Access'
WHEN TASKTYPE = 35 THEN 'Fire Fighter Instance Revoke Access'
ELSE 'UNKNOWN'
END AS TASKTYPE_NAME,
COUNT(*) AS Completed_Task_Count
FROM ARSTASKS
WHERE STATUS=3
GROUP BY TASKTYPE;
Comments
nan
Pending Task Count of Task Types
Module: ARS Tasks
Description
Get the count of all Pending tasks, grouped by tasktype (print tasktype,count(*))
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT
TASKTYPE,
CASE
WHEN TASKTYPE = 1 THEN 'Add Access'
WHEN TASKTYPE = 2 AND ENTITLEMENT_VALUEKEY IS NULL THEN 'Remove Account'
WHEN TASKTYPE = 2 AND ENTITLEMENT_VALUEKEY IS NOT NULL THEN 'Remove Access'
WHEN TASKTYPE = 3 THEN 'New Account'
WHEN TASKTYPE = 4 THEN 'New Role Request'
WHEN TASKTYPE = 5 THEN 'Change Password'
WHEN TASKTYPE = 6 THEN 'Enable Account'
WHEN TASKTYPE = 7 THEN 'Proposed Account Owners'
WHEN TASKTYPE = 8 THEN 'Delete Account'
WHEN TASKTYPE = 9 THEN 'Update User'
WHEN TASKTYPE = 12 THEN 'Update Account'
WHEN TASKTYPE = 13 THEN 'Proposed Entitlement Owners'
WHEN TASKTYPE = 14 THEN 'Disable Account'
WHEN TASKTYPE = 23 THEN 'Modify Privilege'
WHEN TASKTYPE = 24 THEN 'Create Entitlement'
WHEN TASKTYPE = 25 THEN 'Update Entitlement- Add Access'
WHEN TASKTYPE = 26 THEN 'Update Entitlement- Remove Access'
WHEN TASKTYPE = 27 THEN 'Update Entitlement'
WHEN TASKTYPE = 28 THEN 'Delete Entitlement'
WHEN TASKTYPE = 29 THEN 'Fire Fighter ID Grant Access'
WHEN TASKTYPE = 30 THEN 'Fire Fighter ID Revoke Access'
WHEN TASKTYPE = 31 THEN 'Extend Access- Update Access End Date'
WHEN TASKTYPE = 32 THEN 'Lock Account'
WHEN TASKTYPE = 33 THEN 'Unlock Account'
WHEN TASKTYPE = 34 THEN 'Fire Fighter Instance Grant Access'
WHEN TASKTYPE = 35 THEN 'Fire Fighter Instance Revoke Access'
ELSE 'UNKNOWN'
END AS TASKTYPE_NAME,
COUNT(*) AS Count
FROM ARSTASKS
WHERE STATUS IN (1,2)
GROUP BY TASKTYPE;
Comments
nan
Query copied!