Query working in data analyzer but not in Analytics

[savuser17]

Hi team,

Working on a query to retrieve certain users and the ad groups they have.  However, whatever query I come up with, works in data analyser but not when I create an analytics report. It doesnt let me save, doesnt even show preview.

These are the two versions of the query I had:

select u.username, ev.ENTITLEMENT_VALUE, a.accountid from accounts a, entitlement_values ev, users u, entitlement_types et where a.endpointkey = et.ENDPOINTKEY and et.ENTITLEMENTTYPEKEY = ev.ENTITLEMENTTYPEKEY and a.customproperty2 = u.email and a.endpointkey = 2 and u.username like '%UAT%'

and 

SELECT u.username, a.accountid as Id, ev.ENTITLEMENT_VALUE FROM entitlement_values ev INNER JOIN entitlement_types et ON ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY INNER JOIN accounts a ON et.ENDPOINTKEY = a.endpointkey INNER JOIN users u ON a.customproperty2 = u.email WHERE a.endpointkey = 2 and u.username like '%UAT%'

Does someone know what the issue is here, why its not creating in analytics?

Thanks

[rushikeshvartak]

Please try below

SELECT 
    u.username, 
    ev.ENTITLEMENT_VALUE, 
    a.accountid 
FROM 
    accounts a
JOIN 
    entitlement_types et ON a.endpointkey = et.ENDPOINTKEY
JOIN 
    entitlement_values ev ON et.ENTITLEMENTTYPEKEY = ev.ENTITLEMENTTYPEKEY
JOIN 
    users u ON a.customproperty2 = u.email
WHERE 
    a.endpointkey = 2 
    AND u.username LIKE '%UAT%' limit 10;

[savuser17]

Hi,

This works, however it only gives the top ten results and we need the whole report to do our analysis. As soon I remove the limit 10 part it goes back to not saving.

Thanks

[rushikeshvartak · Accepted solution]

remove limit 10 from custom query 

update analyticsconfiges set ANALYTICSQRY=replace(ANALYTICSQRY,'limit 10','') where ANALYTICSKEY=11111 limit 1;

[Manu269]

@savuser17 is the elastic search running properly?

we have similar case in the past and opened an FD with Saviynt Ops