analytics run now issue.

[RevatiTarale]

Hii Team,

I am having an issue with analytics. The dry run works fine and shows the result of the record but the run now doesn't show the result. 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.

[rushikeshvartak]

• Please share configuration screenshot and job configuration
•  

[RevatiTarale]

@rushikeshvartak 

Please find the attached snap of the analytics configuration.

[rushikeshvartak]

In allowed action keep only Update Account Action

[RevatiTarale]

@rushikeshvartak Changed allowed the action to update the account only. But still the same issue.

Thank you

[rushikeshvartak]

Create job and run report - 

[RevatiTarale]

@rushikeshvartak 

I Ran a job for analytics. I attached a mail template and I got the report in the mail where I see the results expected (I got all users data as shown in dry run). But still, when I do run now I get 0 results. In run history, there is also 0 count.

Please let me know what is misconfigured here?

[This message has been edited by moderator to mask sensitive information]

[Raghu]

@RevatiTarale  can you check Base Count any value is der? please remove or it shuld be > Number of History to Keep value,

try it again.

[rushikeshvartak]

This is expected as you have selected save to file. You need to select save data in order to see actual count. 

[itinjic]

The query has some syntax errors and can be optimized. Here's the fixed and optimized version of the 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
JOIN user_accounts ua ON u.userkey = ua.userkey
JOIN accounts a ON ua.accountkey = a.accountkey
JOIN endpoints e ON a.endpointkey = e.endpointkey
WHERE 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) OR (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 JOIN user_accounts uaa ON uu.userkey = uaa.userkey JOIN accounts aa ON uaa.accountkey = aa.accountkey JOIN endpoints ee ON aa.endpointkey = ee.endpointkey WHERE ee.endpointname = 'AzureAD Dev' AND aa.status IN (1, 'Manually Provisioned') )) );
```
In this optimized version, I have replaced the old-style comma-separated join syntax with the modern JOIN syntax. This makes the query more readable and maintainable. I have also removed unnecessary subqueries by merging the conditions using OR operators.