Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
05/25/2024 10:05 AM - edited 05/25/2024 10:10 AM
Hi,
Need your help in adding both queries, When i try to add both queries getting errors. please try it from your end in adding both queries into single query and please send
PFA queries.
Thanks in Advance.
Solved! Go to Solution.
05/25/2024 01:09 PM
Query 2
select arstasks.taskkey,SUBSTRING_INDEX(ars.jbpmprocessinstanceid, '.', -1) AS requestnumber,(case when arstasks.tasktype=1 then "Add Access" when arstasks.tasktype=2 then "Remove Access" when arstasks.tasktype=3 then "New Access" when arstasks.tasktype=4 then "Role" when arstasks.tasktype=5 then "Change Password" when arstasks.tasktype=6 then "Enable Account" when arstasks.tasktype=7 then "Proposed Account Owners" when arstasks.tasktype=8 then "Delete Account" when arstasks.tasktype=12 then "Update Account" when arstasks.tasktype=14 then "Disable Account" when arstasks.tasktype=7 then "Claim Account" when arstasks.tasktype=9 then "Update User" else 'arstasks.tasktype' END) as 'Task Type', a.name AS 'Account Name', ev.entitlement_value AS 'Access Value', arstasks.taskdate AS 'Task CreateDate', arstasks.updatedate AS 'TaskCompletionDate', (case arstasks.status when 1 then 'New' when 2 then 'In Progress' when 3 then 'Completed' when 4 then 'Discontinued' when 1 then 'No Action Required' END) as taskstatus,e.endpointname,u1.username as 'TASK BENEFICIARY USERID' from arstasks left join ars_requests ars on ars.requestkey=arstasks.requestkey left join users u on u.userkey = arstasks.upadteuser left join users u1 on u1.userkey= arstasks.userkey join endpoints e on e.endpointkey=arstasks.endpoint left join accounts a on a.accountkey=arstasks.accountkey left join entitlement_values ev ON ev.entitlement_valuekey = arstasks.entitlement_valuekey where arstasks.endpoint in (280,305);
05/25/2024 01:10 PM
Query 1 - Run from analytics
select e.endpointname as 'Application Name', r.Role_NAME AS 'Role Name', rh.creationdate as 'Role Change dat', rh.fieldname as 'Change Field', rh.oldvalue as 'Old Value', rh.newvalue as 'New Value', rh.operation as 'Operation', rh.comments as 'Comment', u.username as 'Role changed by' from roles_historychangelog rh, endpoints e, roles r, users u where rh.rolekey=r.rolekey and r.endpointkey = e.endpointkey and rh.updateuser= u.userkey and e.endpointkey in (280,305)
05/26/2024 05:20 AM
I need help in clubbing both queries into single query.
05/26/2024 08:22 AM
Both query have different column how can you club?
what is requirement?
05/27/2024 07:57 AM
A comprehensive report needs to be created that hosts the required endpoint creation date data, all the application role changes for the end point from creation date till date and all the tasks for the end point from creation date till date.
05/27/2024 07:59 AM
It should be 2 different reports as columns between 2 report are different
05/26/2024 09:56 AM
Hi @User_ID_Singh1 ,
Both queries are different, and if you still want to join the query1 and query2, please find the below:
select
e.endpointname as 'Application Name',
arstasks.taskkey as 'Task Key',
SUBSTRING_INDEX(ars.jbpmprocessinstanceid, '.', -1) as 'Request ID',
(case
when arstasks.tasktype=1 then "Add Access"
when arstasks.tasktype=2 then "Remove Access"
when arstasks.tasktype=3 then "New Access"
when arstasks.tasktype=4 then "Role"
when arstasks.tasktype=5 then "Change Password"
when arstasks.tasktype=6 then "Enable Account"
when arstasks.tasktype=7 then "Proposed Account Owners"
when arstasks.tasktype=8 then "Delete Account"
when arstasks.tasktype=12 then "Update Account"
when arstasks.tasktype=14 then "Disable Account"
when arstasks.tasktype=7 then "Claim Account"
when arstasks.tasktype=9 then "Update User"
else 'arstasks.tasktype'
END) as 'Task Type',
a.name as 'Account Name',
ev.entitlement_value as 'Access Value',
arstasks.taskdate as 'Task CreateDate',
arstasks.updatedate as 'Task CompletionDate',
(case arstasks.status
when 1 then 'New'
when 2 then 'In Progress'
when 3 then 'Completed'
when 4 then 'Discontinued'
when 1 then 'No Action Required'
END) as 'Task Status',
u1.username as 'Task Beneficiary UserID',
'' as 'Role Name',
'' as 'Role Change Date',
'' as 'Change Field',
'' as 'Old Value',
'' as 'New Value',
'' as 'Operation',
'' as 'Comment',
'' as 'Role changed by'
from
arstasks left join ars_requests ars on ars.requestkey=arstasks.requestkey
left join users u on u.userkey = arstasks.upadteuser
left join users u1 on u1.userkey= arstasks.userkey
left join endpoints e on e.endpointkey=arstasks.endpoint
left join accounts a on a.accountkey=arstasks.accountkey
left join entitlement_values ev ON ev.entitlement_valuekey = arstasks.entitlement_valuekey
where
arstasks.endpoint in (280,305)
UNION
select
e.endpointname as 'Application Name',
'' as 'Task Key',
'' as 'Request ID',
'' as 'Task Type',
'' as 'Account Name',
'' as 'Access Value',
'' as 'Task CreateDate',
'' as 'Task CompletionDate',
'' as 'Task Status',
'' as 'Task Beneficiary UserID',
r.Role_NAME as 'Role Name',
rh.creationdate as 'Role Change Date',
rh.fieldname as 'Change Field',
rh.oldvalue as 'Old Value',
rh.newvalue as 'New Value',
rh.operation as 'Operation',
rh.comments as 'Comment',
u.username as 'Role changed by'
from
roles_historychangelog rh,
endpoints e,
roles r,
users u
where
rh.rolekey=r.rolekey
and r.endpointkey = e.endpointkey
and rh.updateuser= u.userkey
and e.endpointkey in (280,305)
If this helps, please consider selecting Accept As Solution and hit Kudos
05/27/2024 05:56 AM
Hi @PremMahadikar
I have created the query and in preview i can see data coming up. But when i saved it and ran the query its not showing the data. I have waited for 30 mins which is EIC analytics guardrails timeline for Analytics. Still I couldnt get the data in Analytics History Page. Please help me in this.
05/27/2024 07:58 AM
Add limit 10 in end and validate
05/27/2024 08:05 AM
Hi @rushikeshvartak ,
I have provided you with the requirements above.
I tried adding a limit of 10 and executed it. Still, its the same behavior as above mentioned. Data is not coming up.
05/27/2024 08:08 AM
Since data is different use 2 separate reports for your requirements
05/27/2024 08:12 AM
We have made changes in above query with different scenarios. Please have a look into it. Attached query.
05/27/2024 08:15 AM
In query we have splitted 1st part as one report and remaining part as second report and executed it with limit 10. Still its same behavior for separate reports.
05/27/2024 08:18 AM
From second union non of the column are displayed hence no use of 2nd union
05/27/2024 08:39 AM
I tried made changes as you suggested still same behavior. Can you please help me by giving the query.
05/27/2024 08:47 AM
Try below
select e.endpointname as 'Application Name', arstasks.taskkey as 'Task Key', SUBSTRING_INDEX(ars.jbpmprocessinstanceid, '.', -1) as 'Request ID', (case when arstasks.tasktype=1 then "Add Access" when arstasks.tasktype=2 then "Remove Access" when arstasks.tasktype=3 then "New Access" when arstasks.tasktype=4 then "Role" when arstasks.tasktype=5 then "Change Password" when arstasks.tasktype=6 then "Enable Account" when arstasks.tasktype=7 then "Proposed Account Owners" when arstasks.tasktype=8 then "Delete Account" when arstasks.tasktype=12 then "Update Account" when arstasks.tasktype=14 then "Disable Account" when arstasks.tasktype=7 then "Claim Account" when arstasks.tasktype=9 then "Update User" else 'arstasks.tasktype' END) as 'Task Type', a.name as 'Account Name', ev.entitlement_value as 'Access Value', arstasks.taskdate as 'Task CreateDate', arstasks.updatedate as 'Task CompletionDate', (case arstasks.status when 1 then 'New' when 2 then 'In Progress' when 3 then 'Completed' when 4 then 'Discontinued' when 1 then 'No Action Required' END) as 'Task Status', u1.username as 'Task Beneficiary UserID' from arstasks left join ars_requests ars on ars.requestkey=arstasks.requestkey left join users u on u.userkey = arstasks.upadteuser left join users u1 on u1.userkey= arstasks.userkey left join endpoints e on e.endpointkey=arstasks.endpoint left join accounts a on a.accountkey=arstasks.accountkey left join entitlement_values ev ON ev.entitlement_valuekey = arstasks.entitlement_valuekey where arstasks.endpoint in (280,305)
05/27/2024 08:57 AM - edited 05/27/2024 08:58 AM
@rushikeshvartak Tried this query, its giving all the tasks and requests related data for mentioned endpoints.
I see you have not mentioned Roles, Roles history table.
Scenarios we need is by combining those two queries we need data like this scenarios:
Scenario 1 : role updated and no task
scenario 2 : role updated and task
scenario 3 : arstasks for abc endpoint
05/27/2024 09:14 AM
As mentioned all data is not linked hence you need to prepare different report for use case
06/02/2024 10:31 PM
@rushikeshvartak created different report for different usecase. In preview, we are able to see 3 records on specific date. but after saving report and running it, its exceeding 20 mins timeline and not giving us the data in Analytics History Page.
06/03/2024 07:36 PM
Data must be huge add more filters
06/03/2024 09:42 PM
@rushikeshvartak we have used date filter which is today's date. In preview i can see only 3 records, But after saving and running its not giving the data.
06/03/2024 10:06 PM
Share logs