Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Campaign Analytics issue

arti_rathod7
New Contributor II
New Contributor II

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?

10 REPLIES 10

Raghu
Valued Contributor III
Valued Contributor III

@arti_rathod7  can create dublicate and check again

still issue please share query


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

arti_rathod7
New Contributor II
New Contributor II

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
Valued Contributor III
Valued Contributor III

can you share analytics query  config report ss please?


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

arti_rathod7
New Contributor II
New Contributor II

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
Valued Contributor III
Valued Contributor III

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


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

arti_rathod7
New Contributor II
New Contributor II

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

Raghu
Valued Contributor III
Valued Contributor III

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;


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

arti_rathod7
New Contributor II
New Contributor II

Thank you so much Raghu, I got the solution.

RevatiTarale
New Contributor II
New Contributor II

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
All-Star
All-Star

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

Regards
Manish Kumar
If the response answered your query, please Accept As Solution and Kudos
.