Issue With Running New Analytics

[Ryne_G]

I am currently trying to run an analytics search that will produce any user with some user information and their manager’s email when the user contract expiration date is within a certain time. The query I built works successfully in Data Analyzer, but when I try to run in Analytics I get the following message:

Saviynt Security Manager

Alert: Control execution is taking longer than expected. You will be notified when the result is ready

I reviewed the logs and I see this repeating:

2023-10-12T15:06:26-04:00-ecm-worker--null-tzfds--[4]: index [osaic60daycwcontractexpiration], type [analytics], id [47tIJYsBA3EUfIzEbmY7], message [ElasticsearchException[Elasticsearch exception [type=mapper_parsing_exception, reason=failed to parse field [Contract End Date] of type [date] in document with id '47tIJYsBA3EUfIzEbmY7']]; nested: ElasticsearchException[Elasticsearch exception [type=illegal_argument_exception, reason=Invalid format: "2023-10-20" is too short]];]

Below is the query I am using. (Please note that right now the query has datediff set to = 3 just because I am trying to successfully pull one user for testing purposes). This is currently in Dev 23.10.

Select u.username,

       u.firstname AS 'User First Name',

                   u.lastname AS 'User Last Name',

                   u.employeetype AS 'Employee Type',

                   u.email,

                   u.customproperty41 AS 'Contract End Date',

                   m.firstname AS 'Manager First Name',

                   m.lastname AS 'Manager Last Name',

                   m.email AS 'Manager Email'

FROM users u inner join users m

       ON m.userkey = u.manager

WHERE u.employeetype = 'Contingent Worker' AND u.statuskey = ‘1’ AND DATEDIFF(u.customproperty41,Curdate()) = 3

[Saathvik · Accepted solution]

@Ryne_G Try below, generally column name that ends with Date will give error

Select u.username,
u.firstname AS 'User First Name',
u.lastname AS 'User Last Name',
u.employeetype AS 'Employee Type',
u.email,
u.customproperty41 AS 'End Date of Contractor',
m.firstname AS 'Manager First Name',
m.lastname AS 'Manager Last Name',
m.email AS 'Manager Email'
FROM users u
inner join users m ON m.userkey = u.manager
WHERE u.employeetype = 'Contingent Worker'
AND u.statuskey = 1
AND DATEDIFF(u.customproperty41, Curdate()) = 3

[Ryne_G]

@Saathvik Thank you very much. That was the issue. Have accepted your response as the solution.