Report is not sorting data as per order by clause

[Deepa_Vimal]

Hi, One of our client requires dormant accounts with proper leave type, leave start date and end date details, should come in the end of the report. The above mentioned columns with null values should come first. I have tried with order by clause and it's not sorting data. 

I have shared the Sql query below its not sorting data.

SELECT u.username AS 'Username', u.FIRSTNAME AS 'User First Name', u.LASTNAME AS 'User Last Name', e.DISPLAYNAME AS 'Application Name', CASE WHEN (a.status = 1) THEN 'Active' ELSE a.status END AS 'Account Status', a.name AS 'Account Name',u.CUSTOMPROPERTY9 as 'Leave type',u.CUSTOMPROPERTY10 as 'Leave StartDate',u.CUSTOMPROPERTY11 as 'Leave EndDate' , a.lastlogondate AS 'Last Logon', monthname(a.lastlogondate) as lastlogonmonth FROM users u inner join user_accounts ua on ua.USERKEY= u.USERKEY AND u.statuskey <> 0 inner join accounts a on a.ACCOUNTKEY=ua.ACCOUNTKEY inner join endpoints e on a.ENDPOINTKEY = e.ENDPOINTKEY WHERE a.status IN ('1' , 'Manually Provisioned','Active') AND a.ENDPOINTKEY = 4 AND u.CUSTOMPROPERTY15 >= 90 AND u.Customproperty15 IS Not NULL order by u.customproperty9 is null desc,u.customproperty10 is null desc,u.customproperty11 is null desc

I have breakdown the query to test its sorting the data but if I use username column in the select fields again the result is not sorting as per order by clause. Guide me on the same.

SELECT

   u.CUSTOMPROPERTY9,

    u.CUSTOMPROPERTY10,

    u.customproperty11

FROM users u

INNER JOIN user_accounts ua ON ua.USERKEY = u.USERKEY AND u.statuskey <> 0

INNER JOIN accounts a ON a.ACCOUNTKEY = ua.ACCOUNTKEY

INNER JOIN endpoints e ON a.ENDPOINTKEY = e.ENDPOINTKEY

WHERE

    a.status IN ('1', 'Manually Provisioned', 'Active')

    AND a.ENDPOINTKEY = 4

    AND u.CUSTOMPROPERTY15 >= 90

    AND u.CUSTOMPROPERTY15 IS NOT NULL

ORDER BY u.CUSTOMPROPERTY9 is null desc, u.CUSTOMPROPERTY10 is null desc, u.CUSTOMPROPERTY11 is null desc;

Hi, The following query with order by clause is not executing if we select the report type as SQL query and the report to be sent be sent as Email attachment. If we run the report, getting an alert "the control execution is taking longer to execute, you will be notified when the result is ready". But even after the 24 hours also report is not available. If we check the same query in dataanalyser its fetcing only 15 records. 

The same query without order by clause executing fine and it has been triggered as email attachment after running the analytics.

Help me resolve the issue.

SELECT u.username AS 'Username', u.FIRSTNAME AS 'User First Name', u.LASTNAME AS 'User Last Name', e.DISPLAYNAME AS 'Application Name', CASE WHEN (a.status = 1) THEN 'Active' ELSE a.status END AS 'Account Status', a.name AS 'Account Name',u.CUSTOMPROPERTY9 as 'Leave type',u.CUSTOMPROPERTY10 as 'Leave StartDate',u.CUSTOMPROPERTY11 as 'Leave EndDate' , a.lastlogondate AS 'Last Logon', monthname(a.lastlogondate) as lastlogonmonth FROM users u inner join user_accounts ua on ua.USERKEY= u.USERKEY AND u.statuskey <> 0 inner join accounts a on a.ACCOUNTKEY=ua.ACCOUNTKEY inner join endpoints e on a.ENDPOINTKEY = e.ENDPOINTKEY WHERE a.status IN ('1' , 'Manually Provisioned','Active') AND a.ENDPOINTKEY = 4 AND u.CUSTOMPROPERTY15 >= 90 AND u.Customproperty15 IS Not NULL order by u.customproperty9 is null desc,u.customproperty10 is null desc,u.customproperty11 is null desc

[This message has been edited by moderator to merge reply comment]

[rushikeshvartak]

Does it work from data analyzer ?

[Deepa_Vimal]

Yes its working from data analyser and its sorting data. But in reports its not sorting data, I have checked logs its showing exception  that is ElasticsearchException[Elasticsearch exception [type=illegal_argument_exception, reason=Invalid format: "12-03-2021" is malformed at "21"]];]

But in Saviynt we have configured in global config 'mm-dd-yyyy format only. In cp10,cp11 column we passed values in the same format.

How to handle this in query.

I have tried using all date conversion functions, but none of them working.

[rushikeshvartak]

use below query

