Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
05/02/2024 12:14 AM
Hi Team,
We have prepared one campaign analytics when we do a dry run then it shows expected records but when we do run now it does not show any records and also it is taking more execution time to run but In the data analyzer we can see expected records. for reference, I have attached the screenshot. Can you please help with this?
Solved! Go to Solution.
05/02/2024 12:34 AM - edited 05/02/2024 12:35 AM
@arti_rathod7 can create dublicate and check again
still issue please share query
05/02/2024 12:58 AM
Hi Raghu,
Yes, I tried a duplicate also but still the same issue.
For your reference mentioned below query-
select distinct u.username as Username,u.firstname As FirstName, u.lastname as LastName , u.email as Email,a.name as AccountName,ev.entitlement_value as AccessName , ep.endpointname as ApplicationName ,date_format(ce.startdate,'%Y-%m-%d') as StartDate , date_format(ce.enddate,'%Y-%m-%d') as EndDate,case when cae.certified=2 then 'Approved' else 'Rejected' end as Action , (Select concat(firstname,' ',lastname)from users where ce.CERTIFIER =userkey) as Certifier from Account_entitlements1 ae , Certification_account_entitlement1_status cae, Certification_account ca, Certification_entitlement_value cev , Accounts a, Entitlement_values ev, Campaign c, Certification ce , users u , user_accounts ua , endpoints ep where cae.cert_accountkey = ca.CERT_ACCOUNTKEY and ca.accountkey= a.accountkey and a.accountkey = ae.accountkey and cae.cert_entitlement_valuekey = cev.CERT_ENTITLEMENT_VALUEKEY and cev.entitlement_valuekey = ev.ENTITLEMENT_VALUEKEY and ev.ENTITLEMENT_VALUEKEY= ae.entitlement_valuekey and c.id = ce.campaignKey and ce.CERTKEY = cae.certkey and c.campaign_name = 'User Manager Access Review April 2024' and u.userkey=ua.userkey and a.accountkey = ua.accountkey and a.endpointkey = ep.endpointkey;
05/02/2024 02:22 AM
can you share analytics query config report ss please?
05/02/2024
02:29 AM
- last edited on
05/02/2024
04:07 AM
by
Sunil
Hi Raghu,
I hope this configuration SS you want.
[This message has been edited by moderator to mask sensitive information from an attached image]
05/02/2024 02:31 AM
@arti_rathod7 can you remove base count 10 and save and try it
05/02/2024 03:12 AM
Raghu - Yes I removed the base count, saved, and ran analytics but still the same error.
05/02/2024 03:18 AM
now it will show at least records show am right,
query change and check
select distinct u.username as Username,u.firstname As FirstName, u.lastname as LastName , u.email as Email,a.name as AccountName,ev.entitlement_value as AccessName , ep.endpointname as ApplicationName ,date_format(ce.startdate,'%Y-%m-%d') as StartDatee , date_format(ce.enddate,'%Y-%m-%d') as EndDatee,case when cae.certified=2 then 'Approved' else 'Rejected' end as Action , (Select concat(firstname,' ',lastname)from users where ce.CERTIFIER =userkey) as Certifier from Account_entitlements1 ae , Certification_account_entitlement1_status cae, Certification_account ca, Certification_entitlement_value cev , Accounts a, Entitlement_values ev, Campaign c, Certification ce , users u , user_accounts ua , endpoints ep where cae.cert_accountkey = ca.CERT_ACCOUNTKEY and ca.accountkey= a.accountkey and a.accountkey = ae.accountkey and cae.cert_entitlement_valuekey = cev.CERT_ENTITLEMENT_VALUEKEY and cev.entitlement_valuekey = ev.ENTITLEMENT_VALUEKEY and ev.ENTITLEMENT_VALUEKEY= ae.entitlement_valuekey and c.id = ce.campaignKey and ce.CERTKEY = cae.certkey and c.campaign_name = 'User Manager Access Review April 2024' and u.userkey=ua.userkey and a.accountkey = ua.accountkey and a.endpointkey = ep.endpointkey;
05/02/2024 06:20 AM
Thank you so much Raghu, I got the solution.
05/10/2024 05:13 AM
Hii @Raghu
I am having the same issue again. Can you tell me what is exactly causing this issue?
Find the below query:
Select distinct ua.userkey as userKey, a.accountkey as acctKey, a.name as accName, e.endpointkey as 'Endpoint', 'Update Account' as Default_Action_For_Analytics from users u, accounts a , user_accounts ua , endpoints e where u.userkey=ua.userkey and ua.accountkey=a.accountkey and a.endpointkey=e.endpointkey and a.status in (1,'Manually Provisioned') and a.endpointkey = '4' and u.customproperty5='New FlyScoot User Onboarding Started' and u.comments='Workday Imported' and u.statuskey='1' and DATEDIFF(u.startdate,current_date()) = 1 and u.username not in (Select distinct uu.username from users uu, user_accounts uaa, accounts aa, endpoints ee where uu.userkey = uaa.userkey and aa.accountkey=uaa.accountkey and aa.endpointkey=ee.endpointkey and ee.endpointname= 'Active Directory Dev' and aa.status in (1,'Manually Provisioned') )
UNION
Select distinct ua.userkey as userKey, a.accountkey as acctKey, a.name as accName, e.endpointkey as 'Endpoint', 'Update Account' as Default_Action_For_Analytics from users u, accounts a , user_accounts ua , endpoints e where u.userkey=ua.userkey and ua.accountkey=a.accountkey and a.endpointkey=e.endpointkey and a.status in (1,'Manually Provisioned') and a.endpointkey = '4' and u.customproperty5='AD Onboarding Started' and u.comments='Workday Imported' and u.statuskey='1' and u.username in (Select distinct uu.username from users uu, user_accounts uaa, accounts aa, endpoints ee where uu.userkey = uaa.userkey and aa.accountkey=uaa.accountkey and aa.endpointkey=ee.endpointkey and ee.endpointname= 'AzureAD Dev' and aa.status in (1,'Manually Provisioned') );
Please let me know what needs to change in this query.
05/02/2024 03:21 AM
@arti_rathod7 we had a similar case where doing dry run the results were shown, whereas when running actually it took lot of time.
Possible issues :
1. The query used may not be optimized.
2. Try attaching Report Email Template and see if works.
We also ended up with checking Saviynt Ops team and later they checked the elastic search.