Role History Query

[User_ID_Singh1]

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.

[rushikeshvartak]

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);

[rushikeshvartak]

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)

[User_ID_Singh1]

I need help in clubbing both queries into single query.

[rushikeshvartak]

Both query have different column how can you club? 
what is requirement?

[User_ID_Singh1]

1. Extract all the application role changes for the end point from creation date till date
2. Extract all the tasks for the end point from creation date till date.

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.

[rushikeshvartak]

It should be 2 different reports as columns between 2 report are different 

[PremMahadikar · Accepted solution]

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

[User_ID_Singh1]

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.

[rushikeshvartak]

Add limit 10 in end and validate

[User_ID_Singh1]

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.

[rushikeshvartak]

Since data is different use 2 separate reports for your requirements 

[User_ID_Singh1]

We have made changes in above query with different scenarios. Please have a look into it. Attached query.

[User_ID_Singh1]

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.

[rushikeshvartak]

From second union non of the column are displayed hence no use of 2nd union

[User_ID_Singh1]

I tried made changes as you suggested still same behavior. Can you please help me by giving the query.

[rushikeshvartak]

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)

[User_ID_Singh1]

@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

[rushikeshvartak]

As mentioned all data is not linked hence you need to prepare different report for use case

[User_ID_Singh1]

@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.

[rushikeshvartak]

Data must be huge add more filters 

[User_ID_Singh1]

@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.

[rushikeshvartak]

Share logs