SELECT
u.username AS 'Username',
u.FIRSTNAME AS 'User First Name',
u.LASTNAME AS 'User Last Name',
e.DISPLAYNAME AS 'Application Name',
CASE
WHEN (a.status = 1) THEN 'Active'
ELSE a.status
END AS 'Account Status',
a.name AS 'Account Name',
u.CUSTOMPROPERTY9 as 'Leave type',
STR_TO_DATE(u.CUSTOMPROPERTY10, '%d-%m-%Y') as 'Leave StartDate',
STR_TO_DATE(u.CUSTOMPROPERTY11, '%d-%m-%Y') as 'Leave EndDate',
a.lastlogondate AS 'Last Logon',
MONTHNAME(a.lastlogondate) as lastlogonmonth
FROM
users u
INNER JOIN
user_accounts ua
ON ua.USERKEY = u.USERKEY
AND u.statuskey <> 0
INNER JOIN
accounts a
ON a.ACCOUNTKEY = ua.ACCOUNTKEY
INNER JOIN
endpoints e
ON a.ENDPOINTKEY = e.ENDPOINTKEY
WHERE
a.status IN ('1', 'Manually Provisioned', 'Active')
AND a.ENDPOINTKEY = 4
AND u.CUSTOMPROPERTY15 >= 90
AND u.CUSTOMPROPERTY15 IS NOT NULL
ORDER BY
u.CUSTOMPROPERTY9 IS NULL DESC,
STR_TO_DATE(u.CUSTOMPROPERTY10, '%d-%m-%Y') IS NULL DESC,
STR_TO_DATE(u.CUSTOMPROPERTY11, '%d-%m-%Y') IS NULL DESC;

[Deepa_Vimal]

Hi @rushikeshvartak , Thanks for your response. I have created analytics with the query u shared. But if I run the analytics its showing 0 records, but if I dry run its showing 14 records.  

Regards 

Deepa Vimal

[rushikeshvartak]

SELECT
u.username AS 'Username',
u.FIRSTNAME AS 'User First Name',
u.LASTNAME AS 'User Last Name',
e.DISPLAYNAME AS 'Application Name',
CASE
WHEN (a.status = 1) THEN 'Active'
ELSE a.status
END AS 'Account State',
a.name AS 'Account Name',
u.CUSTOMPROPERTY9 as 'Leave type',
STR_TO_DATE(u.CUSTOMPROPERTY10, '%d-%m-%Y') as 'Leave StartDat',
STR_TO_DATE(u.CUSTOMPROPERTY11, '%d-%m-%Y') as 'Leave EndDat',
a.lastlogondate AS 'Last Logon',
MONTHNAME(a.lastlogondate) as lastlogonmonth
FROM
users u
INNER JOIN
user_accounts ua
ON ua.USERKEY = u.USERKEY
AND u.statuskey <> 0
INNER JOIN
accounts a
ON a.ACCOUNTKEY = ua.ACCOUNTKEY
INNER JOIN
endpoints e
ON a.ENDPOINTKEY = e.ENDPOINTKEY
WHERE
a.status IN ('1', 'Manually Provisioned', 'Active')
AND a.ENDPOINTKEY = 4
AND u.CUSTOMPROPERTY15 >= 90
AND u.CUSTOMPROPERTY15 IS NOT NULL
ORDER BY
u.CUSTOMPROPERTY9 IS NULL DESC,
STR_TO_DATE(u.CUSTOMPROPERTY10, '%d-%m-%Y') IS NULL DESC,
STR_TO_DATE(u.CUSTOMPROPERTY11, '%d-%m-%Y') IS NULL DESC;

[Deepa_Vimal]

Hi @rushikeshvartak, I have executed the query you shared, but that also is also giving the same response-if I click run now its showing 0 records. I tried to sort the columns which does not have date values, I have excluded the date related columns from select statement and the result is same, query is given below. if I click run now 0 records and in dry run 14 records. But the query works fine in data analyser and runtime analytics. But if I create report in using SQL query option, I am getting above mentioned issues. Order by clause is not working on any of the columns (data type or varchar type). 

Query1

SELECT u.username AS 'Username', u.FIRSTNAME AS 'User First Name', u.LASTNAME AS 'User Last Name', e.DISPLAYNAME AS 'Application Name', CASE WHEN (a.status = 1) THEN 'Active' ELSE a.status END AS 'Account State', a.name AS 'Account Name', u.CUSTOMPROPERTY9 as 'Leave type' FROM users u INNER JOIN user_accounts ua ON ua.USERKEY = u.USERKEY AND u.statuskey <> 0 INNER JOIN accounts a ON a.ACCOUNTKEY = ua.ACCOUNTKEY INNER JOIN endpoints e ON a.ENDPOINTKEY = e.ENDPOINTKEY WHERE a.status IN ('1', 'Manually Provisioned', 'Active') AND a.ENDPOINTKEY = 4 AND u.CUSTOMPROPERTY15 >= 90 AND u.CUSTOMPROPERTY15 IS NOT NULL ORDER BY u.CUSTOMPROPERTY9 IS NULL DESC  

[rushikeshvartak]

Share logs

[Deepa_Vimal]

Hi @rushikeshvartak ,

Here are the logs, in logs you can find total records 14, but 0 records as result. 

[rushikeshvartak]

Share logs in txt file