Analytics SQL Query updating is not happening.

[Santosh]

Hi there, we have an Analytics that checks on Completed Tasks for a user. Recently it missed on report creation, and it had a time range of 1 hour to look back, hence, to fix that issue I wanted to hard code the username and pull out the record and even after trying for ~50-60 times in 3 days, different time intervals i am not being able to edit it.

I have checked the edit analytics option on Global config. 3 days back I was able to update the query from, I have attached the picture where I was able to update where condition and while trying to change the u.username ='SAVTEST10.Wednesday', it is not letting me to update.

[rushikeshvartak]

Query must be taking longer time hence its unable to save add limit 1 in end and try

[Santosh]

@rushikeshvartak Not sure if I get the 2nd part of your message, I have attached the configuration image below if that is what you are referring to.

[rushikeshvartak]

Add "limit 1" in end of query and validate

[Santosh]

Did that on Data Analyzer to run the query with LIMIT 1 and it has been going on forever, tried different usernames and still Loading....Please Wait....

Did the application restart already, as you can see it let me do the update once and not letting me do it again which is strange.

[rushikeshvartak]

Your query does not have proper joins or have more columns hence its taking time

[Santosh]

Here is the query i am using,

SELECT
COLUMN NAMES
FROM
users u
JOIN
user_accounts ua ON ua.userkey = u.userkey
JOIN
accounts a ON a.accountkey = ua.accountkey AND a.status != 'Manually Suspended' AND a.status != 'SUSPENDED FROM IMPORT SERVICE'
JOIN
endpoints e ON e.endpointkey = a.endpointkey
AND e.endpointname NOT IN --->LIST OF EXCLUDED Applications
JOIN
entitlement_types et ON e.endpointkey = et.endpointkey
LEFT JOIN
account_entitlements1 ae ON ae.accountkey = ua.accountkey
LEFT JOIN
entitlement_values ev ON ae.entitlement_valuekey = ev.entitlement_valuekey
AND (e.endpointname != Certain Application OR ev.entitlement_value = 'Owner')
LEFT JOIN
arstasks at ON at.accountkey = ua.accountkey
LEFT JOIN
users requestor ON at.requestedby = requestor.userkey
INNER JOIN
users m ON m.userkey = u.manager

WHERE

u.username = '     '   


AND NOT (e.endpointname =  Certain Application AND ev.entitlement_value IS NULL)

LIMIT 1;

[rushikeshvartak]

SELECT
COLUMN NAMES
FROM
users u
JOIN
user_accounts ua ON ua.userkey = u.userkey
JOIN
accounts a ON a.accountkey = ua.accountkey
AND a.status NOT IN ('Manually Suspended', 'SUSPENDED FROM IMPORT SERVICE')
JOIN
endpoints e ON e.endpointkey = a.endpointkey
AND e.endpointname NOT IN (LIST OF EXCLUDED Applications)
JOIN
entitlement_types et ON e.endpointkey = et.endpointkey
LEFT JOIN
account_entitlements1 ae ON ae.accountkey = ua.accountkey
LEFT JOIN
entitlement_values ev ON ae.entitlement_valuekey = ev.entitlement_valuekey
AND (e.endpointname != 'Certain Application' OR ev.entitlement_value = 'Owner')
LEFT JOIN
arstasks at ON at.accountkey = ua.accountkey
LEFT JOIN
users requestor ON at.requestedby = requestor.userkey
INNER JOIN
users m ON m.userkey = u.manager
WHERE
u.username = 'your_username' -- Replace with the actual username
AND NOT (e.endpointname = 'Certain Application' AND ev.entitlement_value IS NULL)
LIMIT 1;

[Santosh]

Tried NOT IN(LIST) too, had no luck. On top i check if I could edit another similar Analytics --- No luck there too. The Category of this Analytics is "Information Refresh". If this is a performance issue on Saviynt side, i'm thinking if creating a ticket.

[rushikeshvartak]

Its query issue 

[Santosh]

It was working couple days ago, before my change, I was able to edit then, not being able change the usernames now, it's not letting me even to revert back to working query, other similar reports that are working fine, also not letting me make the edit. I have ROLE_ADMIN SAV_Role.

[rushikeshvartak]

Do you get cloudflare error ?

