Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.
This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Analytics Query Cannot be Saved

shyue
New Contributor
New Contributor

‎03/28/2024 11:09 PM

Hi There,

When I wanted to create a report with "complex" query, it gave me "Error code 520 - Web server is returning an unknown error". But there was no issue when I tried with a simple query. There should not be any issue with query itself as I tested each union part in the data analyzer. Wondering if anyone faced the same issue and how to get it worked. TIA!

Thanks,

SY

------Error Details----------------------

What happened?

There is an unknown connection issue between Cloudflare and the origin web server. As a result, the web page can not be displayed.

What can I do?

If you are a visitor of this website:

Please try again in a few minutes.

If you are the owner of this website:

There is an issue between Cloudflare's cache and your origin web server. Cloudflare monitors for these errors and automatically investigates the cause. To help support the investigation, you can pull the corresponding error log from your web server and submit it our support team. Please include the Ray ID (which is at the bottom of this error page). 

 

Labels:
0 Kudos
3 REPLIES 3

rushikeshvartak
All-Star
All-Star

‎03/29/2024 06:10 AM

Please share query


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.
0 Kudos

shyue
New Contributor
New Contributor
In response to rushikeshvartak

‎03/29/2024 08:47 AM

Hi Rushikesh,

Query is as below

 

SELECT DISTINCT
A.ACCOUNTKEY AS 'AccountKey',
U.USERNAME AS 'UserID',
U.CUSTOMPROPERTY1 AS 'LANID',
U.CUSTOMPROPERTY8 AS 'UserJobstatus',
U.CUSTOMPROPERTY29 AS 'LegacyIndicator',
    CONCAT(U.FIRSTNAME, ' ', U.LASTNAME) AS 'UserName',
    CASE WHEN U.STATUSKEY IS NULL THEN 'NotAvailable' WHEN U.STATUSKEY = 0 THEN 'Inactive' ELSE U.STATUSKEY END 'UserStatus',
U.TERMDATE AS 'DateTerm',
U.ENDDATE AS 'DateEnd',
U.CUSTOMPROPERTY34 AS 'DateSaviyntDisabled',
    E.DISPLAYNAME AS 'ApplicationDisplayName',
    E.ENDPOINTNAME  AS 'ApplicationRawName',
    (
        CASE WHEN U.CUSTOMPROPERTY54 IS NOT NULL
        OR U.CUSTOMPROPERTY55 IS NOT NULL THEN 'Yes' ELSE 'No' END
    ) AS 'Legal Hold Status',
    A.NAME AS 'ApplicationAccountName',
'' AS 'HasAccess',
    CASE WHEN A.STATUS IS NULL THEN 'NotAvailable' WHEN A.STATUS  = 1 THEN 'Active' ELSE A.STATUS END 'ACCOUNTSTATUS',
'Disable Account on Day 1 of Termination' AS 'MismatchCriteria',
'' AS 'RequestNumber'
FROM USERS  U, ACCOUNTS A, ENDPOINTS E, USER_ACCOUNTS UA
WHERE
U.STATUSKEY = 0
AND U.USERNAME NOT IN ('admin')
AND A.ENDPOINTKEY = E.ENDPOINTKEY 
AND A.STATUS IN (1, 'Manually Provisioned','Active')
AND E.STATUS = 1
AND FIND_IN_SET (E.ENDPOINTKEY, (SELECT GROUP_CONCAT(HRA.SCEHEMANAME) FROM HANARULE HR, HANARULEATTRIBUTE HRA WHERE HR.HANARULEKEY = HRA.HANARULE AND HR.NAME = 'Disable on Termination' AND HRA.OBJECTNAME = 12))
AND UA.USERKEY = U.USERKEY 
AND UA.ACCOUNTKEY = A.ACCOUNTKEY
 
UNION 
SELECT DISTINCT
A.ACCOUNTKEY AS 'AccountKey',
U.USERNAME AS 'UserID',
U.CUSTOMPROPERTY1 AS 'LANID',
U.CUSTOMPROPERTY8 AS 'UserJobstatus',
U.CUSTOMPROPERTY29 AS 'LegacyIndicator',
    CONCAT(U.FIRSTNAME, ' ', U.LASTNAME) AS 'UserName',
    CASE WHEN U.STATUSKEY IS NULL THEN 'NotAvailable' WHEN U.STATUSKEY = 0 THEN 'Inactive' ELSE U.STATUSKEY END 'UserStatus',
