Campaign Analytics issue

[arti_rathod7]

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?

[Raghu]

@arti_rathod7  can create dublicate and check again

still issue please share query

[arti_rathod7]

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;

[Raghu]

can you share analytics query  config report ss please?

[arti_rathod7]

Hi  Raghu,

I hope this configuration SS you want.

[This message has been edited by moderator to mask sensitive information from an attached image]

[Raghu]

@arti_rathod7  can you remove base count 10 and save and try it

[arti_rathod7]

 Raghu - Yes I removed the base count, saved, and ran analytics but still the same error.

[Raghu · Accepted solution]

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;

[arti_rathod7]

Thank you so much Raghu, I got the solution.

[RevatiTarale]

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.

[Manu269]

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