[Santosh]

I don't think so, this is what i'm getting on the logs,

2024-07-10T16:30:00-05:00-ecm-worker-microservice.MicroserviceApiCallService-quartzScheduler_Worker-3-54qd6-DEBUG-responseMap :: [headers:[Transfer-Encoding:chunked, Server:cloudflare, CF-RAY:8a13a709191c0625-IAD, X-Content-Type-Options:nosniff, Connection:keep-alive, Pragma:no-cache, Date:Wed, 10 Jul 2024 21:30:00 GMT, X-Frame-Options:DENY, Referrer-Policy:no-referrer, Strict-Transport-Security:max-age=31536000; includeSubDomains, CF-Cache-Status:DYNAMIC, Cache-Control:no-cache, no-store, max-age=0, must-revalidate, Set-Cookie:INGRESSCOOKIE=1720647001.541.850.109926|3523113d2bd3072fa485626e272241b1; Path=/; Secure; HttpOnly, Vary:Origin,Access-Control-Request-Method,Access-Control-Request-Headers, Expires:0, Content-Type:application/json], resultObject:{"jobId":"676103","STATUS":"OK","response":"In-progress","USERMS":"Endpoint sync and Entitlement sync STARTED. Job Id - 676103"}, responseCode:200]

[rushikeshvartak]

You will get error on UI

[Raghu]

@Santosh  try below

SELECT
-- Replace with actual column names you need
u.userkey,
u.username,
ua.accountkey,
a.status,
e.endpointname,
et.entitlement_type,
ev.entitlement_value,
requestor.username AS requestor_username,
m.username AS manager_username
FROM
users u
JOIN
user_accounts ua ON ua.userkey = u.userkey
JOIN
accounts a ON a.accountkey = ua.accountkey
JOIN
endpoints e ON e.endpointkey = a.endpointkey
JOIN
entitlement_types et ON e.endpointkey = et.endpointkey
LEFT JOIN
account_entitlements1 ae ON ae.accountkey = ua.accountkey
LEFT JOIN
entitlement_values ev ON ae.entitlement_valuekey = ev.entitlement_valuekey
LEFT JOIN
arstasks at ON at.accountkey = ua.accountkey
LEFT JOIN
users requestor ON at.requestedby = requestor.userkey
INNER JOIN
users m ON m.userkey = u.manager
WHERE
u.username = 'your_username' -- Replace with the actual username
AND a.status NOT IN ('Manually Suspended', 'SUSPENDED FROM IMPORT SERVICE')
AND e.endpointname NOT IN (LIST OF EXCLUDED Applications)
AND (e.endpointname != 'Certain Application' OR ev.entitlement_value = 'Owner')
AND NOT (e.endpointname = 'Certain Application' AND ev.entitlement_value IS NULL)
LIMIT 1;

[Santosh]

Tried this as well, this was the same as @rushikeshvartak provided except for the ordering of the condition.

Ran it on Data Analyzer and is going on forever. My concern is the ability to make changes to the Analytics to manipulate the report and it is not happening, does not progress to anything after i click UPDATE button.

[rushikeshvartak]

Optimize query

[Santosh]

I was able to find the pinpoint our issue (if this is discarded the query runs as expected), is there another way to list out the entitlement values in a list with a separator?

[rushikeshvartak]

If data is exceeding limit error will occur. 

[Santosh]

It is working as expected when it was modified outside of our network and every time I try to create on with same logic, it doesn't let me create new or update existing. This listing is a necessary evil as we utilize the generated report to send out an email, and that email need to list an entitlement value in one line or at one block.

[rushikeshvartak]

Same report working outside network ?

[Santosh]

Yes our implementation partners from India were able to login, update the sql on the analytics or analytics as a whole, but im using company’s network. Without dropping that Concat.. ev.entitlement_values from the query i was not able to neither edit the query nor make any update to the analytics l.

[rushikeshvartak]

That can be network issue. Check logs 

[Santosh]

Could not verify that, had the ticket for it, the support agents were able to make changes smoothly to query as well as analytics configurations. It got delegated to L4 support and he suggested I make changes to the query but ended up reducing the query size losing essential functionality and it worked. 

[rushikeshvartak]

Maybe make query on same line unformatted