U.TERMDATE AS 'DateTerm',
U.ENDDATE AS 'DateEnd',
U.CUSTOMPROPERTY34 AS 'DateSaviyntDisabled',
    E.DISPLAYNAME AS 'ApplicationDisplayName',
    E.ENDPOINTNAME  AS 'ApplicationRawName',
    (
        CASE WHEN U.CUSTOMPROPERTY54 IS NOT NULL
        OR U.CUSTOMPROPERTY55 IS NOT NULL THEN 'Yes' ELSE 'No' END
    ) AS 'Legal Hold Status',
    A.NAME AS 'ApplicationAccountName',
'' AS 'HasAccess',
    CASE WHEN A.STATUS IS NULL THEN 'NotAvailable' WHEN A.STATUS  = 1 THEN 'Active' ELSE A.STATUS END 'ACCOUNTSTATUS',
'Delete Account on Day 1 of Termination' AS 'MismatchCriteria',
'' AS 'RequestNumber'
FROM USERS  U, ACCOUNTS A, ENDPOINTS E, USER_ACCOUNTS UA
WHERE
U.STATUSKEY = 0
AND U.USERNAME NOT IN ('admin')
AND A.ENDPOINTKEY = E.ENDPOINTKEY 
AND A.STATUS NOT IN ('SUSPENDED FROM IMPORT SERVICE')
AND E.STATUS = 1
AND FIND_IN_SET (E.ENDPOINTKEY, (SELECT GROUP_CONCAT(HRA.SCEHEMANAME) FROM HANARULE HR, HANARULEATTRIBUTE HRA WHERE HR.HANARULEKEY = HRA.HANARULE AND HR.NAME = 'Disable Applications on Termination' AND HRA.OBJECTNAME = 2 AND (HRA.OBJECTVALUE LIKE '%#AccountsOnly' OR HRA.OBJECTVALUE LIKE '%#Both')))
AND UA.USERKEY = U.USERKEY 
AND UA.ACCOUNTKEY = A.ACCOUNTKEY
 
 
UNION
 
SELECT DISTINCT
A.ACCOUNTKEY AS 'AccountKey',
U.USERNAME AS 'UserID',
U.CUSTOMPROPERTY1 AS 'LANID',
U.CUSTOMPROPERTY8 AS 'UserJobstatus',
U.CUSTOMPROPERTY29 AS 'LegacyIndicator',
    CONCAT(U.FIRSTNAME, ' ', U.LASTNAME) AS 'UserName',
    CASE WHEN U.STATUSKEY IS NULL THEN 'NotAvailable' WHEN U.STATUSKEY = 0 THEN 'Inactive' ELSE U.STATUSKEY END 'UserStatus',
U.TERMDATE AS 'DateTerm',
U.ENDDATE AS 'DateEnd',
U.CUSTOMPROPERTY34 AS 'DateSaviyntDisabled',
    E.DISPLAYNAME AS 'ApplicationDisplayName',
    E.ENDPOINTNAME  AS 'ApplicationRawName',
    (
        CASE WHEN U.CUSTOMPROPERTY54 IS NOT NULL
        OR U.CUSTOMPROPERTY55 IS NOT NULL THEN 'Yes' ELSE 'No' END
    ) AS 'Legal Hold Status',
    A.NAME AS 'ApplicationAccountName',
'' AS 'HasAccess',
    CASE WHEN A.STATUS IS NULL THEN 'NotAvailable' WHEN A.STATUS  = 1 THEN 'Active' ELSE A.STATUS END 'ACCOUNTSTATUS',
'Delete Account After 45 Days of Termination' AS 'MismatchCriteria',
'' AS 'RequestNumber'
FROM USERS  U, ACCOUNTS A, ENDPOINTS E, USER_ACCOUNTS UA
WHERE
U.STATUSKEY = 0
AND DATEDIFF(CURRENT_TIMESTAMP,U.CUSTOMPROPERTY34) >= 45
AND U.USERNAME NOT IN ('admin')
AND A.ENDPOINTKEY = E.ENDPOINTKEY 
AND A.STATUS NOT IN ('SUSPENDED FROM IMPORT SERVICE')
AND E.STATUS = 1
AND FIND_IN_SET (E.ENDPOINTKEY, (SELECT GROUP_CONCAT(HRA.SCEHEMANAME) FROM HANARULE HR, HANARULEATTRIBUTE HRA WHERE HR.HANARULEKEY = HRA.HANARULE AND HR.NAME = 'Delete Applications after 45 days of Termination'AND HRA.OBJECTNAME = 2))
AND UA.USERKEY = U.USERKEY 
AND UA.ACCOUNTKEY = A.ACCOUNTKEY
 
