and more in a single search tool across platforms. Read the announcement here. |
03/24/2023 08:42 AM
Hi,
how can i create a weekly report for each target system that is sent via email only if there are update task failed to the various app owners ?
thank you
03/27/2023 04:14 AM
Following is sample query to get all Provisioning tasks found in Error State.
Please modify the query as per your requirement.
To send this report via email, please create new email template and configure in the Analytics .
SELECT a.taskkey AS 'tasks', u.Username AS 'Username', CASE WHEN u.STATUSKEY = 1 THEN 'Active' WHEN u.STATUSKEY = 0 THEN 'Inactive' ELSE u.STATUSKEY END AS 'UserStatus', a.ACCOUNTNAME AS 'AccountName', DATE(a.taskdate) AS taskdate, e.endpointname AS 'Endpoint', e.endpointKey AS 'endpointKey', ev.entitlement_value AS 'EntitlementValue', a.source AS 'Source', CASE WHEN a.tasktype = 1 THEN 'ADD' WHEN a.TASKTYPE = 2 THEN 'Remove' WHEN a.TASKTYPE = 3 THEN 'NEW ACCOUNT' WHEN a.TASKTYPE = 4 THEN 'NEW ROLE REQUEST' WHEN a.TASKTYPE = 5 THEN 'CHANGE PASSWORD' WHEN a.TASKTYPE = 6 THEN 'ENABLE ACCOUNT' WHEN a.TASKTYPE = 14 THEN 'DISABLE ACCOUNT' WHEN a.TASKTYPE = 12 THEN 'UPDATE ACCOUNT' WHEN a.TASKTYPE = 8 THEN 'DELETE ACCOUNT' ELSE a.tasktype END AS 'TaskType', CASE WHEN ec.status = 0 THEN 'Failed' WHEN ec.status = 1 THEN 'Success' END AS 'Connection Status' FROM arstasks a INNER JOIN users u ON u.userkey = a.userkey INNER JOIN endpoints e ON a.endpoint = e.endpointKey INNER JOIN securitysystems ss ON a.SECURITYSYSTEM = ss.systemkey INNER JOIN externalconnection ec ON ss.EXTERNALCONNECTION = ec.externalconnectionkey LEFT JOIN entitlement_values ev ON a.entitlement_valuekey = ev.entitlement_valuekey WHERE a.status IN (7 , 8);