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 Customers Email History Endpoints Entitlement Values External Connections Job Control Panel Request Access Role Assignments Roles Rules Rulesets Security Systems User Accounts User Group User Groups Users Users, Accounts Users, Accounts, Endpoint Users, Entitlements Users, Roles Users, SAV ROLESRecently Created Tasks
Module: ARS Tasks
Description
Displays recently created ARS tasks.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT accountname, endpoint, tasktype, taskdate
FROM arstasks
ORDER BY taskdate DESC;
Comments
nan
Tasks by Requested User
Module: ARS Tasks
Description
Displays total tasks grouped by requester.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT requestedby, COUNT(*) AS Total_Tasks
FROM arstasks
GROUP BY requestedby
ORDER BY Total_Tasks DESC;
Comments
nan
Tasks with Multiple Provisioning Attempts
Module: ARS Tasks
Description
Displays tasks where provisioning has been attempted multiple times.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT accountname, endpoint, provisioningtries, taskdate
FROM arstasks
WHERE provisioningtries>1
ORDER BY provisioningtries DESC;
Comments
nan
Tasks by Security System
Module: ARS Tasks
Description
Displays total ARS tasks grouped by security system.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT securitysystem, COUNT(*) AS Total_Tasks
FROM arstasks
GROUP BY securitysystem
ORDER BY Total_Tasks DESC;
Comments
nan
Tasks by Endpoint
Module: ARS Tasks
Description
Displays total ARS tasks grouped by endpoint.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT endpoint, COUNT(*) AS Total_Tasks
FROM arstasks
GROUP BY endpoint
ORDER BY Total_Tasks DESC;
Comments
nan
Tasks by Source
Module: ARS Tasks
Description
Displays total ARS tasks grouped by source.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT source, COUNT(*) AS Total_Tasks
FROM arstasks
GROUP BY source
ORDER BY Total_Tasks DESC;
Comments
nan
Pending Provisioning Tasks
Module: ARS Tasks
Description
Displays ARS tasks pending provisioning.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT accountname, endpoint, securitysystem, tasktype, taskdate
FROM arstasks
WHERE status=6
ORDER BY taskdate DESC;
Comments
nan
Provision Failed Tasks
Module: ARS Tasks
Description
Displays ARS tasks with Provision Failed status.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT accountname, endpoint, securitysystem, tasktype, taskdate, provisioningtries
FROM arstasks
WHERE status=7
ORDER BY taskdate DESC;
Comments
nan
ARS Tasks by Task Type
Module: ARS Tasks
Description
Displays total ARS tasks grouped by task type.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT
CASE tasktype
WHEN 1 THEN 'Add Access'
WHEN 2 THEN 'Remove Access'
WHEN 3 THEN 'New Account'
WHEN 4 THEN 'New Role Request'
WHEN 5 THEN 'Change Password'
WHEN 6 THEN 'Enable Account'
WHEN 8 THEN 'Delete Account'
WHEN 9 THEN 'Update User'
WHEN 12 THEN 'Update Account'
WHEN 14 THEN 'Disable Account'
WHEN 23 THEN 'Modify Privilege'
WHEN 24 THEN 'Create Entitlement'
WHEN 25 THEN 'Update Entitlement Access Add'
WHEN 26 THEN 'Update Entitlement Access Remove'
WHEN 27 THEN 'Update Entitlement'
WHEN 28 THEN 'Delete Entitlement'
WHEN 29 THEN 'Firefighter ID Grant Access'
WHEN 30 THEN 'Firefighter ID Revoke Access'
WHEN 31 THEN 'Extend Access'
WHEN 32 THEN 'Lock Account'
WHEN 33 THEN 'Unlock Account'
WHEN 34 THEN 'Firefighter Instance Grant'
WHEN 35 THEN 'Firefighter Instance Revoke'
ELSE CONCAT('Unknown (',tasktype,')')
END AS Task_Type,
COUNT(*) AS Total_Tasks
FROM arstasks
GROUP BY tasktype
ORDER BY Total_Tasks DESC;
Comments
nan
ARS Tasks by Status
Module: ARS Tasks
Description
Displays total ARS tasks grouped by task status.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
SELECT
CASE status
WHEN 1 THEN 'New'
WHEN 2 THEN 'In Progress'
WHEN 3 THEN 'Completed'
WHEN 4 THEN 'Discontinued'
WHEN 5 THEN 'Pending Create'
WHEN 6 THEN 'Pending Provisioning'
WHEN 7 THEN 'Provision Failed'
WHEN 8 THEN 'Error'
WHEN 9 THEN 'No Action Required'
ELSE CONCAT('Unknown (',status,')')
END AS Task_Status,
COUNT(*) AS Total_Tasks
FROM arstasks
GROUP BY status
ORDER BY Total_Tasks DESC;
Comments
nan
Task 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!