UNION
SELECT DISTINCT
A.ACCOUNTKEY AS 'AccountKey',
U.USERNAME AS 'UserID',
U.CUSTOMPROPERTY1 AS 'LANID',
U.CUSTOMPROPERTY8 AS 'UserJobstatus',
U.CUSTOMPROPERTY29 AS 'LegacyIndicator',
    CONCAT(U.FIRSTNAME, ' ', U.LASTNAME) AS 'UserName',
    CASE WHEN U.STATUSKEY IS NULL THEN 'NotAvailable' WHEN U.STATUSKEY = 0 THEN 'Inactive' ELSE U.STATUSKEY END 'UserStatus',
U.TERMDATE AS 'DateTerm',
U.ENDDATE AS 'DateEnd',
U.CUSTOMPROPERTY34 AS 'DateSaviyntDisabled',
    E.DISPLAYNAME AS 'ApplicationDisplayName',
    E.ENDPOINTNAME  AS 'ApplicationRawName',
    (
        CASE WHEN U.CUSTOMPROPERTY54 IS NOT NULL
        OR U.CUSTOMPROPERTY55 IS NOT NULL THEN 'Yes' ELSE 'No' END
    ) AS 'Legal Hold Status',
    A.NAME AS 'ApplicationAccountName',
'YES' AS 'HasAccess',
    CASE WHEN A.STATUS IS NULL THEN 'NotAvailable' WHEN A.STATUS  = 1 THEN 'Active' ELSE A.STATUS END 'ACCOUNTSTATUS',
'Delete Account After 7 Days of Termination' AS 'MismatchCriteria',
(SELECT GROUP_CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(TASK.PROVISIONINGMETADATA, 'Ticket Number=', -1), ';', 1)) FROM ARSTASKS TASK WHERE TASK.ACCOUNTKEY = A.ACCOUNTKEY AND TASK.TASKTYPE = '2' AND TASK.UPDATEDATE >= DATE(U.CUSTOMPROPERTY34) - INTERVAL 7 DAY AND TASK.ENTITLEMENT_VALUEKEY IS NULL) AS 'RequestNumber'
FROM USERS  U, ACCOUNTS A, ENDPOINTS E, USER_ACCOUNTS UA
WHERE
U.STATUSKEY = 0
AND DATEDIFF(CURRENT_TIMESTAMP,U.CUSTOMPROPERTY34) >= 7
AND U.USERNAME NOT IN ('admin')
AND A.ENDPOINTKEY = E.ENDPOINTKEY 
AND A.STATUS NOT IN ('SUSPENDED FROM IMPORT SERVICE')
AND E.STATUS = 1
AND FIND_IN_SET (E.ENDPOINTKEY, (SELECT GROUP_CONCAT(HRA.SCEHEMANAME) FROM HANARULE HR, HANARULEATTRIBUTE HRA WHERE HR.HANARULEKEY = HRA.HANARULE AND HR.NAME = 'Delete Applications on 7th day of Termination' AND HRA.OBJECTNAME = 2))
AND UA.USERKEY = U.USERKEY 
AND UA.ACCOUNTKEY = A.ACCOUNTKEY
 
UNION 
SELECT DISTINCT
A.ACCOUNTKEY AS 'AccountKey',
U.USERNAME AS 'UserID',
U.CUSTOMPROPERTY1 AS 'LANID',
U.CUSTOMPROPERTY8 AS 'UserJobstatus',
U.CUSTOMPROPERTY29 AS 'LegacyIndicator',
    CONCAT(U.FIRSTNAME, ' ', U.LASTNAME) AS 'UserName',
    CASE WHEN U.STATUSKEY IS NULL THEN 'NotAvailable' WHEN U.STATUSKEY = 0 THEN 'Inactive' ELSE U.STATUSKEY END 'UserStatus',
U.TERMDATE AS 'DateTerm',
U.ENDDATE AS 'DateEnd',
U.CUSTOMPROPERTY34 AS 'DateSaviyntDisabled',
    E.DISPLAYNAME AS 'ApplicationDisplayName',
    E.ENDPOINTNAME  AS 'ApplicationRawName',
    (
        CASE WHEN U.CUSTOMPROPERTY54 IS NOT NULL
        OR U.CUSTOMPROPERTY55 IS NOT NULL THEN 'Yes' ELSE 'No' END
    ) AS 'Legal Hold Status',
    A.NAME AS 'ApplicationAccountName',
'' AS 'HasAccess',
    CASE WHEN A.STATUS IS NULL THEN 'NotAvailable' WHEN A.STATUS  = 1 THEN 'Active' ELSE A.STATUS END 'ACCOUNTSTATUS',
'Disable Account on LOA' AS 'MismatchCriteria',
'' AS 'RequestNumber'
FROM USERS  U, ACCOUNTS A, ENDPOINTS E, USER_ACCOUNTS UA
WHERE
U.STATUSKEY = 0
AND U.USERNAME NOT IN ('admin')
AND A.ENDPOINTKEY = E.ENDPOINTKEY 
AND A.STATUS IN (1, 'Manually Provisioned','Active')
AND E.STATUS = 1
AND FIND_IN_SET (E.ENDPOINTKEY, (SELECT GROUP_CONCAT(HRA.SCEHEMANAME) FROM HANARULE HR, HANARULEATTRIBUTE HRA WHERE HR.HANARULEKEY = HRA.HANARULE AND HR.NAME = 'Disable Applications on LoA' AND HRA.OBJECTNAME = 12))
AND UA.USERKEY = U.USERKEY 
AND UA.ACCOUNTKEY = A.ACCOUNTKEY
 
UNION
SELECT DISTINCT
A.ACCOUNTKEY AS 'AccountKey',
U.USERNAME AS 'UserID',
U.CUSTOMPROPERTY1 AS 'LANID',
U.CUSTOMPROPERTY8 AS 'UserJobstatus',
U.CUSTOMPROPERTY29 AS 'LegacyIndicator',
    CONCAT(U.FIRSTNAME, ' ', U.LASTNAME) AS 'UserName',
    CASE WHEN U.STATUSKEY IS NULL THEN 'NotAvailable' WHEN U.STATUSKEY = 0 THEN 'Inactive' ELSE U.STATUSKEY END 'UserStatus',
U.TERMDATE AS 'DateTerm',
U.ENDDATE AS 'DateEnd',
U.CUSTOMPROPERTY34 AS 'DateSaviyntDisabled',
    E.DISPLAYNAME AS 'ApplicationDisplayName',
    E.ENDPOINTNAME  AS 'ApplicationRawName',
    (
        CASE WHEN U.CUSTOMPROPERTY54 IS NOT NULL
        OR U.CUSTOMPROPERTY55 IS NOT NULL THEN 'Yes' ELSE 'No' END
    ) AS 'Legal Hold Status',
    A.NAME AS 'ApplicationAccountName',
'' AS 'HasAccess',
    CASE WHEN A.STATUS IS NULL THEN 'NotAvailable' WHEN A.STATUS  = 1 THEN 'Active' ELSE A.STATUS END 'ACCOUNTSTATUS',
'Disocnnnected apps' AS 'MismatchCriteria',
'' AS 'RequestNumber'
FROM USERS  U, ACCOUNTS A, ENDPOINTS E, USER_ACCOUNTS UA
WHERE
U.STATUSKEY = 0
AND U.USERNAME NOT IN ('admin')
AND A.ENDPOINTKEY = E.ENDPOINTKEY 
AND A.STATUS NOT IN ('SUSPENDED FROM IMPORT SERVICE')
AND E.STATUS = 1
AND FIND_IN_SET (E.ENDPOINTKEY, (SELECT GROUP_CONCAT(HRA.SCEHEMANAME) FROM HANARULE HR, HANARULEATTRIBUTE HRA WHERE HR.HANARULEKEY = HRA.HANARULE
AND ((HR.NAME IN ('Delete Applications after 45 days of Termination', 'Delete Applications on 7th day of Termination') AND HRA.OBJECTNAME = 2) OR (HR.NAME IN ('Disable Applications on Termination','Disable Applications on LoA') AND HRA.OBJECTNAME = 12 ) OR (HR.NAME IN ('Disable Applications on Termination') AND HRA.OBJECTNAME = 2 AND (HRA.OBJECTVALUE LIKE '%#AccountsOnly' OR HRA.OBJECTVALUE LIKE '%#Both')))))= 0
AND UA.USERKEY = U.USERKEY 
AND UA.ACCOUNTKEY = A.ACCOUNTKEY
 
Thank you,
SY
0 Kudos

rushikeshvartak
All-Star
All-Star
In response to shyue

‎03/29/2024 09:55 AM

  • Use limit 1 and save query 
  • after than export report
  • update actual query
  • import report again
  • validate

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.
0 Kudos
Copyright © 2024 Khoros, LLC